Access a DBISAM database with PHP via ODBC

Recently I've been needing to access a DBISAM database created by third-party application, in order to export some of its data. Initially, I didn't even know what format the database was; I'd just found a bunch of files with the following extensions: .dat, .idx, .blb, .ibk, .bbk, and more. After a quick Google search, I stumbled upon a few pages that led me to DBISAM from Elevate Software. It's a proprietary database format that doesn't require a server application, somewhat similar to SQLite.

Accessing the database from PHP was quite complex because I didn't have a proper reference or blog article to detail the process. That's exactly why I'm writing this: to give people directions on a subject that's hardly documented at all. In this tutorial, you'll learn how to access the database via PHP, execute a simple SQL query, and display the result.

ODBC driver and connection

I almost forgot: there's a catch-- even two, and here's why. In order for PHP to be able to communicate with this proprietary database system, you'll need to install an ODBC driver. First catch: as far as I know, the driver is only available for Windows. Second catch: while you'll be able to download the trial version free of charge, it'll cost you $250 to get the full unlocked driver. Right now I'm using the trial version, and I'll probably purchase the retail product when the trial is over.

As I said, we're going to have to use Windows to access our DBISAM database, and I'd highly recommend WAMP for your web server/PHP setup. Begin by registering at Elevate Software and download the trial version of the ODBC driver the install it. To make sure it installed correctly, go to the Windows start menu and look for the "ODBC data sources" program, open the 32-bit version preferably. In the window that appears, click on the "Add..." button and you should see a window like this:

This is a list of data source drivers, among which you should find "DBISAM 4 ODBC Driver". Now we're not going to do anything with this for the time being -- we're just checking that the driver installed correctly; so just click "Cancel" and close the ODBC data sources program.

Connecting to the database

In order to be able to connect to an ODBC data source, your PHP setup must have the ODBC extension enabled. If you are using WAMP as I recommended previously, it should already be there and enabled, as it comes built-in with the PHP distribution from WAMP. However if you're using any other form of PHP setup, you need to run the phpinfo() function and see if you find ODBC in the list of enabled extensions. Check the PHP.net documentation for more information on setting up the ODBC extension.

Now we've got that cleared up, we can actually write our first script that will connect to the DBISAM database. Remember: a DBISAM database has no server, you just need to indicate the path of the database files; the ODBC driver will do the rest (I/O operations).

The function that interests us here is odbc_connect(). It takes three parameters: the DSN (data source name), a login, and a password. Here's a tip: by default, the DBISAM login is "admin" and the password is blank (empty password). At least this worked for me. Now this leaves us to figure out the DSN.

Here is the DSN I am currently using: DRIVER={DBISAM 4 ODBC Driver};ConnectionType=Local;CatalogName=c:/wamp/www/odbc/data/;
There are three components:

• The DRIVER: you must specify the exact name of the driver as it appears in the "ODBC data sources" program that I had you open above (see screenshot above). 
• The ConnectionType: it's set to Local because we're reading files from our local computer, not remotely.
• The CatalogName: simply specify the path of the folder that contain your database files. Use normal slashes instead of backslashes.

So, this is the full command that we'll use to establish the connection:
$db = odbc_connect("DRIVER={DBISAM 4 ODBC Driver};ConnectionType=Local;CatalogName=c:/wamp/www/odbc/data/;","admin","");

If you get a warning, something probably went wrong. Check the driver name, the database path, make sure you typed everything correctly. If no error or warning shows up, you're good, and $db will contain a resource identifier.

Executing a query and displaying its contents

If you've already used MySQL or other database providers before, this will be a piece of cake. The principles are similar: execute an SQL query, run a while loop fetching the results. Let's assume you have a "customers" table; in practice this means you should see these two files in your data folder: customers.dat and customers.idx (there may be more files though - blb, blk, etc.). Here's the full code sample that I wrote.

$db = odbc_connect("DRIVER={DBISAM 4 ODBC Driver};ConnectionType=Local;CatalogName=c:/wamp/www/odbc/data/;","admin","");

$res = odbc_exec($db,"SELECT * FROM customers");

echo odbc_num_rows($res)." rows found";

while($row = odbc_fetch_array($res)) {

    print_r($row);

}

This code sample loops through all the records of the customers table and displays them in the current page. That's it! Now all you have to do is work your brains and write the appropriate SQL queries. If you have any questions on the subject I'd be happy to answer them.