amirkdv/php_mdb.rst

## php_mdb.rst

      
    Raw
  

              php_mdb.rst
            
          
    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):

PHP ODBC module

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.


ODBC Configuration

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.


ODBC MDB driver

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 <= XP
Microsoft Access Driver (*.mdb, *.accdb) in Windows 7+

(not sure of the latter, but the former does work in Windows XP).

Usage

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.

Related Packages


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 the mdbtools package does not
install libmdbodbc1 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 of mdbtools, but as it stands now, the mdbtools package is
irrelevant for enabling ODBC mdb support. Note, also, that the old deb package
(available through apt) 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 the libodbc1 and provides a set of command
line utilies, most importantly isql 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 the odbcinst 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 original mdbtools. Last updated in 2007 (as of Mar 2014),
with the only stable release being version 1.0.0 from November 2007. This
extension is again irrelevent given the PDO/ODBC usage.


General other notes


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.