Site hosted by Angelfire.com: Build your free website today!
'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += ''; window.popUpWin.document.write(zhtm); window.popUpWin.document.close(); // Johnny Jackson 4/28/98 } //--> Visual Basic 6 Database How-To -- Ch 6 -- Connecting to an ODBC Server

www.Sir FreeDom.com.Ar

Visual Basic 6 Database How-To

Previous chapterNext chapterContents

- 6 -
Connecting to an ODBC Server

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.




Table 6.1. Data type conversions between ODBC and Visual Basic.





	

		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.




Table 6.2. Numeric precision conversion between ODBC and Visual Basic.





	

		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:





6.1 Use the ODBC Administrator to Maintain Data Sources


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.




6.2 Use ODBC with the Visual Basic Data Control


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.




6.3 Create an ODBC-Accessible Data Source by Using RegisterDatabase


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.




6.Prevent the Login Dialog Box from Being Displayed When I Open an ODBC Database


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.




6.5 Determine What Services an ODBC Server Provides


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.




6.6 Use ODBCDirect to Connect to Database Servers


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.




6.1 How do I...


Use the ODBC Administrator to
maintain data sources?


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.




Table 6.3. Data source name types.





	

		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.




	

	
1. Double-click your Control Panel icon. You should see a window appear
	similar to the one shown in Figure 6.2. In that window, you should find an icon like
	the one highlighted in the figure.
	





Figure 6.2. The
Control Panel, with ODBC Administrator highlighted.




	

	
2. Double-click the ODBC Administrator icon (usually titled "32bit
	ODBC") to start the Administrator applet. A dialog box similar to the one shown
	in Figure 6.3 should appear, displaying several property pages. The first three property
	pages deal with DSN entries, with each page representing a level of security. The
	User DSN property page provides ODBC access for a data source to a given user on
	a given workstation only; the System DSN allows ODBC access for a data source to
	a given workstation only, but to any user on that workstation. A File DSN entry is
	a file-based data source, usable by any and all who have the needed ODBC drivers
	installed, and it does not need to be local to a user or a workstation
	

	

	
Also, you'll notice three more property pages. The ODBC Drivers property page
	allows the display of all installed ODBC drivers, including the version and file
	information. The Tracing property page offers the capability of tracking all ODBC
	activity for a given data source and saving it to a log file for debugging purposes.
	And, last of all, the About property page gives version and file information on the
	core components that compose the ODBC environment.
	

	

	
3. For your needs, you are going to create a User DSN. Click the Add button
	to start the process of adding a new data source. The first dialog box that appears
	gives you the list of available ODBC drivers from which to choose. One thing you'll
	want to note is the helpful information on each dialog box and property page, summarizing
	its purpose.
	





Figure 6.3. ODBC
Data Source Administrator property pages.




	

	
4. Select the Microsoft Access Driver from the list of drivers and then
	click the Finish button. The next step is to provide the database-related infor-mation
	for the data source, and to that end a dialog box appears, similar to that displayed
	in Figure 6.4. The dialog box that appears is driver specific; that is, if you had
	selected a different ODBC driver, you would probably have gotten a dialog box with
	a completely different set of properties.
	

	

	
5. Type Biblio in the Data Source Name field. Then click the Select button
	in the Database frame and choose your copy of BIBLIO.MDB. Then click the OK button.
	





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.)




6.2 How do I...


Use ODBC with the Visual
Basic Data control?


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.




	

	
1. Create a new project, name it Person.VBP, and add a new form with property
	settings as listed in Table 6.4. Save the form as PERSON.FRM.
	








	
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:
	

	










	

	
2. Add the following code to the declarations section of the form. To
	avoid naming problems, Option Explicit tells Visual Basic to make sure that you declare
	all variables and objects before using them.
	








	
Option Explicit










	

	
3. Add the following code to the form's Load event procedure. After centering
	the form, set the Connect and RecordSource properties of the Data control, as well
	as the DataField properties of the text boxes that will hold the fields of the database.
	This step links each needed field with the text boxes that hold each record's data.
	

	

	
Change the data source name in the Connect property statement indicated to an
	available name in ODBC--or leave the DSN part of the Connect string out, and ODBC
	will prompt you for the information it needs. Also, set the SQL statement to a table
	in that data source, and change the text boxes to actual fields in that table.
	

	

	
