How to communicate with SQL database from drop down selection on the GUI in DIAdem

[Kandukuri_raghavendra]

Hi,

I am completely new to DIAdem and VB, i was wondering if there is any way to interact with the SQL database on drop down selection from GUI or .SUD file. Any explanation is highly appreciated.

Thanks in Advance,

Raghavendra

[DiegoH214]

Hello Raghavendra,

I think a good starting point for you will be the ADO Database Application Example found in DIAdem. Help>>Examples from the Menu

Diego H

National Instruments

[Kandukuri_raghavendra]

Hi, 
yes it helped me in understanding the structure.

Call Globaldim("NEWT") ' name of the variable i am getting from the dropdown
dim myConn
dim myCommand
dim RS

Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=name of the server;Initial Catalog=name of the database;user id ='name of the user';password='password'"
Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn
myCommand.CommandText = "your Querry"
Set RS = myCommand.Execute
MsgBox RS.GetString ' returns the message box with the result, just to cross check if its working or not
myConn.Close

[AndreasK]

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

[Pesmith8]

Andreas,

Nice Software!

Paul

ps. from really hot and humid Ohio.