Figure 5   Core ODBC Functions

Task

Function Name

Purpose

Connection to a data source

SQLAllocEnv

Obtains an environment handle -one environment handle is used for one or more connections

SQLAllocConnect

Obtains a connection handle

SQLConnect

Connects to specific driver by data source name, user ID, and password

Preparing SQL requests

SQLAllocStmt

Allocates a statement handle

SQLPrepare

Prepares an SQL statement for later execution

SQLGetCursorName

Returns the name associated with a statement handle

SQLSetCursorName

Specifies a cursor name

Submitting requests

SQLExecute

Executes a prepared statement

SQLExecDirect

Executes a statement

Retrieving results and information about results

SQLRowCount

Returns the number of rows affected by an insert, update, or delete request

SQLNumResultCols

Returns the number of columns in the result set

SQLDescribeCol

Describes a column in the result set

SQLColAttributes

Describes attributes of a column in the result set

SQLBindCol

Assigns storage for a result column and specifies the data type

SQLFetch

Returns multiple result rows

Terminating a statement

SQLFreeStmt

Ends statement processing

SQLCancel

Cancels an SQL statement

SQLTransact

Commits or rolls back a transaction

Terminating a connection

SQLDisconnect

Closes the transaction

SQLFreeConnect

Releases the connection handle

SQLFreeEnv

Release the environment handle

Figure 7   OpenConnection Parameters

Parameter

Meaning

dsName

The name of a registered ODBC data source

Prompt

This flag specifies whether the ODBC driver manager should display a dialog box

prompting the user for connection information

Readonly

This boolean flag, if set to False, specifies that you want to update data through this connection

Connect

A string that supplies arguments to the ODBC driver manager for the connection (different ODBC data sources require different parameters)

Figure 8   rdo Resultset Cursor Attributes

Attribute

Forward-only

Static

Keyset

Dynamic

Updatable

No*

Yes

Yes

Yes

Membership

Fixed

Fixed

Fixed

Dynamic

Visibility

One row

Cursor

Cursor

Cursor

Move current row

Forward

Anywhere

Anywhere

Anywhere

Result of a join

Yes

Yes

Yes

Yes

* Yes when using server-side cursors on Microsoft SQL Server 6.0

Figure 9   Controlling Cursor Creation with CursorDriver Property

Option

Description

rdUseIfNeeded

(Default) The ODBC driver automatically chooses the appropriate style. Server-side cursors are used if available.

rdUseODBC

RDO uses ODBC cursor library, which builds the cursor on the client computer. This gives better performance for small result sets, but degrades quickly for larger result sets.

rdUseServer

Server-side cursors are always used if available. This usually gives better performance, but can cause more network traffic.

Figure 10   LockType Argument Flag

LockType Flags

Description

rdConcurLock

Pessimistic locking-the row or rows being updated are locked as soon as the Edit or AddNew methods are executed and the lock is held until the Update method is called and has updated the data source

rdConcurRowver

Optimistic locking (the rows are only locked fortheactualupdate)basedonrowversions

rdConcurValues

Optimistic locking-based on row values

rdConcurReadOnly

The cursor is read-only-this is the default

Figure 11   ODBC Handles Available through RDO

Handle-type

RDO Object.Property

Handle created by the ODBC API

Environment

rdoEnvironment.hEnv

SQLAllocEnv

Connection

rdoConnection.hDbc

SQLAllocConnect, SQLDriverConnect

Statement

rdoResultset.hStmt

SQLAllocStmt

Figure 12   DAO Objects and Their RDO Counterparts

DAO Object

Equivalent RDO Object

DBEngine

rdoEngine

User

Not implemented

Workspace

rdoEnvironment

Database

rdoConnection

TableDef

rdoTable

Index

Not implemented

Recordset

rdoResultset

Table-type

Not implemented

Dynaset-type

Keyset-type

Snapshot-type

Static-type

Field

rdoColumn

QueryDef

rdoPreparedStatement

Parameter

rdoParameter

Figure 13   Visual Basic Data Access Methods Compared

Feature

DAO

RDO

ODBC API

Remote connections

Automatic

Automatic and manual

Manual

Types of cursors supported

