![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Day 19ODBC Data Access via the ODBC APIToday, you learn how to create data-entry forms that use the low-level Open Database Connectivity (ODBC) API routines to access existing databases. The ODBC API provides an alternative to using the Microsoft Jet database engine to access data. The ODBC interface usually is faster than the Microsoft Jet engine and uses less workstation memory than Microsoft Jet. The ODBC interface is capable of accessing data in client-server databases, desktop ISAM databases (such as dBASE, FoxPro, and so on), Microsoft Access format databases, and even Excel spreadsheets and text files. Although data access via ODBC is fast, you can work only with Snapshot-type datasets. All data is accessed by using SQL statements to pass data to and from the ODBC data source. Also, data access via the ODBC API requires more code than using data controls or Visual Basic programming code. For these reasons, the ODBC API is not a good choice for every program. After you get an idea of what it takes to write a Visual Basic program using ODBC for data access, you can decide for yourself when to use the ODBC for data access.
In today's lesson, you look at installing the ODBC Administrator on your system and using the Administrator program to define and register an ODBC data source for use with the ODBC API. You also briefly examine the ODBC operational model and the minimum ODBC APIs you need to create your own database programs using the ODBC interface. You then use your knowledge of the ODBC API to construct an ActiveX DLL library that contains the essential API calls and a series of wrapper routines that you can use with all your Visual Basic programs to create data-entry screens for ODBC data sources. Finally, you build a Visual Basic data-entry form that calls the library routines and shows you how to implement a simple data-entry form by using standard Visual Basic controls. When you complete this lesson, you will know how to register new data sources by using the ODBC Administrator program. You also will have a code library you can use to build solid Visual Basic applications that bypass the Microsoft Jet engine and use the ODBC API set to read and write databases. Understanding the ODBC InterfaceThe ODBC interface is a direct interface between your Visual Basic program and the target database. This interface was developed by Microsoft as a way to provide seamless access to external data formats. The first versions of ODBC were a bit buggy and, in some cases, slow. Although the ODBC interface now is one of the fastest data interfaces available, many programmers still mistakenly think the ODBC interface is too slow for production applications. This is not the case. As you'll see in today's lesson, using the ODBC interface usually is faster than using the Microsoft Jet database engine. When you use the Microsoft Jet interface to access an ODBC data source, the Microsoft
Jet interface does the talking to the ODBC interface, which then talks to the intermediate
driver, which talks to the data source your Visual Basic program requested. When
you use ODBC API calls, you bypass the Microsoft Jet layer, and your Visual Basic
program talks directly to the ODBC interface. Figure 19.1 shows how this process
looks on paper.
Even more important, when you use the ODBC interface to link to a data source, your Visual Basic program is not really talking to the data source directly. Your program talks to the ODBC front-end alone. The ODBC front-end uses removable drivers to translate your requests into a format understood by the target data source. The ODBC drivers exist as a middleman between the ODBC front-end and the target data file. Your Visual Basic programs talk to the ODBC front-end. The ODBC front-end talks to the appropriate driver. The driver talks to the target data file. The advantage of this design is that you easily can replace the translator routines (the drivers) to add improved performance or functionality without having to change your Visual Basic program or the target data source. Also, because the ODBC interface rules are published information, anyone who wants to make data available to users can create a new driver, and that driver then can work with all the installed versions of the ODBC interface that already exist. Using the ODBC API interface has its limits, however. When you use the basic ODBC API to select and retrieve data, you actually are dealing with Snapshot-type data objects. You collect a set of data, bring it to your machine, make additions or modifications to the dataset, and send those changes back to the data source. Although this method is fast, it can be a bit cumbersome. Also, when you use the ODBC API, you are not able to use any data-bound controls. You are responsible for reading the data, placing it into form controls, and moving the data from the form controls back to the data source when needed. This means that you have more programming to do before you get a data-entry form up and running. Even with these drawbacks, using the ODBC API to access your data can add increased flexibility to your Visual Basic database programs. Installing the ODBC InterfaceThe most recent version of the ODBC interface is included in the Visual Basic 5 installation files. If you did not install the ODBC interface when you first installed Visual Basic 5, you need to do it now in order to continue the lesson. If you already have installed the ODBC interface, you can skip this section and move on to the section "Registering an ODBC Data Source."
The ODBC kit that ships with Visual Basic 5 contains drivers for SQL Server. Drivers also are available for accessing desktop file formats such as dBASE, FoxPro, Microsoft Access, and Excel spreadsheets. The ODBC installation options appear when you first install Visual Basic 5. For a full installation, you need to check not just the SQL Server and Oracle Drivers, but also the ISAM drivers (for Microsoft Jet use) and the Desktop ODBC drivers (for ODBC use). Now that you have the ODBC Administrator installed, you are ready to define an ODBC data source that you can use with your Visual Basic 5 programs. Registering an ODBC Data SourceThe ODBC interface is based on the idea that defined data sources are available for users and programs to access. Each desktop has its own list of available ODBC data sources. On 16-bit systems, this list of ODBC data sources is kept in the ODBC.INI file in the \WINDOWS\SYSTEM directory. On 32-bit systems, the information is stored in the Registry under the SOFTWARE/ODBC keys.
Each of these entries contains basic information about the defined data source, the drive used to access the data, and possibly additional information, depending on the data source and driver used. It is easy to define and register a new ODBC data source; to understand this process, create an ODBC data source that you can use later in this lesson. First, load the ODBC Administrator program by locating and executing the ODBCAD32.EXE
program. You can find it in the Control Panel, as shown in Figure 19.2; just double-click
the icon.
The Data Source Name field contains the string you use when you call the ODBC
connection from your Visual Basic 5 program. The Description field just contains
a comment to remind you of the contents of the data source. Enter ODBC API Test in
the Data Source Name field and Testing the ODBC API Set in the Description field
(see Figure 19.4).
Figure 19.6 shows the entries in the My Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
section of the Registry file that were created when you added the ODBC API Test data
source. Your entries might vary slightly.
Creating the ODBC API Library RoutinesNow that you know how to define ODBC data sources, you are ready to put together a Visual Basic 5 program that uses the ODBC interface to read and write data. To build your ODBC application, you need to declare several Windows API calls. These calls, along with a handful of predefined constants, are the heart of creating an ODBC-capable database program. This chapter doesn't review all the ODBC API calls--only the essential ones you need to get your ODBC application working.
After you declare the basic APIs, you need to create a set of Visual Basic routines that use these APIs to perform the low-level operations needed to execute ODBC commands from Visual Basic. After the low-level routines, you write a few mid-level functions that hide most of the grittier features of API programming. Finally, you create a few high-level routines you can use from any Visual Basic data-entry form to start off and maintain your ODBC connections. An ODBC API Crash CourseDozens of API calls for the ODBC interface are possible. You can write calls that enable you to inspect the type of ODBC driver you are using, to inspect the various details of the data source (database name, format, and so on), to gather information about the dataset (column names, data types for each field, length of each field, and so on), and to actually connect to the data source and move data to and from the ODBC data source. For this lesson, you focus only on those routines needed to move data back and forth through the ODBC interface. Before you start coding the API calls and wrapper routines, you need to review the basic sequence of ODBC events required to connect to and share data with a registered ODBC data source. Several preliminary steps are involved before you actually can get any data from an ODBC data source. These steps involve defining an environment space for the ODBC connection, completing the actual connection, and then establishing an area of memory for passing data back and forth. Many of the API calls require or return unique values (called handles) to identify the memory spaces reserved for the ODBC interface. Figure 19.7 shows these operations. Most of the preliminary work for establishing an ODBC connection involves creating the handles you use throughout your program. After the connection is established, you easily can share data with the target
data source by using standard SQL statements. You can select a set of rows by using
the SELECT_FROM statement. Whenever you request a dataset from the ODBC
source, you need to go through several steps to actually pass the rows and columns
from the source to your Visual Basic program. First, you execute the SQL statement.
Then, to receive the dataset, you must determine the number of columns to receive
and use that information to tell ODBC to queue up a row of data and send you each
column in the row. You do this until you have received all the rows in the dataset.
Figure 19.8 illustrates the process of executing the SELECT statement and
collecting the resulting data. Figure 19.8. Collecting results of a SELECT query from an ODBC connection.
The last set of ODBC routines you need to call from Visual Basic are the ones that safely close down the ODBC interface before your program exits. The shutdown routine is basically the same as the startup routine in reverse. First, you need to release the statement handle; then, close the ODBC connection and release the connection handle. Finally, you release the environment handle. Throughout the process of communicating with the ODBC interface, you need to check for any error codes returned by ODBC. Because the functions are executing outside your Visual Basic program, ODBC errors do not automatically invoke your Visual Basic error handler. Every major ODBC call returns a success code or an error code. After you execute an ODBC API call, you need to check the return code. If it indicates that an error occurred, you also can call an ODBC routine that returns the detailed error message generated by the data source. When you build your ODBC library, you write a routine to perform this error checking. The Low-Level API Calls The first thing you need to do to build your library is to declare the necessary API calls for the ODBC interface. In your project, you declare only a subset of the total ODBC API calls; these are the calls essential for creating a basic data-entry form. You also need a handful of Visual Basic constants that you use throughout the library. Load Visual Basic 5 and start a new ActiveX project. Add a BAS module to the project and set its Name property to modODBC. Add the API calls in Listing 19.1 to the project.
Listing 19.1. The ODBC API declarations.Option Explicit ` ---------------------------------------------------------- ` ODBC API Declarations ` ---------------------------------------------------------- ` ` basic ODBC Declares Declare Function SQLAllocEnv Lib "odbc32.dll" (env As Long) As Integer Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal env As Long) As Integer Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal env As Long, _ hDbc As Long) As Integer Declare Function SQLConnect Lib "odbc32.dll" (ByVal hDbc As Long, _ ByVal Server As String, ByVal serverlen As Integer, _ ByVal uid As String, ByVal uidlen As Integer, ByVal pwd As String, _ ByVal pwdlen As Integer) As Integer Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal hDbc As Long) _ As Integer Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal hDbc As Long) _ As Integer Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hDbc As Long, _ hStmt As Long) As Integer Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hStmt As Long, _ ByVal EndOption As Integer) As Integer Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hStmt As Long, _ ByVal sqlString As String, ByVal sqlstrlen As Long) As Integer Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal hStmt As Long, _ NumCols As Integer) As Integer Declare Function SQLFetch Lib "odbc32.dll" (ByVal hStmt As Long) As Integer Declare Function SQLGetData Lib "odbc32.dll" (ByVal hStmt As Long, _ ByVal Col As Integer, ByVal wConvType As Integer, ByVal lpbBuf As String, _ ByVal dwbuflen As Long, lpcbout As Long) As Integer Declare Function sqlError Lib "odbc32.dll" Alias "SQLError" (ByVal env As _ Long, ByVal hDbc As Long, ByVal hStmt As Long, ByVal SQLState As _ String, NativeError As Long, ByVal Buffer As String, ByVal Buflen As _ Integer, OutLen As Integer) As Integer Declare Function SQLSetConnectOption Lib "odbc32.dll" (ByVal hDbc&, _ ByVal fOption%, ByVal vParam&) As Integer Declare Function SQLSetStmtOption Lib "odbc32.dll" (ByVal hStmt&, _ ByVal fOption%, ByVal vParam&) As Integer
Listing 19.2. The ODBC constant declarations.` ` misc constants Public Const sqlChar = 1 Public Const sqlMaxMsgLen = 512 Public Const sqlFetchNext = 1 Public Const sqlFetchFirst = 2 Public Const sqlStillExecuting = 2 Public Const sqlODBCCursors = 110 Public Const sqlConcurrency = 7 Public Const sqlCursorType = 6
The ODBC Library RoutinesThe next set of routines are separated into two groups. The first group are routines that deal primarily with the ODBC interface. These routines are just wrappers for the API calls. Wrappers are Visual Basic routines that encapsulate the API call. Using wrappers makes it easy to change the underlying API call without having to change your code. If you want to use the 16-bit version of the ODBC, for example, you only need to change the ODBC32.DLL reference in each of the API calls to ODBC.DLL. Because you are using Visual Basic wrappers, you won't have to make any changes to your Visual Basic programs in order to use 16-bit ODBC. The second set of library routines deals primarily with Visual Basic. These routines take the data from the ODBC and store it in Visual Basic variables and controls for use on your data-entry forms. First, you need to add a few global variables that you use throughout the library. Select the default class module for the project and set its Name property to objODBC. Now add the declarations in Listing 19.3 to the file. Listing 19.3. Adding the local variables to LIBODBC.BAS.Option Explicit ` ` Private Const BUFFERLEN = 256 ` ` sql lock types Public Enum sqlLockType sqlreadonly = 1 sqllock = 2 sqlrowver = 3 sqlValues = 4 End Enum ` ` sql cursor drivers Public Enum sqlCursorDriverType sqluseifneeded = 0 sqlUseODBC = 1 sqlUseDriver = 2 End Enum ` ` cursor types Public Enum sqlResultSetType sqlforwardonly = 0 sqlKeyset = 1 sqldynamic = 2 sqlStatic = 3 End Enum ` ` sqlerror type Public Enum sqlErrorType sqlSuccess = 0 sqlSuccessWithInfo = 1 sqlerr = -1 sqlNoDataFound = 100 End Enum Public Enum sqlStatement sqlClose = 0 sqlDrop = 1 sqlUnbind = 2 sqlResetParams = 3 End Enum ` shared ODBC handle properties: Public hEnv As Long Public hDbc As Long Public hStmt As Long Public NumCols As Integer ` ` local storage for properties Private strDataSource As String Private strUserID As String Private strPassword As String Private strSQL As String Private intRecordCount As Integer Private strTable As String Private strKey As String Private intCursorDriver As Integer Private intLockType As Integer Private intResultSetType As Integer ` internal use Dim intRecNum As Integer
Now define the various properties by using the Visual Basic Property Let and Property Get statements. Listing 19.4 shows the complete listing of all property routines for this class object. Listing 19.4. Coding the property routines for the objODBC class.Public Property Get DataSource() As Variant DataSource = strDataSource End Property Public Property Let DataSource(ByVal vNewValue As Variant) strDataSource = vNewValue End Property Public Property Get UserID() As Variant UserID = strUserID End Property Public Property Let UserID(ByVal vNewValue As Variant) strUserID = vNewValue End Property Public Property Get Password() As Variant Password = strPassword End Property Public Property Let Password(ByVal vNewValue As Variant) strPassword = vNewValue End Property Public Property Get RecordCount() As Variant RecordCount = intRecordCount End Property Public Property Let RecordCount(ByVal vNewValue As Variant) ` na End Property Public Property Get SQL() As Variant SQL = strSQL End Property Public Property Let SQL(ByVal vNewValue As Variant) strSQL = vNewValue End Property Public Property Get Table() As Variant Table = strTable End Property Public Property Let Table(ByVal vNewValue As Variant) strTable = vNewValue End Property Public Property Get Key() As Variant Key = strKey End Property Public Property Let Key(ByVal vNewValue As Variant) strKey = vNewValue End Property Public Property Get CursorDriver() As sqlCursorDriverType CursorDriver = intCursorDriver End Property Public Property Let CursorDriver(ByVal vNewValue As sqlCursorDriverType) intCursorDriver = vNewValue End Property Public Property Get LockType() As sqlLockType LockType = intLockType End Property Public Property Let LockType(ByVal vNewValue As sqlLockType) intLockType = vNewValue End Property Public Property Get ResultSetType() As sqlResultSetType ResultSetType = intResultSetType End Property Public Property Let ResultSetType(ByVal vNewValue As sqlResultSetType) intResultSetType = vNewValue End Property
Listing 19.5. Coding the Class_Initialize event.Private Sub Class_Initialize() ` ` init props ` strDataSource = "" strUserID = "" strPassword = "" strSQL = "" intRecordCount = 0 intCursorDriver = sqlforwardonly intLockType = sqlreadonly ` End Sub
Create a new function called AllocateEnv and add the code in Listing 19.6. Listing 19.6. Coding the AllocateEnv function.Private Function AllocateEnv() ` ` Allocates an ODBC environment handle. ` Stores result to hEnv property ` Dim intResult As Integer ` intResult = SQLAllocEnv(hEnv) ` If intResult <> sqlSuccess Then Err.Raise vbObjectError + 1, App.EXEName, "Cannot allocate environment handle" End If ` AllocateEnv = sqlSuccess ` End Function
Create a new function called Connect, as shown in Listing 19.7. This routine handles the details of completing a connection to the ODBC data source. Listing 19.7. Coding the Connect function.Public Function Connect() As Integer ` ` Allocates and establishes connection ` to DataSource stored in DataSource ` property. ` Dim intResult As Integer ` AllocateEnv ` ` Allocate connection handle: intResult = SQLAllocConnect(hEnv, hDbc) If intResult <> sqlSuccess Then Err.Raise vbObjectError + 3, App.EXEName, "Unable to allocate connection handle" Connect = intResult Exit Function End If ` ` Set cursor driver intResult = SQLSetConnectOption(hDbc, sqlODBCCursors, intCursorDriver) If intResult <> sqlSuccess Then sqlErrorMsg "Error Setting CursorDriver" Exit Function End If ` ` Login to data source intResult = SQLConnect(hDbc, strDataSource, Len(strDataSource), strUserID, Len(strUserID), strPassword, Len(strPassword)) If intResult <> sqlSuccess And intResult <> sqlSuccessWithInfo Then sqlErrorMsg "Unable to connect to DataSource [" & strDataSource & "]" Connect = intResult Exit Function End If ` ` Allocate statement handle. intResult = SQLAllocStmt(hDbc, hStmt) If intResult <> sqlSuccess Then sqlErrorMsg "Unable to allocate statement handle" Connect = intResult Exit Function End If ` ` set cursor type (result set) intResult = SQLSetStmtOption(hStmt, sqlCursorType, intResultSetType) If intResult <> sqlSuccess Then sqlErrorMsg "Error Setting ResultsetType" Exit Function End If ` ` set locktype intResult = SQLSetStmtOption(hStmt, sqlConcurrency, intLockType) If intResult <> sqlSuccess Then sqlErrorMsg "Error Setting LockType" Exit Function End If ` Connect = sqlSuccess ` End Function
You also need to disconnect the ODBC link when you exit the program. Create a new function called Disconnect and add the code in Listing 19.8. Listing 19.8. Coding the Disconnect method.Public Function Disconnect() ` ` disconnect from the data source ` Dim intResult As Integer ` ` Deallocate statement handle: If hStmt <> 0 Then intResult = SQLFreeStmt(hStmt, sqlDrop) If intResult <> sqlSuccess Then Err.Raise vbObjectError + 6, App.EXEName, "Unable to free statement handle" Disconnect = intResult End If End If ` ` Disconnect If hDbc <> 0 Then intResult = SQLDisconnect(hDbc) If intResult <> sqlSuccess Then Err.Raise vbObjectError + 7, App.EXEName, "Unable to disconnect from data source" Disconnect = intResult End If End If ` ` Deallocate connection handle If hDbc <> 0 Then intResult = SQLFreeConnect(hDbc) If intResult <> sqlSuccess Then Err.Raise vbObjectError + 8, App.EXEName, "Unable to deallocate connection handle" Disconnect = intResult End If End If ` DeallocateEnv ` Disconnect = sqlSuccess ` End Function
Of course, you need a routine to release the environment handle, too. Create the DeallocateEnv function and enter the code in Listing 19.9. Listing 19.9. Coding the DeallocateEnv function.Private Function DeallocateEnv() ` ` Frees specified env. handle ` clears stored in hEnv property ` Dim intResult As Integer ` If hEnv <> 0 Then intResult = SQLFreeEnv(hEnv) If intResult <> sqlSuccess Then Err.Raise vbObjectError + 2, App.EXEName, "Unable to free environment handle" DeallocateEnv = intResult Exit Function End If End If ` DeallocateEnv = sqlSuccess ` End Function
The last mid-level routine you need is an ODBC error routine. This routine gathers any error information sent to your Visual Basic program from the ODBC data source. ODBC data sources are capable of sending more than one line of error information. For this reason, you write the routine as a loop that continues to ask for error messages until there are none to be found. Create a new Public Sub called sqlErrorMsg and enter the code in Listing 19.10. Listing 19.10. Coding the sqlErrorMsg subroutine.Public Sub sqlErrorMsg(strMsg As String) ` ` report detailed SQL Error ` Dim strSQLState As String * 16 Dim strErrorMsg As String * sqlMaxMsgLen Dim intErrorMsgLen As Integer Dim intOutLen As Integer ` Dim lngErrCode As Long Dim strErrCode As String Dim intResult As Integer Dim intTemp As Integer ` strSQLState = String(16, 0) strErrorMsg = String(sqlMaxMsgLen - 1, 0) ` Do intResult = sqlError(hEnv, hDbc, hStmt, strSQLState, lngErrCode, strErrorMsg, Len(strErrorMsg), intErrorMsgLen) If intResult = sqlSuccess Or intResult = sqlSuccessWithInfo Then If intErrorMsgLen = 0 Then Err.Raise vbObjectError + 9, App.EXEName, "Success or SuccessWithInfo Error" Else If lngErrCode = 0 Then strErrCode = "" Else strErrCode = Trim(CStr(lngErrCode)) & " " End If Err.Raise vbObjectError + 10, App.EXEName, "<" & strMsg & "> " & strErrCode & Left(strErrorMsg, intErrorMsgLen) End If End If Loop Until intResult <> sqlSuccess ` End Sub
Save this class module before you continue with the last set of ODBC library routines. High-Level Routines The last set of ODBC library routines deals primarily with the duties required to make Visual Basic capable of displaying, reading, and writing data via the ODBC interface. These routines take the datasets returned by ODBC and store them in Visual Basic list and grid controls. These controls then are used as holding areas by your Visual Basic program for filling and updating textboxes on your data-entry form. This method of storing result sets in a Visual Basic control reduces the amount of traffic over the ODBC link and improves the response time of your program.
The first high-level routine you build actually creates a dataset for your Visual Basic program. This routine handles the creation of the dataset by using all the properties that already have been set. This method is called right after the Connect method. Create a new function called Refresh and add the code in Listing 19.11. Listing 19.11. Coding the Refresh function.Public Function Refresh() ` ` collect data from result set ` Dim intResult As Integer Dim intCols As Integer Dim intRows As Integer Dim strBuffer As String * BUFFERLEN Dim strItem As String Dim strData As String Dim lngOutLen As Long Dim intLoop As Integer ` ` run the query intResult = ExecDirect If intResult <> sqlSuccess Then Refresh = sqlerr Exit Function End If ` ` get the column count intResult = NumResultCols(intCols) If intCols = 0 Then Refresh = sqlerr Exit Function Else NumCols = intCols End If ` ` set up for collection frmSQLData.rtbSQlData = "" strBuffer = String(BUFFERLEN, 0) intRows = 0 ` ` get data Do intResult = FetchRow() ` get a row Select Case intResult Case sqlNoDataFound If intRows > 0 Then Exit Do ` we're done Else Refresh = sqlerr Exit Function End If Case sqlSuccess intRows = intRows + 1 strItem = "" For intLoop = 1 To intCols intResult = GetColumn(strBuffer, intLoop) SaveColumn strItem, strBuffer Next ` get another column SaveRow strItem Case Else intResult = SQLFreeStmt(hStmt, sqlClose) Refresh = sqlerr Exit Function End Select Loop ` get another row ` intRecNum = 0 ` clear record pointer intRecordCount = intRows Refresh = sqlSuccess frmSQLData.Refresh ` End Function
Now add a single form to your project. Set its Name property to frmSQLData. Add
a rich text control to the form and set its Name property to rtbSQLData. Now save
the form as frmSQLData. It should look similar to the form shown in Figure 19.9 at
this point. You refer to this form again later in the project.
Listing 19.12. Coding the ExecDirect method.Public Function ExecDirect() ` ` perform an SQL statement ` Dim intResult As Integer ` ` clear any in-process stuff If hStmt <> 0 Then intResult = SQLFreeStmt(hStmt, sqlClose) intResult = SQLFreeStmt(hStmt, sqlUnbind) intResult = SQLFreeStmt(hStmt, sqlResetParams) End If ` If intResult <> sqlSuccess Then ExecDirect = intResult Err.Raise vbObjectError + 11, App.EXEName, "Error freeing old statement handle" intResult = SQLFreeStmt(hStmt, sqlClose) Exit Function End If ` ` Do the query & wait intResult = SQLExecDirect(hStmt, SQL, Len(SQL)) Do While intResult = sqlStillExecuting intResult = SQLExecDirect(hStmt, SQL, Len(SQL)) DoEvents Loop ` ` check for errors If intResult <> sqlSuccess Then ExecDirect = intResult Err.Raise vbObjectError + 12, App.EXEName, "Error executing Query" intResult = SQLFreeStmt(hStmt, sqlClose) Exit Function End If ` ExecDirect = sqlSuccess ` End Function
Listing 19.13. Coding the FetchRow method.Public Function FetchRow() ` ` get a row of data ` Dim intResult As Integer ` intResult = SQLFetch(hStmt) If intResult <> sqlSuccess Then If intResult <> sqlNoDataFound Then Err.Raise vbObjectError + 14, App.EXEName, "Error fetching row" FetchRow = intResult Else FetchRow = intResult End If Else FetchRow = sqlSuccess End If ` End Function Next, you need to add the GetColumn method. This moves a column of data from the data source to the local memory space. Add the code in Listing 19.14 to the class. Listing 19.14. Coding the GetColumn method.Private Function GetColumn(strBuffer As String, intCol As Integer) ` ` get a column from the current row ` Dim intResult As Integer Dim lngBufferLen As Long ` intResult = SQLGetData(hStmt, intCol, sqlChar, strBuffer, BUFFERLEN, lngBufferLen) If intResult <> sqlSuccess Then GetColumn = intResult Err.Raise vbObjectError + 15, App.EXEName, "Error retrieving column data" Exit Function Else If lngBufferLen > 0 Then strBuffer = Left(strBuffer, lngBufferLen) Else strBuffer = "" End If End If ` GetColumn = sqlSuccess ` End Function
Listing 19.15. Adding the SaveColumn method to the class.Private Sub SaveColumn(strRow As String, strColumn As String) ` ` add column to row line ` If Trim(strColumn) <> "" Then If Trim(strRow) = "" Then strRow = "||" & Trim(strColumn) ` mark first field Else strRow = strRow & "|" & (strColumn) End If Else strRow = strRow & "|" End If ` End Sub
Now add the code in Listing 19.16 to the class. This is the SaveRow method. This is the code that copies the complete line of data into the rich textbox control for storage. Listing 19.16. Adding the SaveRow method.Private Sub SaveRow(strRow As String) ` ` save row to rtb control ` frmSQLData.rtbSQLData.Text = frmSQLData.rtbSQLData.Text & strRow & vbCrLf ` End Sub
Listing 19.17. Coding the GetSQLRec method.Private Sub GetSQLRec(intLine As Integer, strLine As String, frmTemp As Object) ` ` move a rec from the rtb into array ` Dim intLoop As Integer Dim lngPosMark As Long Dim lngRecEnd As Long Dim intRec As Integer Dim strData As String ` strData = frmSQLData.rtbSQLData.Text ` lngPosMark = 1 For intLoop = 1 To intRecordCount lngPosMark = InStr(lngPosMark, strData, "||") If lngPosMark <> 0 Then intRec = intRec + 1 If intRec = intLine Then Exit For End If lngPosMark = lngPosMark + 1 End If Next ` If intRec <> 0 Then lngRecEnd = InStr(lngPosMark, strData, vbCrLf) strLine = Mid(strData, lngPosMark + 2, lngRecEnd - (lngPosMark + 2)) End If ` ` now get fields If strLine <> "" Then For intLoop = 1 To NumCols frmTemp.sqlfield(intLoop - 1) = GetSQLField((strLine), intLoop) `frmTemp.sqlfield(intLoop) = GetSQLField(strRecLine, intLoop) Next End If End Sub
Next, you need the GetSQLField method. Add this from the code in Listing 19.18. Listing 19.18. Coding the GetSQLField method.Private Function GetSQLField(strLine As String, intField As Integer) ` ` get a field from the line ` Dim intLoop As Integer Dim lngPosMark As Long Dim lngPosEnd As Long Dim intCol As Integer ` strLine = "|" & strLine & "|" lngPosMark = 1 lngPosEnd = 0 intCol = 0 ` For intLoop = 1 To NumCols lngPosMark = InStr(lngPosMark, strLine, "|") If lngPosMark <> 0 Then intCol = intCol + 1 If intCol = intField Then lngPosEnd = InStr(lngPosMark + 1, strLine, "|") Exit For End If lngPosMark = lngPosMark + 1 End If Next ` If lngPosEnd <> 0 Then GetSQLField = Mid(strLine, lngPosMark + 1, lngPosEnd - (lngPosMark + 1)) Else GetSQLField = "" End If ` End Function
Now that you have a method for retrieving a row and each field, you're ready to build the routines that handle moving the record pointer and loading the caller's form. Listing 19.19 shows all the code for the MoveFirst, MoveNext, MovePrevious, and MoveLast methods of the class. Add these methods to your project. Listing 19.19. Coding the Move methods for the class.Public Sub MoveFirst(frmTemp As Object) ` ` move items from rtb into form controls ` Dim strRecLine As String ` ` position record pointer intRecNum = 1 ` GetSQLRec intRecNum, strRecLine, frmTemp ` End Sub Public Sub MoveLast(frmTemp As Object) ` ` move items from rtb into form controls ` Dim strRecLine As String ` ` position record pointer intRecNum = intRecordCount ` GetSQLRec intRecNum, strRecLine, frmTemp ` End Sub Public Sub MovePrevious(frmTemp As Object) ` ` move items from rtb into form controls ` Dim strRecLine As String ` ` position record pointer If intRecNum > 1 Then intRecNum = intRecNum - 1 Else intRecNum = 1 End If ` GetSQLRec intRecNum, strRecLine, frmTemp ` End Sub Public Sub MoveNext(frmTemp As Object) ` ` move item from rtb into form controls ` Dim strRecLine As String ` ` position record pointer If intRecNum < intRecordCount Then intRecNum = intRecNum + 1 End If ` GetSQLRec intRecNum, strRecLine, frmTemp ` End Sub
First, create the DelRow method and enter the code in Listing 19.20. Listing 19.20. Coding the DelRow method.Public Sub DelRow(frmTemp As Object) ` ` remove a row from the table ` Dim intResult As Integer Dim strSQL As String ` ` make statement strSQL = "DELETE * FROM " & strTable & " WHERE " strSQL = strSQL & strKey & "='" & frmTemp.sqlfield(0) & "`" ` ` make the call intResult = SQLExecDirect(hStmt, strSQL, Len(strSQL)) If intResult <> sqlSuccess Then sqlErrorMsg "Unable to delete row" intResult = SQLFreeStmt(hStmt, sqlClose) End If ` End Sub
The routine in Listing 19.20 builds a standard DELETE query using the parameters you supplied to it and then executes the SQL DELETE, returning any error messages that might result. Now you build the AddRow method. This routine builds a standard APPEND query using the INSERT INTO syntax. Create the new function and add the code in Listing 19.21. Listing 19.21. Coding the AddRow method.Public Sub AddRow(frmTemp As Object) ` ` add a new row of data to the table ` Dim intResult As Integer Dim strSQL As String Dim intLoop As Integer ` strSQL = "INSERT INTO " & strTable & " VALUES(" ` For intLoop = 1 To NumCols strSQL = strSQL & "`" & frmTemp.sqlfield(intLoop - 1) & "`" If intLoop <> NumCols Then strSQL = strSQL & "," End If Next strSQL = strSQL & ")" ` ` now make the call intResult = SQLExecDirect(hStmt, strSQL, Len(strSQL)) If intResult <> sqlSuccess Then sqlErrorMsg "Unable to add row" intResult = SQLFreeStmt(hStmt, sqlClose) End If ` End Sub
Create the UpdateRow method and add the code in Listing 19.22. Listing 19.22. Coding the UpdateRow method.Public Sub UpdateRow(frmTemp As Object) ` ` replace an existing row ` Dim intResult As Integer Dim strDelSQL As String Dim strAddSQL As String Dim intLoop As Integer ` strDelSQL = "DELETE * FROM " & strTable & " WHERE " strDelSQL = strDelSQL & strKey & "='" & frmTemp.sqlfield(0) & "`" ` strAddSQL = "INSERT INTO " & strTable & " VALUES(" ` For intLoop = 1 To NumCols strAddSQL = strAddSQL & "`" & frmTemp.sqlfield(intLoop - 1) & "`" If intLoop <> NumCols Then strAddSQL = strAddSQL & "," End If Next strAddSQL = strAddSQL & ")" ` ` make the calls intResult = SQLExecDirect(hStmt, strDelSQL, Len(strDelSQL)) If intResult <> sqlSuccess Then sqlErrorMsg "Unable to delete row" intResult = SQLFreeStmt(hStmt, sqlClose) End If ` intResult = SQLExecDirect(hStmt, strAddSQL, Len(strAddSQL)) If intResult <> sqlSuccess Then sqlErrorMsg "Unable to add row" intResult = SQLFreeStmt(hStmt, sqlClose) End If ` End Sub
Save the class module. You now have completed the ODBC library routines. The next step is to build a simple data-entry form that uses the ODBC library to open a dataset and pass information to and from the data via the ODBC interface. Using the ODBC Library to Create a Data-Entry FormNow that you have your ODBC library, you are ready to build a data-entry form that uses the ODBC interface for database access. For this example, you build a simple data-entry form that has the usual navigation buttons (First, Next, Back, and Last) and the record buttons (Add, Delete, and Update). You write this form by using the new class library in a way that is almost identical to the way you create a data-entry form using the standard data control. You add controls, set a few properties, and then execute a few methods. Building the ODBC Test Data-Entry FormAdd a new Standard EXE project to the current project group (the one with the
prjODBC project). Use Figure 19.10 and Table 19.1 as a guide for laying out the test
form.
Save this form as FRMSQLTEST.FRM and the project as PRJSQLTEST.VBP. Now you're ready to add the code to the form. Coding the ODBC Data-Entry FormYou need to add code in just a few places on the form. First, you need to add two form-level variables, as shown in Listing 19.23. These variables are used throughout the form. Listing 19.23. Coding the general declaration section of the form.Option Explicit ` ` form-level vars Dim objSQL As Object Dim blnAdding As Boolean
Listing 19.24. Coding the StartDB routine.Public Sub StartDB() ` ` handle chores of connecting and getting data ` ` create reference to ODBC object Set objSQL = New objODBC ` ` populate properties objSQL.DataSource = "ODBC API Test" objSQL.UserID = "admin" objSQL.Password = "" objSQL.SQL = "SELECT * FROM TestTable" objSQL.Table = "TestTable" objSQL.Key = "Name" objSQL.ResultSetType = sqlStatic objSQL.CursorDriver = sqlUseODBC objSQL.LockType = sqlValues ` ` do real work objSQL.Connect ` establish connection objSQL.Refresh ` build dataset objSQL.MoveFirst Me ` display first row ` End Sub
Now add the following lines to the Form_Load event of the form: Private Sub Form_Load() ` ` startup connection ` StartDB ` End Sub You need to add the code that handles all the user actions behind the cmdMove command button array. This button array handles the navigation chores (First, Last, Next, and Back). Enter the code in Listing 19.25 into the Click event of the cmdMove button. Listing 19.25. Coding the cmdMove_Click event.Private Sub cmdMove_Click(Index As Integer) ` ` move record pointer ` Select Case Index Case 0 ` objSQL.MoveFirst Me Case 1 objSQL.MovePrevious Me Case 2 objSQL.MoveNext Me Case 3 objSQL.MoveLast Me End Select ` End Sub
Listing 19.26. Coding the cmdAction_Click event.Private Sub cmdAction_Click(Index As Integer) ` ` handle action selections ` Select Case Index Case 0 ` add sqlField(0) = "" sqlField(1) = "" blnAdding = True Case 1 ` delete If blnAdding = True Then blnAdding = False End If objSQL.DelRow Me Case 2 ` udpate If blnAdding = True Then objSQL.AddRow Me blnAdding = False Else objSQL.UpdateRow Me End If Case 3 ` close objSQL.Disconnect Unload Me End Select ` End Sub
Now save the project as PRJTEST.VBP. You now are ready to run the ODBC data-entry form. Running the ODBC Data-Entry FormNow that both the library and the form routines are completed, you are ready to run the program. When you first run the program, you see the data-entry form with the first record displayed, as shown in Figure 19.11. You now can use this screen to walk through the dataset by using the command buttons
(First, Last, Next, and Back). You also can add, edit, and delete records in the
dataset by using the appropriate buttons.
Looking at Other ODBC ConsiderationsNow that you know how to build ODBC data-entry forms, you should keep in mind a few ODBC-related items as you build ODBC-enabled Visual Basic applications:
SummaryToday you learned how to use the ODBC API set to directly link your Visual Basic program to target data sources via the ODBC interface. The ODBC interface generally is faster than Microsoft Jet when it comes to linking to ODBC-defined data sources. You also looked at installing the ODBC interface on your workstation and using the ODBC Administrator program to install ODBC driver sets and to define data sources for ODBC connections. You learned how to build a program library that uses a minimum set of ODBC API calls along with several Visual Basic wrapper routines. This library set provides the basic functions necessary to read and write data to and from a defined ODBC data source. You can use these routines to create fully functional data-entry forms for ODBC data sources. Finally, you used the library routines to build a data-entry form that opens a link to a defined ODBC data source and enables users to read and write data records for the ODBC data source. Quiz
ExercisesSuppose that you have been given the assignment of creating a remote data-entry form for reviewing and updating data in a centrally located data file. The data currently is stored in a Microsoft Access database on the central file server, but it might soon be converted to an SQL Server database in another location. You cannot always know the actual columns that exist in the data table, because the layout of the table changes based on information entered each month. The form should be flexible enough to determine the columns available and present those columns to the user for data entry. The program also should be flexible enough to allow for minimum disruption of the file even when the database is converted from Microsoft Access to SQL Server database format. Your first task is to define an ODBC data source at your workstation that has the Microsoft Access data file C:\TYSDBVB\CHAP19\EXER19.MDB as its data source name. You want to access the Transactions table that exists in the EXER19.MDB database. The key field of the Transactions table is called OrderNbr. Then, modify the TYSODBC.VBP project to open this data source and enable users to review and update data in the spreadsheet. |
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |