I am having trouble debugging a connection to an MSAccess 2010 .accdb file.
I'm running a Scotch Box Vagrant vm (v3.5) and have installed the MDBTools odbc driver via sudo apt-get install mdbtools.
My /etc/odbcinst.ini file looks like this:
# To enable unixODBC tracing, add this section to odbcinst.ini.
# Include the [ODBC] section heading.
[ODBC]
Trace = yes
TraceFile = /var/www/unixODBC.log
[MDBTools]
Description=MDBTools Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libmdbodbc.so
FileUsage=1
UsageCount=1
I've also created a datasource in /etc/odbc.ini
[CONTACTS]
Driver = MDBTools
Description = Contacts Database
Database = /data/contacts-db.accdb
Server = localhost
The test php file I'm using to attempt a connection is called testMSACCESS.php and looks like this:
<?php
try {
$dbFile = '/data/contacts-db.accdb';
echo 'Does file exist at ' . $dbFile . '? ';
var_dump(file_exists($dbFile));
var_dump(file_exists($dbFile));
echo 'Is the file readable ? ';
var_dump(is_readable($dbFile));
echo 'Is the file writable ? ';
var_dump(is_writable($dbFile));
$dbh = new PDO("odbc:Driver=MDBTools;DBQ={$dbFile};");
// or $dbh = new PDO("odbc:Driver=MDBTools;DSN=CONTACTS;");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if ($dbh){
echo "success <br/>";
}
$dbh = null;
}catch (Exception $e){
echo '<pre>';
echo 'Exception caught: could not connect to database.' . PHP_EOL;
echo (string) $e;
echo '</pre>';
}
The db file, contacts-db.accdb, is in a dir at the root of the machine /data.
When I try to connect by loading the script in a browser, the Exception thrown looks like this:
Does file exist at /data/contacts-db.accdb? bool(true)
Is the file readable ? bool(true)
Is the file writable ? bool(true)
Exception caught: could not connect to database.
PDOException: SQLSTATE[00000] SQLDriverConnect: 0 in /var/www/public/testMSACCESS.php:6
Stack trace:
#0 /var/www/public/testMSACCESS.php(6): PDO->__construct('odbc:Driver=MDB...')
#1 {main}
When running the script via the cmd line, I get a bit more info:
vagrant@scotchbox:~$ php /var/www/public/testMSACCESS.php
Does file exist at /data/contacts-db.accdb? bool(true)
Is the file readable ? bool(true)
Is the file writable ? bool(true)
Unknown Jet version.
File not found
Unable to locate database /data/contacts-db.accdb
<pre>Exception caught: could not connect to database.
PDOException: SQLSTATE[00000] SQLDriverConnect: 0 in /var/www/public/testMSACCESS.php:6
Stack trace:
#0 /var/www/public/testMSACCESS.php(6): PDO->__construct('odbc:Driver=MDB...')
#1 {main}</pre>
And this is what the unixODBC trace log looks like:
[ODBC][1515][1536073973.983218][__handles.c][460]
Exit:[SQL_SUCCESS]
Environment = 0x5591139d5d60
[ODBC][1515][1536073973.985108][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x5591139d5d60
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][1515][1536073973.985588][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][1515][1536073973.990811][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x5591139d5d60
Attribute = SQL_ATTR_CP_MATCH
Value = (nil)
StrLen = 0
[ODBC][1515][1536073973.991928][SQLSetEnvAttr.c][363]
Exit:[SQL_SUCCESS]
[ODBC][1515][1536073973.992707][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x5591139d5d60
[ODBC][1515][1536073973.993173][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x5591139d6400
[ODBC][1515][1536073973.993682][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x5591139d6400
Attribute = SQL_ATTR_AUTOCOMMIT
Value = 0x1
StrLen = -6
[ODBC][1515][1536073973.994129][SQLSetConnectAttr.c][681]
Exit:[SQL_SUCCESS]
[ODBC][1515][1536073973.994892][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x5591139d6400
Attribute = SQL_ATTR_ODBC_CURSORS
Value = (nil)
StrLen = -6
[ODBC][1515][1536073973.995351][SQLSetConnectAttr.c][681]
Exit:[SQL_SUCCESS]
[ODBC][1515][1536073973.995872][SQLDriverConnect.c][726]
Entry:
Connection = 0x5591139d6400
Window Hdl = (nil)
Str In = [Driver=MDBTools;DBQ=/var/www/public/contacts-db.accdb;][length = 54]
Str Out = 0x7ffdb1be6d00
Str Out Max = 1023
Str Out Ptr = 0x7ffdb1be6cf8
Completion = 0
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
[ODBC][1515][1536073974.001199][SQLDriverConnect.c][1353]
Exit:[SQL_ERROR]
[ODBC][1515][1536073974.001731][SQLGetDiagRec.c][680]
Entry:
Connection = 0x5591139d6400
Rec Number = 1
SQLState = 0x7f11dce6c010
Native = 0x7f11dce6c218
Message Text = 0x7f11dce6c016
Buffer Length = 511
Text Len Ptr = 0x7ffdb1be688a
[ODBC][1515][1536073974.002167][SQLGetDiagRec.c][717]
Exit:[SQL_NO_DATA]
[ODBC][1515][1536073974.002707][SQLEndTran.c][421]
Entry:
Connection = 0x5591139d6400
Completion Type = 1
[ODBC][1515][1536073974.003125][SQLEndTran.c][433]Error: 08003
[ODBC][1515][1536073974.003551][SQLDisconnect.c][208]
Entry:
Connection = 0x5591139d6400
[ODBC][1515][1536073974.004006][SQLDisconnect.c][237]Error: 08003
[ODBC][1515][1536073974.004462][SQLFreeHandle.c][284]
Entry:
Handle Type = 2
Input Handle = 0x5591139d6400
[ODBC][1515][1536073974.004999][SQLFreeHandle.c][333]
Exit:[SQL_SUCCESS]
[ODBC][1515][1536073974.005544][SQLFreeHandle.c][219]
Entry:
Handle Type = 1
Input Handle = 0x5591139d5d60
I can see where the failure is happening in the trace, [SQLDriverConnect.c][726] and [SQLDriverConnect.c][1353]. It appears to be a connection error, but I cannot make sense of the information contained in the following block in the trace log, [SQLGetDiagRec.c][680].
Also, I don't understand why the database can't be found if PHP can find it or why it would say it doesn't know the Jet Engine version since the file was created with an 8 year old version of MS Access (2010).
If anyone has any ideas as to how to debug the error I would greatly appreciate it.