4. Add the following code to the Click event of the cmdQuit command button.
	This is the exit point that terminates the program.
	








	
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:




	

	
1. Leave the Data control's DatabaseName property blank to use an ODBC
	data source. If you enter a database name here, Visual Basic attempts to open the
	database using its native data formats.
	

	

	
2. Set the Connect property of the Data control to the connect string
	that ODBC needs to connect to the database. This is the same connect string that
	other How-To's in this chapter use for defining a QueryDef, in RegisterDatabase,
	and in setting up other uses of ODBC directly. You can also simply set this property
	to ODBC, and ODBC will prompt the user at runtime for information it needs in order
	to make the connection.
	

	

	
3. Set the Data control's RecordSource property to a SQL statement or
	the name of a table you want to use to select the data from the database. Any SQL
	statement that creates a resultset can be used by Visual Basic to populate the bound
	text boxes.
	

	

	
4. Set each text box's DataSource to the name of the Data control--dtaData
	in this example. This is the normal Visual Basic way of binding a control to a Data
	control. Remember too that you can have as many Data controls on a form as you want,
	with different sets of controls bound to different bound controls and, therefore,
	to different databases.
	

	

	
5. Set the Data control's DefaultType to 1 - UseODBC. If this property
	is not set, trying to access an ODBC database with the Data control will cause Visual
	Basic to hang.
	

	

	
6. Lastly, set each text box's DataField property to the particular field
	name in the resultset of data records. This might be the name of the field in the
	database itself, but it is actually the name of the field that is returned in the
	resultset. The two can be the same, but the SQL statement can rename the fields or
	even return calculated fields that don't exist in the database.
	





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.




6.3 How do I...


Create an ODBC-accessible
data source by using RegisterDatabase?


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.




	

	
1. Create a new project named REGISTER.VBP. Add the form ODBCErrors.FRM
	and the code module ODBC API Declarations.BAS, using Visual Basic's File | Add menu
	command. The code module contains all the declarations needed for the ODBC API functions
	and the constants used in many of the functions, and the form makes it easier to
	examine ODBC errors.
	





Figure 6.6. The
project's ODBC Data Sources form, displaying the data source list.




	

	
2. Make sure that the Microsoft Common Controls components are available
	to this project. To add, select the Project | Components menu item. When the dialog
	box appears, look for a component titled Microsoft Windows Common Controls 6.0, and
	ensure that it is checked. Click OK. The ODBCErrors.FRM form uses the TreeView control
	to display ODBC errors in a hierarchical fashion.
	

	

	
3. Name the new project's default form frmODBC and save the file as REGISTER.FRM.
	Add the controls shown in Figure 6.6, setting the properties as given in Table 6.5.
	








	
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:
	

	










	

	
4. Put the following code in the declarations section of frmODBC. Option
	Explicit tells Visual Basic to check the variables for you. The dynamic arrays hold
	the information about installed drivers and data sources retrieved from calls to
	the ODBC API.
	








	
