1010data VBA SDK
 All Data Structures Files Functions Variables
examples/vbasdk1010_examples.bas
Attribute VB_Name = "VBASDK1010_Examples"
Global ttsid As Long
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Const ENABLE_LOG As Boolean = False ' Write the xml transactions to log file
Const LOGIN_THREAD As Boolean = True ' Use the threaded login
Const USERNAME = "1010DATA_USERID" ' Replace with user specific info
Const PASSWORD = "1010DATA_PASSWORD" ' Replace with user specific info
Sub Run_All_Examples()
StartSession
If ttsid <> 0 Then
Example_Query_Weather_Stations
Example_Threaded_Functions
Example_Query_Get_Odd_Columns
Example_Get_Table_Info
Example_Get_Directory_Info
Example_Threaded_Get_Info
Example_Save_Table
Example_Save_to_FTP
Example_Merge_Tables
EndSession
End If
End Sub
Sub StartSession()
ChDir ThisWorkbook.Path ' Need to change directory to find the lib1010vbasdk32.dll that comes
' with this examples workbook.
Do While ThisWorkbook.Sheets.count < 3
Worksheets.Add
Loop
' check if the session is already started
If ttsid Then
Exit Sub
End If
Debug.Print "LOCATION: " & CurDir
Dim rc As Long, msg As String
Debug.Print "VERSION: " & TentenSDKVersion()
ttsid = TentenNewHandle()
Const url As String = "https://www2.1010data.com/cgi-bin/gw.k"
' Shows how to use the two different Login functions
If LOGIN_THREAD Then
rc = TentenLoginThread(ttsid, url, USERNAME, PASSWORD, TENTEN_POSSESS)
rc = Wait_For_Thread
Else
' this one will block until the login completes - may cause Excel to spin and look Not Responding
rc = TentenLogin(ttsid, url, USERNAME, PASSWORD, TENTEN_POSSESS)
End If
TentenMsg ttsid, msg
Debug.Print "Login(" & rc & "):" & msg
If rc <> 0 Then ' failed to login
ttsid = 0
Else
If ENABLE_LOG Then
rc = TentenEnableLog(ttsid, "vbasdk1010_examples_logfile.txt", False)
TentenMsg ttsid, msg
Debug.Print "EnableLog(" & rc & "): " & msg
End If
End If
End Sub
''' Sleeps until the SDK Thread is complete and returns the return code of the threaded call
''' Developers could use the time between calls to TentenCheckThread to do something
''' more interesting than just sleeping.
Function Wait_For_Thread() As Long
Dim rc As Long, threadcheck As Long
Do
DoEvents ' passes control to OS to allow Excel to be responsive
Sleep 100 ' sleep for 100 milliseconds
threadcheck = TentenCheckThread(ttsid, rc)
Loop While threadcheck <> 0 And rc = TENTEN_THREAD_STILL_ACTIVE
Wait_For_Thread = rc
End Function
Sub EndSession()
Dim rc As Long, msg As String
If ttsid Then
rc = TentenLogout(ttsid)
TentenMsg ttsid, msg
Debug.Print "Logout(" & rc & "):" & msg
End If
ttsid = 0
End Sub
''' This example shows the most basic Query + GetData transactions.
''' It opens a small table and downloads all the columns from the table.
''' Then puts the data into the Excel worksheet.
Sub Example_Query_Weather_Stations()
StartSession
Dim rc As Long, msg As String, qid As Long
TentenSetTable qid, "pub.demo.weather.stations"
TentenSetOps qid, ""
rc = TentenExecuteQuery(ttsid, qid)
TentenMsg ttsid, msg
Debug.Print "Query(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Dim nrows As Double, rowsgotten As Long
nrows = TentenNumRows(qid)
Debug.Print " NROWS: " & nrows
rc = TentenGetRows(ttsid, qid, True, TENTEN_ALL_COLUMNS, 1, nrows, rowsgotten)
TentenMsg ttsid, msg
Debug.Print "GetData(" & rc & "):" & msg
If rc <> 0 Then
Exit Sub
End If
Populate_Excel_With_Data qid, 1
End Sub
''' Helper routine to put a queries downloaded data into the Worksheet
Sub Populate_Excel_With_Data(ByVal qid As Long, ByVal index as long)
Dim sheet as Worksheet
Set sheet = ThisWorkbook.Worksheets(index)
sheet.Cells.Clear
Dim colinfo() As TentenColumn
ReDim colinfo(0 To 0) ' need to pre-dim the array for the library call to work
rc = TentenGetColumnInfo(qid, colinfo)
If rc <> 0 Then
Exit Sub
End If
Dim idx As Long, offset As Long, data As Variant, rowsgotten As Long
offset = 0
For idx = LBound(colinfo) To UBound(colinfo)
data = Null
rowsgotten = TentenNumRowsGotten(qid, colinfo(idx).name)
If rowsgotten > 0 Then
rc = TentenGetColumnData(qid, colinfo(idx).name, data, TENTEN_NO_FLAGS)
sheet.Range("A1").offset(0, offset).Value = colinfo(idx).title
sheet.Range("A2").offset(0, offset).Value = colinfo(idx).name
sheet.Range("A3").offset(0, offset).Resize(rowsgotten, 1).Value = data
offset = offset + 1
End If
Next idx
End Sub
''' Example to show how to call the Tenten*Thread functions and check when they are done.
''' These threading functions execute the transaction in a separate thread which allows the
''' vb script to continue doing work. Here, we just sleep to allow control to revert to Excel.
''' You must use TentenCheckThread to find out when that transaction is done executing, but you
''' could do more than just loop and sleep in the mean time between periodic TentenCheckThread
''' calls. You still are limited to executing one transaction per session id at a time, but you
''' could perform other non-1010 work while waiting for the thread to complete.
Sub Example_Threaded_Functions()
StartSession
Dim rc As Long, msg As String, qid As Long
TentenSetTable qid, "pub.demo.weather.pwcodes"
TentenSetOps qid, ""
rc = TentenExecuteQueryThread(ttsid, qid) ' Execute the query transaction in another thread
Debug.Print "Starting Threaded Query(" & rc & ")"
If rc <> 0 Then
Exit Sub
End If
rc = Wait_For_Thread()
TentenMsg ttsid, msg
Debug.Print "Query(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Dim nrows As Double
nrows = TentenNumRows(qid)
Debug.Print " NROWS: " & nrows
rc = TentenGetRowsThread(ttsid, qid, True, TENTEN_ALL_COLUMNS, 1, nrows)
Debug.Print "Starting Threaded GetData(" & rc & ")"
rc = Wait_For_Thread()
TentenMsg ttsid, msg
Debug.Print "GetData(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Populate_Excel_With_Data qid, 2
End Sub
''' Example to run a query and selectively download the data
''' This gets the odd numbered columns of the top 10% of records
''' by elevation of the weather stations data
Sub Example_Query_Get_Odd_Columns()
StartSession
Dim rc As Long, msg As String, qid As Long
TentenSetTable qid, "pub.demo.weather.stations"
TentenSetOps qid, "<sort col=""elev"" dir=""down""/>" ' sort by elevation
rc = TentenExecuteQuery(ttsid, qid)
TentenMsg ttsid, msg
Debug.Print "Query(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Dim nrows As Double, nrowstoget As Long, rowsgotten As Long, ncols As Long, ncols_odd As Long
nrows = TentenNumRows(qid)
nrowstoget = nrows \ 10 ' only get the top 10 % of records
Debug.Print " NROWS: " & nrowstoget
ncols = TentenNumCols(qid)
ncols_odd = ((ncols + (ncols Mod 2)) \ 2)
Debug.Print " NCOLS: " & ncols_odd
Dim colinfo() As TentenColumn
ReDim colinfo(1 To 1) ' need to pre-dim the array for the library call to work
rc = TentenGetColumnInfo(qid, colinfo)
Dim idx As Long, count As Long, odd As Boolean, colnames() As String
ReDim colnames(1 To ncols_odd)
count = 1
odd = True
For idx = LBound(colinfo) To UBound(colinfo)
If odd Then
colnames(count) = colinfo(idx).name
count = count + 1
End If
odd = Not odd
Next idx
rc = TentenGetRows(ttsid, qid, False, colnames, 1, nrowstoget, rowsgotten) ' False means don't compress data over the wire.
' There is some time overhead of compressing/decompressing data. For really small get data transactions
' the compressed data size likely isn't much smaller than the uncompressed data, so the savings in transfer times
' might not be more than the overhead time of compressing/decompressing the data.
TentenMsg ttsid, msg
Debug.Print "GetData(" & rc & "):" & msg
If rc <> 0 Then
Exit Sub
End If
Populate_Excel_With_Data qid, 3
End Sub
''' Helper routine to run a query with a select
Sub Select_On_Weather_Stations(ByVal selector As String)
StartSession
Dim rc As Long, msg As String, qid As Long
TentenSetTable qid, "pub.demo.weather.stations"
TentenSetOps qid, "<sel value=""" & selector & """/>"
rc = TentenExecuteQuery(ttsid, qid)
TentenMsg ttsid, msg
Debug.Print "Query [SEL * FROM pub.demo.weather.stations WHERE " & selector & "] (" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
End Sub
''' Example showing how to save a query result set into a new table on 1010data.
Sub Example_Save_Table()
StartSession
Dim rc As Long, msg As String
Select_On_Weather_Stations ("tz=-5") ' Select eastern time zone
rc = TentenRC(ttsid)
If rc <> 0 Then
Exit Sub
End If
Dim tabinfo As TentenTabDir
tabinfo.name = "PATH.TO.LOCATION.ON.1010DATA.WHERE.YOU.HAVE.WRITE.PERMISSION" ' Replace with user specific info
tabinfo.title = "Stations - EST"
tabinfo.sdesc = "Weather Stations in the Eastern Time Zone"
tabinfo.ldesc = "Subset of stations in Eastern time zone from the table pub.demo.weather.stations"
tabinfo.maxdown = TENTEN_NO_MAX
rc = TentenSaveTable(ttsid, tabinfo, True, False, True, False, TENTEN_PRIVATE)
TentenMsg ttsid, msg
Debug.Print "SaveTable(" & rc & "): " & msg
End Sub
''' Example showing how to save query result sets to you 1010data FTP folder as comma separated list
''' with one row per line.
Sub Example_Save_to_FTP()
StartSession
Dim rc As Long, msg As String
Select_On_Weather_Stations ("tz=-6") ' Select central time zone
rc = TentenRC(ttsid)
If rc <> 0 Then
Exit Sub
End If
rc = TentenSaveToFTP(ttsid, "central_stations", True, True, ",", vbCrLf)
TentenMsg ttsid, msg
Debug.Print "SaveToFTP(" & rc & "): " & msg
End Sub
''' Prints out the columns in a TentenColumn array
Sub Print_Column_Names(cols() As TentenColumn)
Dim idx As Long
Dim text As String
text = " COLUMNS: | "
For idx = LBound(cols) To UBound(cols)
text = text & cols(idx).name & " | "
Next idx
Debug.Print text
End Sub
''' Prints out the info about a table or directory
Sub Print_Info(tabinfo As TentenTabDir)
Dim text As String
If tabinfo.istable Then
text = " TAB: "
Else
text = " DIR: "
End If
text = text & tabinfo.name & " | " & tabinfo.title
If tabinfo.rows Then
text = text & " | " & tabinfo.rows & " rows | " & tabinfo.bytes & " bytes "
End If
Debug.Print text
End Sub
''' Example that uses the two functions that can get info about a table
Sub Example_Get_Table_Info()
StartSession
Dim rc As Long, msg As String, cols() As TentenColumn, tabinfo As TentenTabDir
ReDim cols(0 To 0)
' GetTableInfo gets basic table info as well as column info for each column
rc = TentenGetTableInfo(ttsid, "pub.demo.weather.stations", tabinfo, cols)
TentenMsg ttsid, msg
Debug.Print "GetTableInfo(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Print_Info tabinfo
Print_Column_Names cols
' GetTableMetaInfo gets more info about the table than GetTableInfo, such as #rows, #bytes, #segs
rc = TentenGetTableMetaInfo(ttsid, "pub.demo.weather.pwcodes", tabinfo)
TentenMsg ttsid, msg
Debug.Print "GetTableMetaInfo(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Print_Info tabinfo
End Sub
''' Example that gets info about a directory, its parent directories and its children
''' tables/directories.
Sub Example_Get_Directory_Info()
StartSession
Dim rc As Long, msg As String, dir As TentenTabDir, parents() As TentenTabDir, children() As TentenTabDir
ReDim parents(0 To 0) ' Can't be undimensioned before the function call
ReDim children(0 To 0) ' Same as above
rc = TentenGetDirectory(ttsid, "pub.demo.weather", False, dir, parents, children)
TentenMsg ttsid, msg
Debug.Print "GetDirectory(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Print_Info dir
Dim idx As Long
Debug.Print "--- PARENTS ---"
For idx = LBound(parents) To UBound(parents)
Print_Info parents(idx)
Next idx
Debug.Print "--- CHILDREN ---"
For idx = LBound(children) To UBound(children)
Print_Info children(idx)
Next idx
End Sub
''' Example that gets info about 1010data object using the *Thread functions
Sub Example_Threaded_Get_Info()
StartSession
Dim rc As Long, msg As String, cols() As TentenColumn, tabinfo As TentenTabDir
ReDim cols(0 To 0)
' GetTableInfo gets basic table info as well as column info for each column
rc = TentenGetTableInfoThread(ttsid, "pub.demo.weather.stations")
Debug.Print "Starting Threaded - GetTableInfo(" & rc & ")"
If rc <> 0 Then
Exit Sub
End If
rc = Wait_For_Thread()
' Use non-threaded version with TENTEN_THREAD_RESULTS constant to retrieve the data from the threaded call
rc = TentenGetTableInfo(ttsid, TENTEN_THREAD_RESULTS, tabinfo, cols)
TentenMsg ttsid, msg
Debug.Print "GetTableInfo(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Print_Info tabinfo
Print_Column_Names cols
' ---------------------------------------------------------------
' GetTableMetaInfo gets more info about the table than GetTableInfo, such as #rows, #bytes, #segs
rc = TentenGetTableMetaInfoThread(ttsid, "pub.demo.weather.pwcodes")
Debug.Print "Starting Threaded - GetTableMetaInfo(" & rc & ")"
If rc <> 0 Then
Exit Sub
End If
rc = Wait_For_Thread()
' Use non-threaded version with TENTEN_THREAD_RESULTS constant to retrieve the data from the threaded call
rc = TentenGetTableMetaInfo(ttsid, TENTEN_THREAD_RESULTS, tabinfo)
TentenMsg ttsid, msg
Debug.Print "GetTableMetaInfo(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Print_Info tabinfo
' ---------------------------------------------------------------
Dim dir As TentenTabDir, parents() As TentenTabDir, children() As TentenTabDir
ReDim parents(0 To 0) ' Can't be undimensioned before the function call
ReDim children(0 To 0) ' Same as above
' Get Directory + descendants
rc = TentenGetDirectoryThread(ttsid, "pub.demo", True)
Debug.Print "Starting Threaded - GetDirectory(" & rc & ")"
If rc <> 0 Then
Exit Sub
End If
rc = Wait_For_Thread()
' Use non-threaded version with TENTEN_THREAD_RESULTS constant to retrieve the data from the threaded call
rc = TentenGetDirectory(ttsid, TENTEN_THREAD_RESULTS, True, dir, parents, children)
TentenMsg ttsid, msg
Debug.Print "GetDirectory(" & rc & "): " & msg
If rc <> 0 Then
Exit Sub
End If
Print_Info dir
Dim idx As Long
Debug.Print "--- PARENTS ---"
For idx = LBound(parents) To UBound(parents)
Print_Info parents(idx)
Next idx
Debug.Print "--- CHILDREN ---"
For idx = LBound(children) To UBound(children)
Print_Info children(idx)
Next idx
End Sub
''' Example on how to merge a several tables into one
Sub Example_Merge_Tables()
StartSession
Dim rc As Long, msg As String, tablenames(0 To 5) As String
tablenames(0) = "pub.demo.weather.hourly90"
tablenames(1) = "pub.demo.weather.hourly91"
tablenames(2) = "pub.demo.weather.hourly92"
tablenames(3) = "pub.demo.weather.hourly93"
tablenames(4) = "pub.demo.weather.hourly94"
tablenames(5) = "pub.demo.weather.hourly95"
Dim tabinfo As TentenTabDir
tabinfo.name = "PATH.TO.LOCATION.ON.1010DATA.WHERE.YOU.HAVE.WRITE.PERMISSION" ' Replace with user specific info
tabinfo.title = "Hourly Weather"
tabinfo.sdesc = "1990's Hourly Weather Data"
tabinfo.ldesc = "Merged table from all the pub.demo.weather.hourly9X tables"
tabinfo.maxdown = TENTEN_NO_MAX
rc = TentenMergeTables(ttsid, tablenames, tabinfo, True, TENTEN_PRIVATE)
TentenMsg ttsid, msg
Debug.Print "MergeTables(" & rc & "): " & msg
End Sub