Connect PHP to DB2 and Cloudscape via PDO

Develop high performance PHP database applications

Comments

Please read the disclaimer before you read this article.

A little background

With the release of PHP 5, with its more mature OO syntax, PHP is more and more often coming under the scrutiny of larger and larger institutions, and it is becoming increasingly important (if not somewhat overdue) for PHP to present a more consistent and accessible data access API.

PHP has always had something of a love affair with the popular open source relational database management system (RDBMS) known as MySQL. The success of this pair is largely due to their free availability and low barrier to entry, and this synergy has helped to boost both products to their current ubiquitous status.

Many PHP application developers cut their teeth on the PHP-MySQL combination, to the point that PHP's support for other databases usually mimics the MySQL client library API. However, not all database client APIs are the same, and not all databases offer the same features; despite the mimicry, the various PHP database extensions have their own quirks and differences that make migration from one database to another into something of a difficult prospect. While this was not the driving reason for creating PDO, it certainly played a part in the design process.

If you're reading this article with the intent of using PHP together with DB2, there is a fair chance (given the class of people that aim for DB2) that you fall into one of the following generalizations:

  • You started out as a small company, running PHP on MySQL (for example), and your business has grown to the point that you need the scalability / reliability / support or other features offered by DB2. You would like to migrate the code to work with DB2 but the API changes mean that you would need to write or implement an abstraction layer to be able to continue running on the old database while you test-in the application against DB2. Not only that, but you want to keep your options open, and would like to have the possibility of supporting another RDBMS, because you understand that some clients might already be tied into another platform.
  • You built a small departmental application using PHP on MySQL (again, for example; I'm not picking on MySQL!). The application proved itself very useful and is now used outside of the department and has fallen under the watchful eye of the CIO/CTO--now you need to comply with the mandated standard database of the business. (Yes, this is a variation on the first point.)
  • You have an existing DB2 instance on the backend of some other complex enterprise level applications; you'd like to leverage PHP's rapid application development and prototyping to generate dynamic reports.

Goals

Now that we have a handle on the background of databases-and-PHP, this is an ideal point to mention some of the design goals behind PDO:

  • To provide a consistent API for the common features found in most database APIs
  • To be extensible, so that database vendor X can still expose feature Y and remain PDO compatible
  • To provide a number of basic compatibility quirks, to make it easier to create cross-database compatible applications
  • To NOT provide full abstraction or emulation of features (such as sequences) that are otherwise missing from a given database API. The PDO class is intended to give you consistent access to native features of the database, with minimal interference.
  • To simplify the creation of PHP database drivers by centralizing the code that deals with the PHP internals (which are the hardest part to write)

That last point is quite important. PDO is modular in structure, separated into a common core that provides the API that you use in your scripts (PDO itself), and one or more driver extensions that bridge PDO to the native RDBMS client API libraries. DB2 users will want to use the PDO_ODBC driver, which boasts the following features:

  • It was written from the ground-up to support ODBC V3 compliant drivers and driver managers. Support for DB2 specific features and optimizations was considered and incorporated as part of this design process--it was not an afterthought.
  • It supports large objects and stored procedures has been tried and tested. Not only does it work, but it is very pleasant to use.
  • Performance of DB2 access through the PDO_ODBC driver for simple fetches of 10,000 rows is approximately 10 times faster than the traditional PHP Unified ODBC extension. This dramatic difference is due to the light-weight forward-only cursor that is the default in PDO.

Obtaining and installing PDO

PHP 5.1 is set to ship with PDO, however, you can also use PDO with PHP 5.0.3 and up via PECL, the PHP Extension Repository. If you're running Windows®, you'll be pleased to note that installation is much simpler.

I'm going to assume that you have already had some experience with configuring PHP 5 to work with your Web server of choice, as this assumtion allows me to focus on more relevant details. Likewise, I'm going to assume that you are running a DB2 Universal Database server or an IBM Cloudscape database in network server mode, and that you accepted the default installation options of user db2inst1 and password ibmdb2. If you are compiling the driver yourself, you will need to have a DB2 client installation with application development headers present on the same machine that you are building on; otherwise the compilation will fail.

Installation on PHP 5.0.3 and up, via PECL

By default, PHP is configured to install the "PEAR" package management system. Your OS distribution of choice is likely to have created a package containing the components of PEAR, and it is likely that you already have it installed and ready to run. Let's try it out and see.

Listing 1. Listing the installed PEAR packages
				$ pear list
Installed packages:
===================
Package        Version State
Archive_Tar    1.1     stable
Console_Getopt 1.2     stable
PEAR           1.3.4   stable
XML_RPC        1.1.0   stable

This package list shows that I have PEAR 1.3.4 installed. The chances are that you will have that version as well. In order to install PDO successfully, you need to upgrade to PEAR 1.3.5; this is a quick and painless process:

$ sudo pear upgrade PEAR

Now you're all set to install PDO:

$ sudo pear install PDO

You've installed the PDO core; you need to enable it in your php.ini file for it to take effect. You need to add the following line:

extension=pdo.so

And now for the ODBC driver for PDO, which you need if you want to connect to DB2, Cloudscape or Apache Derby:

$ sudo pear install PDO_ODBC

You will be prompted flavour,dir ? (just leave blank for help). This is a slightly cryptic prompt asking you which type of ODBC driver you want to configure, and where it is installed. If you chose the default installation options when you installed DB2, you can enter ibm-db2, which is equivalent to ibm-db2,/home/db2inst1/sqllib. If you chose an alternative install location, you should substitute it for /home/db2inst1/sqllib. Press enter once you have typed in the correct details; the driver will then build and install.

You need to activate the driver by adding it to your php.ini file as well. Make sure that you add this line after the pdo.so line that you added above, otherwise PHP won't initialize correctly.

extension=pdo_odbc.so

Installation on PHP 5.1 and up

PHP 5.1 ships with PDO. All you need to do for DB2 support is add the following switch to your configure line. You will almost certainly want to add more configure options to match your Web server; consult the PHP documentation for more information on that. I'm going to assume that you're running on a recent Linux® distribution, running Apache 2:

$ tar xjf php-5.1.0.tar.bz2
$ cd php-5.1.0
$ ./configure --with-pdo-odbc=ibm-db2,/home/db2inst1/sqllib \
              --with-apxs2=/usr/sbin/apxs
$ make
$ sudo make install

Installation on Windows

Installing on Windows is somewhat simpler than on UNIX®. If you've downloaded PHP 5.1, you'll already have the relevant DLLs as part of the package. Otherwise, you'll need to download them from the PHP snapshot site (see the downloads section at the end of this article).

To activate PDO, add the following two lines to your php.ini file:

extension=php_pdo.dll
extension=php_pdo_odbc.dll

Now restart the Web server

With the install done, you should perform a full restart of your Web server to ensure that PHP loads the new extensions, and then you're ready to start using PDO. If you're running on the UNIX platforms, you need to source the DB2 instance environment for the DB2 client to initialize correctly. If you're using a bourne shell style shell, you can acheive this by running the command . /home/db2inst1/sqllib/db2profile. (Note: the leading period is supposed to be there!) You will need to arrange for this to happen automatically in your Web server startup scripts.

Key concepts in PDO

There are 5 key concepts that you need to be aware of, to get the most out of PDO. They are:

Connections and connection management

Connections are established by creating instances of the PDO base class. It doesn't matter which driver you want to use; you always use the PDO class name. The constructor accepts parameters for specifying the database source (known as the DSN) and optionally for the username and password (if any). The final parameter is used for passing additional tuning parameters through to PDO or the underlying driver--more on that shortly. Here's a short sample script that connects to DB2:

Listing 2. How to connect to DB2 using PDO
				try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
  echo "Connected\n";
} catch (Exception $e) {
  echo "Failed: " . $e->getMessage();
}