Option Explicit
`Dynamic arrays to hold data
Dim strDBNames() As String
Dim strDBDescs() As String
Dim strDvrDescs() As String
Dim strDvrAttr() As String










	

	
5. Add the following code to the Load event of frmODBC. This code handles
	all the setup chores, including centering the form on the screen and allocating an
	ODBC environment handle. Each ODBC application that calls the API needs to have one,
	and only one, environmental handle. ODBC keeps track of what information goes where
	through the use of this handle, along with the connection and statement handles.
	The actual work of extracting the lists of data sources and drivers is handled in
	other procedures in this form, but called from here.
	








	
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










	

	
6. Insert these procedures into frmODBC. The first procedure, cmdCreateDSN_Click,
	calls the procedure that validates data and actually creates the data source name
	in ODBC. The second procedure, cmdQuit_Click, creates an exit point in the application.
	








	
Private Sub cmdCreateDSN_Click()
    CreateNewDSN
End Sub
Private Sub cmdQuit_Click()
    End
End Sub










	

	
7. Add the following subroutine to frmODBC. Here is the first of two subroutines
	that extract the existing lists of data sources and drivers from ODBC. GetODBCdbs
	obtains the list of data source names and the descriptions of each, employing a function
	named ODBCDSNList in the ODBC API Declarations.BAS as a wrapper for the SQLDataSources
	API call. The ODBCDSNList function does all the work; the Do While loop extracts
	one data source name at a time with a call to SQLDataSources. If no error is returned
	from the function call, the data source name and its description are concatenated
	and added to the lstODBCdbs list box, showing all the data sources that are already
	installed. When the last data source has been returned to this function, the SQLDataSources
	function returns the SQL_NO_DATA_FOUND result, and the loop terminates. The function
	returns a variant array, if successful, or nothing, if unsuccessful.
	








	
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










	

	
8. The next function, GetODBCdvrs, gets a list of the drivers that ODBC
	has installed. Put the following code in frmODBC. This function operates very similarly
	to GetODBCdbs, calling a function named ODBCDriverList, which loops through calls
	to the ODBC SQLDrivers function, returning one driver name at a time, and returning
	the names and descriptions in a local variant array to be added to the cboODBCdrivers
	drop-down list.
	








	
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










	

	
9. Add the following code to frmODBC. The CreateNewDSN procedure sets
	up and calls RegisterDatabase so that the new data source name is recorded in the
	ODBC system. The procedure first checks to make sure that a name is entered in the
	txtDSNname text box so that a descriptive name will be in the list (the description
	is optional). If all is well, the procedure assembles the following set of variables
	to pass to RegisterDatabase:
	










	

	
Before actually making the call to RegisterDatabase, the Visual Basic error handler
	is set to go to the CantRegister error-handling routine if there is any problem registering
	the data source. If there is an error, a MsgBox informs the user of what the trouble
	is and continues the procedure--giving the user the opportunity to rectify any problems
	and try again. If the error is anything other than error 3146, it is passed on to
	the default Visual Basic error-handling routines.
	








	
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










	

	
10. Add this code in the Unload event of frmODBC. This code makes cleanup
	calls to ODBC functions, releasing the memory and handles allocated to make the calls
	to the ODBC API. The first call is to ODBCDisconnectDS, which releases and then frees
	the connection handle and memory. The second call, SQLFreeEnv, releases the ODBC
	environment handle and memory.
	








	
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.




6.How do I...


Prevent the login dialog box
from being displayed when I open an ODBC database?


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.




	

	
1. Create a new project named CONNECT.VBP. Add the form ODBCErrors.FRM
	and the code module ODBC API Declarations.BAS, using Visual Basic's File | Add menu
	command. The code module contains all the declarations needed for the ODBC API functions
	and the constants used in many of the functions.
	

	

	
2. Name the default form frmODBC, and save the file as CONNECT.FRM. Add
	the controls as shown in Figure 6.7, setting the properties as listed in Table 6.6.
	








	
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:
	

	










	

	
3. Add the following code to the declarations section of frmODBC. To avoid
	naming problems, Option Explicit tells Visual Basic to make sure that you declare
	all variables and objects before using them.
	








	
Option Explicit
`Module level globals to hold connection info
Dim dbfTemp As Database, recTemp As Recordset










	

	
4. Add this code to the form's Load event. After the form is loaded, memory
	and a handle for the ODBC environment and connection are allocated. If either of
	these fails, there is no need to proceed, so the program is exited.
	








	
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










	

	
5. Add the following code to the Click event of cmdConnect. Before you
	can get connection data, you need to select a data source name for the connection
	information you want. For this procedure, let the built-in ODBC dialog boxes do the
	work. The line
	








	
Set dbfTemp = OpenDatabase("", False, False, "ODBC;")










	

	
tells Visual Basic to open a database but gives no information about which one,
	other than it is an ODBC database. ODBC responds by opening its Select Data Source
	dialog box for selection of a data source, as shown in Figure 6.9.
	








	
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.




	

	
After the user selects a data source name, the procedure loops through the TableDefs
	collection using a Visual Basic For Each...Next loop, retrieves the table name of
	each table available in that data source, and adds each table name to the lstTables
	list box. If a connection is made and any tables are available, the cmdGetConnect
	command button is Enabled for the next step, which is retrieving the connection information.
	The error-handling routine is important in this procedure and is discussed with the
	APIConnect procedure code.
	

	

	
