Notes for PDO ODBC in PHP 5.1
PHP Data Objects (PDO) is a fantastic class to simplify your common database coding needs. It allows you to access any database using one, consistent calling convention. I used it for a project where I needed to access a SQLite in-memory database, and a legacy Visual Foxpro database. By using PDO, I had to remember less function names and write fewer lines of code. PDO was just released with PHP 5, so there are still some limitations and problems as of 12/14/2005.
Setting up PHP 5.1 with PDO
Installing PDO/PHP 5.1 is a bit tricky. PHP for Windows is now split into three downloads, which is very confusing for everyone. Assuming you have IIS installed, first you need to run the PHP installer. Then, you need to unzip the PHP zip file over the directory where you installed PHP to get the standard extensions. Finally, if you want any of the PECL extensions, you have to download and install them too. Someone let me know if there is a better way.
Installation does not stop there. You have to set file and folder permissions for IUSR_MachineName. Windows XP has "cacls", which allows permission setting to be scripted, but PHP does not do this for you. Finally, you have to open PHP.ini in Notepad and make the necessary changes to load the extensions. The lines for the PDO extensions are not there yet, so you have to paste them in yourself.
extension_dir = "c:\program files\php\ext\" ; Windows Extensions extension=php_pdo.dll extension=php_pdo_sqlite.dll extension=php_pdo_odbc.dll
See the Windows Installation instructions for more help, but be prepared for a long read. If PHP could be installed in a few clicks without poring through half-a-dozen online docs, that would be great.
PDO Limitations
One limitation is that there is no way to obtain the names of the columns when the query result has no rows. Therefore, you can not print the column names when there are no rows. Your code has to look something like this.
<?php // query the database
$DbToQuery = new PDO("sqlite:c:\mydb.sl3");
$sql = "SELECT * FROM MyTable";
$result = $DbToQuery->query($sql);
// Get the number of columns
$Cols = $result->columnCount();
// Loop through the results
$countrows = 1;
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
if($countrows == 1) {
// Print column names
print join(",", array_keys($row));
}
$countrows++;
// handle the row data
// ...
} ?>
PDO Wrapper For Testing
There are bugs that make debugging your program difficult. See Bug #35620 and Bug #35552. Since it was just released, this is understandable. If your ODBC query crashes PHP, you ca not see where the crash occurred or why, so you have to revert to debugging style of the dark ages (putting echo statements everywhere). So I made a PDO wrapper class that allows you to switch between the original ODBC methods and the new PDO method. It supports a very limited subset of PDO, but it works for my project.
<?php // PDO wrapper
class PDOWrapperODBC
{
private $UsePDO = false;
private $db;
function __construct($DSN) {
if($this->UsePDO) {
$this->db = new PDO("odbc:Driver={Microsoft Visual FoxPro Driver};".
"SourceType=DBF;SourceDB=C:\;Exclusive=No");
} else {
$DSN = str_replace("odbc:","", $DSN);
$this->db = odbc_connect("$DSN", "", "") or die("Error connecting to $DSN");
}
}
function __destruct() {
if($this->UsePDO) {
$this->db = null;
} else {
odbc_close($this->db);
}
}
function setAttribute($par1, $par2) {
if($this->UsePDO) {
$this->db->setAttribute($par1, $par2);
}
}
function query($query) {
return new ResultWrapperODBC($this->UsePDO, $this->db, $query);
}
function errorInfo($query) {
if($this->UsePDO) {
return $this->db->errorInfo();
} else {
return odbc_errormsg($this->db);
}
}
function quote($input) {
if($this->UsePDO) {
return $this->db->quote($input);
} else {
return "'".str_replace("'", "''", $input)."'";
}
}
}
// Result wrapper
class ResultWrapperODBC
{
private $UsePDO;
private $result;
function __construct($UsePDOParam, $db, $query) {
$this->UsePDO = $UsePDOParam;
if($this->UsePDO) {
$this->result = $db->query($query);
} else {
$this->result = odbc_exec($db, $query);
}
return $this->result;
}
function __destruct() {
if($this->UsePDO) {
$this->result = null;
} else {
odbc_free_result($this->result);
}
}
function fetch($FetchOption = null) {
if($this->UsePDO) {
if($FetchOption == null) {
$row = $this->result->fetch();
} else {
$row = $this->result->fetch($FetchOption);
}
} else {
if($FetchOption == PDO::FETCH_ASSOC) {
$row = odbc_fetch_array($this->result);
} else {
odbc_fetch_into($this->result, $row);
}
}
return $row;
}
function fetchColumn($ColumnIndex) {
if($this->UsePDO) {
return $this->result->fetchColumn($ColumnIndex);
} else {
$row = odbc_fetch_row($this->result);
if($row) {
return $row[$ColumnIndex];
} else {
return false;
}
}
}
function columnCount() {
if($this->UsePDO) {
return $this->result->columnCount();
} else {
return odbc_num_fields($this->result);
}
}
} ?>
PDO Utility Functions
Here are some handy PDO utility functions. Subsitute "new PDOWrapperODBC" in place of "new PDO" to use the above PDO wrapper. In the PDOWrapperODBC, set the $UsePDO to true or false. Then, your PDO code will work by using either PDO or the original ODBC functions. This is just a temporary workaround until PDO itself is more stable.
<?php
// Open connection to PDO.
try {
$db = new PDO("odbc:Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;".
"SourceDB=C:\;Exclusive=No");
} catch (PDOException $e) {
die("Error #".$e->getCode()." " . $e->getMessage() . "<br>");
}
// Helper function that prints any query result to HTML
// NOTE: row headers not printed if no rows in result
function DebugPrintTable($db, $query, $tabdelimited = false) {
print "<hr>$query";
$result = $this->RunQuery($db, $query);
if($tabdelimited) {
print "<pre>";
} else {
print "<table border=\"1\">";
}
// Get the number of columns
$Cols = $result->columnCount();
// Print the table to show all the records
$countrows = 0;
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
if($countrows == 0) {
// Print field names
if($tabdelimited) {
print "<b>".join("\t",array_keys($row))."</b>\n";
} else {
print "<tr><th>".join("</th><th>",array_keys($row))."</th></tr>\n";
}
}
$countrows++;
// Print row values
if($tabdelimited) {
print join("\t",$row)."\n";
} else {
print "<tr><td>".join("</td><td>",$row)."</td></tr>\n";
}
}
if($tabdelimited) {
// Print number of rows
if($countrows == 0) {
print "<i>zero rows</i>\n";
} else {
print "<i>$countrows rows</i>";
}
print "</pre>";
} else {
print "</table>";
}
print "<hr>";
}
// Execute a query and returns reference to the
// PDOStatement object, with error checking
function RunQuery($db, $sql) {
$result = $db->query($sql)
or die("Error ".print_r($db->errorInfo()).
"<p>Query:<p>".$sql);
return $result;
}
// Helper function that returns the value
// of first row and column from result of query.
// Returns null if there was no value.
function SingletonQuery($db, $sql) {
$result = $this->RunQuery($db, $sql);
if($result) {
return $result->fetchColumn(0);
} else {
return null;
}
}
?>
You can get the ODBC driver for Visual Foxpro from Microsoft. The code above is provided to the public domain, caveat emptor.
Disclaimer: This content is provided as-is. The information may be incorrect.