Examples: FieldExpectedDataType method

1. This agent sets the expected data type for fields 2, 3, and 4 to DB_LONG and checks the return value to make sure that the field is convertible to that data type.

   Uselsx "*LSXODBC"
   Sub Initialize
     Dim con As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim result As New ODBCResultSet
     Dim firstName As String
     Dim lastName As String
     Dim msg As String
     Set qry.Connection = con
     Set result.Query = qry
     con.ConnectTo("ATDB")
     qry.SQL = "SELECT * FROM GETSCALE"
     result.Execute
     If result.FieldExpectedDataType(2, _
     DB_LONG) <> DB_LONG Then
       Messagebox _
       "Data type wrong for field 2",, "Bad data type"
       Exit Sub
     End If
     If result.FieldExpectedDataType(3, DB_LONG) <> DB_LONG Then
       Messagebox "Data type wrong for field 2",, _
       "Bad data type"
       Exit Sub
     End If
     If result.FieldExpectedDataType(4, DB_LONG) <> DB_LONG Then
       Messagebox "Data type wrong for field 2",, _
       "Bad data type"
       Exit Sub
     End If
     msg = "Getscale data:" & Chr(10)
     Do
       result.NextRow
       msg = msg & Chr(10)
       For i = 1 To result.NumColumns
         msg = msg & "  " & result.GetValue(i)
       Next
     Loop Until result.IsEndOfData
     Messagebox msg,, "Getscale Data"
     con.Disconnect
   End Sub

2. This agent executes an SQL query; sets the expected data type of each field in the result set; and displays the field ID, field name, and expected data type. The expected data type is set by interpolating from the native data type.

Uselsx "*LSXODBC"
Sub Initialize
  Dim con As New ODBCConnection
  Dim qry As New ODBCQuery
  Dim result As New ODBCResultSet
  Dim msg As String
  Set qry.Connection = con
  Set result.Query = qry
  con.ConnectTo("ATDB")
  qry.SQL = "SELECT * FROM STUDENTS"
  result.Execute
  msg = "Fields in STUDENTS table:" & Chr(10)
  For i = 1 To result.NumColumns
    Call result.FieldExpectedDataType(i, _
    SetDataType(result.FieldNativeDataType(i)))
    msg = msg & Chr(10) & i & " " & _
    result.FieldName(i) _
    & " " & _
    GetDataType(result.FieldExpectedDataType(i))
  Next
  Messagebox msg,, "Field names and expected data types"
  result.Close(DB_CLOSE)
  con.Disconnect
End Sub

Function GetDataType(typeConst As Integer) As String
  Select Case typeConst
  Case DB_TYPE_UNDEFINED : GetDataType = "UNDEFINED"
  Case DB_CHAR : GetDataType = "CHAR"
  Case DB_SHORT : GetDataType = "DECIMAL"
  Case DB_LONG : GetDataType = "SHORT"
  Case DB_DOUBLE : GetDataType = "DOUBLE"
  Case DB_DATE : GetDataType = "DATE"
  Case DB_TIME : GetDataType = "TIME"
  Case DB_BINARY : GetDataType = "BINARY"
  Case DB_BOOL : GetDataType = "BOOL"
  Case DB_DATETIME : GetDataType = "DATETIME"
  End Select
End Function

Function SetDataType(typeConst As Integer) As Integer
  Select Case typeConst
  Case SQL_CHAR : SetDataType = DB_CHAR
  Case SQL_NUMERIC : SetDataType = DB_TYPE_UNDEFINED
  Case SQL_DECIMAL : SetDataType = DB_TYPE_UNDEFINED
  Case SQL_INTEGER : SetDataType = DB_SHORT
  Case SQL_SMALLINT : SetDataType = DB_SHORT
  Case SQL_FLOAT : SetDataType = DB_DOUBLE
  Case SQL_REAL : SetDataType = DB_DOUBLE
  Case SQL_DOUBLE : SetDataType = DB_DOUBLE
  Case SQL_DATE : SetDataType = DB_DATE
  Case SQL_TIME : SetDataType = DB_TIME
  Case SQL_TIMESTAMP : SetDataType = DB_DATETIME
  Case SQL_VARCHAR : SetDataType = DB_CHAR
  Case SQL_BINARY : SetDataType = DB_BINARY
  Case SQL_VARBINARY : SetDataType = DB_BINARY
  Case SQL_LONGVARCHAR : SetDataType = DB_CHAR
  Case SQL_LONGVARBINARY : SetDataType = DB_BINARY
  Case SQL_BIGINT : SetDataType = DB_LONG
  Case SQL_TINYINT : SetDataType = DB_SHORT
  Case SQL_BIT : SetDataType = DB_BINARY
  End Select
End Function