Accessing DB2 from PHP

The place for general PHP questions and hints for PHP on IBM i

Accessing DB2 from PHP

Postby bradliberty895 on Fri May 25, 2012 5:26 am

I would like to connect to DB2 on i from PHP. I have done this regularly when the PHP environment was running on my AS400. But now I have a situation where the PHP environment is on a totally different (Linux) web server. And I want to connect to DB2 on AS400. Can I somehow copy the db2_connect functions from my PHP environment on i and bring them to my Linux server?
bradliberty895
 
Posts: 2
Joined: Fri May 25, 2012 5:23 am

Re: Accessing DB2 from PHP

Postby aseiden on Fri May 25, 2012 7:48 am

Brad,

You can do it if you purchase and install the "DB2 Connect" product from IBM.

For more information, check out pages 58-59 of my presentation ""DB2 and PHP Best Practices on IBM i"
linked from this page: http://www.alanseiden.com/presentations/

Alan
aseiden
 
Posts: 831
Joined: Thu Apr 09, 2009 5:45 pm

Re: Accessing DB2 from PHP

Postby aseiden on Fri May 25, 2012 7:56 am

You can also use ODBC. It's not as efficient as ibm_db2 (db2_connect) but it might be OK depending on your needs.
aseiden
 
Posts: 831
Joined: Thu Apr 09, 2009 5:45 pm

Re: Accessing DB2 from PHP

Postby arielvtpma on Fri Aug 24, 2012 6:13 am

bradliberty895 wrote:I would like to connect to DB2 on i from PHP. I have done this regularly when the PHP environment was running on my AS400. But now I have a situation where the PHP environment is on a totally different (Linux) web server. And I want to connect to DB2 on AS400. Can I somehow copy the db2_connect functions from my PHP environment on i and bring them to my Linux server?


hello,

I have the following in application.ini:

resources.db.adapter="Db2"
resources.db.params.hostname="as400"
resources.db.params.dbname="as400db"
resources.db.params.os="i5"
resources.db.params.username="user"
resources.db.params.password="pass"
resources.db.params.driver_options.i5_naming = DB2_I5_NAMING_ON
resources.db.params.driver_options.autocommit = DB2_AUTOCOMMIT_ON
resources.db.params.driver_options.i5_libl = "lib1 lib2"

only get "internal server error"

ibm i
v7r1
zend server installed on ibm i

thanks in advance
arielvtpma
 
Posts: 4
Joined: Fri Aug 24, 2012 1:32 am

Re: Accessing DB2 from PHP

Postby erich_hieden on Fri Aug 24, 2012 10:11 am

"internal server error" could be anything, from a missing semicolon to a misconfigured db-connection. Please take a look at php.log either via ZendServer Admin GUI or at /usr/local/zendsvr/var/log.
erich_hieden
 
Posts: 377
Joined: Tue Jul 07, 2009 9:01 am

Re: Accessing DB2 from PHP

Postby arielvtpma on Fri Aug 24, 2012 10:27 pm

erich_hieden wrote:"internal server error" could be anything, from a missing semicolon to a misconfigured db-connection. Please take a look at php.log either via ZendServer Admin GUI or at /usr/local/zendsvr/var/log.


Check my spool on power i and i got a severe error

" Mensaje . . . . : Nombre de objeto calificado SYSCOLUMNS no válido. " (qualified object name SYSCOLUMNS not valid)
the full description is about sintax on schema/object-name.

I have resources.db.params.driver_options.i5_naming = DB2_I5_NAMING_ON in application.ini

don't know how to solve this problem
arielvtpma
 
Posts: 4
Joined: Fri Aug 24, 2012 1:32 am

Re: Accessing DB2 from PHP

Postby erich_hieden on Mon Aug 27, 2012 8:12 am

This either means that you're using the wrong syntax (lib/table vs. lib.table), which seems ok in your case, or that the table simply doesn't exist in the requested library. Try running the exact same SQL query directly in a 5250 session using STRSQL.
erich_hieden
 
