Attached you will find some example code extending the ADO example.
It Consists of:
SQLTableReader.vbs: vbs utility to read content of sql tables
SQLBrowser.SUD: SUD dialog showing the content of the SQL database (tables, columns) loading channels tables when you double click.
SQLBrowser.vbs: Showing the above dialog
This is just a start for an example but it might help.
I attach the Code of the Table reader for direct view.
How to use it:
Option Explicit
scriptinclude currentScriptPath & "SQLTableReader.vbs"
AdoConStrGet : dim connStr : connStr = ADOConString
data.Root.clear
dim tr : set tr = new SQLTableReader.Init(connStr)
dim schema : set schema = tr.schema()
dim tableName : for each tableName in schema.keys
dim grpO : set grpO = data.Root.ChannelGroups.Add(tableName)
dim columnDict : set columnDict = schema.item(tableName)
dim columnName : for each columnName in columnDict.keys
dim chO : set chO = grpO.Channels.Add(columnName,columnDict.item(columnName))
dim columnVals : columnVals = tr.GetColumnValues(tableName, columnName)
if not isempty(columnVals) then
call ArrayToChannels(columnVals,Array(chO.GetReference(eRefTypeIndexIndex)),True)
end if
Next
Next
SQLTableReader.vbs:
Option Explicit
class SQLTableReader
function Init(byVal adoConnectionString)
set Init = Me
set conn_ = CreateObject("ADODB.Connection")
conn_.Mode = 1
conn_.Open adoConnectionString
conn_.CursorLocation = 3
end function
function Schema()
Set schema_ = CreateObject("Scripting.Dictionary")
Dim TablesSchema : Set TablesSchema = conn_.OpenSchema(20)
Do While Not TablesSchema.EOF
dim tableName : tableName = TablesSchema("TABLE_NAME")
dim tableType : tableType = TablesSchema("TABLE_TYPE")
' Add if this is no SYSTEM or ACCESS table
if (0 = InStr(1, tableType, "SYSTEM", 1)) AND (0 = InStr(1, tableType, "ACCESS", 1)) then
dim tableDict : set tableDict = CreateObject("Scripting.Dictionary")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Get all columns of the current table
Dim ColumnsSchema : Set ColumnsSchema = conn_.OpenSchema(4, Array(Empty, Empty, tableName))
ColumnsSchema.Sort = "ORDINAL_POSITION ASC"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add all channels
Do While Not ColumnsSchema.EOF
dim columnName : columnName = ColumnsSchema("COLUMN_NAME")
dim columnDataType: columnDataType = GetDataType__(ColumnsSchema("DATA_TYPE"))
if NOT isempty(columnDataType) then ' If valid data type import it
call tableDict.Add(columnName, columnDataType)
end if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Next column
ColumnsSchema.MoveNext
Loop
call schema_.Add(tableName, tableDict)
end if
' Next table
TablesSchema.MoveNext
Loop
set Schema = schema_
end function
function GetColumnValues(byVal tableName, byVal columnName)
dim sqlSelect : sqlSelect = GetColumnSelect_(tableName, columnName)
dim getResult : set getResult = conn_.Execute(sqlSelect)
if NOT (getResult.BOF or getResult.EOF) then
GetColumnValues = getResult.GetRows(-1, 0, 0)
else
GetColumnValues = empty
end if
end function
private function GetColumnSelect_(byVal tableName, byVal columnName)
dim tableNameT : tableNameT = sqlStringSeperator_ + tableName + sqlStringSeperator_
dim columnNameT : columnNameT = sqlStringSeperator_ + columnName + sqlStringSeperator_
dim rv : rv = sqlGetStatement_
rv = replace(rv, "@@TABLENAME@@", tableNameT)
rv = replace(rv, "@@COLUMNNAME@@", columnNameT)
GetColumnSelect_ = rv
end function
Private Sub Class_Initialize()
sqlGetStatement_ = "SELECT @@COLUMNNAME@@ FROM @@TABLENAME@@"
sqlTerminator_ = ";"
sqlStringSeperator_ = "" ' e.g. " or `
set conn_ = nothing
set schema_ = nothing
End Sub
Private Sub Class_Terminate()
set conn_ = nothing
End Sub
private sqlGetStatement_
private sqlTerminator_
private sqlStringSeperator_
private conn_
private schema_
end class
Function GetDataType__(adoDataType)
if NOT isNumeric(cInt(adoDataType)) then
GetDataType__ = empty
exit Function
end if
const adBigInt = 20 ' Indicates an eight-byte signed integer (DBTYPE_I8).
const adBinary = 128 ' Indicates a binary value (DBTYPE_BYTES).
const adBoolean = 11 ' Indicates a boolean value (DBTYPE_BOOL).
const adBSTR = 8 ' Indicates a null-terminated character string (Unicode) (DBTYPE_BSTR).
const adChapter = 136 ' Indicates a four-byte chapter value that identifies rows in a child rowset (DBTYPE_HCHAPTER).
const adChar = 129 ' Indicates a string value (DBTYPE_STR).
const adCurrency = 6 ' Indicates a currency value (DBTYPE_CY). Currency is a fixed-point number with four digits to the right of the decimal point. It is stored in an eight-byte signed integer scaled by 10,000.
const adDate = 7 ' Indicates a date value (DBTYPE_DATE). A date is stored as a double, the whole part of which is the number of days since December 30, 1899, and the fractional part of which is the fraction of a day.
const adDBDate = 133 ' Indicates a date value (yyyymmdd) (DBTYPE_DBDATE).
const adDBTime = 134 ' Indicates a time value (hhmmss) (DBTYPE_DBTIME).
const adDBTimeStamp = 135 ' Indicates a date/time stamp (yyyymmddhhmmss plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).
const adDecimal = 14 ' Indicates an exact numeric value with a fixed precision and scale (DBTYPE_DECIMAL).
const adDouble = 5 ' Indicates a double-precision floating-point value (DBTYPE_R8).
const adEmpty = 0 ' Specifies no value (DBTYPE_EMPTY).
const adError = 10 ' Indicates a 32-bit error code (DBTYPE_ERROR).
const adFileTime = 64 ' Indicates a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (DBTYPE_FILeTime).
const adGUID = 72 ' Indicates a globally unique identifier (GUID) (DBTYPE_GUID).
const adIDispatch = 9 ' Indicates a pointer to an IDispatch interface on a COM object (DBTYPE_IDISPATCH).
const adInteger = 3 ' Indicates a four-byte signed integer (DBTYPE_I4).
const adIUnknown = 13 ' Indicates a pointer to an IUnknown interface on a COM object (DBTYPE_IUNKNOWN).
const adLongVarBinary = 205 ' Indicates a long binary value (Parameter object only).
const adLongVarChar = 201 ' Indicates a long string value (Parameter object only).
const adLongVarWChar = 203 ' Indicates a long null-terminated Unicode string value (Parameter object only).
const adNumeric = 131 ' Indicates an exact numeric value with a fixed precision and scale (DBTYPE_NUMERIC).
const adPropVariant = 138 ' Indicates an Automation PROPVARIANT (DBTYPE_PROP_VARIANT).
const adSingle = 4 ' Indicates a single-precision floating-point value (DBTYPE_R4).
const adSmallInt = 2 ' Indicates a two-byte signed integer (DBTYPE_I2).
const adTinyInt = 16 ' Indicates a one-byte signed integer (DBTYPE_I1).
const adUnsignedBigInt = 21 ' Indicates an eight-byte unsigned integer (DBTYPE_UI8).
const adUnsignedInt = 19 ' Indicates a four-byte unsigned integer (DBTYPE_UI4).
const adUnsignedSmallInt = 18 ' Indicates a two-byte unsigned integer (DBTYPE_UI2).
const adUnsignedTinyInt = 17 ' Indicates a one-byte unsigned integer (DBTYPE_UI1).
const adUserDefined = 132 ' Indicates a user-defined variable (DBTYPE_UDT).
const adVarBinary = 204 ' Indicates a binary value (Parameter object only).
const adVarChar = 200 ' Indicates a string value (Parameter object only).
const adVariant = 12 ' Indicates an Automation Variant (DBTYPE_VARIANT).
const adVarNumeric = 139 ' Indicates a numeric value (Parameter object only).
const adVarWChar = 202 ' Indicates a null-terminated Unicode character string (Parameter object only).
const adWChar = 130 ' Indicates a null-terminated Unicode character string (DBTYPE_WSTR).
Select Case cInt(adoDataType)
case adBigInt GetDataType__ = DataTypeChnFloat64
case adBoolean GetDataType__ = DataTypeChnFloat64
case adBSTR GetDataType__ = DataTypeChnString
case adChar GetDataType__ = DataTypeChnFloat64
case adCurrency GetDataType__ = DataTypeChnFloat64
case adDate GetDataType__ = DataTypeChnDate
case adDBDate GetDataType__ = DataTypeChnDate
case adDBTime GetDataType__ = DataTypeChnDate
case adDBTimeStamp GetDataType__ = DataTypeChnDate
case adDecimal GetDataType__ = DataTypeChnFloat64
case adDouble GetDataType__ = DataTypeChnFloat64
case adFileTime GetDataType__ = DataTypeChnFloat64
case adInteger GetDataType__ = DataTypeChnFloat64
case adNumeric GetDataType__ = DataTypeChnFloat64
case adSingle GetDataType__ = DataTypeChnFloat64
case adSmallInt GetDataType__ = DataTypeChnFloat64
case adTinyInt GetDataType__ = DataTypeChnFloat64
case adUnsignedBigInt GetDataType__ = DataTypeChnFloat64
case adUnsignedInt GetDataType__ = DataTypeChnFloat64
case adUnsignedSmallInt GetDataType__ = DataTypeChnFloat64
case adUnsignedTinyInt GetDataType__ = DataTypeChnFloat64
case adVarChar GetDataType__ = DataTypeChnFloat64
case adVariant GetDataType__ = DataTypeChnFloat64
case adVarNumeric GetDataType__ = DataTypeChnFloat64
case adVarWChar GetDataType__ = DataTypeChnString
case adWChar GetDataType__ = DataTypeChnString
Case Else GetDataType__ = empty
End Select
End Function
