2013-09-03

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.

12 comments:

Jesus Manuel Conejo Sarabia said...

Thanks so much!!..It's very useful!!.

Anonymous said...

Could you possibly include a demo of your php file including the code please ?

Clément Nedelcu said...

Hi Anonymous,
Try this: http://cnedelcu.net/DBISAM.zip
It's a quick wrapper I made. Check test.php first, then configure DBISAM.php.

Make sure:
1) you have installed the DBISAM ODBC driver (check Elevate Software website to download it)
2) you properly define the path of your database files (in DBISAM.php from the zip)
3) if you have a table called "customers.idx" / "customers.dat", you can do "SELECT * FROM customers". The name of the table corresponds to the name of the file.

Let me know if you need any help!

AvengersArmy said...
This comment has been removed by the author.
John Johnson said...

Good report is often based on SQL View or Stored Procedure. And professional design often involves computer graphics in the form of logos, database driven posters, etc. Here you have to be comfortable with such things as Photoshop, Illustrator and maybe even Flash file creation software.

Java Development Company

mdeva said...

I find pen and paper are both faster and more accurate than Excel.

Unknown said...

Hello Mr. Clement Nedelcu

Receive my most cordial greeting mia siguinete siguinete pregunat is the need to connect to a DBISAM BD with PHP to extract some data which should be compared with other BD DBISAM and then update the data in both BD rather update the inventory of two stores each while, actually I have some ideas, but have failed to do so please could help me, my email is jcontasti@gmail.com

baixar mobogenie said...

Thanks for sharing this quality information with us. I really enjoyed reading.
----
i like play game click jogos online free and apply baixar facebook movel online free and apply mobogenie online free

Roshini RS said...

Great Post, Actually PHP is a beautiful source for developing a database driven web application, I love this post, thanks for spending your time for discussing about this topic.
Regards,
PHP Institutes in Chennai

five nights at freddy's said...

I think I need it. Thank you for update information. i like your blog.
----
facebook móvil | five nights at freddy's download | facebook movil

Melisa said...

Thanks for sharing this niche useful informative post to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future so do your SAP training in chennai
Regards,
SAP Course in Chennai|sap training institute in Chennai|SAP BI Training In Chennai

jhansi joe said...

Well post, Thanks for sharing this to our vision. In recent day’s customer relationship play vital role to get good platform in business industry, Sales force crm tool helps you to maintain your customer relationship enhancement.
Regards,
Salesforce training in Chennai|Salesforce training center in Chennai|Salesforce training in velachery

Search This Blog

Loading...