Working both with Firebird SQL and PDO (but not yet at the same time) I am interested in the combination of both.
According to the documentation the extension is still in experimental status, but I was still curious about how far it has progressed. I decided to redo the experiments Lorenzo Alberton did in an article in 2006.

I performed these tests on PHP Version 5.3.6 with Firebird 2.5 on a Windows 7 machine.

Ok, off we go:

A quick test – Create a sample table

$db = new PDO ("firebird:dbname=c:\\test.fdb", 'SYSDBA', 'masterkey');
//set the error mode to throw exceptions

$query_create_table = <<< EOD
CREATE TABLE testuser (
ID INTEGER NOT NULL,
NAME VARCHAR(100) NOT NULL,
ADDRESS VARCHAR(100) NOT NULL,
COMPANY VARCHAR(100) NOT NULL
);
EOD;
$db->exec($query_create_table);
$db->exec('ALTER TABLE testuser ADD CONSTRAINT INTEG_13 PRIMARY KEY (ID);');

This still works: the database connection is established and the table is created

Test the error handlers

echo 'Test Exception:';
try {
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec("INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES ('BOGUS_PK', 'a', 'b', 'c')");
echo 'the script should not echo this line';
} catch(PDOException $e) {
echo 'error during db access. '.$e->getMessage();
}

echo 'Test Silent:';
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$db->exec("INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES ('BOGUS_PK', 'a', 'b', 'c')");
var_dump($db->errorInfo());

output:

Test Exception:
error during db access. SQLSTATE[HY000]: General error: -413 conversion error from string "BOGUS_PK"

Test Silent:
array(3) { [0]=> string(5) "HY000" [1]=> int(-413) [2]=> string(40) "conversion error from string "BOGUS_PK" " }

No changes here

Test some DB attributes:

print_r($db->getAttribute(PDO::ATTR_CONNECTION_STATUS));
print_r($db->getAttribute(PDO::ATTR_DRIVER_NAME));
print_r($db->getAttribute(PDO::ATTR_SERVER_VERSION));
print_r($db->getAttribute(PDO::ATTR_CLIENT_VERSION));
print_r($db->getAttribute(PDO::ATTR_SERVER_INFO));

output:

1

Firebird/x86/Windows NT (access method), version "WI-V2.5.0.26074 Firebird 2.5" Firebird/x86/Windows NT (remote server), version "WI-V2.5.0.26074 Firebird 2.5/tcp (YS-IX)/P12" Firebird/x86/Windows NT (remote interface), version "WI-V2.5.0.26074 Firebird 2.5/tcp (YS-IX)/P12" on disk structure version 11.2

This time around all these functions return a result, the ATTR_SERVER_INFO and ATTR_SERVER_VERSION return the same result. Usually you get different values for these attributes. MySQL for instance gives just a version number for ATTR_SERVER_VERSION and information about uptime, queries per second, etcetera for ATTR_SERVER_INFO.

Test standard queries

$res = $db->exec("INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES (1, 'user1', 'address1', 'company1')");
if ($res === false) {
var_dump($db->errorInfo());
} else {
print_r($db->query('SELECT * FROM testuser')->fetchAll(PDO::FETCH_ASSOC));
}

output:

Array (
[0] => Array ( [ID] => 1 [NAME] => user1 [ADDRESS] => address1 [COMPANY] => company1 )
)

Nice, this bug has been fixed.

Test prepared queries with named parameters

$query = 'INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES (:ID, :NAME, :ADDRESS, :COMPANY)';
$stmt = $db->prepare($query);
$values = array(
':ID' => 2,
':NAME' => 'user2',
':ADDRESS' => 'address2',
':COMPANY' => 'company2'
);
if ($stmt->execute($values) === false) {
var_dump($db->errorInfo());
} else {
print_r($db->query('SELECT * FROM testuser')->fetchAll(PDO::FETCH_ASSOC));
}

