StarQuest Technical Documents

Using StarSQL with PHP and php-odbc or PDO-ODBC

Last Update: 30 January 2015
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 either the php-odbc extension or the PDO-ODBC extension to access a DB2 database.

The PHP Data Objects (PDO) extension defines a high level 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. It can be used to call stored procedures that take IN and/or INOUT parameters and return OUTPUT parameter data; the php-odbc extension is limited to calling stored procedures that take only IN parameters.

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

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. Use the command php -m | grep odbc to determine if the php-odbc module is already installed.
2. Configure php to use the PDO extension.

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. Use StarSQL (64-bit) with the 64-bit versions of PHP.
• If you are using the 64-bit version of PHP with PDO-ODBC on UNIX/Linux, be sure your version of PHP includes the following 64-bit fixes related to PDO and 64-bit:

  Bug #50444 PDO-ODBC typedef problems changes for 64-bit (fixed in php 5.5.14 and 5.6.0 and later)

  Bug #50445 PDO-ODBC stored procedure call from Solaris 64-bit causes seg fault (fixed in php 5.3.2 and later)

• For additional PDO information, see the StarQuest tech note SQV00SQ053 Using StarSQL with PHP and PDO to Execute Stored Procedures.

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 (PDO-ODBC) 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();
}
?>

2. 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.
3. Save the file as test.php.
4. Execute the application using the following command:

$ php test.php

Create and Execute a Standalone PHP (php-odbc) Application

The following sample program performs the same function using php-odbc rather than PDO-ODBC. Create and execute the PHP program as described above.

<?php

$connect = odbc_connect("odbc:mydsn", 'myuser', 'mypassword');

# query the users table for name and surname
$query = "SELECT * FROM MYTABLE";

# perform the query
$result = odbc_exec($connect, $query);

# fetch the data from the database
 while(odbc_fetch_row($result)){
   $column1 = odbc_result($result, 1);
   $column2 = odbc_result($result, 2);
    print("$column1 $column2\n");
}

# close the connection
odbc_close($connect); 
?>

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 (PDO-ODBC)

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();
}
?>

2. 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.
3. Save the file as webtest.php.
4. Copy or move the webtest.php file to the html directory (e.g., /var/www/ or /srv/www/htdocs).
5. 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).

Traces: 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.

Using DSN-less connections: If your PHP application is having problems locating the ODBC data source (e.g. when using apache+php), consider using DSN-less connections instead. See the StarQuest Tech Note SQV00SQ036 Using a DSN-less Connection with StarSQL.

SELinux policy: The following error may be the result of SELinux (Security-Enhanced Linux) blocking apache+php from making outgoing network connections. SELinux is often enabled by default on Red Hat and related distributions such as Fedora, CentOS, and Scientific Linux.

[unixODBC][StarSQL][StarSQL CLI Driver]Communications link failure., SQL state 08S01 in SQLConnect

To verify whether SELinux is the source of connectivity problems, temporarily configure permissive mode by changing the contents of /selinux/enforce from 1 to 0:

# echo 0 > /selinux/enforce

To configure SELinux to allow apache+php to make outgoing network connections:

# /usr/sbin/setsebool -P httpd_can_network_connect true

References

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

The Official PHP ODBC Manual
http://www.php.net/odbc

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