| Chapter
19
Chapter 19
ODBC Data
Access via the ODBC API
Today, 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.
NOTE: You have a number of ODBC-type
connection options with Visual Basic 5. In many cases, it is easier to use RDOs/RDC (for
Visual Basic 5 Enterprise users) or the ODBCDirect options of the standard data control.
This material is included here for those who want direct access to the ODBC interface via
Visual Basic code instead of by using the object and ActiveX interfaces.
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 Interface
The 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.
Figure 19.1. ODBC versus
Microsoft Jet interface.
The ODBC interface doesn't really talk to databases. Instead, it links your Visual Basic
program to defined data sources. These sources of data can be flat-file databases (such as
dBASE and FoxPro), relational databases (such as Microsoft Access and SQL Server), or any
file format for which an ODBC interface driver is available. Microsoft provides an ODBC
interface driver for Excel spreadsheets and even delimited text files, for example. As
long as a driver is available, you can use ODBC to access the data.
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 Interface
The 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."
NOTE: You also might have other software
packages that installed the ODBC interface on your system. Look for a program called ODBCAD32.EXE.
If you do not find this program, refer to the Visual Basic 5 installation disks or CD-ROM
to install the ODBC interface.
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 Source
The 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.
WARNING: Even though you can call up the ODBC.INI
file with a text editor or open the Windows Registry by using REGEDIT.EXE, I do
not recommend that you alter these entries using anything other than the ODBC
Administrator program. Incorrect data in the ODBC entries in the INI file or in the
Registry can cause the ODBC interface to behave unpredictably or to fail completely.
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.
Figure 19.2. Locating the
ODBC Administrator program (ODBCAD32.EXE).
When you first start the ODBC Administrator, you see a tabbed dialog box that lists all
the data sources currently registered for your workstation, as shown in Figure 19.3.
Figure 19.3. Viewing the
registered ODBC data sources.
To define a new ODBC data source, make sure that the User DSN tab is selected and click
Add. The Create New Data Source dialog box appears. Select the Microsoft Access Driver (*.mdb)
and click Finish. The ODBC Microsoft Access 97 Setup dialog box for creating a new ODBC
data source then appears.
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.4. Defining a
new ODBC data source.
In the Database section, click Select to access the Select Database dialog box shown in
Figure 19.5. Locate and select the C:\TYSDBVB5\SOURCE\CHAP19\TEST\SQLODBC.MDB
database. This is the database your program connects to each time it calls the ODBC data
source TYSODBC. Your screen now should resemble the one shown in Figure 19.5.
Figure 19.5. Setting the
Database property of the ODBC.
Click OK to store the new data-source definition to the ODBC.INI file. You now
should be able to see the ODBC API Test data source in the listbox in the first ODBC
dialog box form.
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.
Figure 19.6. Viewing the
Registry entries for the new data source.
You can register as many data sources as you want. You can add various options to the
data-source definition, depending on the target data file with which you are working. With
Microsoft Jet databases, for example, you can add the SYSTEM security file to the
data source to force users to provide valid user IDs and passwords. You also can adjust
time-out values and mark the data source for exclusive use only. Other possible entries
for other data file formats exist as well.
TIP: Review the ODBC Administrator online
help file for more information on configuring ODBC data sources.
Creating the
ODBC API Library Routines
Now 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.
TIP: Visual Basic 5 ships with an API viewer
that enables you to search for a particular API call and then copy and paste the
information from the viewer directly into your Visual Basic 5 application.
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 Course
Dozens 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.7. The
preliminary steps to establish an ODBC data-source connection.
Figure 19.8. Collecting results of a SELECT
query from an ODBC connection.
You can perform single record adds, updates, and deletes by using SQL INSERT, UPDATE,
and DELETE statements. You accomplish this simply by sending the SQL statement to
the data source. You even can perform data table CREATE and DROP
statements for most data sources.
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.
TIP: If you want to save yourself some typing
(and possible typing errors), you can find the MODODBC.BAS file in the C:\TYSDBVB5\SOURCE\
CHAP19\PRJODBC directory on the CD. You can load this file into your project by
choosing Project | Add File.
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.1 contains the ODBC API calls needed to implement basic connect, data-transfer,
and disconnect operations. Now add the constants in Listing 19.2 to the module.
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 |
Save the module as MODODBC.BAS, and save the project as PRJODBC.VBP. Now
you are ready to build the library functions that use these API calls to perform ODBC
operations.
The ODBC
Library Routines
The 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 |
Most of the items in Listing 19.3 define shared Public variables or enumerated data types
for properties of the new class object. You use these values and storage locations both
internally within the new class and from your external programs that call this class.
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 |
After coding all the property handlers, add the code from Listing 19.5 to the Class_Initialize
event.
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 |
Save the class modules as objODBC.cls before continuing to the next section. Now
you're ready for the first set of Visual Basic routines. Mid-Level Routines The mid-level
routines handle the direct calls to the ODBC API and provide simple error checking. The
first of the routines allocates an environment handle. This handle is needed before you
can attempt to connect to the ODBC interface.
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 |
The routine in Listing 19.6 calls the SQLAllocEnv API and checks for any errors.
The SQLAllocEnv API establishes an environment for all ODBC transactions for this
session. The hEnv variable that is set here holds a unique number that identifies
all transactions that pass from your Visual Basic program to the ODBC interface.
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 |
The routine in Listing 19.7 performs several chores. The first operation establishes a
data-source connection handle. Then, after setting the connection type by using the
selected CursorDriver, the actual attempt to connect to the data source is performed. The
DataSource, UserID, and Password properties are used for this. Next, the ODBC statement
handle is established, and the ResultSetType and LockType properties are used to define
the details of the connection. The statement handle is used as the unique identifier
whenever you want to share data with the ODBC data source.
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 |
You can see that Listing 19.8 performs the same three functions as ODBCConnect,
only this time in reverse. First, it releases the statement handle, and then it performs
the actual disconnect of the ODBC interface. Finally, the routine releases the connection
handle.
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 |
Listing 19.9 is a simple routine. It tells the ODBC interface that you are done with the
session and returns any resulting codes.
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 |
The routine in Listing 19.10 checks the state of the error code and returns any messages
it can find. There are times when the error code is set by ODBC, but no message is
returned. The routine checks for this and creates its own message, if needed.
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.
NOTE: In the examples here, you access
relatively small datasets. If your ODBC interface requires the passing of very large
datasets, you need to develop more sophisticated methods for storing and retrieving the
resulting datasets. It is always a good idea to limit the size of the result set as much
as possible, though, because passing large amounts of data over the ODBC link can
adversely affect not just your Visual Basic program, but all programs that are using the
same network.
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 |
The routine in Listing 19.11 performs a number of important tasks. First, it calls the
internal ExecDirect method. This method executes the actual SQL statement. Then,
after returning from the ExecDirect call, the routine gets the number of data
columns and clears a utility form that holds a rich text control. This rich text control
is used as an internal buffer area for the downloaded dataset. You build that form in just
a moment. Finally, a Do loop is established to collect each column of data and
save it as a new row. When all rows have been collected, the routine exits.
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.
Figure 19.9. Adding the
frmSQLData utility form to the project.
Next, you need to code the ExecDirect method. This is the wrapper method for the SQLDirectExec
API call. Add the code in Listing 19.12 to the class module.
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 |
The code in Listing 19.12 clears any current pending statement and then executes the SQL
statements stored in the SQL property of the class. The Refresh method calls a
number of other important methods. The first of these is the FetchRow method.
This gets a row of data from the ODBC data source. Add the code in Listing 19.13 to your
class.
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 |
Now, add the SaveColumn method in Listing 19.15 to the class.
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 |
This routine just copies the retrieved column data into a hold variable. The marking
values "||" (for a new record) and "|" (for a new
field) are used to retrieve the data from local storage to the caller's data-input form
controls later.
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 |
Now that you have loaded the rich textbox control with the data from the data source, you
need a routine that moves individual rows from the control to the caller's input controls
on the data-entry form. To do this, you need some support routines: the GetSQLRec
and GetSQLField methods. Add the code in Listing 19.17 to your class.
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 |
Note that the code in Listing 19.17 requires a few parameters. The first is the number of
the rows requested. The second parameter is the data row returned, and the last is the
caller's data form that contains the input controls. Notice that the routine assumes that
the controls on the caller's form are called sqlField and are part of a control array.
This is a requirement for all forms that call this library.
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 |
The routine in Listing 19.18 asks for the control to read the column number and the
character used to delimit the columns in the list control. It takes this information and
returns a string that can be used to populate a text control (or any other control) on a
data-entry form. You learn how to use this in your data-entry forms in the next section.
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
|
You need only three more library functions before you have a complete ODBC database kit.
You need routines that can write an updated existing record, add a new record, and delete
an existing record from the dataset. These three routines can be called from your
data-entry form and look much like the standard add, edit, and delete operations used with
data-bound controls.
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 is designed to delete the current record loaded into the text
controls on the form, and it requires only a pointer to the form as a parameter. The name
of the data table you are updating and name of the key field are pulled from properties
already set by the user. For all your ODBC datasets, you are assuming that the first field
in the list is the primary key field.
NOTE: Assuming that the primary key field is
always the first physical field in the dataset can be a limitation when you're dealing
with secondary tables and other non-normalized datasets. For now, however, this assumption
handles most of your data-entry needs. As you develop more skill with ODBC routines, you
can modify these routines or add others that give you more flexibility in sharing data
over ODBC connections.
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 |
The last routine in your library performs an update of an existing record. The simplest
way to accomplish this is to delete the existing record and replace it with the new
updated version. This can be done with two SQL statements: a DELETE query,
followed by an INSERT INTO statement. A more sophisticated approach would be to
build a series of UPDATE statements that update each field of the row, one at a
time. For the example here, you use the DELETE/INSERT method, because it takes
less code and is easier to understand.
WARNING: In certain situations, you do not
want to perform updates by using the DELETE/INSERT method. If you have defined a
delete cascade in a relationship between two tables, performing a DELETE/INSERT
on the one side of the one-to-many relationship results in the deletion of all the related
records on the many side of the relationship. In cases where yot?might define delete
cascades, you should use only the UDPATE method.
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 |
As you can see, this routine first executes a DELETE query, and then it executes
an INSERT statement.
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 Form
Now 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 Form
Add 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.
Figure 19.10. Laying out
the SQLTest form.
Table 19.1. Controls for
the SQLTest form.
| Control |
Property |
Setting |
| VB.Form |
Name |
frmSQLTest |
|
Caption |
"SQL ODBC API Test Form" |
|
ClientHeight |
1515 |
|
ClientLeft |
60 |
|
ClientTop |
345 |
|
ClientWidth |
4890 |
| VB.CommandButton |
Name |
cmdAction |
|
Caption |
"&Close" |
|
Height |
300 |
|
Index |
3 |
|
Left |
3900 |
|
Top |
1140 |
|
Width |
900 |
| VB.CommandButton |
Name |
cmdAction |
|
Caption |
"&Update" |
|
Height |
300 |
|
Index |
2 |
|
Left |
3900 |
|
Top |
780 |
|
Width |
900 |
| VB.CommandButton |
Name |
cmdAction |
|
Caption |
"&Add" |
|
Height |
300 |
|
Index |
0 |
|
Left |
3900 |
|
Top |
60 |
|
Width |
900 |
| VB.CommandButton |
Name |
cmdMove |
|
Caption |
"&Last" |
|
Height |
300 |
|
Index |
3 |
|
Left |
2940 |
|
Top |
1140 |
|
Width |
840 |
| VB.CommandButton |
Name |
cmdMove |
|
Caption |
"&Next" |
|
Height |
300 |
|
Index |
2 |
|
Left |
1980 |
|
Top |
1140 |
|
Width |
900 |
| VB.CommandButton |
Name |
cmdMove |
|
Caption |
"&Back" |
|
Height |
300 |
|
Index |
1 |
|
Left |
1020 |
|
Top |
1140 |
|
Width |
900 |
| VB.CommandButton |
Name |
cmdMove |
|
Caption |
"&Top" |
|
Height |
300 |
|
Index |
0 |
|
Left |
60 |
|
Top |
1140 |
|
Width |
900 |
| VB.TextBox |
Name |
sqlField |
|
Height |
300 |
|
Index |
1 |
|
Left |
1320 |
|
Top |
720 |
|
Width |
2400 |
| VB.TextBox |
Name |
sqlField |
|
Height |
300 |
|
Index |
0 |
|
Left |
1320 |
|
Top |
360 |
|
Width |
1200 |
| VB.CommandButton |
Name |
cmdAction |
|
Caption |
"&Delete" |
|
Height |
300 |
|
Index |
1 |
|
Left |
3900 |
|
Top |
420 |
|
Width |
900 |
| VB.Label |
Name |
Label2 |
|
Caption |
"EMail:" |
|
Height |
300 |
|
Left |
60 |
|
Top |
780 |
|
Width |
1200 |
| VB.Label |
Name |
Label1 |
|
Caption |
"Name:" |
|
Height |
300 |
|
Left |
60 |
|
Top |
420 |
|
Width |
1200 |
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 Form
You 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
Next, you need to create a custom routine that sets the new class properties and
initializes the data connection. Create a new Public Sub called StartDB
and enter the code in Listing 19.24.
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 |
Most of the material here should look familiar. After creating a reference to the class
and setting several properties, the Connect, Refresh, and MoveFirst
methods are executed to fill the form with data.
NOTE: The values used to initialize the
variables are related to the ODBC data source you defined in the "Registering an ODBC
Data Source" section of this chapter. If you have not completed the first part of
this chapter, you cannot run this program with these variables.
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 |
Finally, you need to code the Click event of the cmdAction button. This handles
the record-modification chores (Add, Update, Delete, and Refresh). Add the code in Listing
19.26 to the project.
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 |
NOTE: The code in Listing 19.26 is only the
most basic code needed to add, delete, and update a record. If you want to use this form
in a production setting, you should add code to confirm deletes and enable users to cancel
updates or adds.
Now save the project as PRJTEST.VBP.
You now are ready to run the ODBC data-entry form.
Running the
ODBC Data-Entry Form
Now 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.
Figure 19.11. Running the
SQLTest project.
You now have a fully functional data-entry screen for ODBC data sources. You can improve
this form by adding other routines that improve the error handling and increase the user
friendliness of the form. You even can use this form as a basis for your own ODBC
data-entry forms.
Looking at
Other ODBC Considerations
Now 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:
- ODBC connection usage: With some RDMBS systems, each
connection you make counts as a user connection to the back-end data source. If your
client has a 10-user license and your Visual Basic application opens three ODBC datasets,
only seven connections are left for the entire network. If you run three versions of the
same program at the same time, you are using nine connections. SQL Server is able to use
smart caching to eliminate this problem, but some systems do not. It is a good idea to
minimize the number of open connections your Visual Basic programs require.
- Install files: If you are using ODBC to connect to
data, you need to include the ODBC setup files with your Visual Basic program setup kit.
See the Visual Basic documentation on the Setup Wizard for more information on the
required files to include for ODBC-enabled applications.
- .INI and Registry settings: A number of ODBC-related
variables can affect performance. The 16-bit ODBC interface uses ODBC.INI (the
list of defined data sources), ODBCINST.INI (the list of installed ODBC drivers
available), and ODBCISAM.INI and ODBCDDP.INI (which deal with the
ISAM-type interfaces, such as Microsoft Access, Excel, FoxPro, and so on). In 32-bit
systems, this information is stored in the system Registry. Although you should not edit
these files directly, you should know where these values are stored when you're debugging
your ODBC applications.
- Tracing ODBC activity: You can turn on ODBC trace
files from the ODBC Administrator program. This enables you to watch the message activity
between your application and the ODBC interface, which can be very informative when you're
attempting to locate bugs or performance problems. The Trace log does take up a lot of
disk space after a short time, though. You should turn on the trace capability only when
you absolutely need it.
- Remote data control alternative: If your Visual Basic
program will be operating only in a 32-bit environment and you have the Enterprise Edition
of Visual Basic, you can use the RDC and the RDOs to connect to the ODBC data source. The
RDC/RDOs platform is a replacement for the ODBC API you learned today. Although it is
easier to deal with the RDC/RDOs platform, you must use the 32-bit version of Visual
Basic, and your program must run on a 32-bit operating system. If your program must run on
a 16-bit operating system, you still can use the ODBC API and the examples from this
chapter.
Summary
Today 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
- 1. What do the letters ODBC stand for?
2. Why is the ODBC API interface faster than the Microsoft Jet interface when
connecting to defined ODBC data sources?
3. What are some of the drawbacks to using the ODBC API to link to databases?
4. What program do you use to define an ODBC data source for the workstation?
5. Can you use the ODBC interface to connect to nondatabase files, such as
spreadsheets or text files?
6. When you write ODBC-enabled Visual Basic applications, can you use the same set
of API declarations for the 16-bit version of Visual Basic 5 that you use for the 32-bit
version of Visual Basic 5?
7. What are the four preliminary steps you must complete before you can pass an SQL
SELECT statement to the newly opened ODBC data source?
Exercises
Suppose 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.
|
Learn HTML