Posts: 377
Joined: Tue Jul 07, 2009 9:01 am

Re: Accessing DB2 from PHP

Postby arielvtpma on Mon Aug 27, 2012 8:20 pm

erich_hieden wrote:This either means that you're using the wrong syntax (lib/table vs. lib.table), which seems ok in your case, or that the table simply doesn't exist in the requested library. Try running the exact same SQL query directly in a 5250 session using STRSQL.


I accessed the file using STRSQL and works fine when used lib/table, but it failed if I use lib.table(gave same error message)
This means that ZEND Server is not accepting resources.db.params.driver_options.i5_naming = DB2_I5_NAMING_ON

do you have a template for application.ini to access Zend Server on iSeries?
or should i use the i5 toolkit?
arielvtpma
 
Posts: 4
Joined: Fri Aug 24, 2012 1:32 am

Re: Accessing DB2 from PHP

Postby erich_hieden on Tue Aug 28, 2012 8:48 am

Hmmm.... normally it should work.

Have you checked the version of your db2 extension? You need at least 1.5.1 for i5_naming and 1.8.0 for i5_libl (see db2_connect)

If these requirements are met, try using db2_connect and the likes as standalones, aka without ZendFramework. Just write a small test script to check if i5_naming is used or not.
erich_hieden
 
Posts: 377
Joined: Tue Jul 07, 2009 9:01 am

Re: Accessing DB2 from PHP

Postby arielvtpma on Tue Aug 28, 2012 5:30 pm

erich_hieden wrote:Hmmm.... normally it should work.

Have you checked the version of your db2 extension? You need at least 1.5.1 for i5_naming and 1.8.0 for i5_libl (see db2_connect)

If these requirements are met, try using db2_connect and the likes as standalones, aka without ZendFramework. Just write a small test script to check if i5_naming is used or not.


the following code examples work fine
-------------------------------------------------------------------------------------------------
//"i5_naming" => DB2_I5_NAMING_ON

$config = array(
'dbname' => '*LOCAL',
'username' => 'USER',
'password' => 'PASS',
'os'=>'i5',
'driver_options'=> array("i5_commit" =>DB2_I5_TXN_READ_UNCOMMITTED,
"autocommit"=>DB2_AUTOCOMMIT_OFF,
"i5_libl"=>'PROBPDATA PROBPLIB',
"i5_naming" => DB2_I5_NAMING_ON)
);


$conn = new Zend_Db_Adapter_Db2($config);

/* Construct the SQL statement */
$sql = "INSERT INTO PROBPDATA/DUMMY VALUES('AA', 'BB', 'CC')";

/* Prepare, bind and execute the DB2 SQL statement */
$stmt = $conn->query($sql);


//"i5_naming" => DB2_I5_NAMING_OFF

$config = array(
'dbname' => '*LOCAL',
'username' => 'USER',
'password' => 'PASS',
'os'=>'i5',
'driver_options'=> array("i5_commit" =>DB2_I5_TXN_READ_UNCOMMITTED,
"autocommit"=>DB2_AUTOCOMMIT_OFF,
"i5_lib"=>'PROBPDATA',
"i5_naming" => DB2_I5_NAMING_OFF)
);


$conn = new Zend_Db_Adapter_Db2($config);

/* Construct the SQL statement */
$sql = "INSERT INTO PROBPDATA.DUMMY VALUES('AA', 'BB', 'CC')";

/* Prepare, bind and execute the DB2 SQL statement */
$stmt = $conn->query($sql);
-----------------------------------------------------------------------------------------------------------------------------
The extension version is 1.9.0, so I think that the problem is application.ini, it is not passing the parameters correctly.
Could it be the zend server version I am running?
Where can I check the Zend Server version ?

By the way I am thankfull for the help received
arielvtpma
 
Posts: 4
Joined: Fri Aug 24, 2012 1:32 am

Next

Return to PHP Questions / Hints

Who is online

Users browsing this forum: No registered users and 2 guests