WHERE clause does not work in SQL query using mdbtools

Question

I worked on a project using a LAMP server, which requires frequent reading of the MS-ACCESS database stored in the * .mdb file. The database contains one table and about 40,000 rows.

I installed mdb-tools and configured unixODBC to use it. The first impression was positive:

 SELECT * FROM [Table] 

It worked fine, but returned the huge structure that flew over my buffer, and the data tail was truncated.

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

It worked fine, but the amount of data was still on the verge of usability (it almost exceeded the buffer). So I tried to limit the result with the WHERE , 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' 

does not work. They simply return an empty structure. But if the condition is all-match, it works:

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

returned that huge mass of data

I know that mdb-tools is a fairly ancient solution that provides read-only access, but this is normal for me. I just want it to work. I tried both versions - from sourceforge and from github.

Answer 1

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

... WHERE [col1] LIKE 'xxx' 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%'

must match Toronto, Tokyo, Toledo, etc ....

Edit

The manual page for the mdb-sql MDB Tools command claims to support LIKE , so I put together a test .mdb file with a table named [Clients] containing

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

I did sudo apt-get install mdbtools on my test server (Ubuntu 04/12/02), downloaded 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 the WHERE clauses work in this context. If they do not work, your MDB tools are broken. If they really work, we will need to continue research.

Edit

I spent some time trying to recreate this problem in PHP on a Ubuntu 12.04 test server. I could not do this just because I was unable to get the odbc_ functions in PHP to work with mdbtools in general . I can establish the “correct” connection (without errors), and I could “execute” the request (again, without errors), but I could not get any request to actually return results, not even SELECT * FROM Clients .

In search of help with my problem, I came across a Stack Overflow article, which further prevented me from continuing to consider this issue.

Instead, since this is apparently an active Access database hosted on a Windows computer, I personally would prefer to use ODBTP . This is a free TCP / IP (GPL) protocol that allows you to transfer requests to a Windows computer, which then sends the request through its ODBC driver and sends the results back to you.

This requires the service to be installed and running on a Windows machine, and the PHP client component must be compiled into Linux boxes, but if these requirements are not too complicated, then this is a pretty reliable solution for this type of problem. (I have used it several times in the past.)

Although it is true that ODBTP has not been updated for several years, it still works: I tested it only now when my LAMP server ran the following PHP script to request a .mdb file located on my old Vista laptop:

 <?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>'; 

Results displayed in my browser window

 ODBTP test: 1: Thompson 4: Thompson 

Answer 2

Some time has passed...

mdb-tools is a CRAP.

---

We ended up creating our own Windows service that listened for SQL queries on the specified port and maximized them in Access via an ADO connection.

So, if you have such an opportunity, I recommend that you do not use dead mdb tools.

Answer 3

I have a suspicious suspicion that your WHERE col data contains some special characters, such as single quotes or double quotes OR there are some problems with PrimaryKey.

I use MDBTools, and I successfully select cols from wildcard tables, BUT, when I try to select from a specific col that has several double quotes in the data, I get ZERO. When I run php blah.php in this particular column, I get an error

CRITICAL: cannot find column with internal identifier ### in index PrimaryKey

Found in the code on line 309, but still haven't passed it ... https://github.com/brianb/mdbtools/blob/master/src/libmdb/index.c