Category Archives: odbc

Execute SQL remotely on an IBM i via PHP’s PDO ODBC from a Windows or Linux

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

https://godzillai5.wordpress.com/2015/07/08/setting-a-default-sql-library-and-library-list-on-a-odbc-connection-for-a-ibmi-db2-connection-in-windows-and-linux/

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.

Here’s an example on how to run a SQL extenal stored procedure

 

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 sslodbc windows secure.png

Linux 

Setup stunnel

guide is here: 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

Setting a default SQL library and library list on a ODBC connection for a IBMi DB2 connection in Windows and Linux

UPDATE: In a JDBC connection using JTOpen 400, library lists are only used when the connection properties is set to naming=system;libraries=MYLIB1,MYLIB AND there’s no default “Schema=” or default schema in the Database URL (i.e. jdbc:as400:MYIBMI/DEFAULTSCHEMA). I’m unsure if this is the same way in ODBC.


ODBC allows you to set a Default SQL Schema or Library and a Library list. This allows you to point your application at different IBMi DB2 libraries.  This could be used so you can have a development, qa, and production libraries and have your application change what data it uses based on your odbc settings.  This also allows you to not need to fully qualify your table names such as SELECT * FROM MYLIB.MYTABLE would be come just SELECT * FROM MYTABLE .  They way this happens is  the default library will by tried first {DefaultLibrary}.MYTABLE and if it doesn’t find it in there it will try the libraries in the library list {library_list…}.MYTABLE [MYLIB1.MYTABLE, MYLIB2.MYTABLE …] .

Below is how to set this up in Windows and in Linux environments.  Make sure you’ve done the steps in this guide to get the odbc driver: https://godzillai5.wordpress.com/2016/09/02/execute-sql-remotely-on-an-ibm-i-via-phps-pdo-odbc-from-a-windows-linux-or-mac/ (mainly this guide http://www-01.ibm.com/support/docview.wss?uid=nas8N1010355)

Windows (ODBC Administration)

Its under the server tab of ODBC Administration tools in windows.  This tool can be found at

C:\Windows\System32\odbcad32.exe – 32 bit
C:\Windows\SysWOW64\odbcad32.exe – 64 bit

You then select your data-source name, configure the data-source, go to the server tab, enter your Default SQL Schema or Library, and the Library list for this connection.

odbc-default-library-and-library-list-ibmi-db2

Linux (unixODBC Manager)

edit the /etc/odbc.ini. 

1) add your default library to DefaultPkgLibrary

2) add your library list to DefaultLibraries 

[iSeriesDSN]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = iSeriesSystemName
UserID =
Password =
Naming = 0
DefaultLibraries = MYLIB,MYLIB2,MYLIB3
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = MYLIB
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

JDBC

Below is the JDBC connection string with system naming on and the library list to use.  Default schema can’t be specified or it won’t use the libraries.

jdbc:as400:MYIBMI/;naming=system;libraries=MYLIB1,MYLIB

Work-arounds

1. Change the library list in the job description (JOBD) for the ODBC connection. Do a WRKACTJOB and look for QZDASOINIT jobs handles connections and see what JOBD they are using and change it to include the libraries you need.
2. Call the Stored procedure QCMDEXC to run ADDLIBLE.

CALL QSYS . QCMDEXC (‘ADDLIBLE MYLIB’ ) ; 

This will add the library to the library list entries.

PHP: odbc_prepare weird issue with bind parameter on Linux to IBMi DB2 connection

I came across this weird issue using odbc_prepare to prepare a SQL statement with ? in place for the parameters. The query would odbc_prepare “successfully” and the odbc_execute would return “true”, but when i tried to fetch the row nothing came back. I eventually figured out that because my parameter was only 5 characters instead of 6 characters that was the reason the query wasn’t pulling back the records I was expecting.

PHP Code

$queryToPrepare = "SELECT * FROM MYLIB.TABLE WHERE DATE >= ?"
array_push($queryParameters,  str_pad($date, 6, " ", STR_PAD_LEFT));
$preparedQuery = odbc_prepare($this->connection_id, $queryToPrepare);
$success = odbc_execute($preparedQuery, $parameters);
if (odbc_fetch_row($result))
{
echo odbc_result($result, "MYFIELD");
}

iSeries Access 64 Bit Driver problems Segmentation Faults

Update: May 17 2016 – At common I found out that 64 bit issues should now all be resolved when using the latest PHP 5.6, the unixODBC manager 2.2.14+ and IBM i Access for Linux ODBC driver.  This was effecting PHP extension: PDO ODBC, and ODBC_* functions

PHP 5.6.x+
http://php.net/downloads.php

UnixODBC Manager
http://www.unixodbc.org/

IBM i Access ODBC Driver [for Linux] (ibm-iaccess-1.1.0.1-1.0.x86_64)
http://www-03.ibm.com/systems/power/software/i/access/linux.html

Kevin Adler’s Article from IBM
https://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-linux/

Segmentation Fault Problem

I’ve had a lot of trouble with using the iSeries Access 64 bit odbc driver for Linux (iSeriesAccess-5.4.0-1.6.x86_64.rpm) on my RHEL linux box to connect toa  V5R4 iSeries server.  I switched back to using the 32 bit driver and saying i’ll wait until they update the 64 bit driver to work.

I was going to the PHP page and finding just a blank screen returned to the browser, which made it a pain to debug.  I found out after looking at the apache error log file in /var/log/httpd/error_log  that there were segmentation faults with the following error:

[notice] child pid XXXXX exit signal Segmentation fault (11)

To try and debug the PHP to see what causing the problem i put die(‘here’) statements in my code until i found out where the interpretor wasn’t getting to because of the seg fault.   I found out on the return of the Zend Framework’s PDO ODBC Fetchall statement the program was causing the seg fault.  I also noticed that this only occured when i had a null field in the DB2 udb for iSeries database.  The field was a char datatype.

If you’re having this problem i’d recommend just installing the 32 bit driver(iSeriesAccess-5.4.0-1.2.i386.rpm).

Debugging

Here’s how i understand requests get called in case you want to try and debug the problem:

  1. Request for page comes in from Apache
  2. PHP runs and makes db calls through  a DSN
  3. All db calls go through unixODBC manager
  4. The db call then goes through the IBM Client Access driver you have specified and executes the sql on the iSeries.
  5. If the driver has a problem with a null field it would create a segmentation fault.

Here’s a link to someone’s explanation of the problems using the 64 bit driver:

https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14510046&#14510046

Need help understanding how to install the driver and setup all the linux files (/etc/odbc.ini /etc/odbcisnt.ini)

Here’s the link to the driver download

iSeries Access Linux Driver download

Remember to edit your
/etc/odbcinst.ini

[iSeries Access ODBC Driver]

Description             = iSeries Access for Linux ODBC Driver
Driver          = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup           = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
NOTE1           = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2           = the following Driver64/Setup64 keywords will provide that support.
Driver64                = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64         = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading               = 2
DontDLClose             = 1
UsageCount              = 1

and /etc/odbc.ini

[iSeriesDSN]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = iSeriesSystemName
UserID =
Password =
Naming = 0
DefaultLibraries = QGPL
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

Giving credit to IBM for this example file