Those who use PowerBuilder appreciate its productivity, its rich user interface, and its capable implementation of object orientation. But database connectivity is the cornerstone of PowerBuilder's power. PowerBuilder incorporates native drivers for several popular DBMSs, including Informix, Sybase Adaptive Server Enterprise, and Oracle. If the only things that were delivered with PowerBuilder were these native interfaces, PowerBuilder's ability to connect to many useful and popular databases would be somewhat limited. Fortunately, in addition to the native drivers, PowerBuilder provides almost limitless connectivity through the ODBC interface. This article examines the ODBC interface, how to use it, and how to troubleshoot ODBC connections.

What Is ODBC?

ODBC stands for Open Database Connectivity. It is an API for database access that has been created by Microsoft Corporation. ODBC defines a standard interface that programmers can use to access any number of DBMSs from different manufacturers. The developer writes code that uses the ODBC API, and the driver then translates that into the appropriate calls that the target DBMS requires. Figure 1 illustrates the interplay between the developer, the ODBC driver, and the DBMS.

There are a few things that ODBC is not. It is not a DBMS. It's a common interface to a number of DBMSs. It's not a programming language. It supports generic SQL as a language for insert, update, delete, and query.

What is ODBC? ODBC is a DBMS interface. It consists of a defined API and functionality that implements, through ODBC drivers, an interface for programs that require database operations. It's used with any number of programming languages to establish a connection to a database and work with the data in that database.

ODBC supports a common subset of SQL operations among the supported ODBC drivers, although it also supports "pass-through" statements so that database-specific functionality can be utilized.

How Is ODBC Used?

ODBC is used to establish a connection and execute database operations using a standard set of API calls that request functionality from the ODBC driver. Here are the steps that must be followed.

Establish Configuration
Each ODBC database connection that you use must be set up within the operating system. Each possible connection that you set up in ODBC is called a data source. The standard tool used for this task is the ODBC Administrator. Later in the article I'll discuss how to use the ODBC Administrator to set up data sources. Once you have created one or more data sources, you can use them in your applications.

Connect to the Database
Next you need to connect to the database. In PowerBuilder, this involves populating a transaction object with the correct information, then calling the CONNECT method on the transaction object. The following code provides an example of populating the default transaction object, SQLCA, and connecting to the database.

// Profile Mba Leads
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Connectstring='DSN=mba_leads;UID=dba;PWD=sql'"
CONNECT USING SQLCA;
If SQLCA.SQLCode <> 0 Then
MessageBox ("Cannot Connect to Database", SQLCA.SQLErrText )
End If

Set Up Calls
Once you have established a connection, you can then make calls to the database (through the ODBC driver) requesting functionality. When using a third generation language (3GL) such as C++, you need to do quite a bit of setup before you can actually execute a call. This involves creating several objects and populating the objects with the SQL and commands to be sent to the DBMS. In PowerBuilder, prepare for database calls in one of two ways: you can use SetTransObject to associate your transaction object with a DataWindow or data store, or prepare dynamic SQL statements for later execution. You can also simply execute SQL statements directly.

Execute Calls
If you're using a 3GL, call the appropriate execute method on your object. When using PowerBuilder, simply use a PowerBuilder statement to execute your SQL or database command. If using a DataWindow, code a RETRIEVE statement for the DataWindow to request a result set from the DBMS:

dw_1.retrieve

Or, if using embedded SQL, write the SQL statement as usual and follow it with a semicolon:

SELECT emp_fname, emp_lname, emp_zip FROM employee;

Examine Return
Of course, you need to determine whether your database request completed successfully. Once you've executed your statement, examine the SQLcode member of the transaction object (for example, SQLCA.SQLcode) for the result.

Setting Up an ODBC Data Source

To set up an ODBC data source (DSN), use the ODBC Administrator. The ODBC Administrator is available several ways. It can be started from the control panel, it shows up as an icon in program groups for DBMS software such as Sybase's SQL Anywhere, and, finally, you can start it from PowerBuilder's database painter (you'll find it in the objects pane under "ODB ODBC | Utilities").

When starting the ODBC Administrator, you'll see a tab for each User DSN, System DSN, and File DSN. User DSNs are stored in the registry and are unique to each user signed onto the system, while System DSNs are also stored in the registry but are available to all users of the system. File DSNs do not use the registry; instead they keep the information needed to connect to the database in a file stored within the operating system's file system. By storing the settings in a file, multiple users can share the same connection configurations, for example, by storing the file on a common network folder. Choose among these selections based on who should be able to access the data source.

To establish a new data source, click on "Add" from your choice of User, System, or File DSN, then choose a driver from the list of installed drivers that is presented to you and click "Finish". This will display the configuration dialog specific to the driver you have chosen. The configuration dialog will differ among different drivers, so some of the items shown for one driver may not be present when using another driver. Specify "Data Source Name" regardless of the driver chosen, and also direct the driver to the database file or server. Depending on the driver, you'll also be able to specify "User ID" and "Password", as well as configuration parameters specific to the selected driver. Figure 2 shows the ODBC Configuration dialog for Adaptive Server Anywhere 8.0.

Where Does the System Keep ODBC Data Sources?

What actually happens when you establish an ODBC Data Source using the ODBC Administrator? Except in the case of File DSNs, the ODBC Administrator uses the system registry to keep track of the data sources you have set up. User Data Source configurations are placed under the registry key:

HKEY_CURRENT_USERSoftwareODBCODBC.ini

while System DSN information is kept under the registry key:

HKEY_LOCAL MACHINESoftwareODBCODBC.ini

File DSNs are stored in the folder you specified when creating the DSN.

Single-Tier vs Multiple-Tier Drivers

ODBC drivers can be either single tier or multiple tier. When a driver is single tier, that means all of the functionality required to manipulate the database is contained within the driver. This type of driver is typically used for file-based DBMSs such as dBase or Microsoft Access. Multiple-tier drivers contain functionality that interfaces with the database engine, allowing the database engine to perform the work of manipulating the database. Multiple-tier drivers are typically used with server-based DBMSs such as Adaptive Server Anywhere. Figure 3 illustrates the difference between single-tier and multiple-tier drivers.