<a href="http://linux.techass.com/projects/xdb/"><img src="xbase_logo.png" border="0" align="right" /></a>
<p>
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.
</p>
<p>
The DBXbase module is a compilation of two open-source libraries :</p>
<ul>
<li>The <a href="http://linux.techass.com/projects/xdb/">Xbase</a> library allows the manipulation of "xbase"-style database files, like those for use with dBase, FoxPro and Clipper databases.</li>
<li><a href="http://www.quaking.demon.co.uk/xbsql/">xbSQL</a> is a wrapper for Xbase which adds SQL query execution.</li>
</ul>
<h2>Requirements</h2>
<p>
Everything you need is included with the module.
</p>
<h2>Accessing an Xbase Database</h2>
<p>To enable an Xbase connection, you simply pass a <i>dbtype</i> of &quot;<b>XBASE</b>&quot; to the
<a href="../../database.mod/doc/commands.html#LoadDatabase">LoadDatabase</a> function.
</p>
<p>The second parameter <i>dbname</i> is a directory in which the database table-files (.dbf) are located.<br>
To refer to the applications current directory, the parameter "." will suffice.
</p>
<p>The module takes care of opening the correct files, based on the table-names used in the SQL statements.</p>
<p>The <i>host</i>, <i>port</i>, <i>user</i>, <i>password</i> and <i>server</i> parameters are not required for this driver.</p>
<h2>Prepared Statement Placeholders</h2>
<p>Xbase uses the question mark - ? - notation for placeholders.<br>
Each question mark represents a separate placeholder to which you need to bind a value.</p>
<h2>Transactions</h2>
<p>Xbase does <b>NOT</b> support transactions. The methods <a href="../../database.mod/doc/commands.html#startTransaction">startTransaction</a>, <a href="../../database.mod/doc/commands.html#commit">commit</a> and <a href="../../database.mod/doc/commands.html#rollback">rollback</a> simply return True.<br>
<h2>SQL with Xbase</h2>
<p>The following syntax description is taken from the documentation for xbSQL</p>
<p>
Valid SQL syntax includes the following :
</p>
<ul>
<li>SELECT e1, ... FROM t1, ... [ WHERE c1 ... ] [ GROUP BY g1, ... ] [ HAVING h ] [ ORDER BY o1, ... ]</li>
<li>INSERT INTO t VALUES (e1, ...)</li>
<li>INSERT INTO t (c1, ...) SELECT ...</li>
<li>INSERT INTO t SELECT ...</li>
<li>INSERT INTO t (c1, ...) VALUES (e1, ...)</li>
<li>UPDATE t SET c1 = e1, ... WHERE c1, ...</li>
<li>DELETE FROM t WHERE c1, ...</li>
<li>CREATE TABLE t (colspec, ...)</li>
<li>DROP TABLE t</li>
</ul>
<p>
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.
</p>
<p>
The following aggregate functions are available:
</p>
<ul>
<li>MIN(e)</li>
<li>MAX(e)</li>
<li>SUM(e)</li>
<li>COUNT(*)</li>
</ul>
<p>
The following general functions are implemented:
</p>
<ul>
<li>UPPER(e)</li>
<li>LOWER(e)</li>
<li>NULLIF(e1,e2)</li>
<li>TOCHAR(e)</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
<p>
Available column types for create column specifications are as below. A blob is binary data; XBase itself does not have a time data type.
</p>
<ul>
<li>int</li>
<li>double</li>
<li>char</li>
<li>date</li>
<li>blob</li>
</ul>
<p>
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.
</p>
<p>
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).
</p>
<p>
Memo fields are always created with a notional width of 10, as required by the XBase specification.</p>
<p>
Unsupported SQL includes
</p>
<ul>
<li>sub-selects</li>
<li>create .... as</li>
<li>alter table ...</li>
<li>inner, etc., joins </li>
</ul>
