How to use a foreign DBMS Professional

This chapter demonstrates the basic steps required for accessing a foreign DBMS, such as an SQL DBMS, for example. The programming technique follows always the same pattern, no matter what ODBC compliant software provides for the data source. When you understand the basic steps, you can access any data source, or database, that is accessible via the ODBC standard. Here is the source code outlining the basic steps:

01:   #pragma Library( "Adac20b.lib" ) 
02:   
03:   PROCEDURE DbeSys 
04:      DbeLoad( "ODBCDBE" ) 
05:      DbeSetDefault( "ODBCDBE" ) 
06:   RETURN 
07:   
08:   PROCEDURE Main 
09:      LOCAL cConnect, oSession 
10:   
11:      cConnect := "DBE=ODBCDBE" 
12:      cConnect += ";DSN=OrderProcessing" 
13:      cConnect += ";UID=John" 
14:      cConnect += ";PWD=Camelot" 
15:   
16:      oSession := DacSession():new( cConnect ) 
17:   
18:      IF .NOT. oSession:isConnected() 
19:         ? "Unable to connect to server !" 
20:         ? oSession:getLastMessage() 
21:         QUIT 
22:      ENDIF 
23:   
24:      USE Customer 
25:      Browse() 
26:      CLOSE ALL 
27: 
28:      oSession:disconnect() 
29:   RETURN 

The program starts with a #pragma directive which instructs the linker to link the import library ADAC20B.LIB to the application program. This is the best way to make sure that the ADAC20B.DLL file will be loaded when the program is started. The ADAC20B.DLL contains the DacSession class which is always required when the ODBCDBE is used by an application program.

The program code continues with the DbeSys() procedure. If you have developed applications with Xbase++ already, you are familiar with DbeLoad() and DbeSetDefault() (line #3 and #4). The functions load the ODBC database engine and define it as the default DBE for opening tables, or database files.

When DbeSys() returns, procedure Main() is executed and its first task is to build a Connection String (lines #11-14). This is a character string containing information required for establishing a connection between an application program (client side) and a data source (server side). The connection string contains a list of name=value pairs separated with semi-colons. This string is the most important part for successfully using the ODBCDBE since a connection will fail if connection data is incorrect. If the connection fails, the data source is not available and the ODBCDBE cannot be used.

Once the connection string is prepared, it is used by a DacSession object created in line #16. This object establishes a connection to the DBMS on instantiation. A successful connection is then checked in line #18, and the program terminates when the connection failed. However, this is not the case in the example program since all data required for a connection is included in the connection string:

DBE=

"ODBCDBE" is the name of the Xbase++ database engine which handles database access.

DSN=

"OrderProcessing" is the ODBC Data Source Name. It identifies the ODBC data source on the computer to be used for the connection. ODBC data sources are created with the ODBC Data Source Administrator tool described in the following chapter. For now it is sufficient to know that an ODBC data source defines the ODBC driver that must be used, for example, on which server computer the DBMS is actually running and which database is accessed on the server. Just assume an SQL database having the same name as the ODBC data source. The database is used for order processing and consists of tables holding Customer data, Product data, Order data and Payment data, for example.

UID=

The user ID is required for login. In the example, the user "John" logs in.

PWD=

"Camelot" is the password required for login.

When the connection to the server is established by the DacSession object, the SQL database can be accessed using the Xbase++ programming language. In line #24, for example, the Customer table of the OrderProcessing database is opened with the USE command. This SQL table is viewed by calling the Browse() function in line #25, and is closed in line #26.

The final step in using data sources with the ODBCDBE is line #28 which interrupts the connection to a data source by calling the :disconnect()method of the DacSession object.

Summary

The programming pattern for accessing ODBC compliant data sources with the ODBCDBE includes always the following steps: