ODBC Socket Server

“System Administration: It’s a dirty job, but someone said I had to do it”
Seen on a T-shirt

One of the most commonly faced dilemmas of system administrators is making dissimilar
operating systems and tools work with each other. It is my hope that this article will
make those system adminstrators’ jobs a little easier when it comes to making Access and PHP work
together.

If you want to use your Microsoft Access database with PHP you have a limited number of
methods you can try. The most obvious one is to run PHP on Windows and use the ODBC
functions. This is not an acceptable solution to many people because it forces you to
use IIS and NT as your web server environment. For those preferring other operating systems,
the most popular choice is an ODBC to ODBC bridge as sold by Openlink
(http://www.openlinksw.com/) and easysoft
(http://www.easysoft.com/). Both of these products
come with a limited version that you can download and use for free. I wanted a open source
alternative to these products and finally found what I was looking for in ODBCSocketServer
(http://odbc.linuxave.net/). This tutorial will take
you through configuring your machines and using the socket server.

In order to accomplish this task, you are going to need two machines. One machine will have
the operating system of your choice, the web server of your choice and PHP on it. The other machine
will have Windows, MS Access, and ODBC on it. The socketserver on
the Windows machine will look for connections on a TCP/IP port, PHP will generate XML
commands and send them to the socket server. The socket server will then execute the SQL
statements in the commands and pass another XML document back to PHP. Finally, PHP will parse
the XML document and manipulate the resulting recordset. Here is a simple diagram.

   MS-Access                                 Browser
      |                                         |
   ODBC/ADO                                 www server
      |                                         |
 socket serverPHP
                      XML Data
 Windows Machine                          Linux Machine
 

What is really interesting is that PHP does not need any database services compiled in only XML and
the built in socket services are needed.

Ok, lets get started. I will be referring to the Linux machine as the client and the windows
machine as the server for the rest of this document.