odbc:SAMPLE informs PDO that it should use the ODBC driver, and that it should use the "SAMPLE" database. If you're using a driver manager, SAMPLE can be replaced by an ODBC-level data source name. In fact, any valid ODBC data source connection string may be specified after the colon character.

If the connection succeeds, you will see the message "Connected", otherwise, PDO will throw a PDOException explaining why the connection failed. Possible reasons include invalid parameters, incorrect user/password or even just that you forgot to load the driver.

It's worth noting that, unless you catch exceptions that are thrown from the constructor, your PHP script will terminate if it could not connect to the database. The is an important difference from the traditional PHP database extensions. For those of you that dislike exceptions, this is one of only two "hard-failure" points where exceptions are thrown (the other is for missing transaction support when you attempt to use transactions); for all other errors, PDO will use you choice of error handling settings.

The connection will remain open until all references to it are released. If you open the connection at the top of your main script and store the handle in a global variable, then the connection will remain open until your script ends, or until you set the the $dbh variable to null. If you open the connection from within a function, and only store the variable in a local variable, the connection will be closed when that function returns. These semantics are the same for any object in PHP; it's nothing new.

Quite often, for sites with a lot of traffic, it is beneficial to have PHP cache opened connections between requests, so that the cost of establishing the connection is only paid for once per process (per unique set of connection parameters). Although this sounds like a great idea, you should carefully evaluate how this might impact your systems, as a large number of cached connections sitting idle could do more harm than good.

