Nov 252010

update: The contents of this blog post has now been merged into the Doctrine 2 cookbook entry (

A few months ago, we started using Doctrine 2 on the project we’re developing for a customer of ours. Doctrine 2 has proven to be both fast and stable which has certainly saved us time and effort. We recently had to implement table prefixes on our tables in Doctrine 2, which turned out to be not so trivial, and led me to a journey inside the Doctrine 2 metadata parser, event hooks and relation mapper. Fun stuff!

Prefixing regular tables

The first thing I had to do was to get prefixes working. Contrary to Doctrine 1, where you could use the Doctrine_Core::ATTR_TBLNAME_FORMAT to set a table prefix (or suffix), there isn’t yet a similar feature for Doctrine 2 (looking at some bug reports like this one, it doesn’t look like it’s high up on the list of priorities either). However, the solution suggested was to add a loadClassMetadata listener to provide the table prefixes by rewriting the defined table names as they were being generated and add the prefix programatically in that event. The cookbook entry describes the procedure for implementing this in detail, and after adding the suggested lines of code to our Doctrine loader tables were being prefixed all around.

Prefixing many-to-many jointables

However, the many-to-many automatically generated jointables were *not* being prefixed, and there turned out to be a good explanation. The loadClassMetadata event was being fired in the table that defined the many-to-many joined relations, but there was no separate loadClassMetadata event fired for the generated jointables where I could rewrite the table name with the configured prefix. As Benjamin Beberlei mentioned in a follow-up comment to the bug report we filed for the cookbook entry, this was both (semi-)intended behaviour and a known limitation of the code in the cookbook entry with an “easy” fix. The fix meant I had to look into taking a peek at the table’s metadata and perform an explicit rewrite on the related jointable. Luckily that’s all documented behaviour and the pointers Benjamin gave me were more than enough to get me started.

The behaviour of the code in the cookbook was to just get the table name from the class metadata as it was being loaded, prepend the table prefix and set the table name back to the new prefixed name. Easy peasy.

$classMetadata = $eventArgs->getClassMetadata();
$classMetadata->setTableName($this->_prefix . $classMetadata->getTableName());

However, to rewrite the table name for many-to-many relation jointables, we have to also traverse the classmetadata properties, look for related tables and rewrite them if the join type is many-to-many:

foreach ($classMetadata->getAssociationMappings() as $fieldName => $mapping) {
if ($mapping['type'] == \Doctrine\ORM\Mapping\ClassMetadataInfo::MANY_TO_MANY) {
$tableName = $classMetadata->associationMappings[$fieldName]['joinTable']['name'];
$classMetadata->associationMappings[$fieldName]['joinTable']['name'] = $this->_prefix . $tableName;

Turns out that it wasn’t that much effort needed, and the result is neatly prefixed table names all around. As an extra, we of course also added a ZF application.ini entry to define the table prefix for easy configuration. But that’s a different blog post.

May 072009

Last night I nailed an annoying bug in my bug tracker that has been tricky to locate, and was related to MySQL, transactions and apparently only certain MySQL versions which behaved correctly.

To ensure consistency in the bug tracker, I use a transaction to commit several changes. In this case, after an issue was created, it wouldn’t save stuff being inserted after the transaction->commit(). However – the id fields which were auto_inc – would all increment by one for each row that was never inserted. This would only happen on a select few MySQL versions, which lead me to believe it was actually a bug in those specific MySQL versions, and not in my code (it’s never me, of course :P ). This was also based on the assumption that doing a commit() would stop “transaction mode” and turn “autocommit” back on. One glance at the PHP manual proved me wrong.

It all did come down to the fact that in the class file that was creating an issue, the “autocommit” parameter for mysqli was never set back to “true” after committing a transaction. According to the manual, this should mean that all subsequent queries for that connection would “fail” unless a new “commit()” has been made. However, this obviously didn’t happen, as I have most of my bug tracker installations running properly, without this problem happening at all.

Under no circumstance was a commit() called after that point – which should mean that any queries being executed after that point were being rolled back. However, that only happed on a specific few MySQL versions, and none of the ones I tested on (confirmed at least 5.0.61 and 5.1.3). To make matters worse, the auto_inc id field did increment by one, even though the row was never saved!

The solution was simple – either do a second commit() at the end of the function to make sure all the queries after the initial transaction were commited, or make sure that the transaction turns on autocommit when it was done. In this case, I created a second function in the transaction class to commitAndEnd(), which turned autocommit back on after committing.

Lesson learned: Read manuals, don’t make assumptions. And don’t blindly trust MySQL.

Apr 142009

DB2 is one of IBM’s database server systems, it comes in many flavours, and runs on many platforms including Linux and Windows, but traditionally on IBM’s own mainframes. DB2 has a long history, so if you’re in the business chances are you’ll need to work with it one day.

We bumped into it when we had to connect to said database across both platforms and network. Here’s an account of our experience with it.

The problem

A web application in a LAMP environment (Ubuntu Hardy, Apache 2, MySQL5 and PHP 5.2), where login should be done using a centralised database – the already mentioned IBM DB2, on IBM System i.  As with DB2, System i has a long history, so don’t be surprised if it’s referred to by previous names such as AS/400, or eServer iSeries.

The solution

Our first attempt involved installing DB2 express on the same server as the web application, by using the ibm_db2 driver we got a connection that worked, but we were denied access with the following message:

An attempt to connect to a host failed due to a missing DB2 Connect product or invalid license. SQLSTATE=42968

DB2 Connect is apparently part of some DB2 products, but not DB2 express. A long call to IBM eventually established that we probably needed to pay for this approach to work.

Second attempt

Luckily there is another way to connect to DB2 – using ODBC. Which means we need an ODBC driver for DB2. IBM calls System I Access a connectivity product, and its web page goes on to say: “The entire focus of System i Access is to deliver the strengths and capabilities of System i to the desktop through easy-to-use screens and wizards”. In short, it’s a bundle that includes a 5250 emulator, an EDRS driver, and what we need: an ODBC driver.

  • Get ODBC: We need an ODBC manager and PHP support for it. Download and install it by apt getting unixodbc, unixodbc-bin, php5-odbc and libstdc++5.
  • Get System i Access: We also need an ODBC driver for DB2, available for dowload free of charge as an RPM package from Remember to choose the correct version for your system (32-bit or 64-bit). To install it you can for instance run the following:

$ sudo rpm -ivh –nodeps iSeriesAccess-5.4.0-1.6.i386.rpm

It will be set up under the catalogue /opt/ibm/

The ODBC manager needs to know about the odbc driver and what to say to it. This is configured in two files: odbc.ini and odbcinst.ini. If they haven’t already been created by installing System i Access, they should be created in the /etc folder.

odbc.ini should look something like this:

Description             = iSeries Access ODBC Driver
Driver                  = iSeries Access ODBC Driver
Driver64                = iSeries Access ODBC Driver 64-bit
System                  =
UserID                  = ELVIS
Password                = PRICILLA
Naming                  = 0
DefaultLibraries        = HAWAII,QGPL,qiws
Database                = GRACELAND
ConnectionType          = 0
CommitMode              = 2
ExtendedDynamic         = 1
DefaultPkgLibrary       = QGPL
DefaultPackage          = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression    = 1
MaxFieldLength          = 32
BlockFetch              = 1
BlockSizeKB             = 128
ExtendedColInfo         = 0
LibraryView             = 0
AllowUnsupportedChar    = 0

Each connection has its own ini section. The following parameters are the ones you are most likely to change:

  • Driver: has to match a section in odbcinst.ini, which we’ll get to shortly.
  • System: The ip adress to where the db2 is
  • UserID
  • Password
  • DefaultLibraries: QGPL and qiws are standard, but most likely you need extra libraries
  • Database: The name of the database you want to connect to

odbcinst.ini should look something like this:

[iSeries Access ODBC Driver]
Description     = iSeries Access for Linux ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib64/
Setup           = /opt/ibm/iSeriesAccess/lib64/
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN’s,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64        = /opt/ibm/iSeriesAccess/lib64/
Setup64         = /opt/ibm/iSeriesAccess/lib64/
Threading       = 2
DontDLClose     = 1
UsageCount      = 1

[iSeries Access ODBC Driver 64-bit]
Description     = iSeries Access for Linux 64-bit ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib64/
Setup           = /opt/ibm/iSeriesAccess/lib64/
Threading       = 2
DontDLClose     = 1
UsageCount      = 2

Notice there’s no mention of port anywhere. You cannot configure this, the port is 8471 so make sure it’s open in both ends. Also note that the Driver and Driver64 parameters in odbc.ini matches the two sections in odbcinst.ini


Using the connection
The setup should be up and running now, so to use it in PHP we need to create a connection. A simplified connection can look something like this:

function getConnection()
$dsn             = sfConfig::get(‘db2_conn_dsn’, ‘DB2CONN’);
$user            = sfConfig::get(‘db2_conn_usr’, ‘ELVIS’);
$pwd             = sfConfig::get(‘db2_conn_pwd’, ‘PRICILLA’);

$connection = odbc_connect($dsn, $user, $pwd) or die ("Can not connect to $dsn.");

return $connection;

An example of a simple login query that uses a stored procedure from the hawaii library:

function checkUserNameAndPassword($username, $password)

$conn            = getConnection();
$user            = false;
if ($conn)
$sql       = utf8_decode("{CALL HAWAII.LOGIN(‘$username’, ‘$password’)}");
$callstore = odbc_exec($conn, $sql) or die ("Can not perform query");
$user    = odbc_fetch_array($callstore, 1);

return $user;

For debugging purposes, a useful way to output the result from an odbc_exec can be found in one of the comments at:


Here’s a simplified example of how to insert data using a prepared statement:

function insertUser($user)

$conn = getConnection();

$sql .= utf8_decode(" VALUES (?,?,?,?)");
$res  = odbc_prepare($conn, $sql) or die ("Can not prepare query");
$parameters = array(
if(!odbc_execute($res, $parameters)) die("Can not perform query");

return true;

You can also insert without using the prepare statement (by using odbc_exec), but we had trouble with character encoding. The example above worked for us.


Good luck!