To be able to use PHP5 PDO with MS Access mdb files the following is required
(the same applies for the PHP4 style of using odbc_X
except for the
obviously PDO specific requirements):
In Linux this is achieved by intalling the php5-odbc
package:
php -i | grep PDO # PDO # PDO support => enabled # PDO drivers => mysql, odbc # PDO Driver for MySQL => enabled # PDO_ODBC # PDO Driver for ODBC (unixODBC) => enabled In Windows, typical PHP installation contains ODBC support.
Independent of PHP, the O/S should have the libraries to handle ODBC API. In
Linux this is achieved by installing the libodbc1
(formerly libodbc
)
package. Note that, libodbc1
comes with common distributions of Linux (see PHP
configuration above says PDO Driver for ODBC (unixODBC) => enabled
). Same
applies to Windows.
ODBC uses drivers for each DBMS. Furthermore in ODBC parlance, a connection to a database is made via a Data Source Name. Accordingly, in Linux, ODBC respects two main configuration files:
/etc/odbcinst.ini
: contains definitions of drivers and general configuration (for example whether odbc should keep logs). Each driver that is installed must update this file with a block that typically looks like the following:[MyDriver] Description = MyDriver Driver for MyDBMS Driver = /path/to/module.so Setup = /path/to/module.so FileUsage = 1 UsageCount = 1
/etc/odbc.ini
: contains configuration for specific data sources. For example:[MyDataSource] Description = The Source of My Data Driver = MyDriver ServerName = localhost Database = MyDatabase UserName = admin Password = something
Note that the driver name, whatever it may be, must be defined in a corresponding block in
/etc/odbcinst.ini
.
In Linux, this driver is provided by the libmdbodbc1
(formerly
libmdbodbc
) package. Once the package is installed, the following block is
added to /etc/odbcinst.ini
:
[MDBTools] Description = MDBTools Driver Driver = libmdbodbc.so.1 Setup = libmdbodbc.so.1 FileUsage = 1 UsageCount = 1
This defines the MDBTools
driver for ODBC (in some versions the driver
name that it creates is called MDBToolsODBC
). In Windows, support for MDB
files is built in, however the driver name is different:
Microsoft Access Driver (*.mdb)
in Windows <= XPMicrosoft Access Driver (*.mdb, *.accdb)
in Windows 7+
(not sure of the latter, but the former does work in Windows XP).
Having installed the above (i.e libmdbodbc1
and php5-odbc
, note that libodbc1
comes with all main distribution of Linux), the following PHP code will work in Ubuntu and
Windows XP:
$query = 'SELECT * FROM MyTable'; $mdb_file = '/path/to/example.mdb'; $uname = explode(" ",php_uname()); $os = $uname[0]; switch ($os){ case 'Windows': $driver = '{Microsoft Access Driver (*.mdb)}'; break; case 'Linux': $driver = 'MDBTools'; break; default: exit("Don't know about this OS"); } $dataSourceName = "odbc:Driver=$driver;DBQ=$mdb_file;"; $connection = new \PDO($dataSourceName); $result = $connection->query($query)->fetchAll(\PDO::FETCH_ASSOC); print_r($result);
Note that the entire dataSourceName
definition could have been replaced
by odbc:MyDSN
if we had created a block in /etc/odbc.ini
with
equivalent configuration.
mdbtools
package is a collection of utilities to work with.mdb
files. It provides command line utilites such as the following:mdb-tables my_access_db.mdb # lists all tables # print the schema of the given db as per MySQL's dialect of SQL mdb-schema -I mysql my_access_db.mdb # print SQL INSERT statements for the contents of the given table as per # MySQL's dialect of SQL mdb-export -I mysql my_access_db.mdb my_access_table
Note, however, that
mdbtools
is completely unnecessary for handling mdb files using PHP. Also note that installing themdbtools
package does not installlibmdbodbc1
which is what ODBC needs to operate on mdb files. From the documentation of mdbtools it seems like the driver was first developed as part ofmdbtools
, but as it stands now, themdbtools
package is irrelevant for enabling ODBC mdb support. Note, also, that the old deb package (available throughapt
) is simple to install, but the newer GitHub repo chokes on different parts while being built from source, see the repo issue queue).unixodbc
: depends on thelibodbc1
and provides a set of command line utilies, most importantlyisql
which is an interactive shell to query an arbitrary ODBC drived database. For example, in the MDB case above, if the/etc/odbc.ini
contains the following block:[MyMDB] Description = The Source of My Data Driver = MDBTools ServerName = localhost Database = /path/to/file.mdb
one can interactively run queries against the above DB by:
isql -v MyMDB
unixodbc
also provides theodbcinst
command line tool to configure ODBC drivers. None of these utilities are needed to enable the PHP ODBC driver in Linux.PECL
mdbtools
extension is a seemingly abondoned PECL extension based on the work of the originalmdbtools
. Last updated in 2007 (as of Mar 2014), with the only stable release being version1.0.0
from November 2007. This extension is again irrelevent given the PDO/ODBC usage.
Common erros are:
If PDO is not aware of its ODBC driver, you will get:
'PDOException' with message 'could not find driver'
If ODBC fails to find the driver specified, e.g. you use
Microsoft Access Driver (*.mdb)
in Linux:'PDOException' with message 'SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified'
There has been some effort to circumvent the need to interact with MS Access databases:
- export mdb to sqlite (written in Java): http://code.google.com/p/mdb-sqlite/
- use mdbtools to export mdb to equivalent dumps in supported SQL dialects. For example, see this gist or this slightly modified other one.
If sample mdb file is needed for testing, try this; it has one table and two columns.
already had to use utf8 in this context?:
$query = 'SELECT * FROM MyTable'; $mdb_file = '/path/to/example.mdb'; $uname = explode(" ",php_uname()); $os = $uname[0]; switch ($os){ case 'Windows': $driver = '{Microsoft Access Driver (*.mdb)}'; break; case 'Linux': $driver = 'MDBTools'; break; default: exit("Don't know about this OS"); } $dataSourceName = "odbc:Driver=$driver;DBQ=$mdb_file;"; $connection = new \PDO($dataSourceName); $result = $connection->query($query)->fetchAll(\PDO::FETCH_ASSOC); print_r($result);