Chapter 16: Using ADS with ODBC, PHP, and DBIPerl

Each of the preceding chapters in this section describes building client applications using an IDE (integrated development environment) such as Delphi, JBuilder, Visual Studio, or Visual Studio .NET. This final chapter introduces you to a collection of related data access mechanisms that are not associated with a particular IDE, or even a specific operating system. These are the Advantage ODBC (open database connectivity) Driver, the Advantage PHP Extension, and the Advantage DBI Driver (for Perl).

As in the previous chapters on writing ADS client applications, this chapter is not intended to show you how to program in the environments supporting the covered drivers, focusing instead on how to connect to and use ADS. For information on developing in the languages covered in this chapter, refer to the documentation or a good book on the subject.

Accessing ADS Using the Advantage ODBC Driver

ODBC (open database connectivity) is based on the Open SQL CLI (call-level interface), a SQL-based standard for accessing data. Advantage supplies ODBC drivers for both Windows and Linux.

ODBC is an API (application programming interface). However, most developers who use ODBC to access their data do not make direct ODBC calls. Instead, they use an IDE that supports ODBC.

A good example of this can be found in the Borland products that use the BDE (Borland Database Engine). The BDE provides three mechanisms for accessing data, including direct local table access to Paradox and dBase file formats, and Borland SQL Links for Windows native SQL drivers that support a number of remote database servers, such as MS SQL Server and Oracle. The third data access mechanism supported by the BDE makes use of the ODBC API, through which you can use any installed ODBC driver. However, developers who use ODBC through the BDE rarely make direct ODBC calls. Instead, they use the TDataSet interface of the VCL (visual component library). The TBDEDataSet classes that implement the TDataSet interface encapsulate calls to ODBC.

While ODBC is an older standard, compared with ADO (ActiveX data objects) and ADO.NET, ODBC is easily the most widely supported data access mechanism for Windows and Linux. Nearly every database currently available is supported by at least one, and in some cases many, ODBC drivers. Consequently, every development environment for Windows and Linux that we are aware of provides some support for ODBC. Even Java, with its JDBC-ODBC bridge class 1 driver (Java database connectivity/open database connectivity), and .NET, with its classes in the System .Data.Odbc third-level namespace of the FCL (framework class library), provide support for ODBC.

The Advantage ODBC Driver is compliant with the core API and level 1 API for ODBC 2.0. In addition, it supports most of the level 2 API functions. For a complete list of ODBC functions and information on Advantage’s ODBC conformance level, see the ADS help.

Who Should Use the Advantage ODBC Driver

There are two groups of users who should use the Advantage ODBC Driver. The first group consists of those developers who are using a development environment for which there are no alternative drivers. For example, if you are using a proprietary development environment that does not support the ACE (Advantage Client Engine) API, Java, .NET, or VCL, the Advantage ODBC Driver is your fallback solution.

For those developers for whom there is an alternative to ODBC, ODBC is usually a poor choice for connecting to ADS. This is because the alternative solutions offer more options than does ODBC. In short, ODBC is the lowest common denominator for data access. All other data access solutions, including the two others covered in this chapter, provide a more extensive API than that supplied by ODBC alone.

The second group of developers who will use ODBC to access ADS are those that use the Advantage PHP Extension or the Advantage DBI Driver. Both of these drivers, which are used by Web developers through the PHP and Perl languages, respectively, connect to ADS through the ODBC driver. However, these drivers supply additional support beyond that provided by plain ODBC.

Connecting to ADS Using the Advantage ODBC Driver

Before you can execute SQL statements against ADS, you must obtain a connection to ADS through the ODBC driver. ADS supports two ODBC API functions for obtaining a connection. These are SQLConnect and SQLDriverConnect.

You use SQLConnect when there is a DSN (data source name) for the directory or data dictionary that you want to connect to. SQLDriverConnect, by comparison, does not require a DSN. Instead, the connection request is passed to SQLDriverConnect in its connection string. How you connect using these functions is described in the following sections.

Connecting Through ODBC Using a Data Source Name

A DSN is a definition that is stored on the workstation, and can be used to connect to data using an ODBC driver. Under the Windows operating system, DSN definitions are stored in the Windows registry, while in Linux these definitions appear in a configuration file named odbc.ini.

Windows users typically do not add the Windows registry entries for a DSN manually. Instead, they use an applet found in either the Control Panel (for older Windows installations), or the Administrative Tools page of the Control Panel. The name of this applet depends on which operating system you are using, but it always includes the letters ODBC. In Windows 2000 and Windows XP, for example, it is called Data Sources (ODBC).

To define a DSN manually, run this utility after you have installed the Advantage ODBC Driver on the workstation. If your client application is going to run under an end users account, you can add a user DSN. If your application is going to run under some other account, such as IUSER_MACHINE (used by Microsoft’s Internet Information Server), add a system DSN.

Once you have decided to add either a user or system DSN (by selecting either the User or System tabs of this applet), click the Add button. Windows responds by displaying the Create New Data Source dialog box. Select Advantage StreamlineSQL ODBC from this list, then click Finish.

You will then see the Advantage StreamlineSQL ODBC Driver Setup dialog box, shown in Figure 16-1. You use this dialog box to configure the DSN.

Figure 16-1: The Advantage StreamlineSQL ODBC Driver Setup dialog box

Set Data Source Name to the name you will use to refer to this DSN, and provide an optional description for the Description field.

