Somacon.com: Articles on websites & etc.

§ Home > Index > Web Development

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.


Created 2005-12-14, Last Modified 2011-07-24, © Shailesh N. Humbad
Disclaimer: This content is provided as-is. The information may be incorrect.