To establish a cached connection (or *pconnect() if you're more familiar with the traditional database extensions), you pass in an attribute when you instantiate your database connection:

Listing 3. How to connect to DB2 using PDO, using persistent (cached) connections
				try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', 
                 array(PDO_ATTR_PERSISTENT => true));
  echo "Connected\n";
} catch (Exception $e) {
  echo "Failed: " . $e->getMessage();
}

Transactions and auto-commit

Now that you're connected via PDO, you should to understand how PDO manages transactions before you start issuing queries. If you've never encountered transactions before, they offer 4 major features: Atomicity, Consistency, Isolation and Durability (ACID). In layman's terms, any work carried out in a transaction, even if it is carried out in stages, is guaranteed to be applied to the database safely, and without interference from other connections, when it is committed. Transactional work can also be automatically undone at your request (provided you haven't already committed it), which makes error handling in your scripts easier.

Transactions are typically implemented by "saving-up" your batch of changes to be applied all at once; this has the nice side effect of drastically improving the efficiency of those updates. In other words, transactions can make your scripts faster and potentially more robust (you still need to use them correctly to reap that benefit).

Unfortunately, not every database supports transactions, so PDO needs to run in what is known as "auto-commit" mode when you first open the connection. Auto-commit mode means that every query that you run has its own implicit transaction, if the database supports it, or no transaction if the database doesn't support transactions. If you need a transaction, you must use the PDO::beginTransaction() method to initiate one. If the underlying driver does not support transactions, a PDOException will be thrown (regardless of your error handling settings: this is always a serious error condition). Once you are in a transaction, you may use PDO::commit() or PDO::rollBack() to finish it, depending on the success of the code you run during the transaction.

When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back. This is a safety measure to help avoid inconsistency in the cases where the script terminates unexpectedly--if you didn't explicitly commit the transaction, then it is assumed that something went awry, so the rollback is performed for the safety of your data.

Listing 4. Executing a batch in a transaction
try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', 
      array(PDO_ATTR_PERSISTENT => true));
  echo "Connected\n";
  $dbh->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate) 
      values (23, 50000, NOW())");
  $dbh->commit();
  
} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}

In the sample above, let's assume that we are creating a set of entries for a new employee, who has been assigned an ID number of 23. In addition to entering the basic data for that person, we also need to record their salary. It's pretty simple to make two separate updates, but by enclosing them within the beginTransaction() and commit() calls, we are guaranteeing that no one else will be able to see those changes until they are complete. If something goes wrong, the catch block rolls back all changes made since the transaction was started, and then prints out an error message.

You're not limited to making updates in a transaction; you can also issue complex queries to extract data, and possibly use that information to build up more updates and queries; while the transaction is active, you are guaranteed that no one else can make changes while you are in the middle of your work. In truth, this isn't 100% correct, but it is a good-enough introduction, if you've never heard of transactions before.

A note on security in PHP applications

One of the most common flaws in a lot of PHP scripts is their lack of input validation. This is the kind of flaw can be exploited and leads to both XSS (Cross Site Scripting) and SQL injection attacks. SQL injection is where untrusted data (for example: feedback posted to a Web page) is simply concatenated together with some other text to build up a query. Attackers can deliberately craft their input so that it escapes out of quoting, and chain an arbitrary query on the end of the one you had intended to run. This kind of attack allows the attacker to update, insert or delete data, and perhaps also reveal arbitrary information from your database.

XSS is a similar kind of problem; this time, the untrusted data is targeted at the people browsing your site, rather than your application itself. By sending in text containing a combination of HTML or javascript, the attacker is hoping that you will later be outputing that data direct to other people visiting your site, allowing malicious code to run on the browsers of your visitors.