Dynaset, Snapshot

Keyset, dynamic, static, and forward-only

Keyset, dynamic, static, and forward-only

Automatic background population of cursors while users work

Yes, using the Jet data control

Yes, using the Remote data control and asynchronous mode of RDO

No

Asynchronous queries

No

Yes

Yes

Support for Microsoft SQL Server 6.0 server-side cursors

No

Yes

Yes

Row access without cursors

Yes

Yes

Yes

Result set caching

Yes

Yes

Yes

Stored procedures (SP)

Yes, with SQLPassThrough

Yes, with Singleton execution or in batches

Yes, with Singleton execution or in batches

SP parameters provided

Concatenated

Concatenated or via the rdoParameters collection of prepared statement

Concatenated or integrated into API

SP results

Yes

Yes

Yes

SP output parameters

No

Yes

Yes

SP result codes

No

Yes

Yes

Support for multiple SQL statements in batches

Yes, with SQLPassThrough

Yes

Yes

Error handler

Populate the Errors collection and raise trappable error

Populate rdoErrors collection and raise trappable error

After function returns SQL_ERROR, call SQLError to retrieve error

Retry/continue after query time-out

No

Yes (in asynchronous mode)

Yes (in asynchronous mode)

Messages returned

Messages logged in.MDB table with Log Messages property

Checked post operation, coupled with errors

Checked post operation, coupled with errors

Complex data type

Automatic through bound controls and programmable

Automatic through bound controls and programmable

Programmable

Access to bound controls

Yes

Yes

No

Heterogeneous joins from multiple databases

Yes

No

No

Figure 14   VBDB

VBDBFORM.FRM

ODBC32.TXT

VBDB.VBP

To obtain complete source code listings, see page 5.

ENUMDATA.BAS

 Attribute VB_Name = "Enumarate"
Option Explicit
Public MyDb As Database
Public FieldName As String, FieldType As String
Public TableName As String, DataName As String
Public DBLen As Integer

Public Sub listTable(TbName As String)
Dim td As TableDef, fld As Field
Dim Type2Str(13) As String, TmpAttrib As String, TmpFld As String

TmpAttrib = ""
If Len(TbName) = 0 Then VBDBForm.listtb.Clear Else VBDBForm.listfld.Clear
For Each td In MyDb.TableDefs
  If (td.Attributes And dbSystemObject) <> 0 Then TmpAttrib = " *System"
  If (td.Attributes And dbAttachedTable) <> 0 Then TmpAttrib = " *Attached"
  If (td.Attributes And dbAttachedODBC) <> 0 Then TmpAttrib = " *Attached ODBC"
  If Len(TbName) = 0 Then VBDBForm.listtb.AddItem td.Name & TmpAttrib

  'Map Fields for each TableDef
  TmpAttrib = ""
  If Len(TbName) > 0 And td.Name = TbName Then
    For Each fld In td.Fields
       Type2Str(8) = "Data/Time  "
       Type2Str(10) = "Text      "
       Type2Str(12) = "Memo      "
       Type2Str(1) = "Yes/No     "
       Type2Str(3) = "Integer    "
       Type2Str(4) = "Long       "
       Type2Str(5) = "Currency   "
       Type2Str(6) = "Single     "
       Type2Str(7) = "Double     "
       Type2Str(2) = "Byte       "
       Type2Str(11) = "Long Binary"
    
       Type2Str(0) = "Unknown    "
       TmpFld = fld.Name
       VBDBForm.listfld.AddItem TmpFld & "   " & Type2Str(fld.Type)        '    TmpAttrib
    Next fld
  End If
Next td
End Sub

Sub ComputeRPS(ReadOnly As Boolean, Iterations As Integer, _
 DataName As String, TableName As String, _
 FieldName As String, FieldType As String, connect As String)
 VBDBForm.TxtDAO.Text = Format(0, "0.####")
 VBDBForm.TxtRDO.Text = Format(0, "0.####")
 VBDBForm.TxtODBC.Text = Format(0, "0.####")
 DoEvents
 Dim i As Integer, StartTime As Double
 Dim CountRec As Long

 VBDBForm.LabMsg.Caption = VBDBForm.labdao.Caption & ":"
 DoEvents
 StartTime = Timer
 CountRec = 0
 For i = 1 To Iterations
 ' DAO
 VBDBForm.LabCount.Caption = i
 DoEvents
 CountRec = CountRec + ReadWrite.DBRead(DataName, ReadOnly, FieldName, _
                FieldType, connect, 0)
 VBDBForm.TxtDAO.Text = Format(CountRec / (Timer - StartTime), "0.####")

 DoEvents