This one is not fixed yet, we still get:

array(3) { [0]=> string(5) "00000" [1]=> int(-999) [2]=> string(22) "Invalid parameter name" }


Test prepared queries with ordered parameters

$query = 'INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES (?, ?, ?, ?)';
$stmt = $db->prepare($query);
$values = array(
0 => 3,
1 => 'user3',
2 => 'address3',
3 => 'company3'
);
$res = $stmt->execute($values);
if ($res === false) {
var_dump($db->errorInfo());
} else {
print_r($db->query('SELECT * FROM testuser')->fetchAll(PDO::FETCH_ASSOC));
}

This works, the fetchAll also gives the correct result:

Array (
[0] => Array ( [ID] => 1 [NAME] => user1 [ADDRESS] => address1 [COMPANY] => company1 )
[1] => Array ( [ID] => 3 [NAME] => user3 [ADDRESS] => address3 [COMPANY] => company3 )
)


Test portability options

$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
print_r($db->query('SELECT * FROM testuser WHERE ID=3')->fetchAll(PDO::FETCH_ASSOC));

output:

Array ( [0] => Array ( [id] => 3 [name] => user3 [address] => address3 [company] => company3 ) )

good, let’s try PDO::ATTR_FETCH_TABLE_NAMES

$db->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, 1);
print_r($db->query('SELECT * FROM testuser WHERE ID=3')->fetchAll(PDO::FETCH_ASSOC));

output:

Array (
[0] => Array ( [TESTUSER.ID] => 3 [TESTUSER.NAME] => user3 [TESTUSER.ADDRESS] => address3 [TESTUSER.COMPANY] => company3 ) )

good, this has been fixed also

Test variable binding

try {
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->exec("CREATE TABLE test(name VARCHAR(255) NOT NULL, surname VARCHAR(255));");
$stmt = $db->prepare("insert into test (name, surname) values (:name, :surname)");
// bind php variables to the named placeholders in the query
// they are both strings that will not be more than 64 chars long
$stmt->bindParam(':name', $name, PDO::PARAM_STR, 64);
$stmt->bindParam(':surname', $surname, PDO::PARAM_STR, 64);
// insert a record
$name = 'Foo';
$surname = 'Bar';
$stmt->execute();
// and another
$name = 'Fu';
$surname = 'Ba';
$stmt->execute();
// more if you like, but we're done
$stmt = null;
} catch(PDOException $e) {
echo 'ERROR: '.$e->getMessage();
}

unfortunately, this still yields an error:

ERROR: SQLSTATE[HY000]: General error: -804 Dynamic SQL Error SQL error code = -804 Incorrect values within SQLDA structure

However we can use the bindParam method with positional parameters, like so:

try {
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $db->prepare("insert into test (name, surname) values (:name, :surname)");
// bind php variables to the named placeholders in the query
// they are both strings that will not be more than 64 chars long
$stmt->bindParam(1, $name, PDO::PARAM_STR, 64);
$stmt->bindParam(2, $surname, PDO::PARAM_STR, 64);
// insert a record
$name = 'Foo';
$surname = 'Bar';
$stmt->execute();
// and another
$name = 'Fu';
$surname = 'Ba';
$stmt->execute();
// more if you like, but we're done
$stmt = null;
} catch(PDOException $e) {
echo 'ERROR: '.$e->getMessage();
}
print_r($db->query('SELECT * FROM test')->fetchAll(PDO::FETCH_ASSOC));

the result is:

Array (
[0] => Array ( [NAME] => Foo [SURNAME] => Bar )
[1] => Array ( [NAME] => Fu [SURNAME] => Ba ) )


Ok! So that means we can go back to our testuser INSERT query and try it like this:

