Database Driver - Xbase

The DBXbase database driver allows you to connect to Xbase databases (dBase, FoxPro, Clipper, etc) through the standard API provided by the Database Framework module.

The DBXbase module is a compilation of two open-source libraries :

• The Xbase library allows the manipulation of "xbase"-style database files, like those for use with dBase, FoxPro and Clipper databases.
• xbSQL is a wrapper for Xbase which adds SQL query execution.

Requirements

Everything you need is included with the module.

Accessing an Xbase Database

To enable an Xbase connection, you simply pass a dbtype of "XBASE" to the LoadDatabase function.

The second parameter dbname is a directory in which the database table-files (.dbf) are located.
To refer to the applications current directory, the parameter "." will suffice.

The module takes care of opening the correct files, based on the table-names used in the SQL statements.

The host, port, user, password and server parameters are not required for this driver.

Prepared Statement Placeholders

Xbase uses the question mark - ? - notation for placeholders.
Each question mark represents a separate placeholder to which you need to bind a value.

Transactions

Xbase does NOT support transactions. The methods startTransaction, commit and rollback simply return True.

SQL with Xbase

The following syntax description is taken from the documentation for xbSQL

Valid SQL syntax includes the following :

• SELECT e1, ... FROM t1, ... [ WHERE c1 ... ] [ GROUP BY g1, ... ] [ HAVING h ] [ ORDER BY o1, ... ]
• INSERT INTO t VALUES (e1, ...)
• INSERT INTO t (c1, ...) SELECT ...
• INSERT INTO t SELECT ...
• INSERT INTO t (c1, ...) VALUES (e1, ...)
• UPDATE t SET c1 = e1, ... WHERE c1, ...
• DELETE FROM t WHERE c1, ...
• CREATE TABLE t (colspec, ...)
• DROP TABLE t

The driver supports several aggregate functions, GROUP BY, and HAVING. Note, however, that there are currently no sanity checks (hence, select n, sum(n) is valid. The results of such a query are not defined.

The following aggregate functions are available:

• MIN(e)
• MAX(e)
• SUM(e)
• COUNT(*)

The following general functions are implemented:

• UPPER(e)
• LOWER(e)
• NULLIF(e1,e2)
• TOCHAR(e)

Since XBase has no notion of null values, NULLIF is only defined for text arguments; the result is the value of the first argument unless it is an empty string, in which case the result is the value of the second argument.

Expressions e1 are currently fairly limited, just some basic arithmetic and string concatenations, plus equality/inequality, greater/less greater-or-equal/less-or-equal. like is also supported using either % as the wildcard character, and with (by default) case-insensitive matching.

Available column types for create column specifications are as below. A blob is binary data; XBase itself does not have a time data type.

• int
• double
• char
• date
• blob

The SQL parser accepts as column names any non-empty string of alphanumeric characters, or the underscore _ character, provided that at least one character a alphabetic or the underscore. To allow for column names in exising table which are the same as the SQL keywords, or which contain a space, the notation [name] can be used, hence select [The Date], [The Time] from MyTable is syntactically valid.

XBase files all contain fixed-width columns, with the exception of the blob type, which maps to a memo column. The first three cases can therefore have (width) appended. Individual columns can be indexed, for example, (...., ident int(10) index, ...). The date type will default to a width of 8 if the width is not specified or is specified as zero (since XBase stores dates in the format YYYYMMDD).

Memo fields are always created with a notional width of 10, as required by the XBase specification.

Unsupported SQL includes

• sub-selects
• create .... as
• alter table ...
• inner, etc., joins

Module Information