Status of PHP PDO_Firebird as of May 2011
May 31st, 2011Working 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 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() [pdo.exec]: SQLSTATE[HY000]: General error: -413 conversion error from string "BOGUS_PK" in C:\apache2\htdocs\firebird\index.php on line 28
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
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
WI-V6.3.0.26074 Firebird 2.5
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!
Kunstroute Kralingen-Crooswijk 2009




random picture
currently reading
last good movies I saw
weekly artist top 10