Home > Uncategorized > Accessing DB2 from sMash

Accessing DB2 from sMash

I couldn’t find a simple example that explicitly shows how to connect to DB2 from sMash, so I put one together. You can grab the resulting example sMash application using the zero command line like this:

1
2
zero modulegroup addurl http://soal.org/sMash/repo/
zero create db2App from soal:db2App

(On a side note, notice how I’m hosting a zero repository containing my app right here on soal.org! You can learn how to host your own repositories – it’s really just a question of running “zero package” and “zero publish” on you app, then copying the appropriate directory structure from under zero/zero-repository to somewhere that is exposed on the web.)

Alternatively, if you don’t want to use sMash’s repository system to get the sample code, you can download the application zip file directly.

Lots of Options!

sMash applications can be written in PHP or Groovy. PHP has a data-access abstraction layer called PDO, most of which is supported by sMash. Additionally, sMash provides its very own data-access layer called zero.data, which can be used from both PHP and Groovy. Therefore, the sample app contains 3 very simple scripts which illustrate different ways of connecting to DB2 from sMash:

  • public/db2pdo.php: access db2 from a PHP script using the PDO API
  • public/db2zero.groovy: access db2 from a Groovy script using the zero.data API
  • public/db2zero.php: access db2 from a PHP script using the zero.data API

I expect all approaches to be roughly equivalent in terms of functionality, so which one you choose is up to you. The example scripts don’t exploit the full functionality of the APIs – they just do the following:

  • Drop a table, ignoring the error if the table didn’t exist.
  • Create a table.
  • Insert entries into a table.
  • Read from a table.

(Update: Another approach, which I don’t demonstrate here, would be to use your favourite Java library to connect to DB2. In sMash apps, any Java library can be invoked directly from Groovy as well as from PHP using sMash’s PHP-Java bridge.)

The next sections of this article describe how to build the sample app from scratch. I’m assuming you already have a DB2 instance to connect to (if not, you could download the DB2 Express-C installer or grab an Ubuntu VMWare image with DB2 pre-installed – or even run DB2 in the cloud). I also assume you have installed sMash and created an empty app.

1. Common Setup Steps

There are a few steps that need to be carried out regardless of the language and API you use to access the DB2 database:

  • Download the DB2 client driver.
  • Extract db2jcc.jar from the nested archive, and copy it to your sMash application under lib/db2jcc.jar (the archive also contains a file called db2jcc4.jar, which you can ignore).
  • Resolve your application’s dependencies. This ensures db2jcc.jar and any other jars in /lib are put on your application’s classpath. To do this from the command line, run “zero resolve” in your application’s directory. Or from AppBuilder, go to your app’s dependencies tab and update dependencies.

2. Accessing DB2 with PHP using PDO

  • Add PHP support to your application by adding a dependency on zero.php. You can do this from AppBuilder in the application’s dependencies tab, or by manually editing config/ivy.xml and adding the following line to the dependencies element:
    1
    <dependency org="zero" name="zero.php" rev="[1.0.0.0, 2.0.0.0["/>
  • Enable the following extensions in your app’s php.ini (config/php.ini). They should be in php.ini already, but commented out – uncomment them.
    1
    2
    extension = com.ibm.p8.library.pdo.PdoLibrary
    extension = com.ibm.p8.library.pdo.PdoJdbcDb2Library
  • Resolve your application’s dependencies and restart your app if it is already started.

Done! Now you’re ready to write PHP scripts which access DB2 via PDO. See the PDO documentation for more information about the API. Remember that the PHP engine in sMash supports most of PDO but not the full API. If you come across anything you need but isn’t supported, don’t hesitate to let us know on the forum.

Here’s an example PDO script. You will of course need to change the db server hostname, port, username, password and database name to suit your environment.

public/db2pdo.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?php
$hostname = '192.168.0.7';
$port = 50000;
$username = 'db2inst1';
$password = 'passw0rd';
$database = 'mydb';
$dsn = "jdbc:db2://$hostname:$port/$database";

