dBASE DRIVER

 
This document describes the INTERSOLV DataDirect ODBC driver for dBASE.  It contains the following sections:

 Introduction
Setting the Library Path Environment Variable
Configuring Data Sources
Connecting to a Data Source
Attributes
Data Types
Column Names
Select Statement
ROWID Pseudo Column
Create Index Statement
Drop Index Statement
Pack Statement
Locking

INTRODUCTION

 
The dBASE driver is called qedbfnn.so for Solaris and AIX. For HP-UX, the extension is .sl.
 
It supports the following:
 

•dBASE III, IV, and V files. 
 

•Clipper files. 
 

•FoxPro and FoxBASE files. 

 
The dBASE driver executes the SQL statements directly on dBASE, Clipper, FoxPro, and FoxBASE files.  You do not need to own the dBASE product to access these files.
 
The dBASE driver provides the following features:
 

•Transaction support for dBASE-compatible files. 
 

•Core, Level 1, and Level 2 functions listed in Appendix C, "Supported ODBC Functions," in the INTERSOLV DataDirect ODBC Drivers Reference.  Also, the driver supports the Level 2 function SQLSetPos as well as backward and random fetching in SQLExtendedFetch. 
 

•Minimum SQL grammar. 
 

•Read committed isolation level. 
 

•File- and record-level locking. 
 

•Forward and backward scrolling and random fetching. 
 

•Multiple connections and multiple statements per connection. 

 

SETTING THE LIBRARY PATH ENVIRONMENT VARIABLE

 
You must include the full path to the dynamic link libraries in the environment variable LD_LIBRARY_PATH (on Solaris), LIBPATH (on AIX), and SHLIB_PATH (on HP-UX). For example, if you install the ODBC drivers in the system directory /opt, then the fully qualified path for the ODBC Pack is /opt/${REV}/odbc/dlls. During installation, a shell startup script is created and stored in the odbc directory. This shell script sets up the odbc environment for you.
 
For C shell users, the shell startup script is called .odbc.csh. This script can be sourced from a user’s own .login script. For example:
 

source /opt/${REV}/odbc/.odbc.csh

 
For Bourne or Korn shell users, the shell startup script is called .odbc.sh. This script can also be sourced from a user’s own .profile script. For example:
 
 

 .  /opt/${REV}/odbc/.odbc.sh

 
If you do not include the path /opt/${REV}/odbc in the environment variable LD_LIBRARY_PATH (on Solaris), LIBPATH (on AIX), and SHLIB_PATH (on HP-UX), then your applications are unable to load the ODBC drivers dynamically at runtime.
 

CONFIGURING DATA SOURCES

 
To configure a dBASE data source, use any plain text editor (vi, for example) to edit the file .odbc.ini using the attributes defined in "Connecting to a Data Source." The .odbc.ini  file has the following structure:

 [ODBC Data Sources]   ;Lists data sources available to ODBC.
ds_name1=description  ;Lists each data
source name followed by
                      ;  a description.
ds_name2=description
 ...
[ds_name1]            ;Defines the actual ODBC Driver source;
                      ;  for example, dBASE.
Driver=path/dll       ;Defines the actual path to the driver
                      ;  DLL.
 ...
[ds_name2]
Driver=path/dll
 ...

 

In this description, comment lines begin with a semicolon (;). 
 
The [ODBC Data Sources] section is mandatory. It provides the Driver Manager with a list of data sources that are supported for your connection requests. You can change the names in this list, but each entry must match its corresponding [ds_name] section in this file.
 
The [ds_name] sections contain a Driver= specification that points to the location of the installed driver, as well as a Description= specification that describes the driver. If you change the location of a driver, you must change the Driver= specification to match the new location, or you can use just the name of the driver and the driver manager will attempt to locate the driver based on information obtained from your environment.
 
Additionally, you might need to assign other entries depending on the driver that is being configured.  See the "ATTRIBUTES" section for a list of entries supported for dBASE data sources.  Note that in the .odbc.ini file, you are required to use the long name of these attributes.
 
For an example of the odbc.ini file, see the .odbc.ini man page.
 

TRANSLATING DATA

 
To perform a translation of your data from one character set to another, use the TranslationDLL keyword and optionally, the TranslationOption keyword, in the dBASE data source section of the .odbc.ini file.
 
Set the TranslationDLL keyword to the full path of translation DLL.
 
Set the TranslationOption to the ASCII representation of the 32-bit integer translation option.
 
 

