Can anyone tell me if there is limited SQL support using unixODBC drivers on Ubuntu with PHP? I've setup a basic lamp server on Ubuntu 11.10, and I'm trying to query an Access database. I've installed php5-odbc and MDB Tools. Here is some sample code:
<?php
$conn = odbc_connect('logindb','','');
if (!$conn) {
echo "failed";
}
$sql = "SELECT * FROM class";
//$sql = "SELECT class.desc, event_classes.event_class_id FROM class inner join
//event_classes on class.class_id = event_classes.class_id";
$rs = odbc_exec($conn, $sql);
while ($d = odbc_fetch_array($rs)) {
var_dump($d);
}
?>
The first query, the simple select, works just fine. However, when trying to perform a join (second commented query), I receive 0 records. I was able to run the SQL successfully in the SQL editor within Access, so I know my SQL is correct and the join is working there. Has anyone had any experience with this? I know Linux wasn't made to be compatible with proprietary Microsoft products, but unfortunately I have to make reading an Access database on Ubuntu work. Any help is greatly appreciated.
mdbtools on sourceforge has not been updated since 2004. I see someone has put it on github and seems to have made a few small changes. The SQL support in it was never that good, it was read only and I know from experience trying it under Perl with DBD::ODBC a lot of the tests failed. I think you are going to have a hard time with it.
There are other more reliable ways to access a MS Access database from Linux but they are all to my knowledge commercial. To access a MS Access database from Linux I know of the following:
Easysoft have an ODBC-ODBC Bridge (which can be used to access a MS Access DB on a windows machine using the MS Access ODBC Driver) but it requires installing a service on the Windows machine. Easysoft also have a MS Access ODBC driver which can be used for direct access to the mdb/accdb file so long as it is visible from Linux.
There are other commercial ODBC bridges from Openlink.