StarQuest Technical Documents

Using StarSQL with PHP and php-odbc

Last Update: 29 September 2009
Product: StarSQL
Version: 5.5x (Windows), 5.51 (UNIX) or later
Article ID: SQV00SQ014

Abstract

PHP Hypertext Preprocessor is an open source, server-side scripting language that allows programmers to create Web pages with dynamic content that can interact with databases. StarSQL can be used with PHP and the PDO extension to access a DB2 database.

The PHP Data Objects (PDO) extension defines an interface for accessing databases in PHP. PDO ships with PHP 5.1 and is available as a PECL extension for PHP 5.0. PDO requires the new OO features in the core of PHP 5 and thus it will not run with earlier versions of PHP.

This document explains how to set up and use StarSQL and unixODBC with Apache and PHP on UNIX. The same concepts should also apply to using PHP on Windows. This document also provides instructions on how to create and run a stand-alone PHP application and a PHP Web server application.

The following discussion assumes that StarSQL has already been installed and ODBC system data sources have been configured. Run all UNIX commands as root user.

System Requirements:

  1. On Windows, verify that you have PHP 5.1 (or 5.0 with the PDO extension) installed using the php -v command. On UNIX, use the command php -m | grep PDO_ODBC to determine if the PDO module is already installed.
  2. Configure php to use the PDO extension.

    3. Windows users: add extension=php_pdo.dll and extension=php_pdo_odbc.dll to the php.ini file.

    UNIX users: add extension=pdo.so to the php.ini file and configure PDO according to the PHP:PDO manual.

Considerations:

  • The 32-bit version of StarSQL should only be used with the 32-bit version of PHP and PDO. Use StarSQL (64-bit) with the 64-bit versions of PHP and PDO.
  • If using the 64-bit version of PHP 5.3.1 and PDO on UNIX/Linux, contact StarQuest Customer Support to obtain a special version of StarSQL for UNIX.

Solution

The following sections demonstrate how to access a StarSQL DSN from a php application and configure StarSQL to work with an Apache HTTP server.

Create and Execute a Standalone PHP Application

The sample program will fetch the contents of a table and display the data in the first two columns.

  1. Copy the code provided below and paste it into a text editor.

<?php
try{

// Connect to the database using a StarSQL ODBC DSN
$dbh = new PDO("odbc:mydsn", 'myuser', 'mypassword');

// Set SQL query
$sql = "SELECT * FROM MYTABLE";

// Run query and display results
foreach ($dbh->query($sql) as $row) {
// Display the data for the first two columns
print $row[0] . ", " . $row[1] . "\n";
}
$dbh = null;
}catch (PDOException $e) {
print "Error!: " . $e->getMessage();
die();
}
?>

  1. Modify the code that you pasted into the text editor, replacing mydsn, myuser, and mypassword in the connection string to values appropriate in your environment. In addition, change the table name from MYTABLE to the name of a valid table in your database.
  2. Save the file as test.php.
  3. Execute the application using the following command:

$ php test.php

Configure the Apache HTTP Server to Use StarSQL

The procedure outlined below was tested with the Apache2 HTTP Server running on Linux.

The apache environment must be configured with the location of the unixODBC Driver Manager to use with StarSQL, whether it be an existing, supported unixODBC installation or the one included with StarSQL.

  1. Log on as root user.
  2. Locate the apache configuration file (e.g., /usr/lib/apache2/build/envvars on AIX, /etc/sysconfig/apache2 on SUSE, /etc/apache2/envvars on Ubuntu). Refer to the apache2 documentation for your platform if necessary.
  3. Set the library path environment variable (LD_LIBRARY_PATH, LIBPATH, etc.) in the apache configuration file. For example:

    32 bit: export LD_LIBRARY_PATH=/usr/share/starsql/odbc/lib

    64 bit: export LD_LIBRARY_PATH=/usr/share/starsql64/odbc/lib

  4. Restart the apache server (e.g., /etc/init.d/apache2 restart).

Create and Execute a PHP Web Application

The following short program fetches the contents of a table and displays the data in the first two columns.

  1. Copy the code provided below and paste it into a text editor.

<?php

putenv("HOME=/var/www");

try{

# Connect to the database using a StarSQL ODBC DSN
$dbh = new PDO('odbc:mydsn', 'myuser', 'mypassword');

# Set SQL query
$sql = "SELECT * FROM MYTABLE";

print '<table border="1">';

# Run query and display results
foreach ($dbh->query($sql) as $row) {
# Display the data for the first two columns
print"<tr><td>$row[0]</td><td> ., . $row[1] . </td></tr>\n";
}
print "</table>\n";

# Close the connection
$dbh = null;

}catch (PDOException $e) {
print "Error!: " . $e->getMessage();
die();
}

?>

  1. Modify the code that you pasted into the text editor, replacing mydsn, myuser, and mypassword in the connection string to values appropriate for your environment. In addition, change the table name from MYTABLE to a name of a valid table in your database.
  2. Save the file as webtest.php.
  3. Copy or move the webtest.php file to the html directory (e.g., /var/www/ or /srv/www/htdocs).
  4. Run the application by pointing a Web browser to the URL: http://mywwwserver/webtest.php.

Troubleshooting

For errors related to the Web server, examine the Web server error log (e.g., /var/log/httpd/error_log).

The sample applications demonstrate how to use error handling to return connection and statement errors. If the error message text alone does not provide enough information to resolve the error condition, an ODBC trace log may help. Enable ODBC tracing by editing /usr/local/etc/odbcinst.ini (on UNIX/Linux) or using the Windows ODBC tracing tool. After reproducing the problem, examine the ODBC trace log.

References

The Official PHP Homepage
http://www.php.net

The Official PHP Data Objects Manual
http://www.php.net/pdo

Enabling ODBC support in PHP under Apache
http://www.easysoft.com/developer/languages/php/apache_odbc.html

PX : the PHP code exchange
http://px.sklar.com/

Tutorials & examples
http://www.devshed.com/c/b/PHP/

Forums and articles
http://www.phpbuilder.com

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.