If you will use this DSN to connect to a data dictionary, check the Data Dictionary checkbox and enter the full path to the data dictionary in the provided field. If you will use this DSN to connect to free tables, enter the data directory path here. In most cases you will want to use a UNC (universal naming convention) path in this field.

Use the Table Type, Locking Type, Advantage Locking, Character Set, and Packet Compression dropdown lists to define what data you are connecting to and how. To configure the size of memo blocks created by ODBC, or to adjust the number of tables to cache, set the corresponding fields. Also, you can configure the ODBC driver to show deleted rows for DBF tables, as well as trim trailing spaces from character fields.

When you are done configuring your DSN, click the OK button to save the DSN definition in the registry.

It is also possible to create a DSN by writing to the Windows registry programmatically. This approach is useful if you want to create an automated setup for your client applications, rather than having to enter the DSN information manually on every machine. Note, however, that you should extensively test any code that writes to the Windows registry, after making a backup of the registry, as inappropriate changes to the Windows registry can render a computer unstable or even unusable.

Tip

For guidance on creating registry entries programmatically, start by adding a DSN using the Data Sources (ODBC) applet. Then, inspect the entries that this applet added to the registry for the keys, values, and data that you need to insert. You will find these entries in HKEY_CURRENT_USER\ SOFTWARE\ODBC\ODBC.INI for user DSNs, and HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ ODBC.INI for system DSNs. You can also find information on creating DSNs at http://www .msdn.Microsoft.com.

In Linux, you create a DSN by adding entries to the odbc.ini file. Refer to the ADS help for information on working with the odbc.ini file.

Once you have created the DSN that you are going to use, you will be able to call the SQLConnect function of the ODBC API. This function takes seven parameters. The first parameter is a connection handle that you previously allocated by calling SQLAllocHandle. The remaining parameters are string and integer pairs, where you pass the DSN name, user name, and password in the second, fourth, and sixth parameters, respectively; and the length of these strings in the third, fifth, and seventh parameters.

Connecting Through ODBC Using a Connection String

The primary drawback to using a DSN is that you must define the DSN on each workstation, which increases the complexity of your client installations. Fortunately, ODBC provides an alternative to using a DSN. This second mechanism employs a connection string.

The ODBC API includes two functions that accept a connection string: SQLDriverConnect and SQLBrowseConnect. ADS only supports SQLDriverConnect.

SQLDriverConnect takes eight parameters. The first parameter is a connection handle, which you obtain by calling SQLAllocHandle, and the second is the Windows handle of your client application. The third and fifth parameters are used for the input connection string and the completed connection string, respectively. (The completed connection string is the version of the connection string used by ODBC to connect to the database. It includes any parameters that have been expanded by ODBC, as well as any default values not included in the input connection string.) The fourth parameter is the size of the input connection string, and the sixth parameter is the size of the buffer that you have allocated for the completed connection string.

The seventh parameter is the size of the completed connection string that was written to the buffer referenced in the fifth parameter, and the eighth parameter permits you to configure whether or not the ODBC driver manager should prompt the user for additional connection information, if needed. For example, if you pass empty strings in place of the user name and password parameters, and a user name and password is required to connect, you can instruct SQLDriverConnect to prompt the user for this information at runtime.

The connection string itself consists of zero or more parameters that you use to connect to ADS in the form of name/value pairs. The name and value parts are separated by an equal sign (=), and individual name/value pairs are separated by semicolons. Table 16-1 shows a complete list of the connection string parameters and the values that you can assign to them.

Table 16-1: Connection String Parameters

Parameter	Description
AdvantageLocking	Set to ON to use Advantage proprietary locking, or OFF to use compatibility locking. The default value is ON.
CharSet	Set to either ANSI or OEM. The default is ANSI.
Compression	Set to ALWAYS, INTERNET, NEVER, or empty. If left empty (the default), the ADS.INI file will control the compression setting. This parameter is not used by ALS.
DataDirectory	The path to your free tables or data dictionary. If you are using a data dictionary, you must include the data dictionary filename in this path. It is recommended that this path be a UNC path. This is a required parameter.
DefaultType	Set to FoxPro, Advantage, or Clipper. This parameter is ignored for data dictionary connections, but is required for free tables. The default is Advantage.
Description	This parameter is not used.
MaxTableCloseCache	Set this parameter to the number of underlying tables to hold in cache when cursors are opened and closed. The default value is 25.
MemoBlockSize	Use this parameter to define the block size that ODBC will use for memo fields. This value is always 512 for Clipper- compatible DBF tables (DBF/DBT). The default is 64 for FoxPro-compatible DBF tables (DBF/FPT), and 8 for Advantage proprietary tables (ADT/ADM).
PWD	When connecting to a data dictionary that requires logins, set to the user's password.
RightsChecking	Set to OFF to ignore client rights, or ON to respect them. The default is ON.
Rows	Set to TRUE to display deleted records in DBF files, or FALSE to suppress them. The default is FALSE.
ServerTypes	Set to an integer between 1 and 7 to define the server types the ODBC driver should attempt to connect to. Set to 1 for ALS, 2 for ADS, and 4 for Internet. To attempt to connect to more than one server type, set this parameter to the sum of the values. For example, to attempt to connect to ADS and then to ALS if the ADS connection fails, set ServerType to 3.
TrimTrailingSpaces	Set to TRUE to trim trailing spaces from character fields, or FALSE to preserve trailing spaces.
UID	If connecting to a data dictionary that requires logins, set to the user's user name.

Examples of ODBC connections strings are provided in the following discussion of PHP.