6. Add the following code to the cmdGetConnect's Click event. This command
	button is enabled only when a connection is made and tables are available for selection.
	After you select a table name, a connection is made to that table by creating a dynaset.
	This makes the connection information available. The connection information is retrieved
	by copying the value of the dynaset's Connect property to the txtConnect text box,
	running it through the AddSpaces function as discussed in the following text. Finally,
	the cmdCopyConnect command button is enabled.
	








	
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










	

	
7. Add the following code to frmODBC. When you receive the raw connect
	string back from ODBC after making the connection to the database table, it is strung
	together with no spaces, unless a space happens to be in any of the strings enclosed
	in quotation marks. Sometimes the connect string can become lengthy, so use a text
	box with the MultiLine property set to True. Even with that, an unbroken string with
	no spaces can exceed any width you make the text box. So this function simply loops
	through the length of the string, replacing all the semicolon separators with a semicolon-space
	pair of characters. ODBC uses semicolons to separate the different phrases in a connect
	string.
	








	
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










	

	
8. Add the code for cmdCopyConnect's Click event as shown here. This is
	added as a convenience for the programmer. When you have connected to the data source
	and have received the connect string, just click the Copy Connect String command
	button, and the full string is copied to the Windows Clipboard, ready to paste into
	your application.
	








	
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










	

	
9. Add the following code to the form's Unload event and the cmdQuit command
	button's event. The cmdQuit command button ends the program, triggering the form's
	Unload event. As usual for Visual Basic applications that make direct calls to the
	ODBC API, the code needs to clean up after itself, releasing the memory and handles
	needed for connection to ODBC.
	








	

	
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










	

	
10. Add the following code to the DblClick event procedure of the lstTables
	list box. This code simply adds the convenience of being able to double-click on
	a table in lstTables to retrieve the connect string, saving the work of also clicking
	the cmdGetConnect command button.
	








	

	
Private Sub lstTables_DblClick()
    cmdGetConnect_Click
End Sub










	

	
11. Add the following code to the code section of the form. Sometimes
	it is necessary to handle errors generated by Visual Basic but caused by the ODBC
	system. This is one example of such a situation.
	

	

	
There are two sorts of Visual Basic errors that need to be handled in the cmdConnect_Click
	procedure. The first sort of error is when, for any of a number of reasons, there
	is a problem making the connection to the data source (for example, the database
	is not available, the connection couldn't be made because of network traffic, and
	so on). One common error is the attempt to open an ODBC database that is one of the
	databases Visual Basic handles natively, such as an Access .MDB file or one of the
	ISAM databases.
	

	

	
This is where the APIConnect procedure comes in. Even though the error is generated
	by the Visual Basic error handler, there is some reason lurking in ODBC for why the
	connection can't be made, and a call to the ODBC SQLError function will usually (but
	not always) give more information about the problem. SQLError doesn't always have
	information to give, for whatever internal reason. Basically, APIConnect just calls
	SQLError from within the error procedure, gets whatever additional information it
	can obtain, disconnects the ODBC connection (but not the handle--you might need that
	again for another attempt to make a connection), and returns to frmODBC.
	








	
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










	

	
The other error that must be handled is one that occurs when the user clicks
	Cancel when ODBC's Select Data Source dialog box is shown. If this happens, the Sub
	procedure is exited and the user is returned to the main form.
	





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.




Table 6.7. ODBC functions for establishing data source connections.





	

		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.


6.5 How do I...


Determine what services an ODBC
server provides?


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.




	

	
1. Create a new project called SERVICES.VBP. Add the form ODBCErrors.FRM
	and the code module ODBC API Declarations.BAS using Visual Basic's File | Add menu
	command. The code module contains all the declarations necessary for the ODBC API
	functions and the constants used in many of the functions, and the form allows for
	an easy-to-read display of ODBC errors.
	

	

	
2. Add a new form, frmODBC, and save the file as SERVICES.FRM. Add the
	controls shown in Figure 6.11, setting the properties as given in Table 6.8. The
	area under the Properties label is the MSFlexGrid control, grdResults.
	








	
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:
	

	










	

	
3. Put the following code in the declarations section of the form. This
	code declares the dynamic array that will be used to hold the names of the data sources
	and drivers available to ODBC. To avoid naming problems, Option Explicit tells Visual
	Basic to make sure you declare all variables and objects before using them.
	








	
