How to connect to rare databases in PHP (informix, oracle, db2)

Recently I took part in integration project and faced interesting problem. PHP application that I deploy must communicate with Informix database for data selecte. This php application aims to corporate market, so its not a extraordinary case. I see that it may require connecto to Oracle or DB2 in near future.

Yes, PHP itself has wide choose of database connectors, which is a good option to have native database support. The only problem is that most of them are used so rare that are usualy not compiled in all modern Linux destributos. For example in Ubuntu server 12.04 there are five most pupular php5-sybase php5-interbase php5-mssql php5-mysql php5-pgsql.

Thinking of it, I found one interesting solution.

The right way

The right way seems to compile extention you need for PHP and live happilly. Yes it will work, but has some drawbacks I do not like:

  1. You have to make a build environment for PHP – which takes time
  2. Include in build all extension PHP already has – install all needed library in -dev versions
  3. Download SDK from database vendor, install it and tell PHP where to look for files – time consuming.
  4. Build PHP install it and manually add to apache, check and rebuild again to add missing extentions
  5. Keep upgrading PHP manually after security patched are out
  6. Document all work done, so somebody later can do this on another server to match your application specification

You see, it does not look so simple after analysis. So what is another way?

JDBC – Java DataBase Connectivity

Some years ago I was quite deep in Java world. One thing I like the best is that how Java works with databases though JDBC. You only need one *.jar file with database driver written by the vendor. This file is usually small in size and has no other dependency.

In my case, for Informix, I found ifxjdbc.jar, which is 800Kb in size and that was all I need.

I imagine next setup:

PHP  (calls—->) JAVA program (DB exchange)  —–>  PHP (receive results)

Java program will be a very simple program to get query on input, connect to database, execute the query and pass results to PHP.

Looks like not a right way, but has almost no negative side effects:

  1. Java is easy to install on every linux distribution, just type apt-get install java, or yum install java and you are done
  2. Any Java version will work, since task is very simple (openjdk, jdk, icetea, etc..)
  3. Java program can be distributed with your PHP application
  4. JDBC driver in sinlge .jar file and  as well, can be distributed with PHP application
  5. Simple to support

So why I love JSON?

Next step is communication with Java program. There is need to pass something and get something back.

First that comes to mind is to re-invent the wheel, like this:

output result like this

That means parsing, parsing and catch unpredictable errors. Not good.

What PHP and Java can do well? Yes, its JSON serialization/deserialization. Lets use it, php will run external java program and pass json data on standard input. Java will do it’s work and pass json on standard output, were PHP will catch it.

For PHP its simple json_encode/json_decode

For Java I used json-simple-1.1.1.jar library (20KB).

Below are sources that can give you an idea how it works.

PHP Source

JAVA Source

To make it better, I would add passing JDBC driver name as a parameter as well as connection timeout. May be add support for INSERT, UPDATE, DELETE

1 comment

Leave a Reply

Your email address will not be published.