blog.mycado.fr

DB2 queries with PHP

You want to use PHP as programmation language to query your DB2 databases. We will see how to do. We only need a HTTP server with the PECL ibm_db2 extension. This extension allow us to use new functions, relative to IBM DB2, but also work with IBM Cloudscape and Apache Derby.

After the installation, we will use these functions as normal PHP functions. This is a connection example to the SAMPLE database. You can click on the function to get more information:

<?php
$conn = db2_connect('SAMPLE', 'db2user', 'secretpass');

if($conn) {
  echo "connection to sample: ok.";
} else {
  echo "connection to sample: failed.";
}

db2_close($conn);
?>

Nothing very complicated here, we connect to the SAMPLE database with the db2_connect function, and we check if the connection works, then we close the connection with db2_close.

Now, let’s do something more interesting, do some query on our tables !

<?php
$query = "SELECT * FROM ADMINISTRATOR.EMPLOYEE";
$stmt = db2_prepare($conn, $query);

if($stmt) {
  $ex = db2_execute($stmt);
  if($ex) {
    while($ligne = db2_fetch_array($stmt)) {
      $lastname = ligne[3];
      echo "<br />- $lastname";
    }
  }
}
?>

We put your query in the $query variable, and we use the db2_prepare function with the previous connection ($conn). This function will “prepare” (I’m so smart), it will create an optimized path in DB2, to be more fast. We execute this result ($stmt) with the db2_execute function, who do the query on your database. To finish, we use db2_fetch_array to retrieve our data in an array.

A BLOB example.. or Binary Large OBject, an image, a audio or video file,..

$filename = '/home/mycado/itsme.jpg';
$name = 'My cute picture";

$query = 'INSERT INTO photo (id, name, image) VALUES (?, ?, ?)';
$stmt = db2_prepare($conn, $query);

if($stmt) {
  db2_bind_param($stmt, 1, 'id', DB2_PARAM_IN);
  db2_bind_param($stmt, 2, 'name', DB2_PARAM_IN);
  db2_bind_param($stmt, 3, 'filename', DB2_PARAM_FILE);

  $ex = db2_execute($stmt);
}

We use the flag db2_bind_param we give us more precision with the data type in our request. The first two variables, an id and a string, use DB2_PARAM_IN, an classical input parameter et we put your picture with DB2_PARAM_FILE. Note that the question marks are not an error !

It’s enough for now, I advice you to read the ebook “DB2 Express-C: The Developer Handbook for XML, PHP, C/C++, Java, and .NET‘. About the ibm_db2 functions, you can find them all on php.net.

Tags: blob, DB2, PHP