unixODBC + mdbTools + PHP

Question

I need to connect a PHP script in a LAMP (Linux Ubuntu 12.10, Apache 2, MySQL 5, PHP 5.3) server to an Access MDB database (version 2003).

1. unixODBC and Driver installation

The Ubuntu 12.10 comes with unixodbc 2.2.14 package (http://packages.ubuntu.com/quantal/unixodbc). The installation was easy: apt-get install unixodbc libmdbodbc1 php5-odbc. This way I've unixODBC with the mdbTools driver and the ODBC PHP capability.

I've edited /etc/odbcinst.ini with the mdbtools driver:

[MDBToolsODBC]
Description = MDBTools Driver
Driver = libmdbodbc.so.1

I've edited /etc/odbc.ini with the Access datasource:

[FormPulmo]
Description = FormulariCDRPulmo
Driver = MDBToolsODBC
Servername = localhost
Database = /mnt/svrfit/cdr/bd_pulmo_hardlink.mdb
UserName =
Password =
port = 5432

Finally I tested from shell and it worked:

> isql -v formpulmo
Connected!

2. PHP connection

With PHP initialy all seems to work ok:

$link = odbc_connect ('formpulmo',"","");
$res = odbc_exec ($link,"SELECT * FROM exampleTable");

The first problem was trying to access tables with spaces in their names. Example: "example Table". In Windows I've to put that between brackets ([example Table]) but it didn't worked. Finally I found the solution:

$res = odbc_exec ($link,"SELECT * FROM \"example Table\"");

Before this solutions all the browser response trying to do the odbc_exec were "Error 324 (net::ERR_EMPTY_RESPONSE)"

3. PHP problems !

But now I'm stuck with the UPDATE syntax. The normal query is:

$res = odbc_exec ($link,"UPDATE [Registre cancer de pulmo] SET CIP = 'example' WHERE CIP = 'example'");

The browser response is: "Error 324 (net::ERR_EMPTY_RESPONSE)" (In Firefox: "The connection was reset").

3.1. Tried solutions

UPDATE \"Registre cancer de pulmo\" SET CIP = 'example' WHERE CIP = 'example'
UPDATE \"Registre cancer de pulmo\" SET \"CIP\" = 'example' WHERE \"CIP\" = 'example'
UPDATE {Registre cancer de pulmo} SET {CIP} = 'example' WHERE {CIP} = 'example'

Connect with different cursors:

odbc_connect ($odbcFormPulmo,"","",SQL_CUR_USE_ODBC);
odbc_connect($odbcFormPulmo,"","",SQL_CUR_USE_DRIVER);

I don't know what more I can try :-(

Solution

Several weeks ago in response to this question I did some testing on a setup almost identical to yours (Ubuntu 12.04 instead of 12.10) and found that I could not get mdbtools to work at all. I gather that some people can sometimes get it to sort of work, but IMO mdbtools is not reliable enough for production use.

In my answer to that question I recommended investigating ODBTP as an alternative. It is a free (GPL) TCP/IP protocol that allows you to pass queries to a Windows machine, which then submits the query via its ODBC driver and passes the results back to you. I have used it a few times in the past and it has worked very well.

Comments

[Dimas]

It worked, thx! This solution is not perfect because it depends on a Windows machine, but it's better than nothing. Hopefully some day we will have a good and free odbc/mdb driver.

[Fernando Mertins]

Hi, I agree... Tried to run a simple insert using MDBTools and got "SQL error: Couldn't parse SQL" error.