By Leon Katsnelson
By Susan Visser
By Bernie Spang
By the DB2 Guys
By Fred Ho
By Louis T. Cherian
By Shweta Shandilya
By Lawrence Weber
By Serge Rielau
By Dwaine Snow
Tom Beebe did an excellent webcast on February 12, 2013 about using PHP with IBM® Informix®. I asked Tom to write this article as an introduction to his webcast.This webcast replay is also available at http://www.advancedatatools.com/Informix/Webcasts.html.
You may or may not be familiar with PHP, but either way, you use websites that run on it every day. Facebook, Wikipedia, Google, and many other major organizations all rely on PHP—but why? Because it’s a very fast, flexible, open source language that is closely tied into the web server. It runs on most operating systems and in almost any kind of environment. And now it plays nicely with Informix, too!
For those of you who may have toyed with PHP years ago, there used to be a custom Informix driver available. It was written for Informix Dynamic Server (IDS) version 7.x, but it was unstable and unsupported. However, the language and the drivers have come a long way since then. PHP version 5 introduced PDOs (PHP Data Objects). The PDO system abstracts database drivers through a centralized function set, allowing for standardized statements—which gets away from the old model of each database being handled independently, and often inconsistently.
Currently, you can use two drivers to connect to Informix databases, both of which are supported by IBM. The first one is PDO_IBM, which uses the Distributed Relational Database Architecture (DRDA) connection protocol, and so works only with IDS version 11 and above. It also does not support all of the Informix data types, but it will work with most IBM databases. The other type, which is the focus of this article, is PDO_Informix, which supports any version of IDS. Ongoing compatibility is improving as this driver becomes a fundamental part of the operational acceptance testing (OAT) system. (For details on installing the driver on Linux, click the link to the webcast.)
A common question is, “How hard is it to start using PDO_Informix?” The answer: not hard at all. In fact, it’s very easy. Part of the appeal of PHP is that you can rapidly develop stable and functional code. Getting started is a breeze. (All of the code examples in this article are available for download here.)
Your first connection file looks like this:
$hostname = ‘localhost’;
$database = ‘stores_demo’;
$login = ‘login_name’;
$password = ‘password’;
$informixserver = ‘testsystem_tcp’;
#Only needed if INFORMIXDIR is not already set
$dbh = new PDO(“informix:host=$hostname;service=1516;database=$database;server=$informixserver; protocol=onsoctcp;”, $login, $password);
#Set the database handle to return all column names as lowercase
So how does this work?
$dbh is set to a new PDO object, which can be any PDO database driver installed on the system.
You pass three arguments to it. The first one is the long string with all of the connection information. If you are comfortable with Open Database Connectivity (ODBC), these options will look familiar. Each option is separated by a semicolon, and all of them must be on the same line. The basic format is as follows:
informix:service=<service port/name>;database=<databasename>;server=<informix server>;
You can, of course, add other ODBC-based parameters if you want to manually tune the connection.
The last two arguments to pass to the new PDO object are the login and the password. Once these parameters are established, you now have $dbh available as a database handle for the way you will need it in your application.
The next step shows that connection:
include “db.php”; # Executes db.php so $dbh will now be set.
#Create a string with the statement we want.
$sqlline = “select customer_num, fname, lname, city from customer where customer_num = 103”;
#Prepare the statement, set it to the $sth variable.
$sth = $dbh->prepare($sqlline);
#Execute the statement
#Simple fetch of the next row on the cursor as an object.
$row = $sth->fetch(PDO::FETCH_OBJ);
print “Customer: $row->customer_num $row->fname $row->lname in $row->city\n”;
#close statement handle
Next, do a data change:
#Create the update string, note the statement parameters ‘?’
$sqlline = “update customer set city = ? where customer_num = ?”;
#Create a new statement handle
$sth = $dbh->prepare($sqlline);
$customer_num = 103;
$city = “Annandale”;
#The execution is looking for an array of values, passing a single value will return an error.
#Note, these must be in the same order as you had the parameters.
$var_to_pass = array($city, $customer_num);
#Execute the statement
print “Record Updated, return code: “. $sth->errorCode() .”\n”;
Now when you rerun select.php, you will see the updated value.
These simple scripts are the very basic building blocks with which you can write almost anything you need to run against an Informix database. PHP is an extremely flexible language, so you can easily set it up to do whatever you need when handling data structures.
PHP has come a long way in the last few years. If you have never used it or have not tried it in a while, perhaps it’s time to take an initial (or second) look at the language. PHP can become functional within a few hours, with far more complex systems made possible in a fraction of the time other languages require. There is a reason much of the web runs on PHP—and now it can start running on Informix as well.
What do you think? Let me know in the comments!
DB2 TechTalk: Deep Dive on BLU Acceleration in DB2 10.5, Super Analytics Super Easy
Thursday, May 30: 12:30 – 2:00 PM ET
Big Data Seminar 2013, Featuring Krish Krishnan
June 14 in New York City
marcus evans Pharma Data Analytics Conference
July 10-11 in Philadelphia
IBM Smarter Content Summit 2013
Big Data at the Speed of Business
Broadcast event replay now available
Information on Demand 2013: Early Bird Registration Now Open
November 3-7 in Las Vegas