You’ll first have to configure an ODBC connection “System DSN”/”System Data Source” via the “iSeries Access ODBC driver” or it might be call the IBM i Access driver which you can learn how to download and install here:
http://www-01.ibm.com/support/docview.wss?uid=nas8N1010355
Here’s some of the ways to configure the odbc connection
Once you have the ODBC connection setup you can use it in PHP and here’s a Simple Example on how to execute SQL remotely and safely on an IBM i via ODBC. Note you won’t want to dump the error info in production its there to alert you about errors.
<?php | |
$hostname = "MYIBMI"; | |
$user = "MYPROFILE"; | |
$password = "";//leave blank and prompt. (works in windows not sure if it will work in other situations) | |
$pdoConnection = new PDO("odbc:" . $hostname, $user, $password); | |
$sql="SELECT * FROM MYLIB.MYTABLE WHERE NAME=:NAME AND CITY=:CITY"; | |
$stmt = $pdoConnection->prepare($sql); | |
if (!$stmt) | |
{ | |
echo implode($this->_pdoConnection->errorInfo()); | |
} | |
else | |
{ | |
$stmt->bindValue(":NAME", "Chuck"); | |
$stmt->bindValue(":CITY", "Orlando"); | |
} | |
if ($stmt->execute()) | |
{ | |
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) | |
{ | |
var_dump($row).PHP_EOL; | |
} | |
} | |
else | |
{ | |
echo 'statement failed with '.$stmt->errorInfo(); | |
} |
Here’s an example on how to run a SQL extenal stored procedure
<? | |
//Create db connection | |
$hostname = "MYIBMI"; | |
$user = "MYPROFILE"; | |
$password = ""; | |
$dbconn = new PDO("odbc:" . $hostname, $user, $password); | |
//SQL to run stored proc | |
$sql = "call #LALLAN.getCusInfo(?, ?, ?, ?)"; | |
//Prepare SQL for execution | |
$stmt = $dbconn->prepare($sql); | |
//Create a test object for demo purposes | |
$Customer = new stdClass(); | |
$Customer->ID = 1; | |
$Customer->Data1 = ""; | |
$Customer->Data2 = ""; | |
$Customer->Data3 = 1; | |
//Bind object properties to our object | |
$stmt->bindParam(1, $Customer->ID, PDO::PARAM_INT); | |
$stmt->bindParam(2, $Customer->Data1, PDO::PARAM_INPUT_OUTPUT); | |
$stmt->bindParam(3, $Customer->Data2, PDO::PARAM_INPUT_OUTPUT); | |
$stmt->bindParam(4, $Customer->Data3, PDO::PARAM_INPUT_OUTPUT); | |
//Run it | |
$stmt->execute(); | |
//Dump our object to the screen to see what everything got set to. Or alternatively run xdebug or another debugger to view the $Customer object after execution | |
var_dump($Customer); |
Make sure to enable SSL to encrypt your SQL statements that are sent
Windows
Run C:\Windows\SysWOW64\odbcad32.exe and edit connection options and make sure ssl is selected or IBM i navigator is defaulted to ssl
Linux
Setup stunnel
guide is here: http://www.redbooks.ibm.com/redbooks/pdfs/sg246551.pdf … under 2.6 Encrypt the connection with Secure Sockets Layer
Other References:
Windows: http://www-03.ibm.com/systems/power/software/i/access/windows.html
Linux: http://www-03.ibm.com/systems/power/software/i/access/linux.html