PHP is a server-side scripting language that has proven its popularity and success through explosive growth. In the last five years, the number of domains using PHP to generate Web pages has grown from under two million to over 20 million (http://www.php.net/usage.php). PHP long ago moved from a hobbyist tool for creating dynamic Web pages to a commercially viable option for large Web sites and enterprise-level applications including Google, Yahoo, and SugarCRM.
Most of the PHP applications in existence today rely on a relational database to store the information that is used to create dynamic Web pages. Connecting to these databases in a manner that can handle a high volume of transactions is crucial to ensure availability and performance of Web-based applications. DataDirect Connect® for ODBC drivers provide the Web developer access to all of the major databases, which gives the maximum amount of flexibility for applications. DataDirect Connect for ODBC drivers provide connectivity from UNIX/Linux platforms to ODBC drivers to offer wire protocol solutions that do not require database vendor client libraries such as Oracle Net or DB2 Connect. In addition to eliminating the installation of the database client libraries and reducing the associated maintenance tasks, wire protocol drivers also offer performance advantages by reducing the network communication layers between the application and database.
This article explains how to use DataDirect Connect for ODBC drivers with PHP to maximize a PHP deployment on either UNIX/Linux or Windows. Benefits such as improved performance, reduced server resources, and the elimination of database client libraries are why DataDirect Connect products are in use by more than 270 commercial software vendors and in thousands of corporate applications worldwide.
The PHP function odbc_connect() is used to establish a connection to a database. This function accepts three required and one optional argument:
resource odbc_connect (string dsn, string
user
, string
password
[,
int
cursor_type])
The dsn argument must be a Data Source Name. The user and password arguments identify the user. Finally, the optional cursor_type argument specifies, if necessary, which cursor method to use. For more information about this argument and PHP ODBC functions, consult the PHP documentation.
On Windows, the ODBC Administrator, available through the Control Panel, provides a GUI for creating a data source that stores connection option values. On UNIX and Linux, you store data sources in a file called odbc.ini. Instructions for defining a data source are found in the DataDirect Connect and Connect XE for ODBC User's Guide.
If, for example, you created a data source named DataDirectMSSQL for connecting to a Microsoft SQL Server database, and had a user name of sa and a password admin, you would use the following syntax in PHP:
$conn=odbc_connect(
"DataDirectMSSQL"
,
"sa"
,
"admin"
);
The $conn variable is a resource handle used to refer to this connection in subsequent code.
The following examples demonstrate making a connection and executing SQL statements. Recall that in the previous examples, the $conn variable stores the information for connecting to a database.
This code creates a new EMPLOYEE table and inserts a record:
$stmt=
"CREATE TABLE EMPLOYEE (LAST_NAME varchar(20),
FIRST_NAME varchar(20))"
;
$resultset=odbc_exec($conn,$stmt);
$stmt=
"INSERT INTO EMPLOYEE VALUES('Griffin','Peter')"
;
$resultset=odbc_exec($conn,$stmt);
This code executes a query and displays the results as an HTML table:
$resultset=odbc_exec($conn,
"SELECT * FROM EMPLOYEE"
);
odbc_result_all($resultset,
"border=1"
);
To maximize performance, PHP supports keeping the database connection open after a script has completed. Subsequent calls with identical connection parameters can use an existing connection, which results in faster execution time and reduced system use. The only change necessary in your code is to call odbc_pconnect instead of odbc_connect. Note that this has an effect only when PHP is running as a server module and not a cgi process.
You are encouraged to use connection pooling for optimal performance.
A common PHP configuration in Windows environments uses IIS 5.1, PHP 5.1, and Microsoft SQL Server 2005. The following table lists sources for acquiring the necessary software:
PHP 5.1 |
|
IIS 5.1 |
Included in Windows distributions |
Microsoft SQL Server 2005 |
|
DataDirect ConnectforODBC |
Install the DataDirect Connect for ODBC drivers. Fifteen-day evaluation copies are available at http://www.progress.com/products/datadirect-connect/odbc-drivers/odbc-developer-center/odbc-product-documentation.
Follow the instructions in the DataDirect Connect and Connect XE for ODBC User's Guide for configuring a data source through the ODBC Administrator.
Install IIS 5.1 and Microsoft SQL Server 2005 on your system. Consult www.microsoft.com for IIS and Microsoft SQL Server 2005 installation instructions. No modifications are necessary to work with PHP and DataDirect Connect for ODBC.
Install PHP 5.1 on your system. When you install PHP 5.1 with IIS, we strongly recommend that you use the ISAPI module method described at http://php.net/manual/en/install.windows.php. After PHP 5.1 has been installed and configured, the next step is to verify that ODBC support is included by examining the output of a phpinfo() function inside of a PHP script:
<?php phpinfo(); ?>
If ODBC support is included, the output of the phpinfo() function will include an ODBC section with the following settings:
ODBC Support |
Enabled |
Active Persistent Links |
0 |
Active Links |
0 |
ODBC Library |
Win32 |
Note that ODBC support is included by default in the binary distributions of PHP.
A common PHP configuration in UNIX/Linux environments uses PHP 5.1 running on Red Hat Linux Enterprise 4 with Apache 2.2. The following table lists sources for acquiring the necessary software:
PHP 5.1 |
|
Red Hat Enterprise 4 |
|
Apache 2.2 |
|
DataDirect Connect for ODBC |
Install the DataDirect Connect for ODBC drivers. Fifteen-day evaluation copies are available at http://web.datadirect.com/techres/odbc.htmhttp://www.progress.com/products/datadirect-connect/odbc-drivers/odbc-developer-center/odbc-product-documentation.
Follow the instructions in the DataDirect Connect and Connect XE for ODBC User's Guide for setting environment variables and configuring a data source through the odbc.ini file.
Instructions for building PHP 5.1 as an Apache 2 shared module can be found at http://www.php.net/manual/en/install.unix.apache2.php. These instructions along with the following steps will guide you through the build process.
Set the environment variable ODBC_HOME to point to your installation of the DataDirect Connect for ODBC drivers, for example:
ODBC_HOME=/opt/DataDirect/ODBC; export ODBC_HOME
ODBCINST=/opt/odbc32v52/odbcinst.ini;export ODBCINST
In the $ODBC_HOME/include directory, create a file named odbc.h with the following contents:
#
include
<sql.h>
#
include
<sqlext.h>
#
include
<odbcinst.h>
Set the following environment variables to include DataDirect header (.h) files:
CPPFLAGS=
"-I$ODBC_HOME/include"
; export CPPFLAGS
Set the following environment variables to link DataDirect libraries:
CUSTOM_ODBC_LIBS=
"-L$ODBC_HOME/lib -lodbc -lodbcinst"
;
export CUSTOM_ODBC_LIBS
Add the following directive to the "configure your PHP" step found in the PHP installation instructions:
./configure --with-apxs2=/home/apache2/bin/apxs
--prefix=/home/php5.1 --with-custom-odbc=
$ODBC_HOME
Replace /home/ with the appropriate directory for your setup.
Continue with the PHP installation instructions for the make command until you reach the apachectl start command. Before starting Apache, add the following environment variables to the envvars file found in the bin directory of Apache. For example:
ODBCINI=
"$ODBC_HOME/odbc.ini"
ODBCINST=
"$ODBCINST"
An alternative is to specify the environment variables in each PHP script, such as:
<?php
putenv(
"ODBCINI=/opt/DataDirect/odbc/odbc.ini"
);
putenv(
"ODBCINST=/opt/DataDirect/odbc/odbcinst.ini "
);
//the rest of the connection code...
Consult the Apache and PHP documentation for more information on setting environment variables.
After starting your Apache server, you can verify your PHP installation by running a script with the phpinfo() function such as:
<?php phpinfo(); ?>
If ODBC support is included, the output of the phpinfo() function will include an ODBC section with the following settings:
ODBC Support |
Enabled |
Active Persistent Links |
0 |
Active Links |
0 |
ODBC Library |
custom |
ODBC_INCLUDE |
-$ODBC_HOME/include |
ODBC_LFLAGS |
-$ODBC_HOME/lib |
ODBC_LIBS |
-L$ODBC_HOME/lib -lodbc –lodbcins |
PHP is a robust scripting language useful for everything from small projects to enterprise application development. By using DataDirect Connect for ODBC drivers, you can easily write portable PHP code for all major operating systems and platforms. DataDirect Connect for ODBC offers both performance and scalability benefits through the use of its unique wire protocol architecture. Wire protocol architecture not only improves performance and scalability, but removes the need for vendor client libraries, which results in easier deployment and maintenance of your applications.
DataDirect Connect for ODBC drivers are the most widely used third-party ODBC drivers in the software market today and are backed by an award-winning technical support team. For more information, please see https://www.progress.com/odbc.