CONNECTING TO A DATA SOURCE

 
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section of the .odbc.ini file to use for the default connection information.  Optionally, you may specify attribute/value pairs in the connection string to override the default values stored in the .odbc.ini file.
 
You can specify either long or short names in the connection string.  The connection string has the form:
 

DSN=data_source_name[;attribute= value[;attribute=value];...]

 
An example of a dBASE connection string is
 

DSN=DBASE_FILES;LCK=NONE;IS=0

 

ATTRIBUTES

 
The following list gives the long and short names for each attribute, as well as a description.
 
DataSourceName (DSN)

A string that identifies a single connection to a dBASE database.  Examples include "Accounting" or "dBASE Files."

 
Database (DB)

Specifies the directory in which the dBASE files are stored. 

 
CreateType (CT)

CreateType={dBASE3 | dBASE4 | dBASE5 | Clipper | FoxBASE | FoxPro1 | FoxPro25}. Tells the driver the type of table or index to be created on a Create Table or Create Index statement. The default is DBASE5. 

 
LockCompatibility (LCOMP)

LockCompatibility={Q+E | Q+EVirtual | dBASE | Clipper | Fox}.  The dBASE driver supports several different locking schemes, as follows:
 

LCOMP=DBASE             Borland-compatible locking.  This is the
                        default.
 LCOMP=Q+E               Locks are placed on the actual bytes
                        occupied by the record.  Only
                        applications that use the dBASE
                        driver can read and write to the
                        database.  Other applications are
                        locked out of the table completely
                        (they cannot even read other
                        records).  This locking is compatible
                        with earlier versions of INTERSOLV
                        products.
 LCOMP=Q+EVIRTUAL        The same as Q+E except that other
                        applications can open the file and
                        read the data.
                         The advantage of using a Q+E locking
                        scheme over DBASE locking is that,
                        on Inserts and Updates, Q+E locks
                        only individual index tags, while
                        DBASE locks the entire index.
 LCOMP=CLIPPER           Clipper-compatible locking.
 LCOMP=FOX               FoxPro- and FoxBASE-compatible
                        locking.
 

If you are accessing a table with an application that uses the dBASE driver, your locking scheme does not have to match the Create Type.  However, if you are accessing a table with two applications, and only one uses the dBASE driver, set your locking scheme to match the Create Type.  For example, you don’t have to set LCOMP=FOX to work with a FoxPro table.  But if you are using a FoxPro application simultaneously with an application using the dBASE driver on the same set of tables, set LCOMP=FOX to ensure that your data does not get corrupted. 

 
Locking (LCK)

Locking={NONE | RECORD | FILE}. Determines the level of locking for the database tables.  LCK=NONE offers the best performance but is intended only for single-user environments.  LCK=RECORD locks only the records affected by the statement.  LCK=FILE locks all the records in the table.  See Locking for a detailed discussion of this topic. 

 
FileOpenCache (FOC)

Determines the maximum number of unused file opens to cache.  For example, when FOC is set to 4, and a user opens and closes 4 files, the files are not actually closed.  The driver keeps them open so that if another query uses one of these files, the driver does not have to perform another open, which is expensive.  The advantage of FOC is increased performance.  The disadvantage is that a user who tries to open the file exclusively may get a locking conflict even though no one appears to have the file open. 

 
CacheSize (CSZ)

Determines the number of 64K blocks that the driver uses to cache database records.  The higher the number of blocks, the better the performance.  The maximum number of blocks you can set depends on the system memory available.  If CacheSize is > 0, when browsing backwards, you cannot see updates made by other users until you reexecute the Select statement. 

 
IntlSort (IS)

IntlSort={0 | 1}. Determines the order that records are retrieved when you issue a Select statement with an Order By clause.  If IS=1, the driver uses the international sort order as defined by your operating system.  The sort is case- insensitive (a precedes B); the sorting of accented characters is also affected (check your operating system documentation for more information).  If IS=0, the driver uses the ASCII sort order, where uppercase letters precede lowercase letters (B precedes a). 

 
ModifySQL (MS)

ModifySQL={0 | 1}. Provided for backward compatibility.  Specify MS=0 to have the driver understand SQL dialects found in earlier drivers.  The default is 1. 

 
Compatibility (COMP)

Compatibility={0 | 1}. Provided for backward compatibility with previous INTERSOLV products.  Use COMP=DBASE for backward compatibility; use COMP=ANSI for portability.  The default is ANSI. 

 
UltraSafeCommit (USF)

