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 seems to compile extention you need for PHP and live happilly. Yes it will work, but has some drawbacks I do not like:
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:
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.
// 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;
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);
}
}