Lorenzo Alberton

London, UK · Contact me ·

« Articles

PHP PDO_Firebird status

PHP, PEAR, Database, Firebird SQL, Windows, PDO · 11 April 2006 15:12 · 1 comment

Abstract: A quick roundup of the PHP PDO_Firebird driver. Test of its current status: what works and what doesn't.

If you're working with PHP and Firebird, you already know that the PHP5 Firebird extension (php_interbase) definitely represents an huge improvement over its PHP4 predecessor. Also, you've certainly heard about the PHP Data Objects (PDO) extension, which "defines a lightweight, consistent interface for accessing databases in PHP". But what's the status of the PDO_Firebird driver (docs)? Let's find it out together.

A quick test - Create a sample table

$db = new PDO ("firebird:dbname=localhost:/path/to/pdotest.fdb", 'userid', 'password');
//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);');

The connection to the db is estabilished and the table is correctly created.

Test the error handlers

PDO offers three different error handling strategies:

PDO::ERRMODE_SILENT
PDO::ERRMODE_WARNING
PDO::ERRMODE_EXCEPTION

Let's run an invalid INSERT query and see if they're honored:

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 Warning:';
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$db->exec("INSERT INTO testuser (ID, NAME, ADDRESS, COMPANY) VALUES ('BOGUS_PK', 'a', 'b', 'c')");
 
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 Warning:
Warning: PDO::exec() [function.exec]: SQLSTATE[HY000]: General error: -413 conversion error from string "BOGUS_PK" in /pdotest/fb.php on line XXX
Test Silent:
array(3) { [0]=> string(5) "HY000" [1]=> int(-413) [2]=> string(40) "conversion error from string "BOGUS_PK" " }

So far so good...

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));

The first method correctly returns "1" since the connection is active. The second method correctly returns "firebird". Calling any of the other three methods will result in the script hanging.

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));
}

The INSERT works, as I can see a new record with the correct values is inserted in the table, but the fetchAll() query will return an empty record (i.e.: a record with empty values). There were clearly some issue while populating the result array (@see bug #35386).

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 time the INSERT didn't work, and I got this error back:

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

Unless I miss something obvious, I wouldn't expect this error.

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 time I had more success: the values are inserted. OTOH, while I can clearly see that there are two records in the table ('user2' and 'user3'), and the resultset correctly returns two records, the first one is empty (as it happened before); only the 2nd one is populated:

Array
(
    [0] => Array
        (
            [ID] =>
            [NAME] =>
            [ADDRESS] =>
            [COMPANY] =>
        )
 
    [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));

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

That's nice. I also tried with PDO::ATTR_FETCH_TABLE_NAMES, but it didn't work.

Test variable binding

As a last test, let's see if binding php variables to the named placeholders work:

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();
}

I wasn't lucky again. This time the error message is:

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

Conclusions

As these few tests showed, the PDO_Firebird driver needs A LOT of love. If you're a C programmer, know the PHP internals and want to help, please contact Wez, Marcus, Ilia, George and the other PDO guys or drop a mail on the internals(@)lists.php.net mailing list. Until then, the PHP5 php_interbase driver is the only valid option for PHP and Firebird.

Back