When writing your application, you need to consider both of these kinds of attacks. Both can be prevented by carefully validating and filtering the input. Handling XSS can be quite tricky, so I'm going to avoid talking about that here (see the sidebar for useful resources though). SQL injection is easier to beat; you simply need to properly escape each piece of untrusted data before building your query. This is something of a chore, particularly if you have a large number of fields to deal with, and is quite easy to forget to do it.

While this is useful (and important!) information, you might be wondering why I took the time to mention it, when the focus is on using PDO with DB2. The reasoning is this: PHP is now very widely deployed and, by extension, a large number of popular PHP-based applications are also widely deployed. Whenever one of those applications (that have no affiliation with PHP itself) are found to have an exploit, PHP itself is often misrepresented as being insecure, exploitable or flawed. One of the things we can do to avoid this happening in the future is to encourage application developers to be more security conscious, and thus reduce the potential damage that an honest mistake can cause. This is a long-winded lead-in to...

Prepared statements and stored procedures

Many of the more mature databases support the concept of prepared statements. What are they? You can think of them as a kind of compiled template for the SQL that you want to run, that can be customized using variable parameters. Prepared statements offer two major benefits:

  • The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize it's plan for executing the query. For complex queries this process can take up enough time that it will noticably slow down your application if you need to repeat the same query many times with different parameters. By using a prepared statement you avoid repeating the analyze/compile/optimize cycle. In short, prepared statements use fewer resources and thus run faster.
  • The parameters to prepared statements don't need to be quoted; the driver handles it for you. If your application exclusively uses prepared statements, you can be sure that no SQL injection will occur. (However, if you're still building up other parts of the query based on untrusted input, you're still at risk).

Prepared statements are so useful that PDO actually breaks the rule set out in Goal number 4: if the driver doesn't support prepared statements, PDO will emulate them.

Here are two examples of using prepared statements; the first performs an insert by substituting a name and a value for the named placeholders. The second performs a select statement, using the alternative question mark placeholders.

Listing 4. Repeated inserts using prepared statements
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
Listing 5. Fetching data using prepared statements
				$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array('one'))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}

If the database driver supports it, you may also bind parameters for output as well as input. Output parameters are typically used to retrieve values from stored procedures. Output parameters are slightly more complex to use than input parameters, in that you must know how large a given parameter might be when you bind it. If the value turns out to be larger than the size you suggested, an error is raised.

Listing 6. Calling a stored procedure with an output parameter
				$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO_PARAM_STR, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";

You may also specify parameters that hold values both input and output; the syntax is similar to output parameters. In this next example, the string 'hello' is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.

Listing 7. Calling a stored procedure with an input/output parameter
				$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO_PARAM_STR|PDO_PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";

Errors and error handling

PDO offers 3 different error handling modes to suit different styles of programming:

PDO_ERRMODE_SILENT

This is the default mode. PDO will simply set the error code for you to inspect using the errorCode() and errorInfo() methods on both the statement and database objects; if the error resulted from a call on a statement object, you would invoke the errorCode() or errorInfo() method on that object. If the error resulted from a call on the database object, you would invoke those methods on the database object instead.

PDO_ERRMODE_WARNING

In addition to setting the error code, PDO will emit a traditional E_WARNING message. This setting is useful during debugging/testing, if you just want to see what problems occurred without interrupting the flow of the application.

PDO_ERRMODE_EXCEPTION

In addition to setting the error code, PDO will throw a PDOException and set its properties to reflect the error code and error information. This setting is also useful during debugging, as it will effectively "blow up" the script at the point of the error, very quickly pointing a finger at potential problem areas in your code (remember: transactions are automatically rolled back if the exception causes the script to terminate).

Exception mode is also useful because you can structure your error handling more clearly than with traditional PHP-style warnings, and with less code/nesting than by running in silent mode and explicitly checking the return value of each database call.

PDO standardizes on using SQL-92 SQLSTATE error code strings; individual PDO drivers are responsible for mapping their native codes to the appropriate SQLSTATE codes. As is happens, SQLSTATE is the native error code format for DB2 (and ODBC in general); how convenient! The errorCode() method returns a single SQLSTATE code. If you need more specific information about an error, PDO also offers an errorInfo() method which returns an array containing the SQLSTATE code, the driver specific error code and driver specific error string.

Paging data, scrolling cursors, positioned updates