UltraSafeCommit={0 | 1}. Determines when the driver flushes the file cache.  If USF=1, the driver updates directory entries after each Commit.  This decreases performance.  If USF=0 (default) the driver updates the directory entry when the file is closed.  In this case, a machine "crash" before closing the file causes newly inserted records to be lost. 

 
ExtensionCase (EC)

ExtensionCase={UPPER | LOWER}. Determines whether upper- or lowercase file extensions are accepted.  If EC=LOWER, lowercase extensions are accepted.  If EC=UPPER, uppercase extensions are accepted. 

 
UseLongNames (ULN)

UseLongNames={0 | 1}. Specifies whether the driver uses long filenames . as table names. The default is 0, do not use long filenames. If UseLongNames=1, the driver uses long filenames. The maximum table name length is specific to the enviroment in which you are running (for example, in Windows 95, the maximum table name length is 128. 

 
UseLongQualifiers (ULQ)

UseLongQualifiers={0 | 1}. This attribute specifies whether the driver uses long pathnames as table qualifiers. When you set this check box, pathnames can be up to 255 characters. The default is 0, do not use long pathnames (the default length for pathnames is 128 characters). If UseLongQualifiers=1, the driver uses long pathnames (up to 255 characters). 

 

DATA TYPES

 
The following list shows how dBASE data types map to the standard ODBC data types.
 

     dBASE         ODBC
     -----         ----
     Binary (1)    SQL_LONGVARBINARY
     Char (2)      SQL_CHAR
     Date (3)      SQL_DATE
     Float (4)     SQL_DECIMAL
     General (5)   SQL_LONGVARBINARY
     Logical       SQL_BIT
     Memo (3)      SQL_LONGVARCHAR
     Numeric       SQL_DECIMAL
     Picture (6)   SQL_LONGVARBINARY
      (1)  dBASE V only
     (2)  254 characters maximum (1024 for Clipper)
     (3)  dBASE III, IV, V, FoxPro, FoxBASE, and Clipper
     (4)  dBASE IV and V
     (5)  dBASE V and FoxPro 2.5
     (6)  FoxPro, FoxBASE, and Clipper

 

COLUMN NAMES

 
Column names in SQL statements can be up to 10 characters long.  A column name can contain alphanumeric characters and the hyphen (-).  The first character must be a letter (a through z).
 

SELECT STATEMENT

 
You use the SQL Select statement to specify the columns and records to be read.  dBASE Select statements support all the Select statement clauses. This topic describes the information that is specific to dBASE, which is ROWID.
 

ROWID PSEUDO-COLUMN

 
Each record contains a special field named ROWID.  This field contains a unique number that indicates the record’s sequence in the database.  For example, a table that contains 50 records has ROWID values from 1 to 50 (if no records are marked deleted).  You can use ROWID in Where and Select clauses.
 
ROWID is particularly useful when you are updating records.  You can retrieve the ROWID of the records in the database along with the other field values.  For example,
 
SELECT last_name, first_name, salary, rowid FROM emp
 
Then you can use the ROWID of the record that you want to update to ensure that you are updating the correct record and no other.  For example, UPDATE emp set salary = 40000 FROM emp WHERE rowid=21
 
The fastest way of updating a single row is to use a Where clause with the ROWID.  You cannot update the ROWID column.
 
 

CREATE INDEX STATEMENT

 
The type of index you create is determined by the value of the CreateType attribute, which you set in the .odbc.ini file or as a connection string option.  The index can be any of the following:
 

•A dBASE IV or V (.MDX) index
•A dBASE III (.NDX) index
•A Clipper (.NTX) index
•A FoxBASE (.IDX) index
•A FoxPro (.CDX) index

 
The syntax for creating an index is as follows:
 

CREATE [UNIQUE] INDEX index_name ON base_table_name

(field_name [ASC | DESC] [,field_name
[ASC | DESC][, ...]])

 
index_name is the name of the index file.  For FoxPro 2.5, dBASE IV, and dBASE V, this is a tag, which is required to identify the indexes in an index file.  Each index for a table must have a unique name.
 
UNIQUE means that the driver creates an ANSI-style unique index over the column and ensures uniqueness of the keys. Use of unique indexes improves performance.  ANSI-style unique indexes are different from dBASE-style unique indexes.  With ANSI-style unique indexes, you receive an error message when you try to insert a duplicate value into an indexed field.  With dBASE-style unique indexes, you do not see an error message when you insert a duplicate value into an indexed field.  This is because only one key is inserted in the index file.
 
base_table_name is the name of the database file whose index is to be created.  The .DBF extension is not required because the driver automatically adds it if it is not present.  By default, dBASE IV and V index files are named base_table_name.MDX and FoxPro 2.5 indexes are named base_table_name.CDX.
 
field_name is a name of a column in the dBASE table.  You can substitute a valid dBASE-style index expression for the list of field names.
 
ASC tells dBASE to create the index in ascending order.  DESC tells dBASE to create the index in descending order.  By default, indexes are created in ascending order. You cannot specify both ASC and DESC orders within a single Create Index statement. For example, the following statement is invalid:
 

CREATE INDEX emp_i ON emp (last_name ASC, emp_id DESC)
 
The following table shows the files that are created when you issue a Create Index statement.

                     DBASE                       DBASE    FOXPRO   FOXPRO
CREATE TYPE          III    CLIPPER   FOXBASE   IV & V    2.5      2.5
-----------         -----   -------   -------   ------   ------   ------
 EXTENSION           .NDX     .NTX      .IDX∗    .MDX     .IDX∗∗    .CDX
 DBASE UNIQUE         Yes      Yes       Yes      Yes      Yes      Yes
 DESCENDING           No       Yes       No       Yes      Yes      Yes
 MAXIMUM KEY SIZE     100      250       100      100      240      240
 MAXIMUM KEY EXPR.    219      255       219      220      255      255
 PRODUCTION/          No       No        No       Yes      No       Yes
STRUCTURAL INDEXES
     ∗  IDX indexes are also created as the default for FoxPro 1.0.
    ∗∗ For FoxPro 25, compact IDX indexes have the same internal
       structure as a tag in a CDX file.   Can be created if IDX
       extension is included with the index name in the Create
       Index statement.

 

DROP INDEX STATEMENT

 
The syntax for dropping an index is as follows:
 

DROP INDEX {table_name.index_name}
 
table_name is the name of the .DBF file without the extension.
 
For FoxPro 2.5 and dBASE IV, index_name is the tag. Otherwise, index_name is the name of the index file without the extension.
 
To drop the index emphire.ndx, issue the following statement:
 

DROP INDEX emp.emphire
 

PACK STATEMENT

 
When records are deleted from a dBASE file, they are not removed from the table.  Instead, they are marked as having been deleted.  Also, when memo fields are updated, space may be wasted in the files.  To remove the deleted records and to free the unused space from updated memo fields, you must pack the database.
 
To reduce the size of a dBASE file, use the Pack statement. It has the following form:
 

PACK filename
 
filename is the name of the dBASE file to be packed.  The adds the extension if it is not present.  For example,
 

PACK emp
 
You cannot pack a file that is open by another user and you cannot use the Pack statement in manual commit mode.
 
For the specified file, the Pack statement does the following:
 

•Removes all deleted records from the file. 
 

•Removes all deleted records from .CDX and .MDX files having the same name as the file. 
 

•Removes all deleted records from .NTX, .NDX, and .IDX files specified in qedbf.ini. 
 

•Compresses unused space in the memo (.DBT or .FPT) file. 

 

LOCKING

With the dBASE driver, you can build and run applications that share dBASE database files on a network.  Whenever more than one user is running an application that accesses a shared database file, the applications should lock the records that are being changed.  Locking a record prevents other users from locking, updating, or deleting the record. 
 

LEVELS OF DATABASE LOCKING

 
The dBASE driver supports three levels of database locking:  NONE, RECORD, and FILE.  You can set these levels in

•The connection string (LCK=)
 

•The setup dialog box

 
No locking offers the best performance but is intended only for single-user environments.
 
With record or file locking, the system locks the database tables during Insert, Update, Delete, or Select...For Update statements.  The locks are released when the user commits the transaction.  The locks prevent other users from modifying the locked objects, but they do not lock out readers.
 
With record locking, only records affected by the statement are locked.  Record locking provides better concurrency with other users who also want to modify the table.
 
With file locking, all the records in the table are locked.  File locking has lower overhead and may work better if records are modified infrequently, if records are modified primarily by one user, or if a large number of records are modified.
 

HOW TRANSACTIONS AFFECT RECORD LOCKS

 
When an Update or Delete statement is executed, the driver locks the records affected by that statement.  The locks are released after the driver commits the changes.  Under manual commit mode, the locks are held until the application commits the transaction. Under autocommit mode, the locks are held until the statement is executed.
 
When a Select...For Update statement is executed, the driver locks a record only when the record is fetched.  If the record is updated, the driver holds the lock until the changes are committed.  Otherwise, the lock is released when the next record is fetched.

INTERSOLV, Inc.  —  Last change: 30 September 1995