Next

VBDBForm.LabMsg.Caption = VBDBForm.labrdo.Caption & ":"
DoEvents
StartTime = Timer
CountRec = 0
For i = 1 To Iterations
' RDO
VBDBForm.LabCount.Caption = i
DoEvents
CountRec = CountRec + ReadWrite.DBRead(DataName, ReadOnly, FieldName, FieldType, connect, 1)
VBDBForm.TxtRDO.Text = Format(CountRec / (Timer - StartTime), "0.####")

DoEvents
Next

VBDBForm.LabMsg.Caption = VBDBForm.labodbc.Caption & ":"
DoEvents
StartTime = Timer
CountRec = 0
For i = 1 To Iterations
  ' ODBC
  VBDBForm.LabCount.Caption = i
  DoEvents
  CountRec = CountRec + ReadWrite.DBRead(DataName, ReadOnly, FieldName, FieldType, connect, 2)
  VBDBForm.TxtODBC.Text = Format(CountRec / (Timer - StartTime), "0.####")
  DoEvents
Next

VBDBForm.LabMsg.Caption = ""
VBDBForm.LabCount.Caption = ""
End Sub

ERROR.BAS

 Attribute VB_Name = "Error"
Global Const HNDL_HENV = 1
Global Const HNDL_HDBC = 2
Global Const HNDL_HSTMT = 3

Sub CenterWindow(fP As Form, fC As Form)
fC.Left = fP.Left + ((fP.ScaleWidth - fC.ScaleWidth) / 2)
fC.Top = fP.Top + ((fP.ScaleHeight - fC.ScaleHeight) / 2)
End Sub

' CheckError
'
' Check the Return code for the ODBC Call.  Expect
' SQL_ERROR, for any other Return Code post
' an error and return FALSE.
'
Function CheckError(ByVal nStmtType As Integer, ByVal pHstmtIn As Long, ByVal rc As Integer) As Integer

'Initialize return value
CheckError = True

'Check return code
If ((rc <> SQL_ERROR)) Then
    'Invalid Return post errors on queue
    Call PostError(nStmtType, pHstmtIn)
    CheckError = False
End If

End Function

' CheckSuccess
'
' Check the Return code for the ODBC Call.  Expect
' SQL_SUCCESS or SUCCESS_WITH_INFO.  Any other
' Return Code should post an error and return FALSE.
'
Function CheckSuccess(ByVal nHndlType As Integer, ByVal pHstmtIn As Long, ByVal rc As Integer) As Integer

'Initialize return value
CheckSuccess = True
    
'Check return code
If ((rc <> SQL_SUCCESS) And (rc <> SQL_SUCCESS_WITH_INFO)) Then
    'Invalid Return post errors on queue
    Call PostError(nHndlType, pHstmtIn)
    CheckSuccess = False
End If

End Function

' CheckSuccessOnly
'
' Check the Return code for the ODBC Call.  Expect
' SQL_SUCCESS.  Any other Return Code should
' post an error and return FALSE.
'
Function CheckSuccessOnly(ByVal nHndlType As Integer, ByVal pHstmtIn As Long, ByVal rc As Integer) As Integer
    
'Initialize return value
CheckSuccessOnly = True
    
'Check return code
If ((rc <> SQL_SUCCESS)) Then
    'Invalid Return post errors on queue
    Call PostError(nHndlType, pHstmtIn)
    CheckSuccessOnly = False
End If

End Function

' DisplayErrMsg
'
' Display the Error Message in a normal Message Box
' the users screen.
'
Sub DisplayErrMsg(szSqlState As String, szErrMsg As String)
Dim Msg As String
 
