PHP

Open DataBase Connectivity (ODBC)

For DBMSs that are not supported natively by PHP-such as Microsoft Access- Open DataBase Connectivity (ODBC) functions are available to connect to, query, and retrieve results. ODBC also offers database-tier flexibility where, for example, a low-end DBMS such as Access can be replaced with a high-end DBMS such as Oracle without modifying the middle-tier PHP scripts. In addition, selected DBMSs-including IBM DB2, Adabas D, and Sybase SQL Anywhere-use ODBC functions for direct access; that is, they don't have their own function libraries but use ODBC natively as a function library.

An ODBC client is required for the DBMS if ODBC is to be used. For example, MySQL can be used with ODBC by installing the MyODBC client described in Section 12 of the MySQL manual; the MyODBC client is available from http://www.mysql.com.

Five key ODBC functions are listed here, and Example 4-13 shows these implemented in a modified version of Example 4-1.

resource odbc_connect(string datasource, string username, string password, [int cursor_type])

Establishes a connection to an ODBC data source. On success, the function returns a connection resource handle that can access databases through subsequent commands. The first parameter is a DSN to indicate the data source to connect to. The DSN parameter can require some experimentation; it depends on the DBMS being accessed. The DSN can sometimes be prefixed with DSN= and sometimes this can be omitted. The second and third parameters, as well as the return value (a connection resource), are the same as for mysql_connect( ). The fourth parameter is often unnecessary; however, if problems are encountered using ODBC, try passing through a fourth parameter of SQL_CUR_USE_ODBC.

resource odbc_exec(resource connection, string query)

Runs an SQL query on the connection returned from odbc_connect( ). Returns a result resource handle on success and false on failure.

int odbc_fetch_row(resource result_set)

Fetches the result-set data, row-by-row, following an odbc_exec( ) command using the result_set identifier returned by the query. The results are returned as an array, and the use is identical to mysql_fetch_row( ). false is returned when no more rows are available.

int odbc_num_fields(resource result_set)

Returns the number of attributes associated with a result_set handle, where the result_set handle is returned from odbc_exec( ).

int odbc_close(resource connection)

Closes an ODBC data source opened with odbc_connect( ).

Example 4-13. Connecting to an ODBC data source with PHP
<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
<?php
   // (1) Open the database connection
   $connection =
        odbc_connect("DSN=winestore","fred","shhh");
   // (2) Run the query on the winestore through the
   // connection
   $query = odbc_exec($connection, "SELECT * FROM
                              wine");
   // (3) While there are still rows in the result set
   while ($row = odbc_fetch_row($result))
   {
      // (4) Print out each attribute in the row
      for ($i=0; $i<odbc_num_fields($result); $i++)
         echo $row[$i] . " ";
      // Print a carriage return to neaten the output
      echo "\n";
   }
   // (5) Close the connection
   odbc_close($connection);
?>
</pre>
</body>
</html>