
The Open Database Connectivity (ODBC) standard has made accessing disparate database formats much easier than in older versions of Visual Basic. Starting with version 3.0, Visual Basic data access methods and properties include built-in support for ODBC data sources. This means you can use Visual Basic and ODBC together, either through the Jet database engine that comes with Visual Basic or through direct calls to the ODBC Application Programming Interface (API) or by using both together.
When using Visual Basic to completely handle the connection to the ODBC system, just make a few changes to the Visual Basic OpenDatabase method. This is the syntax of the statement:
Set database = workspace.OpenDatabase(dbname[, exclusive[, read-only[, _
source]]])
The source argument is the main difference when you're using ODBC. In the How-To's throughout this chapter, you'll see how an ODBC connect string is used in the source to specify to which ODBC data source to connect. If you're not sure which data source you want to use, just make the source argument "ODBC" and Visual Basic will team up with ODBC and automatically present a list of the available data sources. Few things in Visual Basic are as straightforward as this.
You can perform many ODBC tasks--including running Structured Query Language (SQL) queries, retrieving results, looping through recordsets, and sending pass-through queries--in Visual Basic without getting any more involved in ODBC than making this change to the OpenDatabase method. Essentially, if Visual Basic has a data access method or property to do what you want, you can almost always use Visual Basic directly without knowing anything about ODBC beyond the existence of the data source you want to use.
ODBC can add a whole new dimension to your application's database access, going far beyond the functionality of Visual Basic's data access function. You can retrieve a wealth of information about a database and manipulate it to your heart's--or client's--content.
Through the How-To's in this chapter, you'll get the details of using straight Visual Basic to access the ODBC API, using the ODBC API directly and bypassing Visual Basic, and combining the two techniques to get the job done.
The guiding philosophy of this chapter is that if there is a way to do it in Visual Basic, that's the way it should be done--unless there is a good reason not to do it that way. The main reason for going straight to the ODBC API is when Visual Basic can't perform the particular task by itself. But there are a few other reasons you'll see along the way.
Using Visual Basic and ODBC Together
If you are going to do any serious work with ODBC, get a copy of Microsoft ODBC 3.0 Programmer's Reference and SDK Guide, published by Microsoft Press. The Software Development Kit (SDK) has all the detailed information you need and a list of all the ODBC functions. This section covers most of the details you'll need in order to use ODBC with Visual Basic.
The ODBC system has four components. Figure 6.1 shows the relationship of these four elements in a Visual Basic ODBC application.
Figure 6.1. An overview of ODBC architecture.
Following are a few other terms you'll encounter when reading about the ODBC system:
ODBC Handles
Before making any direct calls to functions in the ODBC API, you must allocate memory and receive a handle from ODBC for the particular operation. All this means is that ODBC is setting aside a small chunk of memory, through its DLL, to hold information about some aspect of the ODBC system. The handle is essentially a pointer in the Windows environment. As usual, if you are using ODBC through Visual Basic methods only, without direct calls to the ODBC API, you don't need to know or worry about handles at all.
Three handles are necessary for calls to the different ODBC functions:
All ODBC handles must be released before terminating your application. Otherwise, there is no guarantee that the allocated memory will be released at the end of the application, and that is just not good Windows program manners! Eventually, the program will "leak" enough memory to cause performance problems with Windows or even disrupt the operating system entirely.
The handles form a hierarchy. All connection handles in your application are associated with a single environment handle, and all statement handles are associated with one and only one connection handle. In general, all the lower-level handles in the hierarchy must be freed before the next handle in the hierarchy is freed. All the handles can be released and then reused.
ODBC API Conformance Levels
In response to the wide variety of database system capabilities available on every hardware and software platform, ODBC breaks its function list into three levels of conformance. A driver must support, at minimum, the Core-level conformance to be considered an ODBC driver. ODBC also specifies SQL grammar conformance levels, which are not directly related to the API conformance level, as listed here:
It is important to understand that just because a driver calls itself, say, a Level 1 ODBC driver doesn't necessarily mean that it supports all the options of every function at that level. The amount of support is usually tied directly to the capabilities of the underlying database, its limitations, and how aggressively a driver manufacturer is in building capabilities into its driver.
WARNING No independent body certifies drivers as meeting the requirements of an ODBC conformance level. The developer of each driver makes the conformance claim. Buyer beware!
The ODBC API: Visual Basic Declare Statements and ODBC Constants
Because ODBC is implemented in Windows as a DLL, you must include a Declare statement for any ODBC functions you call from a Visual Basic application. By requiring a Declare statement, Visual Basic knows what DLL file to load and the types of parameters each function uses so that it can perform type checking and flag any incorrect types you try to use in a function. The ODBCAPI.BAS file, on the CD-ROM that accompanies this book, contains these ODBC Declare statements and other ODBC constants and user-defined types, ready to be copied to your application.
Debugging ODBC Applications
Even if you don't have access to the ODBC SDK, ODBC provides one valuable tool for debugging applications: the trace log (sometimes called the trace file). This is a log of all calls made by your application and Visual Basic through the ODBC system. The log can be quite educational about how ODBC works, and it lets you see exactly what is being done.
Data Type Conversions Between ODBC and Visual Basic
The correspondence between ODBC and Visual Basic data types is not one-to-one. Fortunately, with Visual Basic's slightly expanded list of data types, the correspondence is a bit closer. Table 6.1 lists the available ODBC data types and the Visual Basic equivalents.
| ODBC DATA TYPE | Grammar Level | Visual Basic Data Type |
| SQL_BIT | Extended | Boolean |
| SQL_TINYINT | Extended | Byte |
| SQL_SMALLINT | Core | Integer |
| SQL_INTEGER | Core | Long |
| SQL_BIGINT | Extended | No equivalent |
| SQL_REAL | Core | Single |
| SQL_FLOAT, SQL_DOUBLE | Core | Double |
| SQL_TIMESTAMP, SQL_DATE | Extended | DateTime |
| SQL_TIME | Extended | Time |
| SQL_CHAR | Minimum | String |
| SQL_VARCHAR | Minimum | String |
| SQL_BINARY | Extended | Binary |
| SQL_VARBINARY | Extended | Binary |
| SQL_LONGVARBINARY | Extended | Binary |
| SQL_LONGVARCHAR | Minimum | String |
| SQL_DECIMAL | Core | Refer to Table 6.3. |
| SQL_NUMERIC | Core | Refer to Table 6.3. |
As reflected in Table 6.2, special handling is required for the SQL_DECIMAL and SQL_NUMERIC data types. Both data types supply a scale and precision to determine what range of numbers the fields can handle. Using this scale and precision, you can make a good determination of variable scope for Visual Basic. In addition, there are two exceptions to this rule because SQL Server can employ a Currency field. ODBC interprets this with two different scope and precision combinations, as mentioned in Table 6.2.
| SCALE | Precision | Visual Basic Data Type |
| 0 | 1 to Integer | |
| 0 | 5 to 9 | Long |
| 0 | 10 to 15 | Double |
| 0 | 16+ | Text |
| 1 to 3 | 1 to 15 | Double |
| 1 to 15 | Double | |
| 16+ | Text | |
| 10 or 19 | Currency (SQL Server only) |
ODBC data types correspond to the SQL grammar level that a driver and database support, similar to the conformance levels that ODBC's functions support. These are the SQL grammar conformance levels:
A database is not necessarily required to support all the data types at a given level, so an application should check to see what variables are available for a given data source by using the SQLGetTypeInfo function (an ODBC Conformance Level 1 function). See How-To 6.5 for a discussion of retrieving such information about an ODBC database.
ODBC Catalog Functions and Search Pattern Arguments
There are certain ODBC functions, called catalog functions, that return information about a data source's system tables or catalog. Most of the catalog functions are Conformance Level 2, so you probably won't encounter them when using Visual Basic and ODBC. Four of the functions, however, are Level 1: SQLColumns, SQLSpecialColumns, SQLStatistics, and SQLTables.
All the catalog functions allow you to specify a search pattern argument, which can contain the metacharacters underscore (_) and percent (%), as well as a driver-defined escape character. A metacharacter in this context is nothing more than a character that has a meaning other than just the character itself. Following is a detailed explanation of the search pattern characters:
For example, to search for all items with a P, use the search pattern argument %P%. To search for all table names having exactly four characters with a B in the second and last positions, use _B_B. Similarly, if the driver-defined escape character is a backslash, use %\_\% to find all strings of any length with an underscore in the second-to-last position and a percent in the last position of the string.
The driver-defined escape character can be found for any ODBC driver using the SQLGetInfo technique demonstrated in How-To 6.6.
Miscellaneous ODBC Topics
This section covers a few miscellaneous details that will make using ODBC and Visual Basic together a bit easier.
Assumptions
This chapter assumes some things about what you are doing and the tools you are using:
Under Windows 95 and Windows NT, ODBC data sources, explained in greater detail in this How-To, have become more complex entities, especially when it comes to issues such as security and network administration. This How-To explains how a simple Control Panel applet turns the administration of these data sources into a simple task.
Using Visual Basic's Data control to access ODBC demonstrates the power of both Visual Basic and ODBC. Accessing databases through ODBC without using the ODBC API is a simple matter, whether that data is on your own hard disk in a format Visual Basic doesn't directly support or half a world away on your network. This How-To shows how easy it is to make the connection.
Even though ODBC might install the driver you need for your application, a data source name must exist before the database can be used by any application. The RegisterDatabase method provides a way to enter a new data source if one doesn't exist (or even if it does!) so that your application can use the database through ODBC.
As long as you have the correct and complete connect string to feed to ODBC, you should be able to connect with any database to which you have access. But how do you discover the exact connect string for each database? ODBC pro-vides the functionality and Visual Basic makes it easy to use, as discussed in this How-To.
Even though ODBC makes connecting with databases through a standard interface much easier, and provides some of the services itself, it still relies on database systems to do most of the work. Not all databases are created equal, with equal capabilities. You'll discover how you can find out what services a database provides, all within your applications.
ODBCDirect, like DAO (Data Access Objects) and RDO (Remote Data Objects), is a way to access databases. Unlike DAO and RDO, however, ODBCDirect is just a thin, but well-behaved, wrapper around the ODBC API. This How-To shows the first step in using this new technology by connecting to a data source and providing detailed information on the ODBCDirect Connection object.
Problem
My Visual Basic program needs access to a Microsoft SQL Server database on another computer. How do I use the ODBC Administrator to connect to that database?
Technique
Visual Basic is a very powerful tool when it comes to quick database application development. At times, however, Visual Basic's native database tools are simply not enough, such as whenever a connection to SQL Server or Oracle databases is needed. ODBC makes the gap between Visual Basic and these databases easy to navigate by providing a way to bridge the two resources with an ODBC connection.
ODBC connections usually start with a data source, an alias used by ODBC to refer an ODBC driver to a specified database so that an ODBC connection can happen. Although you can create an ODBC data source with code, it's much easier to use the ODBC Administrator, a tool specifically designed to perform data source-related tasks.
Data sources under Windows 95 and Windows NT are divided into three major types, as explained in Table 6.3. These three types make a difference in your application; the user DSN, for example, won't work with another user logged in to the same machine. The system DSN assumes that all users on the same machine have security access, and the file DSN is used on a case-by-case basis.
| TYPE | Purpose |
| System DSN | This DSN is usable by all users on a workstation, regardless of user security. |
| User DSN | The default, this DSN is usable only by certain users on a workstation (usually the user who created it). |
| File DSN | A "portable" data source, this DSN can be very useful with network-based applications. The DSN can be used by any user who has the correct ODBC driver(s) installed. |
Steps
The ODBC Administrator is a Control Panel applet, so the first step is to locate the ODBC Administrator icon.
Figure 6.2. The Control Panel, with ODBC Administrator highlighted.
Figure 6.3. ODBC Data Source Administrator property pages.
How It Works
Based on your selections, the ODBC Administrator adds entries to the Registry (or to a file, if you are creating a File DSN.) These Registry entries are vital to the ODBC drivers that require them; in many cases, the entries can determine not only the database that the driver will access but also how and by whom it will be accessed. The only other recommended method for adding ODBC information to the Registry is the RegisterDatabase method, which is covered later in this chapter. Avoid attempting to manually edit ODBC driver Registry entries without first researching the settings to ensure that you fully understand how they work.
Figure 6.4. The ODBC Microsoft Access 97 Setup dialog box.
Comments
It is important to emphasize the fact that each ODBC driver's setup dialog box will be different; if you're creating data sources by hand and using only one driver, this isn't a difficult thing to support. If, however, you really want to ensure a wide degree of compatibility for your programs, you might want to use the method detailed in How-To 6.3 to create your data sources. This technique uses the RegisterDatabase method to create data sources, and it can support a wide variety of options (even running "silently," displaying nothing to the user, if all the information needed for the data source is supplied.)
Problem
I'm using the Visual Basic Data control to process and display data in my application. However, the data I need to access is on the network, in a format that Visual Basic doesn't directly support. How can I get the data and display it in a form? Can I use the Data control?
Technique
The steps necessary to bind the Visual Basic Data control and other bound controls are simple--not that much different from connecting to one of Visual Basic's native data formats using the Jet database engine. This How-To shows exactly what is necessary to set up the controls to make the connection.
Steps
Open the Person.VBP project file. Modify the project to use an existing ODBC data source, or use the database on the CD-ROM included with this book. Having your OBDC source point to that database might make this How-To easier to follow. Run the project. Use the Visual Basic Data control's navigation buttons at the bottom of the form, as shown in Figure 6.5, to move through the database, and then click Quit when you are finished.
Figure 6.5. Chapter 6.2 example.
Private Sub Form_Load()
`Set up the form and connect to data source
Dim dbfTemp As Database, recTemp As Recordset
`Connect to the database
`Change this to your data source
dtaData.Connect = "ODBC;DSN=Personnel Database"
`Set the Data control's RecordSource property
`Change this to your table name
dtaData.RecordSource = "SELECT * FROM Contacts"
`Connect each of the text boxes with the appropriate fieldname
txtContact.DataField = "Contact"
txtName.DataField = "Name"
txtAddress1.DataField = "Addr1"
txtAddress2.DataField = "Addr2"
txtCity.DataField = "City"
txtState.DataField = "State"
txtZip.DataField = "Zip"
End Sub
Table 6.4. Objects and properties for PERSON.FRM.
OBJECT Property Setting Form Name frmODBC Caption Chapter 6.2 Example StartUpPosition 2 - CenterScreen CommandButton Name cmdQuit Caption &Quit Default True TextBox Name txtZip DataSource dtaData TextBox Name txtState DataSource dtaData TextBox Name txtCity DataSource dtaData TextBox Name txtAddress2 DataSource dtaData TextBox Name txtAddress1 DataSource dtaData TextBox Name txtName DataSource dtaData TextBox Name txtContact DataSource dtaData Data Name dtaData Align 2 - Align Bottom Caption Personnel Database RecordSource "" DefaultType 1 - UseODBC Label Name lblPerson Caption Zip: Alignment 1 - Right Justify Index 5 Label Name lblPerson Alignment 1 - Right Justify Caption State: Index 4 Label Name lblPerson Alignment 1 - Right Justify Index 3 Caption City: Label Name lblPerson Alignment 1 - Right Justify Index 2 Caption Address: Label Name lblPerson Alignment 1 - Right Justify Index 1 Caption Company: Label Name lblPerson Alignment 1 `Right Justify Index 0 Caption Contact:
Option Explicit
Private Sub cmdQuit_Click()
End
End Sub
How It Works
The preceding code is all that is required to use ODBC with Visual Basic's Data control. With the built-in navigation controls, you can move about the database.
Several important details are involved in setting up this procedure for use with ODBC. Note that in this How-To most of the setup and initialization is done in code, but you can easily set the properties of the Data control and bound text boxes when designing the form and then simply load the form. In this case, Visual Basic will make the connection for you and display the data directly, and you won't need any code in the form's Load event. To ensure that this happens smoothly, follow the steps outlined here:
Comments
The method for ODBC access presented previously is usually the first, and simplest, method employed by programmers when delving into the ODBC library. You will find, however, that for more complex applications, your needs will quickly outstrip the capabilities of the Data control. For a quick application or basic database access, though, this is a great way to start.
Problem
ODBC provides a program, ODBC Administrator, to make manual changes to a data source, but how can I install a new ODBC data source name using code? I can't make the users of the application do it, and I can't expect them to have the information to give to ODBC. Does this mean that I have to make direct calls to the ODBC API?
Technique
The RegisterDatabase method of Visual Basic is a quick and easy way to register a new data source with ODBC. The method takes four arguments: dbname, driver, silent, and attributes, which are discussed more fully later in this section. After a data source name is created, it becomes available to any application using ODBC, whether it's a Visual Basic application or not.
Steps
Open the REGISTER.VBP file. The ODBC Data Sources form loads, shown in Figure 6.6, getting the list of currently installed drivers and data source names through direct calls to the ODBC system. Enter a name for the new data source, an optional description, and the driver that ODBC will use to connect with the database. Click the New Data Source command button to add it to the ODBC system. If any additional information is necessary to make a connection to the database, another dialog box appears, prompting for any missing items. (Figure 6.4 shows the dialog box for adding a Microsoft Access data source.) The list of data sources then updates to show a current list of installed data sources.
Figure 6.6. The project's ODBC Data Sources form, displaying the data source list.
Table 6.5. Objects and properties for REGISTER.FRM.
OBJECT Property Setting Form Name frmODBC Caption ODBC Data Sources CommandButton Name cmdCreateDSN Caption &New Data Source Frame Name fraRegister Caption New Data Source TextBox Name txtDSNdesc TextBox Name txtDSNname ComboBox Name lstODBCdrivers Sorted True Style 2 - Dropdown List Label Name lblRegister Alignment 1 `Right Justify Index 2 Caption Select ODBC Driver: Label Name lblRegister Alignment 1 `Right Justify Index 1 Caption Description: Label Name lblRegister Alignment 1 `Right Justify Index 0 Caption Name: CommandButton Name cmdQuit Caption &Quit ListBox Name lstODBCdbs Sorted True TabStop 0 `False Label Name lblRegister Index 3 Caption Installed ODBC Data Sources:
Option Explicit `Dynamic arrays to hold data Dim strDBNames() As String Dim strDBDescs() As String Dim strDvrDescs() As String Dim strDvrAttr() As String
Private Sub Form_Load()
`Allocate the ODBC environment handle
If SQLAllocEnv(glng_hEnv) = SQL_SUCCESS Then
`Load the current list of data sources to list box
GetODBCdbs
`Get the list of installed drivers
GetODBCdvrs
lstODBCdrivers.ListIndex = 0
frmODBC.Show
txtDSNname.SetFocus
End If
End Sub
Private Sub cmdCreateDSN_Click()
CreateNewDSN
End Sub
Private Sub cmdQuit_Click()
End
End Sub
Private Sub GetODBCdbs()
Dim varTemp As Variant, I As Integer
lstODBCdbs.Clear
`Call the ODBCDSNList function in ODBC API Declarations.BAS.
varTemp = ODBCDSNList(glng_hEnv, True)
`If the ODBCDSNList function returns an array, populate
`the list box.
If IsArray(varTemp) Then
For I = LBound(varTemp) To UBound(varTemp)
lstODBCdbs.AddItem varTemp(I)
Next
End If
End Sub
Private Sub GetODBCdvrs()
Dim varTemp As Variant, I As Integer
cboODBCdrivers.Clear
varTemp = ODBCDriverList(glng_hEnv, True)
`If the ODBCDriverList function returns an array,
`populate the list box. If not, let the user know.
If IsArray(varTemp) Then
For I = LBound(varTemp) To UBound(varTemp)
cboODBCdrivers.AddItem varTemp(I)
Next
Else
MsgBox "No ODBC drivers installed or available.", _
vbExclamation
End If
End Sub
Sub CreateNewDSN()
`Add a new data source name to the ODBC system
Dim strDSNname As String, strDSNattr As String, strDSNdriver _
As String
Dim intResult As Integer, intSaveCursor As Integer
If txtDSNname = "" Then
MsgBox "You must enter a name for the new data source."
txtDSNname.SetFocus
Else
intSaveCursor = Screen.MousePointer
Screen.MousePointer = vbHourglass
`Format the arguments to RegisterDatabase
strDSNname = txtDSNname.text
strDSNattr = "Description=" & txtDSNdesc.text
strDSNdriver = _
cboODBCdrivers.List(cboODBCdrivers.ListIndex)
On Error GoTo CantRegister
`Trap any errors so we can respond to them
DBEngine.RegisterDatabase strDSNname, strDSNdriver, _
False, strDSNattr
On Error GoTo 0
`Now, rebuild the list of data source names
GetODBCdbs
Screen.MousePointer = intSaveCursor
End If
Exit Sub
CantRegister:
If Err.Number = 3146 Then
`ODBC couldn't find the setup driver specified
`for this database in ODBCINST.INI.
MsgBox "Cannot find driver installation DLL.", vbCritical
Resume Next
Else
MsgBox Err.Number, vbExclamation
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
Dim intResult As Integer
`Clean up the ODBC connections that we allocated
`and opened.
intResult = ODBCDisconnectDS(glng_hEnv, glng_hDbc, glng_hStmt)
intResult = SQLCFreeEnv(ghEnv)
End Sub
How It Works
By providing the RegisterDatabase method, Visual Basic takes care of many details involved in establishing a new data source in ODBC. This is the syntax for the method:
DBEngine.RegisterDatabase dbname, driver, silent, attributes
The first argument is dbname. The Visual Basic Help file describes dbname as "a string expression that is the name used in the OpenDatabase method that refers to a block of descriptive information about the data source." All true, of course, but dbname is just a descriptive name that you chose to call the data source. The name could reflect the origins of the data (such as being from an Oracle database) or the nature of the data (such as Corporate Marketing Research Data).
The driver argument is the name of the ODBC driver used to access the database. This is not the same as the name of the DLL file comprising the driver, but is instead a short, descriptive name that the author of the driver gave to it. SQL Server, Btrieve data, and Oracle are names of widely used drivers.
The third argument is silent. No, the argument isn't silent, but it is your opportunity to control whether ODBC prompts the user for more information when ODBC doesn't have enough information to make the requested connection. The options are True for no dialog boxes and False for ODBC to prompt for the missing information. If silent is set to True and ODBC can't make the connection because of a lack of information, your application will need to trap the error that will occur.
The fourth argument is attributes. Each database system you connect to has its own requirements for the information it needs in order to make a connection. For some items there is a default; for others there isn't. The more attributes you specify here, the fewer the user will need to specify. The attributes string is the string returned from the Connect property of the Data control or the Database, QueryDef, or TableDef objects after a connection is made. How-To 6.5 discusses this information in more detail and shows a way to easily obtain the exact information needed to connect with a particular database. In fact, this How-To and How-To 6.5 give you all the information you need to make an ODBC connection.
Essentially, all RegisterDatabase does is add information to the ODBC.INI file usually located in your \WINDOWS directory--with some validation routines thrown in by ODBC. It checks to make sure that you provide all the information needed to make a connection and that the database is out there someplace and is accessible.
One error that might be returned from ODBC when you use the RegisterDatabase method is The configuration DLL ([file name]) for the [driver name] could not be loaded. When you request that a new data source be established, ODBC looks in an ODBCINST.INI file, located in the same place as the ODBC.INI file, for the name of the DLL that contains the setup routines for that driver. Here are some sample lines for different drivers (there is additional information in each section for each driver):
[Microsoft Access Driver (*.mdb)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\odbcjt16.dll [Microsoft Dbase Driver (*.dbf)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\oddbse16.dll [SQL Server Driver] Driver=C:\WINDOWS\SYSTEM\sqlsrvr.dll Setup=C:\WINDOWS\SYSTEM\sqlsrvr.dll
As you can see, sometimes the setup driver is the same as the driver used for data access, but more commonly the two are different. If that driver is not available at the location specified, ODBC returns an error for the RegisterDatabase call.
Comments
One of the nice things about using ODBC is that it goes out of its way to give you the information you need in order to make the connections to databases. In this How-To, you have seen how ODBC prompts with its own dialog box if you don't give it enough information to make the connection. This is one area in which using Visual Basic to handle the conversation with ODBC doesn't hide any details from you. You'll see another example of using this ODBC feature to good advantage in How-To 6.5, in which the dialog box is used to construct connect strings that you can use directly in an application.
Perhaps, to state the obvious, it is necessary for the ODBC driver to be installed before RegisterDatabase is used. If this method only added entries to the .INI file, the driver wouldn't need to be installed before a data source was created using that driver. But because ODBC does some validation from Visual Basic in response to this method, the driver needs to be available along with the information stored in ODBC.INI and ODBCINST.INI by the driver setup program.
In response to the availability of RegisterDatabase in Visual Basic, it is logical to wonder whether there is an equivalent UnRegisterDatabase or DeleteDatabase. Alas, there is not. For that you would need to make a call to an ODBC Installer DLL function, SQLConfigDataSource, available since ODBC version 1.0. Some other interesting installation functions that were introduced with version 2.0 (SQLCreateDataSource, SQLGetAvailableDrivers, SQLGetInstalledDrivers, and SQLManageDataSources) give finer control over the ODBC setup. Driver manufacturers use these and other functions to install drivers and ODBC itself if necessary, but any application can use them. Information about these functions is available in the Microsoft ODBC 3.0 Programmer's Reference and SDK Guide.
Another Visual Basic property that is useful in connection with RegisterDatabase is the Version property. Version is a property of both the Database object and the DBEngine object. When returned from the Database object, Version identifies the data format version of the object, usually as a major and minor version number, such as 4.03. This gives you one more piece of information about the different components making up your applications.
Problem
I'm trying to use an ODBC driver to open a data source that is on the network, but the driver documentation is not very helpful regarding the information needed to make a connection. I need my application to make the connection (if it is at all possible) without requiring users to make decisions or respond to dialog boxes. How can I get the right connect string without wasting time by guessing? If the connection can't be made, why not?
Technique
As long as you are able to tell Visual Basic that you want to make some sort of connection through ODBC, all you need to do is make a call to Visual Basic's OpenDatabase method with certain default arguments. ODBC responds by prompting for information about what data source you want (from those data sources installed on the system). Then you can define a temporary QueryDef, make the connection, and examine the Visual Basic Connect property. The Connect property at that point contains the fully formed connect string required to make a connection to that data source. The string stored in the Connect property can be copied and used directly in future attempts to connect to the database.
Steps
Open and run the CONNECT.VBP Visual Basic project file. The Retrieve ODBC Connect String window opens, as shown in Figure 6.7. Click on the Connect to Data Source command button, and the ODBC SQL Data Sources window appears, prompting you to select an installed data source name. Visual Basic and ODBC obtain from that data source a list of available tables and put them in the Tables Available list box on the main form. Either double-click on one of the tables or select one and click the Get Connect String command button. The application establishes a connection to that database table and returns the complete connect string, placing it in the Connect String text box, as shown in Figure 6.8. Click the Copy Connect String command button to put the string on the Windows clipboard, and then paste it into your application.
Figure 6.7. The project's main form on startup.
Figure 6.8. The project's main form, after ODBC connect string retrieval.
Table 6.6. Objects and properties for CONNECT.FRM.
OBJECT Property Setting Form Name frmODBC Caption Chapter 6.4 Example CommandButton Name cmdCopyConnect Caption Cop&y Connect String Enabled 0 - False CommandButton Name cmdGetConnect Caption &Get Connect String Enabled 0 - False CommandButton Name cmdQuit Caption &Quit TextBox Name txtConnect MultiLine True ScrollBars 2 - Vertical TabStop False CommandButton Name cmdConnect Caption &Connect to Data Source ListBox Name lstTables Sorted True Label Name lblConnect Index 0 Caption Connect String: Label Name lblConnect Index 1 Caption &Tables Available:
Option Explicit `Module level globals to hold connection info Dim dbfTemp As Database, recTemp As Recordset
Private Sub Form_Load()
`Log on to an ODBC data source
`First, allocate ODBC memory and get handles
Dim intResult As Integer
`Allocate the ODBC environment handle
If SQLAllocEnv(glng_hEnv) <> SQL_SUCCESS Then End
intResult = SQLAllocConnect(glng_hEnv, glng_hDbc)
If intResult <> SQL_SUCCESS Then
intResult = frmODBCErrors.ODBCError("Dbc", glng_hEnv, & _
glng_hDbc, 0, intResult, "Error allocating connection _
handle.")
End
End If
frmODBC.Show
End Sub
Set dbfTemp = OpenDatabase("", False, False, "ODBC;")
Private Sub cmdConnect_Click()
`Connect to a data source and populate lstTables
Dim I As Integer
Dim strConnect As String
Dim tbfTemp As TableDef
Screen.MousePointer = vbHourglass
lstTables.Clear
On Error GoTo ErrHandler
Set dbfTemp = OpenDatabase("", False, False, "ODBC;")
On Error GoTo 0
For Each tbfTemp In dbfTemp.TableDefs
lstTables.AddItem tbfTemp.Name
Next
Screen.MousePointer = vbDefault
If lstTables.ListCount Then
cmdGetConnect.Enabled = True
Else
MsgBox "No tables are available. " & _
"Please connect to another data source."
End If
Exit Sub
ErrHandler:
Screen.MousePointer = vbDefault
Select Case Err.Number
Case 3423
`This data source can't be attached, (or the
`user clicked Cancel, so use ODBC API
APIConnect
Case 3059
`The user clicked on Cancel
Exit Sub
Case Else
`The error is something else, so send it back to
`the VB exception handler
MsgBox Err.Number, vbExclamation
End Select
End Sub
Figure 6.9. The Select Data Source selection dialog box.
Screen.MousePointer = vbHourglass
txtConnect.text = ""
If Len(lstTables.text) Then
Set recTemp = dbfTemp.OpenRecordset(lstTables.text)
txtConnect.text = AddSpaces(dbfTemp.Connect)
Else
MsgBox "Please select a table first."
End If
cmdCopyConnect.Enabled = True
Screen.MousePointer = vbDefault
End Sub
Function AddSpaces (strC As String)
Dim I As Integer
Dim strNewStr As String, strNextChar As String
Dim strNextChar As String
For I = 1 To Len(strC)
strNextChar = Mid$(strC, I, 1)
If strNextChar = ";" Then
strNewStr = strNewStr & strNextChar & " "
Else
strNewStr = strNewStr & strNextChar
End If
Next
AddSpaces = strNewStr
End Function
Private Sub cmdCopyConnect_Click()
`Select the text in txtConnect
With txtConnect
.SetFocus: .SelStart = 0: .SelLength = _
Len(txtConnect.text)
End With
` Copy selected text to Clipboard.
Clipboard.SetText Screen.ActiveControl.SelText
End Sub
Private Sub cmdQuit_Click() End End Sub Private Sub Form_Unload(Cancel As Integer) Dim intResult As Integer intResult = ODBCDisconnectDS(glng_hEnv, glng_hDbc, glng_hStmt) intResult = ODBCFreeEnv(glng_hEnv) End Sub
Private Sub lstTables_DblClick() cmdGetConnect_Click End Sub
Sub APIConnect()
`Can't connect through VB, so go direct
Dim intResult As Integer
Dim strConnectIn As String
Dim strConnectOut As String * SQL_MAX_OPTION_STRING_LENGTH
Dim intOutCount As Integer
strConnectIn = ""
intResult = SQLDriverConnect(glng_hDbc, Me.hWnd, _
strConnectIn,
Len(strConnectIn), strConnectOut, Len(strConnectOut), _
intOutCount, SQL_DRIVER_PROMPT)
If intResult <> SQL_SUCCESS Then
intResult = frmODBCErrors.ODBCError("Dbc", glng_hEnv, _
glng_hDbc, 0, _
intResult, "Problem with call to SQLDriverConnect.")
Exit Sub
End If
txtConnect.text = AddSpaces(strConnectOut)
`Free the connection, but not the handle
intResult = SQLDisconnect(glng_hDbc)
If intResult <> SQL_SUCCESS Then
intResult = frmODBCErrors.ODBCError("Dbc", glng_hEnv, _
glng_hDbc, 0, _
intResult, "Problem with call to SQLDriverConnect.")
End If
cmdCopyConnect.Enabled = True
End Sub
How It Works
Three functions are available in the ODBC API for making a connection to a data source: SQLConnect, SQLBrowseConnect, and SQLDriverConnect. Table 6.7 explains these functions in more detail.
| FUNCTION | Version | Conformance | Primary Arguments |
| SQLConnect | 1.0 | Core | hDbc, data source name, user ID, authorization string |
| SQLDriverConnect | 1.0 | 1 | hDbc, Windows handle (hwnd), connect string in, connect string out, completion option |
| SQLBrowseConnect | 1.0 | 2 | hDbc, connect string in, connect string out |
SQLConnect
SQLConnect is the standard way of connecting to an ODBC data source. All the arguments
must be complete and correct because if anything is wrong, ODBC generates an error.
If everything is right, a connection is established. Valid
return codes are SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, and SQL_INVALID_HANDLE.
Because this function is in the Core conformance level, all ODBC drivers are guaranteed
to support it (or as guaranteed as possible with drivers written by third-party developers
attempting to adhere to a standard), so it is always available. The only flexibility
that SQLConnect provides is when the specified data source name can't be found. In
that case, the function looks for a default driver and loads that one if it is defined
in ODBC.INI. If not, SQL_ERROR is returned, and you can obtain more information about
the problem with a call to SQLError. This is the workhorse function of ODBC connections.
SQLDriverConnect
SQLDriverConnect offers a bit more flexibility for making ODBC connections. This function can handle data sources that require more information than the three arguments of SQLConnect (other than the connection handle hDbc, which all three functions require). SQLDriverConnect provides dialog boxes to prompt for any missing information needed for the connection, and it can handle connections not defined in the ODBC.INI file or registry. SQLDriverConnect provides three connection options:
When a connection is successfully made, the function returns SQL_SUCCESS and returns a completed connection string that can be used to make future connections to that database. It is a pretty safe bet that SQLDriverConnect is the function that Visual Basic uses when this How-To is employed to discover the connect string.
SQLDriverConnect can return SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_ERROR, or SQL_INVALID_HANDLE. Valid choices for the completion option argument are SQL_DRIVER_PROMPT, SQL_DRIVER_COMPLETE, SQL_DRIVER_COMPLETE_REQUIRED, and SQL_DRIVER_NOPROMPT, as described here:
SQLBrowseConnect
The third function, SQLBrowseConnect, is perhaps the most interesting of the three functions. A call to this function initiates an interactive method of discovering what it takes to connect to a particular database. Each time SQLBrowseConnect is called, the function returns additional attributes that are needed to make a connection. An application making the call can parse out the resulting string containing missing attributes (which are marked as required or optional) and return successively more complete connect strings. Attributes that involve selection from a fixed list of items are returned as that full list so that an application can present a list box of choices to the user.
The bad news, for Visual Basic anyway, is that SQLBrowseConnect is a Conformance Level 2 function. Because Visual Basic is designed to require only Level 1 drivers, it doesn't have any functions that can directly use this function. But it is available to any application, including those written in Visual Basic, through direct calls to the ODBC API, if the driver supports Level 2 conformance.
Comments
As mentioned in the introduction to the chapter, you can't make an ODBC connection through an attached table to a database that Visual Basic natively supports, such as a Microsoft Access .MDB file or the ISAM databases Btrieve and dBASE. There normally isn't any reason to do so, although you can always do it by using the ODBC API directly.
Problem
I'd like to be able to connect with the different data sources scattered throughout our network. But all the various drivers have different capabilities, even though they are all accessible through ODBC. How can I find out through code which services are available for each server and keep the code as flexible and portable as possible?
Technique
This example develops a useful ODBC viewer that gathers in one place much of the information needed to evaluate the data sources and drivers available to a particular workstation. You can use the same techniques whether the data is located on a single computer with one hard disk or connected to a network with data of widely varying formats on different hardware.
Steps
Open and run the Visual Basic SERVICES.VBP project. Select an ODBC data source name from the Installed ODBC Data Sources list, and then click the Get Functions command button. After a moment, a list of the functions that can be used with the data source appears in the bottom half of the form, similar to the form shown later in Figure 6.11. Scroll through the list to see the functions available. Then, with the same data source highlighted, click the Get Properties command button. The Get Info window appears, as shown in Figure 6.10. Make the selections you want (from one to all the items in the list), and click the Get Info command button. The list of properties and their current values appears in the Biblio Properties window, as shown in Figure 6.11.
Figure 6.10. The project's Get Info form, showing the selected properties.
Figure 6.11. The project's Details form, showing the available functions.
Table 6.8. Objects and properties for SERVICES.FRM.
OBJECT Property Setting Form Name frmODBC Caption Chapter 6.5 Example - Details CommandButton Name cmdProperties Caption Get Properties CommandButton Name cmdFunctions Caption Get Functions CommandButton Name cmdQuit Caption Quit ListBox Name lstODBCdbs Sorted True Label Name lblGrid Caption Properties BorderStyle 1 - Fixed Single Grid Name grdResults Visible False Scrollbars 2 - flexScrollBarVertical Highlight 0 - flexHighlightNever Label Name lblServices Caption Installed ODBC Data Sources:
Option Explicit `Dynamic arrays to hold data Dim strDBNames() As String
Private Sub Form_Load()
`Log on to an ODBC data source
`First, allocate ODBC memory and get handles
Dim intResult As Integer
`Allocate the ODBC environment handle
If ODBCAllocateEnv(glng_hEnv) = SQL_SUCCESS Then
`Load the current list of data sources to list box
GetODBCdbs
`Show the form
frmODBC.Show
Else
End
End If
End Sub
Private Sub GetODBCdbs()
Dim varTemp As Variant, I As Integer
lstODBCdbs.Clear
varTemp = ODBCDSNList(glng_hEnv, False)
If IsArray(varTemp) Then
For I = LBound(varTemp) To UBound(varTemp)
lstODBCdbs.AddItem varTemp(I)
Next
Else
MsgBox "No ODBC data sources to load!", vbCritical
End
End If
End Sub
Private Sub cmdFunctions_Click()
Dim strDataSource As String
Dim strUserName As String, strPassword As String
Dim intResult As Integer, intErrResult As Integer
ReDim intFuncList(100) As Integer
Dim I As Integer, j As Integer
`First, check to see if anything is selected
`If not, notify user, then return to form.
If lstODBCdbs.ListIndex >= 0 Then
Screen.MousePointer = vbHourglass
strDataSource = lstODBCdbs.List(lstODBCdbs.ListIndex)
If SQLAllocStmt(glng_hDbc, glng_hStmt) Then _
intResult = ODBCConnectDS(glng_hEnv, glng_hDbc, _
glng_hStmt, strDataSource, strUserName, _
strPassword)
If intResult = SQL_SUCCESS Then _
intResult = SQLGetFunctions(glng_hDbc, _
SQL_API_ALL_FUNCTIONS, intFuncList(0))
If intResult <> SQL_SUCCESS Then
intErrResult = frmODBCErrors.ODBCError("Dbc", _
glng_hEnv,
glng_hDbc, 0, intResult, _
"Error getting list of ODBC functions")
Else
`Run through the array and get the number of functions
j = 0
For I = 0 To 99
If intFuncList(I) Then j = j + 1
Next
`Start by clearing the frmODBC grid
With frmODBC.grdResults
.Rows = j
.Cols = 3
.FixedCols = 1
.FixedRows = 0
.ColWidth(0) = 8
.ColWidth(1) = 0.65 * frmODBC.grdResults.Width
.ColWidth(2) = 0.35 * frmODBC.grdResults.Width
End With
lblGrid.Caption = lstODBCdbs.text & ": " & _
Trim(Val(j)) & _
" Functions"
`Populate the grid with the function names
j = 0
For I = 0 To 99
If intFuncList(I) <> 0 Then
With frmODBC.grdResults
.Row = j
.Col = 0: .text = j
.Col = 1: .text = ODBCFuncs(0, I)
.Col = 2: .text = ODBCFuncs(1, I)
End With
j = j + 1
End If
Next
`Move to the top row
frmODBC.grdResults.Row = 0
frmODBC.grdResults.Col = 1
`free the data source connection
intResult = ODBCDisconnectDS(glng_hEnv, glng_hDbc, _
SQL_NULL_HSTMT)
Screen.MousePointer = vbDefault
frmODBC.grdResults.Visible = True
End If
Else
MsgBox "Please select a data source name first.", _
vbCritical, "ODBC Functions"
End If
End Sub
Private Sub cmdProperties_Click()
Dim intResult As Integer
If lstODBCdbs.ListIndex < 0 Then
MsgBox "Please select a data source name first.", _
vbCritical, "ODBC Properties"
Else
intResult = ODBCConnectDS(glng_hEnv, glng_hDbc, _
glng_hStmt, lstODBCdbs.text, "", "")
If intResult = SQL_SUCCESS Then Load frmGetInfo
End If
End Sub
Private Sub cmdQuit_Click()
End
End Sub
Private Sub Form_Unload(Cancel As Integer)
Dim intResult As Integer
intResult = ODBCDisconnectDS(glng_hEnv, glng_hDbc, glng_hStmt)
intResult = ODBCFreeEnv(glng_hEnv)
End Sub
Private Function convCh(inChar As String, num As Variant)
inChar = LTrim$(Left$(inChar, num))
Select Case inChar
Case "Y"
convCh = "Yes"
Case "N"
convCh = "No"
Case Else
convCh = inChar
End Select
End Function
Table 6.9. Objects and properties for GETINFO.FRM.
OBJECT Property Setting Form Name frmGetInfo Caption Chapter 6.5 Example - Get Info TextBox Name txtStatus Text Select the options you want to include. CommandButton Name cmdCancel Cancel True Caption Cancel CommandButton Name cmdGetInfo Caption Get Info CommandButton Name cmdSelection Caption Unselect All CommandButton Name cmdSelection Caption Select All ListBox Name lstGetInfoData MultiSelect Extended Sorted True Label Name lblGetInfo
Option Explicit
Type GetInfo
InfoType As String
ReturnType As String
End Type
Private Sub Form_Load()
`Load the list box with the ODBCGetInfo array
Dim I As Integer
For I = 0 To SQL_INFO_LAST
If ODBCGetInfo(I).InfoType <> "" Then
lstGetInfoData.AddItem ODBCGetInfo(I).InfoType
End If
Next
frmGetInfo.Show
End Sub
Private Sub cmdGetInfo_Click() Dim intSelCount As Integer `count of selected items Dim I As Integer, j As Integer Dim ri As Integer Dim rs As String * 255 Dim rb As Long, rl As Long Dim lngInfoValue As Long Dim lngInfoValueMax As Integer, intInfoValue As Integer, _ intResult As Integer Dim intConnIndex As Integer Dim strTemp As String, strID As String, strErrMsg As String Dim strRowData() As String lngInfoValueMax = 255 `Get the number of rows selected and the type of data intSelCount = 0 For I = 0 To lstGetInfoData.ListCount - 1 If lstGetInfoData.Selected(I) Then ReDim Preserve strRowData(intSelCount + 1) strRowData(intSelCount) = lstGetInfoData.List(I) intSelCount = intSelCount + 1 End If Next If intSelCount = 0 Then MsgBox "No attributes were selected. Please select " & _ "at least one and try again.", vbExclamation Exit Sub End If `Start by clearing the frmODBC grid With frmODBC.grdResults .Rows = intSelCount + 1: .Cols = 3 .FixedCols = 1: .FixedRows = 1 .ColWidth(0) = 8 .ColWidth(1) = 0.45 * frmODBC.grdResults.Width .ColWidth(2) = 0.55 * frmODBC.grdResults.Width .Row = 0 .Col = 1: .text = "Attribute Constant" .Col = 2: .text = "Value" End With frmODBC.lblGrid.Caption = frmODBC.lstODBCdbs.text & " " & _ "Properties" For I = 0 To intSelCount - 1 With frmODBC.grdResults .Row = I + 1 .Col = 0: .text = I + 1 .Col = 1: .text = strRowData(I) .Col = 2 End With
`Get the index of ODBConn - have to do it this way `because there are gaps in the ODBC constants For j = LBound(ODBCGetInfo) To UBound(ODBCGetInfo) If strRowData(I) = ODBCGetInfo(j).InfoType Then Exit _ For Next `Format the data according the return type of `ODBCGetInfo Select Case Left$(ODBCGetInfo(j).ReturnType, 1) Case "S" `String intResult = SQLGetInfo(glng_hDbc, j, ByVal rs, _ Len(rs), intInfoValue) If Len(Trim$(ODBCGetInfo(j).ReturnType)) > 1 Then frmODBC.grdResults.text = _ SpecialStr(strRowData(I), _ Trim$(rs)) Else frmODBC.grdResults.text = Trim$(rs) End If Case "B" `32-bit Bitmask intResult = SQLGetInfo(glng_hDbc, j, rb, 255, _ intInfoValue) frmODBC.grdResults.text = BitMask(rb) Case "I" `Integer intResult = SQLGetInfo(glng_hDbc, j, ri, 255, _ intInfoValue) If Len(Trim$(ODBCGetInfo(j).ReturnType)) > 1 Then frmODBC.grdResults.text = _ SpecialInt(strRowData(I), _ Trim$(ri)) Else frmODBC.grdResults.text = ri End If Case "L" `Long intResult = SQLGetInfo(glng_hDbc, j, rl, 255, _ intInfoValue) If Len(Trim$(ODBCGetInfo(j).ReturnType)) > 1 Then frmODBC.grdResults.text = _ SpecialLong(strRowData(I), _ Trim$(rl)) Else frmODBC.grdResults.text = rl End If Case Else `Error in array frmODBC.grdResults.text = "Error processing _ return value." End Select If intResult <> SQL_SUCCESS Then frmODBC.grdResults.text = "Error getting data." End If Next frmODBC.grdResults.Visible = True Unload Me End Sub
Private Function SpecialStr(Opt As String, RetStr As String) `Do any special processing required for a SQLGetInfo string Select Case Opt Case "SQL_ODBC_SQL_OPT_IEF" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_COLUMN_ALIAS" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_KEYWORDS" SpecialStr = "List of keywords." `&&& Case "SQL_ORDER_BY_COLUMNS_IN_SELECT" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_MAX_ROW_SIZE_INCLUDES_LONG" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_EXPRESSIONS_IN_ORDERBY" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_MULT_RESULT_SETS" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_OUTER_JOINS" Select Case RetStr Case "N" SpecialStr = "No outer joins." Case "Y" SpecialStr = "Yes, left-right segregation." Case "P" SpecialStr = "Partial outer joins."Case "F"
SpecialStr = "Full outer joins." Case Else SpecialStr = "Missing data." End Select Case "SQL_NEED_LONG_DATA_LEN" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_LIKE_ESCAPE_CLAUSE" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_ACCESSIBLE_PROCEDURES" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_ACCESSIBLE_TABLES" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_DATA_SOURCE_READ_ONLY" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_PROCEDURES" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case "SQL_ROW_UPDATES" SpecialStr = IIf(RetStr = "Y", "Yes", "No") Case Else SpecialStr = "Missing special processing." End Select End Function
Private Function SpecialInt(Opt As String, RetInt As Integer)
`Do any special processing required for a SQLGetInfo integer
Select Case Opt
Case "SQL_CORRELATION_NAME"
Select Case RetInt
Case SQL_CN_NONE
SpecialInt = "Not supported."
Case SQL_CN_DIFFERENT
SpecialInt = "Supported but names vary."
Case SQL_CN_ANY
SpecialInt = "Any valid user name."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_NON_NULLABLE_COLUMNS"
Select Case RetInt
Case SQL_NNC_NULL
SpecialInt = "All columns nullable."
Case SQL_NNC_NON_NULL
SpecialInt = "May be non-nullable."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_FILE_USAGE"
Select Case RetInt
Case SQL_FILE_NOT_SUPPORTED
SpecialInt = "Not a single tier driver."
Case SQL_FILE_TABLE
SpecialInt = "Treats data source as table."
Case SQL_FILE_QUALIFIER
SpecialInt = "Treats data source as" _
"qualifier."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_NULL_COLLATION"
Select Case RetInt
Case SQL_NC_END
SpecialInt = "NULLs sorted to end."
Case SQL_NC_HIGH
SpecialInt = "NULLs sorted to high end."
Case SQL_NC_LOW
SpecialInt = "NULLs sorted to low end."
Case SQL_NC_START
SpecialInt = "NULLs sorted to start."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_GROUP_BY"
Select Case RetInt
Case SQL_GB_NOT_SUPPORTED
SpecialInt = "Group By not supported."
Case SQL_GB_GROUP_BY_EQUALS_SELECT
SpecialInt = _
"All non-aggregated columns, no others."
Case SQL_GB_GROUP_BY_CONTAINS_SELECT
SpecialInt = _
"All non-aggregated columns, some others."
Case SQL_GB_NO_RELATION
SpecialInt = "Not related to select list."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_IDENTIFIER_CASE"
Select Case RetInt
Case SQL_IC_UPPER
SpecialInt = "Upper case."
Case SQL_IC_LOWER
SpecialInt = "Lower case."
Case SQL_IC_SENSITIVE
SpecialInt = "Case sensitive."
Case SQL_IC_MIXED
SpecialInt = "Mixed case."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_QUOTED_IDENTIFIER_CASE"
Select Case RetInt
Case SQL_IC_UPPER
SpecialInt = "Upper case."
Case SQL_IC_LOWER
SpecialInt = "Lower case."
Case SQL_IC_SENSITIVE
SpecialInt = "Case sensitive."
Case SQL_IC_MIXED
SpecialInt = "Mixed case."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_ODBC_API_CONFORMANCE"
Select Case RetInt
Case SQL_OAC_NONE
SpecialInt = "No conformance."
Case SQL_OAC_LEVEL1
SpecialInt = "Level 1 supported."
Case SQL_OAC_LEVEL2
SpecialInt = "Level 2 supported."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_CURSOR_COMMIT_BEHAVIOR"
Select Case RetInt
Case SQL_CB_DELETE
SpecialInt = "Close and delete statements."
Case SQL_CB_CLOSE
SpecialInt = "Close cursors."
Case SQL_CB_PRESERVE
SpecialInt = "Preserve cursors."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_CURSOR_ROLLBACK_BEHAVIOR"
Select Case RetInt
Case SQL_CB_DELETE
SpecialInt = "Close and delete statements."
Case SQL_CB_CLOSE
SpecialInt = "Close cursors."
Case SQL_CB_PRESERVE
SpecialInt = "Preserve cursors."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_TXN_CAPABLE"
Select Case RetInt
Case SQL_TC_NONE
SpecialInt = "Transactions not supported."
Case SQL_TC_DML
SpecialInt = _
"DML statements only, DDL cause error."
Case SQL_TC_DDL_COMMIT
SpecialInt = _
"DML statements, DDL commit transaction."
Case SQL_TC_DDL_IGNORE
SpecialInt = "DML statements, DDL ignored."
Case SQL_TC_ALL
SpecialInt = "Both DML and DDL statements."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_QUALIFIER_LOCATION"
Select Case RetInt
Case SQL_QL_START
SpecialInt = "Start of name."
Case SQL_QL_END
SpecialInt = "End of name."
Case Else
SpecialInt = "Missing data."
End Select
Case "SQL_CONCAT_NULL_BEHAVIOR"
Select Case RetInt
Case SQL_CB_NULL
SpecialInt = "Result is NULL valued."
Case SQL_CB_NON_NULL
SpecialInt = _
"Result is non-NULL concatenation."
Case Else
SpecialInt = "Missing data."
End Select
Case Else
SpecialInt = "Missing special integer processing."
End Select
End Function
Private Function BitMask(RetBit As Long)
`Do processing required for a SQLGetInfo bit mask return
Dim i As Long, bin As String
Const maxpower = 30 ` Maximum number of binary digits
` supported.
bin = "" `Build the desired binary number in this string,
`bin.
If RetBit > 2 ^ maxpower Then
BitMask = "Error converting data."
Exit Function
End If
` Negative numbers have "1" in the 32nd left-most digit:
If RetBit < 0 Then bin = bin + "1" Else bin = bin + "0"
For i = maxpower To 0 Step -1
If RetBit And (2 ^ i) Then ` Use the logical "AND"
` operator.
bin = bin + "1"
Else
bin = bin + "0"
End If
Next
BitMask = bin ` The bin string contains the binary number.
End Function
Private Function SpecialLong(Opt As String, RetInt As Integer)
`Do any special processing required for a SQLGetInfo long
Select Case Opt
Case "SQL_DEFAULT_TXN_ISOLATION"
Select Case RetInt
Case SQL_TXN_READ_UNCOMMITTED
SpecialLong = _
"Dirty reads, nonrepeatable, phantoms."
Case SQL_TXN_READ_COMMITTED
SpecialLong = _
"No dirty reads, but nonrepeatable " & _
"and phantoms."
Case SQL_TXN_REPEATABLE_READ
SpecialLong = _
"No dirty or nonrepeatable reads." &
"Phantoms okay."
Case SQL_TXN_SERIALIZABLE
SpecialLong = "Serializable transactions."
Case SQL_TXN_VERSIONING
SpecialLong = _
"Serializable transactions with higher " &
"concurrency."
Case Else
SpecialLong = "Missing data."
End Select
Case Else
SpecialLong = "Missing special Long processing."
End Select
End Function
Private Sub cmdSelection_Click(Index As Integer)
`Select all of the items in the list
Dim I As Integer
For I = 0 To lstGetInfoData.ListCount - 1
lstGetInfoData.Selected(I) = (Index > -1)
Next
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
How It Works
The SQLGetInfo function is a versatile way to get lots of information about an ODBC data source. In a typical application, you would check for a small number of properties, or if a particular function is implemented, instead of retrieving bulk results as in this How-To, you would use the techniques shown here.
Step 13 of this How-To performs and deciphers the SQLGetInfo through a procedure that appears daunting, but in fact is really doing only two main jobs: getting a count and a list of the SQLGetInfo options that have been selected in the list box and then looping through to get their current settings, populating the grid control on the frmODBC form with the results. Let's break this procedure down into more manageable chunks because this procedure is important for you to understand.
After the variables used in the procedure are declared, the lstGetInfoData list box is looped through to find out what selections have been made by the user. For each selection, the RowData dynamic array is expanded by one element, and the name of the option is added. That way, the array will be fully populated with the names of the options selected. The variable selCount keeps a running count of how many options have been selected.
After that process is complete, the options are checked to see whether any were selected. If not, the user is asked to make at least one selection and then try again. There is no reason to proceed if there is nothing to do.
Next, the code clears the grdResults grid control in the frmODBC form, setting it up with three columns and rows equal to intSelCount + 1. One additional row is necessary for column headings.
The real work of the procedure begins, looping through each of the options and actually making the call to the SQLGetInfo ODBC function. This function returns the current setting for the data source for a selected option. Two things make the code a bit more complex. First, an integer needs to be passed to SQLGetInfo representing an index into the attribute or option to be checked. To get that index, loop through the ODBCGetInfo array, comparing the InfoType member to the name in strRowData, until there is a match. Because the names in strRowData came from ODBCInfo in the first place, there will be a match somewhere.
The second complexity arises from the types of values returned from SQLGetInfo. Table 6.10 lists the possible return types.
| TYPE | InfoType | Description |
| String | S | C type NULL-terminated string |
| Bitmask | B | 32-bit, usually with multiple meanings |
| Integer | I | Standard Visual Basic 16-bit number |
| Long | L | Standard Visual Basic 32-bit number |
The InfoType column refers to the InfoType member of the GetInfo structure defined in ODBCAPI.BAS. This is simply an arbitrarily chosen code for use in the Select Case in this procedure so that SQLGetInfo can be called with the right variable type to receive the results.
The InfoType member can be either one or two characters long. The second character, if present, means that some special processing is necessary to make the result meaningful when it is put in the grid on the frmODBC form.
The Select Case structure then puts the results directly into the frmODBC grid.
Some of the property values returned might not be available, in which case the cmdGetInfo Click event procedure places a value of Error Getting Data in the results grid. You might get this result for many reasons, but the primary reason exposes one of the quirks of the ODBC system. Although ODBC has some rather specific demarcations between conformance levels (drivers must be at a Conformance Level 1 to be usable with Visual Basic), there is no guarantee that a driver will implement all the functionality of a given function. SQLGetInfo is no exception to this rule, unfortunately.
One way to determine whether this is the case is to make a call to SQLError (or the ODBCError wrapper function) to obtain more information about the error. In any event, it is safe to assume in most cases that the particular attribute should not be used with the particular data source.
As noted in step 13 of this How-To, the syntax of the SQLGetInfo function is this:
SQLGetInfo(hDbc, fInfoType, rgbInfoValue, cbInfoValueMax, pcbInfoValue)
Table 6.11 shows the arguments to the function.
| ARGUMENT | Description |
| hDbc | Connection handle |
| fInfoType | Type of information (in this How-To, from the ODBCGetInfo array) |
| rgbInfoValue | Variable of the proper type to store results |
| cbInfoValueMax | Maximum length of the rgbInfoValue buffer |
| pcbInfoValue | Total number of bytes available to return in rgbInfoValue |
The syntax of SQLGetFunctions is this:
SQLGetFunctions(hDbc, fFunction, pfExists)
Table 6.12 shows the arguments to the function.
| ARGUMENT | Description |
| hDbc | Connection handle |
| fFunction | The particular function or, in this How-To, SQL_API_ALL_FUNCTIONS |
| pfExists | For SQL_API_ALL_FUNCTIONS, an array with 100 elements for output |
If you look at the contents of the ODBCAPI.BAS file, you'll see two functions, LoadGetInfo and ODBCLoadFuncs, that load global arrays with information about the SQLGetInfo property options and the list of functions available in ODBC. These two arrays are used to provide selection lists for the program in this How-To and loop through to make the actual calls to SQLGetInfo. The SQLGetInfo function has many property options, too numerous to describe here--see the ODBC SDK for a more detailed description of the property options and in which ODBC version they first appeared.
What if a driver is written only to the Core conformance level? Well, in a way, that isn't a problem because you won't be using that driver with Visual Basic anyway: Visual Basic counts on Level 1 conformance to interact with ODBC. Running the program in this How-To will provide an ODBC error, and you are finished. You can still use the driver, but only by making direct calls to the ODBC API from Visual Basic. In that situation, you'll need to consult the driver's documentation to find out what it can and cannot do. As a practical matter, by far and away most drivers are at least Level 1 conformance, so this will rarely be a problem.
The nice thing about the SQLGetFunctions function is that, even though it is a conformance Level 1 function, it is implemented in the ODBC Driver Manager, which sits between all applications using ODBC and the ODBC driver. That way, if the driver doesn't implement SQLGetFunctions, the Driver Manager will still give a list. If the driver does implement the function, the Driver Manager passes the call to the driver.
Comments
Understanding the SQLGetInfo and SQLGetFunctions functions is an extremely important part of understanding the ODBC API. Before moving to another How-To, experiment with the use of these functions, especially between different ODBC drivers, to get a better understanding of how varied different drivers can be in terms of functionality.
Problem
My large application has been recently converted from DAO to ODBC, and the design specifications call for direct ODBC access. How can I get the power of ODBC with the ease of DAO programming in Visual Basic?
Technique
Well, here's good news. An extension of the DAO, called ODBCDirect, allows direct ODBC connection capability, with most of the flexibility of the DAO objects intact. ODBCDirect provides a Connection object, analogous to the DAO's Database object. It even has a Database property to simulate the Database object for your needs. The Connection object is the most important piece of the ODBCDirect object hierarchy, so that will be the focus of this example.
Steps
Open and run the ODBCDirect.VBP Visual Basic project file. Click the Open DSN button, and choose an ODBC data source. The form then opens a Connection object and displays the object's properties for the data source, similar to those shown in Figure 6.12.
Figure 6.12. Chapter 6.6 example.
Table 6.13. Objects and properties for the ODBCDirect form.
OBJECT Property Setting Form Name frmODBC Caption Chapter 6.6 Example TextBox Name txtProperties ScrollBars 2 - Vertical MultiLine True Locked True Font Courier Font.Size 10 CommandButton Name cmdOpen Caption &Open DSN CommandButton Name cmdClose Caption &Close Label Name lblTables Caption No information available
Option Explicit Dim conTemp As Connection
Private Sub Form_Load()
`Notice the dbUseODBC parameter; this determines that DBEngine
`will create an ODBCDirect workspace by default.
DBEngine.DefaultType = dbUseODBC
End Sub
Private Sub cmdOpen_Click()
`Let's create a Connection object. This line will force
`the ODBC driver to prompt the user.
`The ODBCDirect Connection object is identical, in terms of
`DAO object hierarchy, to the Database object.
Set conTemp = Workspaces(0).OpenConnection("", , False, _
"ODBC;")
`If open, let's get the TableDefs from the Database
`property of the Connection object.
If IsObject(conTemp) Then
`Since the Connection object does not support a
`Properties collection, we must iterate through
`each property manually.
lblTables = "Information - ODBCDirect connection to " & _
conTemp.Name & ":"
With conTemp
txtProperties = "Connect " & .Connect
` Property actually returns a Database object.
txtProperties = txtProperties & vbCrLf & _
"Database[.Name]: " & .Database.Name
txtProperties = txtProperties & vbCrLf & _
"Name: " & .Name
txtProperties = txtProperties & vbCrLf & _
"QueryTimeout: " & .QueryTimeout
txtProperties = txtProperties & vbCrLf & _
"RecordsAffected: " & .RecordsAffected
txtProperties = txtProperties & vbCrLf & _
"StillExecuting: " & .StillExecuting
txtProperties = txtProperties & vbCrLf & _
"Transactions: " & .Transactions
txtProperties = txtProperties & vbCrLf & _
"Updatable: " & .Updatable
End With
End If
End Sub
Private Sub cmdClose_Click()
End
End Sub
How It Works
The DBEngine is initialized with the dbUseODBC flag, which tells DAO that all the workspaces, connections, and so on will be generated via ODBCDirect, rather than through the DAO. Note that it's not an either-or situation; the same property exists on Workspace objects. A Workspace object can be created and then flagged for use with ODBCDirect, so DAO and ODBCDirect workspaces can exist together. This, by the way, proves very useful for projects involving data conversion or for communication with server and mainframe databases. After the DBEngine is initialized and the user presses the Open DSN button, the Connection object is created. After successful creation, the form prints each property of the Connection object (laboriously--the Connection object supports neither the Properties nor TableDefs collection) to the text box for your perusal.
The lack of a TableDefs collection can make it difficult to maneuver around a database, but this can be surmounted by workarounds. For example, Microsoft SQL Server allows a user to query the SysTables table, which maintains a list of tables in the database. It might take some doing, but you can find workarounds for many database systems to supply this information.
Comments
One good use of ODBCDirect is in the scaling of an application--if you see the need to move your DAO-based workstation application into the client/server arena, you can do so with a minimum of recoding and still apply the flexibility of ODBC at the same time with the use of ODBCDirect.
© Copyright, Macmillan Computer Publishing. All rights reserved.