Msg = "(" + szSqlState + ")"
Msg = Msg + "  " + szErrMsg
MsgBox Msg, MB_OK + MB_ICONSTOP

End Sub

' PostError
'
' Clear the error queue of any messages that may have been
' stored by the driver or driver manager.  Each error will
' cause a message box to be displayed with the error that
' occurred.
'
' Since there will only be one HENV and HDBC, they will be checked
' the Global HANDLES struct, HSTMT will be passed.
'
Sub PostError(ByVal iHndlType As Integer, ByVal pHstmtIn As Long)
Dim phenv As Long
Dim phdbc As Long
Dim phstmt As Long
Dim rc As Integer
Dim szErrMsg As String * SQL_MAX_MESSAGE_LENGTH
Dim szSqlState As String * 5

'Initialize all Handles to NULL
phenv = SQL_NULL_HENV
phdbc = SQL_NULL_HDBC
phstmt = SQL_NULL_HSTMT
    
' Determine handle type of last executed ODBC call
Select Case iHndlType
    Case HNDL_HSTMT:
        phstmt = pHstmtIn
    Case HNDL_HDBC:
        phdbc = Handles.phdbc
    Case HNDL_HENV:
        phdbc = Handles.phenv
    Case Else
        MsgBox "Invalid Handle Type in ErrorCheck Routine", MB_OK + MB_ICONSTOP
        Exit Sub
End Select

'Process the Errors
rc = SQLError(phenv, phdbc, phstmt, szSqlState, 0, szErrMsg, SQL_MAX_MESSAGE_LENGTH, 0)
While (rc = SQL_SUCCESS)
    Call DisplayErrMsg(szSqlState, szErrMsg)
    rc = SQLError(phenv, phdbc, phstmt, szSqlState, 0, szErrMsg, SQL_MAX_MESSAGE_LENGTH, 0)
Wend

End Sub

READWRITE.BAS

 Attribute VB_Name = "ReadWrite"
Option Explicit

Public Function DBRead(DataName As String, ReadOnly As Boolean, _
FieldName As String, FieldType As String, connect As String, action As Integer) As Long
Dim MyDb As Database, MyRs As Recordset
Dim RetBuf As String * 100
Dim recCount As Long

On Error GoTo catchError
Select Case action
Case 0
  recCount = 0
  Set MyDb = DBEngine.Workspaces(0).OpenDatabase("", False, False, connect)
  Set MyRs = MyDb.OpenRecordset(TableName, dbOpenDynaset)
  Do Until MyRs.EOF
    If ReadOnly = False Then MyRs.Edit
    If ReadOnly Then
      RetBuf = MyRs(FieldName)
    Else
      MyRs(FieldName) = Reverse(MyRs(FieldName))
    End If
    If ReadOnly = False Then MyRs.Update
    recCount = recCount + 1
    MyRs.MoveNext
  Loop
  MyDb.Close
Case 1
  recCount = 10
  Dim mrdoEnv As rdoEnvironment, mrdoConn As Object, sSQL As String
  Dim MyrdoRs As rdoResultset '
  rdoEngine.rdoDefaultCursorDriver = rdUseIfNeeded
  Set mrdoEnv = rdoEngine.rdoCreateEnvironment("", "", "")
  Set mrdoConn = mrdoEnv.OpenConnection("", rdDriverNoPrompt, True, Right(connect, _
          Len(connect) - Len("ODBC;")))
  sSQL = "Select " & FieldName & " from " & TableName
  Set MyrdoRs = mrdoConn.OpenResultset(sSQL, rdOpenKeyset, rdConcurValues)
    
  Do Until MyrdoRs.EOF
  If ReadOnly = False Then MyrdoRs.Edit
  If ReadOnly Then
    RetBuf = MyrdoRs.rdoColumns(FieldName)
  Else
    MyrdoRs.rdoColumns(FieldName).Value = Reverse(MyrdoRs.rdoColumns(FieldName).Value)
  End If
  If ReadOnly = False Then MyrdoRs.Update
  recCount = recCount + 1
  MyrdoRs.MoveNext
  Loop
  
  MyrdoRs.Close
  mrdoConn.Close

