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:

 

java Program --query "query here" --db-server "server" etc

 

output result like this

 

Field1|Field2|Field3
 data1|data1|data1

 

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

 

// Put in classpath all needed librararies
$classpath = join(PATH_SEPARATOR, array(
   dirname(__FILE__).DIRECTORY_SEPARATOR.'.',
   dirname(__FILE__).DIRECTORY_SEPARATOR.'ifxjdbc.jar', // JDBC driver
   dirname(__FILE__).DIRECTORY_SEPARATOR.'json-simple-1.1.1.jar',
));
 
// Prepare command to run (SimpleSelect is java program name)
$cmd = sprintf("java -cp '%s' SimpleSelect", $classpath);
 
$descriptorspec = array(
   0 => array("pipe", "r"),
   1 => array("pipe", "w"),
   2 => array("file", "/tmp/error-output.txt", "a")
);
 
$process = proc_open($cmd, $descriptorspec, $pipes);
if (is_resource($process)) {
   // $pipes now looks like this:
   // 0 => writeable handle connected to child stdin
   // 1 => readable handle connected to child stdout
 
   // Send query to Java program
   $command = array(
      'dsn' => 'jdbc:informix-sqli://10.0.0.1:1526/database;username=xxx;password=yyyy',
      'query' => 'select * from table WHERE id = ?',
      'param' => array(1)
   );
   fwrite($pipes[0], json_encode($command));
   fclose($pipes[0]);
   $output = stream_get_contents($pipes[1]);
   fclose($pipes[1]);
   $return_value = proc_close($process);
 
   // Decode output
   $res = json_decode($output, true);
   if (!empty($res['error'])) throw new Exception($res['error']);
 
   return $res;

 

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

 

import java.io.*;
import java.sql.*;
import java.util.*;
 
import org.json.simple.*;
 
public class SimpleSelect {
    public static Connection conn = null;
    public static JSONObject output;
 
    public static void main(String[] args) throws Exception {
        Statement  stmt = null;
 
        output = new JSONObject();
 
        // Decode jSON
        // http://code.google.com/p/json-simple/wiki/DecodingExamples
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        final JSONObject input = (JSONObject)JSONValue.parse(br.readLine());
        JSONArray param = (JSONArray)input.get("param");
 
        output.put("error", "");
 
        try {
            Class.forName("com.informix.jdbc.IfxDriver");
        } catch (Exception e) {
            SimpleSelect.fatal_error("FAILED: failed to load Informix JDBC driver.");
        }
 
        //  Connect though the thread to handle connection timeout
        Thread tc = new Thread(new Runnable() {
           public void run() {
                try {
                    SimpleSelect.conn = DriverManager.getConnection((String)input.get("dsn"));
                } catch (SQLException e) {
                    SimpleSelect.fatal_error("FAILED: failed to connect to Informix!");
                }
           }
        });
        tc.setDaemon(true);
        tc.start();
 
        // Wait 5 sec for connection
        for(int i = 0 ; i <= 500 ; i++) {
           if (conn != null) break;
           try {Thread.sleep(10);} catch(InterruptedException ex) {}
        }
        if (conn == null) {
            SimpleSelect.fatal_error("FAILED: timeout connect to Informix!");
        }
 
        try  {
               PreparedStatement pstmt = conn.prepareStatement((String)input.get("query"));
               pstmt.setQueryTimeout(10);
                for(int i = 0 ; i < param.size() ; i++)
                    pstmt.setString(i+1, (String)param.get(i));
 
                ResultSet rs = pstmt.executeQuery();
 
                // Get column name
                JSONArray jsColumn = new JSONArray();
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 0; i < rsmd.getColumnCount(); i++ ) {
                    jsColumn.add((String)rsmd.getColumnName(i+1));
                }
                output.put("column", jsColumn);
 
                // Get the data
                JSONArray jsData = new JSONArray();
                while(rs.next()) {
                    JSONArray jsRow = new JSONArray();
                    for (int i = 0; i < rsmd.getColumnCount(); i++ ) {
                        jsRow.add(rs.getString(i+1));
                    }
                    jsData.add(jsRow);
                }
                rs.close();
                pstmt.close();
                output.put("data", jsData);
 
        } catch (SQLException e) {
            SimpleSelect.fatal_error("FAILED: Fetch statement failed: " + e.getMessage());
        }
 
        try {
            conn.close();
        } catch (SQLException e) {
            SimpleSelect.fatal_error("FAILED: failed to close the connection!");
        }
        System.out.print(output);
    }
 
    static public void fatal_error(String str) {
        output.put("error", str);
        System.out.print(output);
        System.exit(0);
    }
 
}