Option Explicit
`Dynamic arrays to hold data
Dim strDBNames() As String










	

	
4. Add the following code to the form's Load event. First, the form is
	centered onscreen so that all the controls and information can be seen. Then, as
	with all applications that make direct calls to the ODBC API, the ODBC wrapper functions
	are called to allocate memory and assign a handle for the ODBC environment and for
	the database connection. This is followed by a call to the GetODBCdbs Sub procedure,
	which the following text explains.
	








	
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










	

	
5. Add the following routine to frmODBC. This procedure extracts the existing
	lists of data sources and drivers from ODBC. GetODBCdbs obtains the list of data
	source names and the descriptions of each. The Do While loop extracts one data source
	name at a time with a call to SQLDataSources. If no error is returned from the function
	call, the data source name is added to the lstODBCdbs list box, showing all the data
	sources that are already installed. When the last data source has been returned to
	this function, the SQLDataSources function returns the SQL_NO_DATA_FOUND result,
	and the loop terminates.
	








	
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










	

	
6. Add the following routine to frmODBC. This procedure first determines
	whether a data source name has been selected in the lstODBCdbs list box. If none
	has been selected, a message box lets the user know that and then exits the procedure.
	

	

	
Assuming that a data source is selected, a call is made to ODBCConnectDS to allocate
	connection memory and a handle, which is required for a later call to SQLGetFunctions.
	If a connection is made successfully, the call to SQLGetFunctions is made, which
	does the actual work of retrieving the function list. The list is put into the FuncList
	array, which has 100 elements. An element of this array is set to true (-1) or false
	(0) if the referenced ODBC function is available with this data source. See the "How
	It Works" section for a discussion about how this array is used to identify
	available functions.
	

	

	
To set the number of rows in the grdResults grid, the number of array elements
	that are True must be counted. The first For...Next loop handles that, so that j
	is the number of true elements at the end of the loop. Then, after setting up the
	grid with the numbers of rows and columns to fit the data, the intFuncList array
	is looped through once again, to put the function names into each row of the grid.
	








	
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










	

	
7. Put the following code in the cmdProperties_Click event subroutine.
	After the program checks to make sure that a data source is selected, it attempts
	to allocate a connection handle; then it loads the frmGetInfo form to continue obtaining
	information.
	








	
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










	

	
8. Enter the following two procedures to the code section of the form.
	The first procedure establishes a command button to quit the application. The Unload
	event then cleans up and releases the memory and handles that were necessary for
	making calls to the ODBC API.
	








	
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










	

	
9. Add the following code to the code section of the form. This function
	provides a convenient way to convert some of the results of the properties to a more
	user-friendly and understandable form.
	








	
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










	

	
10. Add a new form, frmGetInfo, and save the file as GETINFO.FRM. Add
	the controls shown in Figure 6.10, setting the properties as shown in Table 6.9.
	








	
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
	

	










	

	
11. Add the following line of code to the declarations section of the
	GETINFO.FRM form. Option Explicit tells Visual Basic to make sure that you declare
	all variables and objects before using them, to avoid naming problems.
	








	
Option Explicit










	

	
12. Add the following code to the form's Load event. The main job of this
	procedure is to load the lstGetInfoData list box with all the available ODBC functions.
	All the function names are loaded into the ODBCGetInfo array in the LoadGetInfo Sub
	procedure in ODBC API Declarations.BAS. That array is an array of GetInfo types,
	defined in ODBC API as this:
	








	
Type GetInfo
    InfoType As String
    ReturnType As String
End Type










	

	
The array has SQL_INFO_LAST number of elements, as defined in ODBC API Declarations.BAS
	in the declarations section:
	








	
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










	

	
13. Add the following code to the cmdGetInfo's Click event procedure.
	Although this procedure looks foreboding, it 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. For more information on this procedure,
	review the "How It Works" section later in this How-To.
	








	
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










	

	
14. Add the code for SpecialStr to the code section of the module. This
	is the first of the "special" processing functions that make the results
	of the call to SQLGetInfo more meaningful. Most of the Select Case options simply
	convert a "Y" or "N" to "Yes" or "No." Another
	processing function, SQL_KEYWORDS, returns a long list of keywords you can use. To
	keep things from getting too complex, this How-To just indicates that a list is available;
	you could easily put the list into a TextBox or List control to allow closer examination,
	or it could be used by the application.
	

	

	
The return value of the SpecialStr function is the string that is actually displayed
	in the grid.
	








	
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










	

	
15. Add the code for the SpecialInt function to the code section of the
	form. This function handles special integer return values from SQLGetInfo. In all
	of these special cases, the return value is an index to a keyword defined in ODBCAPI.BAS.
	Simply use a Select Case nested within the overall Select Case structure to translate
	the value into a more meaningful string.
	








	
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










	

	
16. Add the code for the BitMask function to the code section of the form.
	One form of return value from the SQLGetInfo function is a 32-bit bitmask. A bitmask
	is a way of packing lots of information into a relatively compact variable because
	each of the 32 bits can be "on" or "off" (1 or 0) to indicate
	the value of some option. This function simply converts the bitmask into a string
	of 32 1s and 0s to show their content. You could expand this function (in a way similar
	to the nested Select Case SpecialInt function) to test for the various values of
	the different options, and you could present a list or otherwise use the information
	in your application.
	

	

	
In practical use in an application, you would be interested in checking for one
	or two characteristics contained in the bitmask and would check for that condition
	instead of just listing the contents of the bitmask.
	








	
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










	

	
17. Add the code for SpecialLong to the code section of the form. This
	is the last of the Special functions, and it is used with return type Long. The single
	case that must be handled here is for the SQL_DEFAULT_TXN_ISOLATION attribute, and
	it is handled similarly to the SpecialInt function. The same nested Select Case structure
	has been used to allow easy expansion of this function for future versions of ODBC
	and handle the error condition if an unexpected Long is sent to the 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










	

	
18. Add the code for the cmdSelection control array Click event. The Select
	All and Unselect All command buttons are provided for convenience in selecting items
	in the list. This procedure simply loops through the lstGetInfoData list box, selecting
	all items if the Index is 1 (Select All was clicked) and deselecting all items if
	the Index is 0 (Unselect All was clicked).
	








	
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










	


	19. Add the code for the cmdCancel Click event. Even though SQLGetInfo uses an
	hDbc handle, the memory was allocated in the frmODBC form, so no special cleanup
	is necessary in this form. Therefore, no form Unload procedure is required.







	
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.




Table 6.10. SQLGetInfo 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.




Table 6.11. Arguments for the SQLGetInfo ODBC 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.




Table 6.12. Arguments for the SQLGetFunctions ODBC 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.




6.6 How do I...


Use ODBCDirect to connect to database
servers?


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.




	

	
1. Create a new project, and save it as ODBCDirect.VBP.
	

	

	
2. Name the default form Form1, and save the file as TRANS.FRM. Add the
	controls shown in Figure 6.12, setting the properties as listed in Table 6.13.
	








	
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
	

	










	

	
3. Add the following code to the declarations section of Form1:
	








	
Option Explicit
Dim conTemp As Connection










	

	
4. Add the following code to the form's Load event. The brevity of this
	routine doesn't explain its importance--the DefaultType property determines whether
	the DAO will construct a DAO or an ODBCDirect object. In this case, you are instructing
	the DBEngine that all objects created under it are ODBCDirect objects. If you planned
	to mix DAO and ODBCDirect access in the same application, you would perform this
	step at the workspace or database level, depending on your needs.
	








	
Private Sub Form_Load()
    `Notice the dbUseODBC parameter; this determines that DBEngine
    `will create an ODBCDirect workspace by default.
    DBEngine.DefaultType = dbUseODBC
End Sub










	

	
5. Add the following code to the Click event of cmdOpen. This routine
	first attempts to open an ODBCDirect Connection object by forcing the user to select
	an ODBC driver from the ODBC Data Sources dialog box. After it is selected, the Connection
	object is created and its properties concatenated into the txtProperties text box
	for display.
	








	
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










	

	
6. Add the following code to the Click event of cmdClose:
	








	
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.







Previous chapterNext chapterContents