A common paradigm in Web applications is paging through the results of a query. You most likely do this every day when you use an Internet search engine. You enter your search term and you're presented with the first 10-20 matches. If you want to see more, you click on the "next page" link. If you want to flick back to the results you saw earlier, you click on the "previous page" link. I remember, years ago now, when I first used this kind of thing on the Web, I thought to myself "urgh, why can't I just scroll through the data?". The answers to that question are both simple and complex--suffice to say that HTTP doesn't lend itself to intelligently keeping a scrollable cursor open on the database, and even if it did, a Web application with high traffic can quickly consume a large number of open scrollable cursors. So, with that out of the equation, the simplest solution is to show the user all the matches--but that can easily overwhelm your user with a large number of results. The next logical step is to artificially format that data into a number of pages, so that your users can view a manageable chunk at a time.

And so people wrote PHP applications that would fetch all the data and then display only the first 10 rows. On another request, they would show rows 11-20 and so on. This was fine for queries that returned small amounts of data, but when you had a lot of matches (more than 100 or so), fetching all the data only to discard more than 90% is wasteful. Rasmus Lerdorf, the creator of PHP, invented a special "LIMIT, OFFSET" clause for the MySQL database especially for this situation. It allows you to inform the database that you are only interested in a subset of the rows, so it won't bother fetching the rest for you. This syntax (or something very similar) has been adopted by other popular open source databases, but the same syntax is not present across the board for all databases. Troels Arvin has collected some very useful information, comparing the different syntaxes supported by different RDBMS.

If you want to implement paged results in your DB2-powered PHP application, you can (and should) use the syntax from the sample below. Here we assume that we have a books table containing book titles and authors, and that we want to show pages containing now more than 10 results at a time:

Listing 8. Paging through data using SQL Standard "Window Functions"
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');

// the offset is passed in from the user when they click on a link
// this cast to integer ensures that no SQL injection can occur
$offset = (int)$_GET['offset'];

$stmt = $db->prepare("select * from (
  select
    ROW_NUMBER() OVER (ORDER BY author) as rownum,
    *
  from books
) as books_window
WHERE rownum > $offset AND rownum <= (10 + $offset)");

if ($stmt->execute()) {
  while (($row = $stmt->fetch()) !== false) {
    print_r($row);
  }
}

Now, if you're coding a more generic application and want to implement paged result sets without writing a lot of specialized code, and don't want to use a heavier-weight abstraction layer, Troels Arvin's very helpful RDBMS information suggests that you may use cursors as a (slightly slower) more portable alternative. It just so happens that PDO has API level support for this; here's how to use it for the equivalent of the sample above:

Listing 9. Paging through data using a scrolling cursor
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select * from books order by author", array(
    PDO_ATTR_CURSOR => PDO_CURSOR_SCROLL));
// the offset is passed in from the user when they click on a link
// this cast to integer ensures that no SQL injection can occur
$offset = (int)$_GET['offset'];
if ($stmt->execute()) {
  // moves the cursor to the requested offset and fetches the first
  for ($tofetch = 10,
        $row = $stmt->fetch(PDO_FETCH_ASSOC, PDO_FETCH_ORI_REL, $offset);
      $row !== false && $tofetch-- > 0;
      $row = $stmt->fetch(PDO_FETCH_ASSOC)) {
    print_r($row);
  }
}

It's worth stressing that, while scrolling cursors are a convenient replacement for the more verbose window function approach, they are much slower. You probably won't notice the speed difference if you're testing in a low traffic environment, but when you scale up you'll start to find it painful.

Positioned updates
Another use for scrollable cursors is to use them to drive updates based on non-trivial criteria that can't be expressed in SQL. If you have a table of Web page links and you need to update that table to reflect the current size of the Web page as part of a nightly batch process, then you might write some code like this:

Listing 10. Using scrolling cursor to make positioned updates
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
// create a named, scrolling, updateable cursor
$stmt = $db->prepare("select url, size from links FOR UPDATE OF size", array(
    PDO_ATTR_CURSOR => PDO_CURSOR_SCROLL,
    PDO_ATTR_CURSOR_NAME => 'link_pos'));
if ($stmt->execute()) {

  // a statement for applying our updates.
  // Notice the WHERE CURRENT OF clause mentions "link_pos",
  // which is the name of the cursor we're using to select the data
  $upd = $db->prepare("UPDATE links set size = ? WHERE CURRENT OF link_pos");
  
  // grab each row
  while (($row = $stmt->fetch()) !== false) {
    // There are much more efficient ways to do this;
    // this is a brief example only: grab all the content
    // from the URL
    $content = file_get_conents($row['url']);
    
    // and measure its length
    $size = strlen($content)
    
    // and pass that as a parameter to our update statement
    $upd->execute(array($size));
  }
}