Case 2
  recCount = 0
  Dim phenv As Long, phdbc As Long, hstmt As Long, cbData As Long, cbRetBuf As Long
  Dim rc As Integer, pszData As String
  pszData = String(100, 0)
  rc = SQLAllocEnv(phenv)
  If (CheckSuccess(HNDL_HENV, 0, rc) = False) Then Exit Function
  rc = SQLAllocConnect(ByVal phenv, phdbc)
  If (CheckSuccess(HNDL_HENV, 0, rc) = False) Then Exit Function
  Dim szConnStrIn As String, szConnStrOut As String, cbConnStrOut As Integer
  szConnStrOut = String(256, 0)
  szConnStrIn = Right(connect, Len(connect) - Len("ODBC;"))
  rc = SQLDriverConnect(ByVal phdbc, 0, ByVal szConnStrIn, _
  SQL_NTS, ByVal szConnStrOut, 255, cbConnStrOut, SQL_DRIVER_COMPLETE)
  If (CheckSuccess(HNDL_HDBC, 0, rc) = False) Then Exit Function
  rc = SQLAllocStmt(ByVal phdbc, hstmt)
  If (CheckSuccess(HNDL_HENV, hstmt, rc) = False) Then Exit Function
  SQLSetScrollOptions ByVal hstmt, SQL_CONCUR_LOCK, SQL_SCROLL_KEYSET_DRIVEN, 1
  sSQL = "SELECT " & FieldName & " from " & TableName
  rc = SQLExecDirect(ByVal hstmt, sSQL, SQL_NTS)
  If (CheckSuccess(HNDL_HSTMT, hstmt, rc) = False) Then Exit Function
  Dim cbrow As Long, RowStatus As Integer
  Dim retbuft(100) As Byte
  Dim temp(100) As Byte, TempStr As String, LenStr As Integer
  If ReadOnly = False Then
    rc = SQLBindCol(ByVal hstmt, 1, SQL_C_CHAR, retbuft(0), 100, cbRetBuf)
    rc = SQLExtendedFetch(ByVal hstmt, SQL_FETCH_NEXT, 1, cbrow, RowStatus)
  Else
    rc = SQLFetch(ByVal hstmt) ' for simple reading fetch is faster
  End If
  While (rc = SQL_SUCCESS Or rc = SQL_SUCCESS_WITH_INFO)
    If ReadOnly Then
      rc = SQLGetData(ByVal hstmt, 1, SQL_C_CHAR, ByVal pszData, 100, cbData)
      rc = SQLFetch(ByVal hstmt) ' for simple reading fetch is faster
    Else
      Dim i As Integer
      TempStr = StrConv(retbuft, vbUnicode)
      LenStr = InStr(TempStr, Chr(0)) - 1
   
      For i = 0 To LenStr - 1
        temp(i) = retbuft(i)
      Next
      For i = 0 To LenStr - 1
        retbuft(i) = temp(LenStr - i - 1)
      Next
      rc = SQLSetPos(ByVal hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE)
      If (CheckSuccess(HNDL_HSTMT, hstmt, rc) = False) Then Exit Function
      rc = SQLExtendedFetch(ByVal hstmt, SQL_FETCH_NEXT, 1, cbrow, RowStatus)
   End If
  recCount = recCount + 1
  Wend
  
  SQLFreeStmt ByVal hstmt, SQL_DROP
  SQLDisconnect ByVal phdbc
  SQLFreeConnect ByVal phdbc
  SQLFreeEnv ByVal phenv

End Select

DBRead = recCount
Exit Function
catchError:
If Err.Number = 94 Then
  Beep
  MsgBox "Null field encoutered: " + DataName + "  " + TableName + "  " + FieldName + _
    vbCrLf + "Please choose another field", vbOKOnly, "Error!"
End If
Beep
End Function

Private Function Reverse(str As String) As String
Dim i As Integer
Reverse = ""
For i = Len(LTrim(str)) To 1 Step -1
   Reverse = Reverse & Mid(str, i, 1)
Next i
End Function

Figure 15   VBDB Tests

Data access method

Number of records read per second

Number of records read and written per second

DAO

4

2

ODBC API

14

7

RDO

20

9