WHERE clause not working in SQL-query using mdbtools

Question

I've been working on a project using the LAMP server that requires frequent read of the MS-ACCESS database, stored in *.mdb file. The database has one table and about 40.000 rows in it.

I've installed mdb-tools and configured unixODBC to use it. First impression was positive:

SELECT * FROM [Table]

Worked fine, however returned a huge structure, that overflew my buffer and the tail of data was truncated.

SELECT [col1], [col2], [col3] FROM [Table]

Worked fine as well, but the amount of data was still on the edge of usability (barely didn't exceed the buffer). So I've tried to limit result with the WHERE statement, but neither

SELECT * FROM [Table] WHERE [col1]=X
nor
SELECT * FROM [Table] WHERE [col2] LIKE 'xxx'
nor
SELECT [col1], [col2], [col3] FROM [Table] WHERE [col1]=X
nor
SELECT [col1], [col2], [col3] FROM [Table] WHERE [col2] LIKE 'xxx'

isn't working. They just return empty structure. But if the condition is all-matching - it works:

SELECT * FROM [Table] WHERE [col2] LIKE '%'

returned that huge bulk of data

I know that mdb-tools is quite an ancient solution, that provides read-only access - but that's ok for me. I just want it to work. I've tried both versions - from sourceforge and from github.

Answer 1

If col1 is a text column then use ... WHERE [col1] = 'some text' (note the use of quotes) for an exact match.

... WHERE [col1] LIKE 'xxx' is equivalent to ... WHERE [col1] = 'xxx'.

When using LIKE the wildcard character % matches zero or more characters, and _ matches one character, so

... WHERE [col1] LIKE 'To%'

should match "Toronto", "Tokyo", "Toledo", etc. ...

Edit

The man page for the mdb-sql command of MDB Tools does claim to support LIKE, so I put together a test .mdb file with a table named [Clients] that contained

ID  LastName    FirstName       Email
--  ----------  --------------  ------------------
1   Thompson    Gord            [email protected]
2   Loblaw      Bob             [email protected]
3   Kingsley    Hank            [email protected]
4   Thompson    Hunter S.       [email protected]

I did sudo apt-get install mdbtools on my test server (Ubuntu 12.04.02), uploaded the .mdb file and did the following

gord@pingu:~$ mdb-sql -p ~/ubuTest2003.mdb
1 => SELECT ID, LastName FROM Clients
2 => go

ID      LastName
1       Thompson
2       Loblaw
3       Kingsley
4       Thompson
4 Rows retrieved
1 => SELECT ID, LastName FROM Clients WHERE LastName LIKE 'Thomp%'
2 => go

ID      LastName
1       Thompson
4       Thompson
2 Rows retrieved

If you are having difficulty with WHERE clauses I would suggest running a similar test (with a small sample dataset) on your system using mdb-sql to see if WHERE clauses work in that context. If they don't work, then your MDB Tools is broken. If they do work, then we'll need to investigate further.

Edit

I spent some time trying to recreate this issue in PHP on my Ubuntu 12.04 test server. I was unable to do so, only because I was unable to get the odbc_ functions in PHP to work with mdbtools at all. I could establish a "valid" connection (no errors) and I could "execute" a query (again, no errors), but I could not get any query to actually return results, not even SELECT * FROM Clients.

In searching for assistance with my problem I happened upon the Stack Overflow article here, which further discouraged me from pursuing the matter further.

Instead, since this is apparently an active Access database hosted on a Windows machine, I personally would opt for using ODBTP. 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.

It does require that a service be installed and run on the Windows machine, and the PHP client component must be compiled on Linux boxes, but if those requirements aren't too daunting then it is a pretty robust solution to this type of problem. (I have used it a few times in the past.)

And while it is true that ODBTP hasn't been updated for several years, it still works: I tested it just now with my LAMP server running the following PHP script to query an .mdb file residing on my older Vista notebook:

<?php
echo '<html><body><pre>';
echo "ODBTP test:\n";

$con = odbtp_connect('192.168.1.254', 
        'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\__tmp\\ubuTest.mdb;UID=admin;PWD=;');
if (!$con) die('_connect error: ' . odbtp_get_error($con));
$rst = odbtp_query("SELECT * FROM Clients WHERE LastName LIKE 'Thomp%'");
if (!$rst) die('_query error: ' . odbtp_get_error($con));
while ($row = odbtp_fetch_assoc($rst)) {
    echo $row['ID'] . ': ' . $row['LastName'] . "\n";
}
echo '</pre></body></html>';

The results as displayed in my browser window are

ODBTP test:
1: Thompson
4: Thompson

Answer 2

It's been a while...

mdb-tools is CRAP.

---

We have ended up by writing out own Windows service listening to SQL queries on a port given and proxing them to Access by an ADO connection.

So if only you have such opportunity, I recommend you not to use dead mdb-tools.