Large objects

At some point in your application, you might find that you need to store "large" data in your database. Large typically means "around 4kb or more", although DB2 can happily handle up to 32kb before data becomes "large". Large objects can be either textual or binary in nature. PDO allows you to works with this large data type by using the PDO_PARAM_LOB type code in your bindParam() or bindColumn() calls. PDO_PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API. Here's a sample:

Listing 11. Fetching an image from a database
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
list($type, $lob) = $stmt->fetch();

header("Content-Type: $type");
fpassthru($lob);

That was short and sweet. Now let's try the opposite; inserting an uploaded image into a database:

Listing 12. Inserting an image into a database
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // some function to allocate a new ID

// assume that we are running as part of a file upload form
// You can find more information in the PHP documentation

$fp = fopen($_FILES['file']['tmp_name'], 'rb');

$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO_PARAM_LOB);

$stmt->execute();

These two examples have been very macro-level; keep in mind that a fetched lob is a stream and can be used with all the regular streams functions, such as fgets(), fread(), fgetcsv() and stream_get_contents().

A brief note on globalization, NLS and charsets

A matter of ever-growing importance in more and more PHP applications is ensuring that they are ready for global consumption. What this means, in practical terms, is that your application needs to be able to correctly handle data in multiple languages (such as English and Japanese) and still function correctly. This is a broad subject area, and can be very tricky to get it right. A good solid first step on this path is to adopt a suitable global encoding for all your data, such as UTF-8. UTF-8 is an ASCII compatible encoding that can encode the whole of the unicode character set using special sequences of characters; it is a multi-byte encoding.

Multi-byte encoded strings are a little bit more tricky to deal with than regular ASCII strings, because one or more characters correspond to a given letter--for instance, UTF-8 allows for sequences of up to 6 characters to map to a single letter in a string. ASCII characters still have the same representation in UTF-8, so if you're dealing with straight-up English text with no special accents, UTF-8 looks just like ASCII. The implication of this is that the familiar string functions, such as strlen() and substr() (which work on bytes rather than character positions) might not have desired effect, depending on just what is in the UTF-8 string. Fortunately, the PHP iconv extension offers a handful of encoding-aware replacements for these functions. Rather than using strlen() to measure the number of characters in a string, you would use iconv_strlen(). Similarly, instead of strpos() or substr() you would use iconv_strpos() and iconv_substr().

The iconv extension provides you with the basic tools you need to work with data in a wide range of encodings. Your application should do its best to ensure that all the data that is sees is UTF-8 encoded. Most browsers will send UTF-8 encoded data if you mark your Web pages with the appropriate Content-Type, and just to be sure, there is an encoding type attribute that can be applied to an HTML FORM tag.

The next step is to arrange for your DB2 instance to use UTF-8 when you talk to it. This is achieved very easily, by running the following command from your DB2 home at the command prompt:

Listing 14. Setting your DB2 instance to UTF-8
				$ db2set DB2CODEPAGE=1208

Having made this change, all textual data that you fetch from your DB2 instance will be UTF-8 encoded. Likewise, DB2 will expect all your input text to be UTF-8 encoded as well. Once every component of your application is working with UTF-8, your application will be ready to consume and display text from any language that can be encoded with UTF-8 (which is all of them, for all practical intents and purposes). As I hinted at, this is just the first step on the road to internationalization. There are many other things to consider, such as localization (adopting a given users regional settings to render dates, times, weights and measures; translating generic text to their native language), right-to-left or bi-di text layout and so on.

It's worth noting that PDO doesn't do anything special, character set wise, with the data at all. Some drivers will allow you to change the encoding you use for a connection, but there is no special logic at the PDO level to handle that. The reason for this is that the PHP internals don't yet have any knowledge of unicode, so it doesn't make sense to try to make PDO aware of it at this time. If you are interested in this topic, you'll be pleased to know that unicode support for PHP is on the horizon, although I can't give you a date for it's debut appearance.

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Please read the PHP license before downloading the files below.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=83073
ArticleTitle=Connect PHP to DB2 and Cloudscape via PDO
publish-date=03102005