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).
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!
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)"
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").
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 :-(
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.