try {
// Create database connection
$c = new PDO($dsn, $username, $password, array(
PDO::ATTR_PERSISTENT => TRUE,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);

// Drop the test table, in case it has already been created.
// Temporarily silence errors, so if it hasn't been created we don't die.
$savedErrorMode = $c->getAttribute(PDO::ATTR_ERRMODE);
$c->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$c->exec('DROP TABLE testtable');
$c->setAttribute(PDO::ATTR_ERRMODE, $savedErrorMode);

// Create table and put some stuff in it.
$c->exec("CREATE TABLE testtable(id int NOT NULL PRIMARY KEY, val VARCHAR(16))");
$c->exec("INSERT INTO testtable VALUES(1, 'Kenny')");
$c->exec("INSERT INTO testtable VALUES(2, 'Cartman')");
$c->exec("INSERT INTO testtable VALUES(3, 'Mr. Garrison')");

// Grab stuff from database and show it
$statement = $c->prepare('SELECT * from testtable');
$statement->execute();
var_dump($statement->fetchAll(PDO::FETCH_ASSOC));

} catch (PDOException $e) {
echo "Problem accessing database: ";
var_dump($e);
}

Expected output:

array(3) {
  [0]=>
  array(2) {
    ["ID"]=>
    int(1)
    ["VAL"]=>
    string(5) "Kenny"
  }
  [1]=>
  array(2) {
    ["ID"]=>
    int(2)
    ["VAL"]=>
    string(7) "Cartman"
  }
  [2]=>
  array(2) {
    ["ID"]=>
    int(3)
    ["VAL"]=>
    string(12) "Mr. Garrison"
  }
}

3. Accessing DB2 with Groovy or PHP using zero.data

The zero.data API is geared towards simplicity and the elimination of boilerplate code. It is built on top of IBM PureQuery. For more information see:

Unlike PDO, zero.data picks up database configuration parameters from the file config/zero.config. You can add multiple database profiles which you can then refer to in your code. For example, by adding the following to my zero.config, I can access by database by creating a data manager which refers to ‘mydb’ (see PHP and Goovy code examples a little further down).

in config/zero.config

1
2
3
4
5
6
7
8
9
/config/db/mydb = {
"class" : "com.ibm.db2.jcc.DB2SimpleDataSource",
"driverType" : 4,
"serverName" : "192.168.0.7",
"portNumber" : 50000,
"databaseName" : "mydb",
"user" : "db2inst1",
"password" : "passw0rd"
}

Note that if your app is already started, you will need to restart it to pick up changes to zero.config.

Next are example scripts showing zero.data access with Groovy and PHP.

Using zero.data with Groovy

public/db2zero.groovy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
import zero.data.groovy.Manager
import com.ibm.pdq.runtime.exception.DataRuntimeException

def dataManager = zero.data.Manager.create('mydb')

// Drop the test table, in case it has already been created.
try {
dataManager.update('DROP TABLE testtable');
} catch (DataRuntimeException e) {
// Table might not exist yet. Ignore errors.
}

// Create table and put some stuff in it.
dataManager.update('CREATE TABLE testtable(id int NOT NULL PRIMARY KEY, val VARCHAR(16))');
dataManager.update("INSERT INTO testtable VALUES(1, 'Kenny')");
dataManager.update("INSERT INTO testtable VALUES(2, 'Cartman')");
dataManager.update("INSERT INTO testtable VALUES(3, 'Mr. Garrison')");

// Grab stuff from database and show it
def results = dataManager.queryList('SELECT * FROM testtable')
println '
<pre>'

println results
println '</pre>
'

Expected output:

[[val:Kenny, id:1], [val:Cartman, id:2], [val:Mr. Garrison, id:3]]

Using zero.data with PHP

public/db2zero.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
$dataManager = dataManager('mydb');

// Drop the test table, in case it has already been created.
// Table might not exist yet. Ignore errors.
dataExec($dataManager, 'DROP TABLE testtable');

// Create table and put some stuff in it.
dataExec($dataManager, 'CREATE TABLE testtable(id int NOT NULL PRIMARY KEY, val VARCHAR(16))');
dataExec($dataManager, "INSERT INTO testtable VALUES(1, 'Kenny')");
dataExec($dataManager, "INSERT INTO testtable VALUES(2, 'Cartman')");
dataExec($dataManager, "INSERT INTO testtable VALUES(3, 'Mr. Garrison')");

// Grab stuff from database and show it
$items = dataExec($dataManager, 'SELECT * FROM testtable');
var_dump($items);

Expected output:

array(3) {
  [0]=>
  array(2) {
    ["val"]=>
    string(5) "Kenny"
    ["id"]=>
    int(1)
  }
  [1]=>
  array(2) {
    ["val"]=>
    string(7) "Cartman"
    ["id"]=>
    int(2)
  }
  [2]=>
  array(2) {
    ["val"]=>
    string(12) "Mr. Garrison"
    ["id"]=>
    int(3)
  }
}

Over to You!

That’s all for now – this was a short tutorial, but hopefully enough to get you going with sMash and DB2. Remember that the code above is included in a sample application which you can download directly here, or pull in via sMash’s module management system (see top of this article). You’ll of course need to change the database parameters (hostname, credentials etc…) in db2pdo.php and zero.config to get it working.

Do post a comment here if something in this article doesn’t work as expected! You can also tell us all about your sMash & DB2 escapades on the projectzero.org forum.

  1. FlorianConstain
    July 14th, 2009 at 13:25 | #1

    Thanks, just what I was looking for.

  1. December 15th, 2009 at 20:07 | #1