MDBTools driver not returning string values with PHP MS-Access

Question

We have an MS Access attendance database, which is updated by biometric hardware. So there is no way to replace MS Access. Now we need to provide the attendance information on our intranet web, for which we are trying to periodically read the MS-Access mdb file on a Windows XP computer, and write to a postgres database through php. PHP - Postgres - Apache are running on Ubuntu 10.04 Server. html pages /reports are to be displayed from the server. When using MDB tools to connect to the MS-Access mdb file from php, only the Number and Date/Time fields are returned (though as String). The Text fields return NULL.

The PHP code is as follows:

$dbName = "/media/winshare/attEngine.mdb";
if (!file_exists($dbName)) 
    die("Could not find database file.");
$dbconn = new PDO("odbc:DRIVER=MDBTools; DBQ=$dbName; Uid=admin; Pwd=pswd;");
if ($dbconn) { 
    echo "mdb connection established.<br />\n";
} else {
    die ("mdb connection could not be established.<br />\n");
}
$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

echo "QRY RESULT (from Access):<pre>\n";
var_dump($result);
echo "\n</pre>\n";

Here: transactionId is AutoNumber in Access; aDate, aDateTime are Date/Time; EmpCode is Number; and EmpName and ControllerNum are Text fields in Access.

When we load the php, it gives result as follows (only first two array-elements shown):

mdb connection established.

QRY RESULT (from Access):

array(31986) {
  [0]=>   array(7) {
    ["transactionId"]=>     string(3) "341"
    ["aDate"]=>     string(17) "11/23/13 00:00:00"
    ["aDateTime"]=>     string(17) "11/23/13 13:01:07"
    ["EmpCode"]=>       string(1) "0"
    ["EmpName"]=>       NULL
    ["ControllerNum"]=>     NULL
  }
  [1]=>   array(7) {
    ["transactionId"]=>     string(3) "342"
    ["aDate"]=>     string(17) "11/23/13 00:00:00"
    ["aDateTime"]=>     string(17) "11/23/13 13:01:12"
    ["EmpCode"]=>       string(1) "0"
    ["EmpName"]=>       NULL
    ["ControllerNum"]=>     NULL
  }

Actually I have 2 questions:

1. What could be the problem in my using MDBTools as above?

2. Or is it better to run / schedule scripts on the Windows computer to connect through odbc to Access and postgres, and transfer data? If so what are the best scripts for that?

Answer 1

This is an edit to my original answer:

After a days of hard struggles, I finally figured out a working solution for your Thread's subject (MDBTools driver not returning string values with PHP MS-Access)

In addition to my old answer which is very limited to the 127 field size for the Text datatype, Here is my new attempt for a solution.

Solution:

Instead of using the PDO Class in manipulating the access db, I recommend using ODBC Functions to do the job.

Example:

In your code block

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

Change it to

$connection = odbc_connect("YourDSN","admin","pswd"); 
$sql = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions";
$result = odbc_exec($connection,$sql);
while($row = odbc_fetch_array($result))
{   echo "<pre>";
    print_r($row);
    echo "</pre>";
}

Where "YourDSN" is a DSN(Data Source Name) that needs to be created in the odbc.ini file in your Ubuntu Server which can be found on the /etc folder. Type the DSN format below in your odbc.ini file.

The DSN is made in this format:

[MyDataSource]

Description = The Source of My Data

Driver = MyDriver

ServerName = localhost

Database = MyDatabase/Complete path of your DB FIle

Which in my sample code is:

[YourDSN]

Description = This is the configured DSN for your access db

Driver = MDBTools

ServerName = localhost

Database = /var/www/{your dns}/{public_html}/.../.../media/winshare/attEngine.mdb

^Note(1) The Database must be the complete directory starting from the root(eg. /var/www/...)

^Note(2) The Driver must be MDBTools

That's it! Just figure out the DSN configuration and you are good to go. You can now finally retrieve Text datatypes in access with its max field size. I hope this helps everyone. Feel free to reply or comment if you have some clarifications.

OLD ANSWER:

This is to answer only your 1st question and the subject of this thread: I think there is nothing wrong with the way you use the MDBTools in your code.

After hours of searching the net. I finally found this one thread that is exactly the same as the problem that I was having(MDBTools driver not returning string values with MSACCESS using PHP running in a linux os). Maybe this only exists on accessing MS ACCESS in PHP which being run in a LINUX os? I don't know.

Luckily for us who faces this very problem, I have seem to find a work around for this.

Instead of using the prepare and execute function of PDO, try to use the query one.

EXAMPLE

Change these lines of code:

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$dbqryprep = $dbconn->prepare($qry);
$dbqryprep->execute();
$result = $dbqryprep->fetchall(PDO::FETCH_ASSOC);

to this:

$qry = "SELECT transactionId, aDate, aDateTime, EmpCode, EmpName, ControllerNum FROM Transactions;"; 
$result = $dbconn->query($qry)->fetchAll(PDO::FETCH_ASSOC);

And then:

In your MSACCESS DB file(.mdb, .accdb), Just change the Field Size of the Text Data Type to 127 or less.

Remember that this work around only works if the values in your Text columns have max. characters of 127 only.

Thus, the Text Datatype must be limited to 127 characters inorder for MDBTools to retrieve the text in PHP.

I don't see this as a solution but rather than a bug discovered. I hope somebody notice this. It would help us a lot. Specially those who will encounter this in the future.

Answer 2

My solution was as follows: to turn it the other way round.

i.e. instead of running the scripts on Linux system, I set up the Windows personal web server on the windows system and ran the php files on Windows, with ODBC connection to the local MS-Access mdb, and postgres on Linux server. In this mode, all the complex SQL queries are supported for Access.

May be I can also schedule the php script on Windows (without GUI) for regular updates!