$query = 'INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES (:ID, :NAME, :ADDRESS, :COMPANY)';
$stmt = $db->prepare($query);
$values = array(
0 => 2,
1 => 'user2',
2 => 'address2',
3 => 'company2'
);
if ($stmt->execute($values) === false) {
var_dump($db->errorInfo());
} else {
print_r($db->query('SELECT * FROM testuser')->fetchAll(PDO::FETCH_ASSOC));
}

The result is:

Array (
[0] => Array ( [ID] => 1 [NAME] => user1 [ADDRESS] => address1 [COMPANY] => company1 )
[1] => Array ( [ID] => 3 [NAME] => user3 [ADDRESS] => address3 [COMPANY] => company3 )
[2] => Array ( [ID] => 2 [NAME] => user2 [ADDRESS] => address2 [COMPANY] => company2 ) )


Conclusion
Almost everything works this time around except binding parameters by name (see also bug #48877).

Update 27th December 2011:
As can be seen in the comments, the bug above has been fixed!
The bugfix is included in the 4th PHP 5.4 release candidate: PHP 5.4.0 RC4 release notes.

This could mean that there is now a fully functioning PDO driver for Firebird!

Update 25th January 2012:
I’ve tried out the latest PHP 5.3.9 from here (had to extract the firebird DLL manually from the .cab file because it is not an option in the installer).

And indeed the bugs have been fixed in this release!

Restyled this page a bit:

• Moved the “about” pages to the header
• You can now find “social shortcuts” on the top right in the header
• Latest “tweet” is on the right

My latest project is on-line:

Kunstroute Kralingen-Crooswijk 2009, more than 50 artist show their work on 3 and 4 October in Kralingen and Crooswijk, Rotterdam), go check out the site if you like.

look at her site for more info and pictures

All the necessary things have been arranged, so preparations for the 14th of December are nearly finished. Carola had her wedding shower last week! Check out some of the photographs.

We will have a very globalized wedding (it will take yeaaaars of biking to work to offset the carbon emissions):

• The bride is from Perú
• The groom is from Fryslân
• One of the witnesses will fly in from Canada
• Another witness will fly in from The United Kingdom
• Yet another witness is from Perú
• The photographer was born in Korea
• and on top of that, the person who will marry us is from Suriname!

A looong time since I’ve posted something here. Lots of changes in my life the last few months.

And more are coming! For instance, Carola and me have decided to spend the rest of our lives together!

To find out more about what this all means you can visit the official wedding page.

I’ve recently started playing around in Fedora 7 (the 64-bit version).
One of the ‘problems’ was getting it to support all the buttons on my Logitech MX Revolution.
However, Olli Salonen has created a nifty utility called btnx

To install:

$ sudo /sbin/modprobe uinput
$ wget http://www.ollisalonen.com/btnx-0.2.11.tar.gz
$ tar -xvvf btnx-0.2.11.tar.gz
$ cd btnx-0.2.11
$ make
$ sudo make install


after that you can sudo ./btnx to start using it.

Thank you mr. Aronofsky! The Fountain is one Shibalba of a movie. I’m still shaking.

Other highly recommended movies: Paprika, The Girl Who Leapt Through Time, Pure Hearts, Special, The Tripper (Reagan vs. Hippies!).

And if you have a strong stomach or if you are not entirely sure about the perversion of (some of) the Japanese:Strange Circus. It gives Ichi the Killer a run for its money in freakiness.

Just before heading of to Amsterdam for the AFFF where I will be watching:
4Th Dimension, Offscreen, Special, Tripper, Strange Circus on saturday and
Pure Hearts, Girl Who Leapt Through Time, The Fountain, Backwoods and Paprika on sunday.

And hopefully be eating at: Wagamama

[drumroll]

My latest project is finished! Check it out at JiP Photography.

Wow, time flies when you are having a lot of fun!

To celebrate this moment, the forces of nature have decided to darken the moon!

It was way too clouded in my hometown to watch it, but I followed it on http://www.astro.uu.nl/~sluys/Hemel/moon/lunareclipse_20070304.html