| only for RuBoard - do not distribute or recompile | |
Interacting with other relational DBMSs is similar to using MySQL. In this section, we outline the key functions to access Microsoft SQL Server, ODBC-compliant, Oracle, and PostgreSQL DBMSs. We illustrate how to interact with different DBMSs by presenting four rewritten versions of Example 4-1 that include different DBMS functionality.
Functions for accessing other databases, including Informix and Sybase, can be found in the PHP manual. For DBMSs that are not supported natively by PHP, ODBC can usually be used; we discuss ODBC later in this section.
Similarly to the MySQL function library, there are many functions for connecting to, querying, and extracting results from Microsoft SQL Server DBMSs.
SQL Server can be used under the Microsoft Windows operating system by making minor changes to THE configuration of PHP in the php.ini file; these changes are discussed in the online PHP manual. SQL Server can also be accessed from a Linux platform by installing the FreeTDS package available from http://www.freetds.org and recompiling PHP with the -with-sybase option; this enables both Sybase and SQL Server support. SQL Server databases can also be accessed using the ODBC library discussed in the next section.
Six functions are listed here, and Example 4-12 shows these implemented in a modified version of Example 4-1.
Establishes a connection to a SQL Server DBMS. On success, the function returns a connection resource handle that can access databases through subsequent commands. Returns false on failure.
The parameters (all of which are optional) and their use are identical to those of the mysql_connect( ) function.
Uses the database on the connection, where the connection is a resource returned from mssql_connect( ).
Runs an SQL command through the connection created with mssql_connect( ) on the database selected with mssql_select_db( ). Returns a resource—a result handle used to fetch the result set—on success and false on failure.
Fetches the result set data, row-by-row, following an mssql_query( ) command using the result_set resource returned by the query. The results are returned as an array, and use is again identical to mysql_fetch_row( ). false is returned when no more rows are available.
Returns the number of attributes in a result_set resource handle, where the result_set handle is returned from mssql_query( ).
Closes a SQL Server connection opened with mssql_connect( ).
<!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 and select the
// winestore
$connection = mssql_connect("localhost","fred","shhh");
mssql_select_db("winestore", $connection);
// (2) Run the query on the winestore through the
// connection
$result = mssql_query("SELECT * FROM wine",
$connection);
// (3) While there are still rows in the result set
while ($row = mssql_fetch_row($result))
{
// (4) Print out each attribute in the row
for ($i=0; $i<mssql_num_fields($result); $i++)
echo $row[$i] . " ";
// Print a carriage return to neaten the output
echo "\n";
}
// (5) Close the database connection
mssql_close($connection);
?>
</pre>
</body>
</html>
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.
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.
Runs an SQL query on the connection returned from odbc_connect( ). Returns a result resource handle on success and false on failure.
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.
Returns the number of attributes associated with a result_set handle, where the result_set handle is returned from odbc_exec( ).
Closes an ODBC data source opened with odbc_connect( ).
<!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>
Oracle is well-supported with PHP functions, and seven key functions are listed here. Example 4-14 shows these functions implemented in a modified version of Example 4-1. The functions require that Oracle 8 client libraries be installed and the functions use the Oracle 8 Call Interface (OCI8). Support for previous versions of Oracle is available through a separate function library we don't discuss here.
Oracle access is a six-step process. A connection is opened, and then a query is first prepared with OCIParse( ) and executed with OCIExecute( ). Then, each row is retrieved with OCIFetch( ) and individual attributes are retrieved from the row with OCIResult( ). Last, the connection is closed. Our treatment of Oracle functions is brief, and more detail can be found in the PHP manual.
The key functions are:
Establishes a connection to an Oracle DBMS. On success, the function returns a connection handle that can access databases through subsequent commands. Parameters are the same as those for mysql_connect( ).
Returns a query resource handle that can subsequently be executed, or returns false on error. The connection resource created with OCILogon( ) is passed as a parameter, along with an SQL_command. The function doesn't execute the query—OCIExecute( ) does that—but this function is required to set up the query for execution.
Runs the query set up with OCIParse( ), taking the return value of OCIParse( ) as the only parameter. Results are subsequently fetched with OCIFetch( ). Returns true on success and false on failure.
Buffers a row from the last OCIExecute( ) call specified with the query_handle returned from OCIParse( ). Returns true if a row is retrieved and false when no more rows are available. Attributes are fetched from this buffer with OCIResult( ).
Returns the number of attributes associated with the query specified in OCIParse( ).
Fetches the value of attribute_number from the current row retrieved with OCIFetch( ). Takes the return result of OCIParse( ) as the first parameter.
Closes an Oracle connection opened with OCILogon( ).
<!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 connections
$connection = OCILogon("fred","shhh", "winestore");
// (2) Setup the query on the winestore through the
// connection
$query = OCIParse($connection, "SELECT * FROM
wine");
// (3) Run the query
OCIExecute($query);
// (4) Output the results
while (OCIFetch($query))
{
// (5) Print out the attributes in this row
for($x=1;$x<=OCINumCols($query);$x++)
echo OCIResult($query,$x);
echo "\n";
}
// (6) Close the database connection
OCILogoff($connection);
?>
</pre>
</body>
</html>
PostgreSQL DBMSs are accessed in much the same way as MySQL and Microsoft SQL Server DBMSs. Again, there are many—often functionally overlapping—functions for connecting to, querying, and extracting results from a PostgreSQL DBMS.
The five key functions are listed here, and Example 4-15 shows these implemented in a modified version of Example 4-1.
Establishes a connection to a PostgreSQL DBMS. On success, the function returns a connection resource handle that can access databases through subsequent commands. It returns false on failure.
The parameters are similar to those of the mysql_connect( ) function, but the parameters are concatenated into a single string that usually includes the keywords host, dbname, user, and password. For example, to connect to localhost, use the winestore database, and log in as fred with password shhh, the format is:
$connection = pg_connect("host=localhost dbname=winestore
user=fred password=shhh");
Runs an SQL command through the connection created with pg_connect( ) (the database is selected with pg_connect( )). Returns a resource—a result handle used to fetch the result set—on success, and false on failure.
Fetches the result-set data, row by row, following a pg_exec( ) command using the result_set resource 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.
Returns the number of attributes in a result_set resource handle, where the result_set handle is returned from pg_exec( ).
Closes a PostgreSQL connection opened with pg_connect( ).
<!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 connections
$connection = pg_connect("host=localhost
user=fred password=shhh dbname=winestore");
// (2) Run the query on the winestore through the
// connection
$result = pg_exec($connection,"SELECT * FROM wine");
// (3) While there are still rows in the result set
while ($row = pg_fetch_row($result))
{
// (4) Print out each attribute in the row
for ($i=0; $i<pg_num_fields($result); $i++)
echo $row[$i] . " ";
// Print a carriage return to neaten the output
echo "\n";
}
// (5) Close the database connection
pg_close($connection);
?>
</pre>
</body>
</html>
| only for RuBoard - do not distribute or recompile | |