StarQuest Technical Documents

Using StarSQL with PHP and PDO to Execute Stored Procedures

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

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 php-odbc to access a DB2 database and run database queries. However, if you want to execute DB2 stored procedures, the php-odbc extension is limited to calling stored procedures that take only IN parameters. To execute stored procedures that take IN and/or INOUT parameters and return OUTPUT parameter data, use the PDO extension instead of php-odbc.

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 provides sample PHP code that illustrates how to call a stored procedure using PDO and a StarSQL data source. If you are using PHP with StarSQL for the first time, review the technical document Using StarSQL with PHP and php-odbc.

System Requirements:

1. Verify that you have PHP 5.1 (or 5.0 with the PDO extension) installed using the php -v command.
2. Configure php to use the PDO extension.

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

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

Solution

The following sample application illustrates how to call a stored procedure that takes three parameters: one IN, one INOUT, and one OUT.

To use this sample, modify the connection string to use a StarSQL ODBC DSN and DB2 user/password credentials valid in your environment. In addition, modify the stored procedure name and parameter attributes, as needed. This application is intended to be run as a standalone PHP application and can be executed using the php <filename> command.

<?php

// Connect to the database
try{
$dbh = new PDO("odbc:MYSTARSQLDSN", 'DB2USER', 'SECRET');
}catch (PDOException $e) {
print "Error!: " . $e->getMessage();
die();
}

// Set parameter values
$inval = 'INPUT VALUE';
$inoutval = 'INOUT VALUE';
$outval = NULL;

// Prepare stored procedure call with three parameters
$sth = $dbh->prepare('CALL MYPROC(?, ?, ?)');

// Bind parameter 1 as IN parameter
// Be sure *not* to set a length to indicate it's an IN parameter
$sth->bindParam(1, $inval, PDO::PARAM_STR);

// Bind parameter 2 as INOUT parameter
$sth->bindParam(2, $inoutval, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 20);

// Bind parameter 3 as OUT parameter
// Be sure to explicitly set a length to indicate it's an OUTPUT parameter
$sth->bindParam(3, $outval, PDO::PARAM_INT, 20);

// Call the stored procedure
print "Executing stored procedure...\n";
$res = $sth->execute();

if ($res){
// Print inout and output parameter data
print "Success! \n";
print "The inout parameter value is: $inoutval. \n";
print "The output parameter value is: $outval.";
// Close connection
$dbh = null;
}else{
$arr = $sth->errorInfo();
print "Execute failed with SQLState: " . $arr[0] . "\n";
print "Error message text: " . $arr[2];
die();
}

?>

Troubleshooting

The sample application demonstrates 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