Re: Query Fails In PHP db2_exec But Is Good in System I Navigator.

Rob,

Discovered two things. First, when I pasted your code into Zend Studio I
discovered a conflict in the quote on the SQL string. I changed the outer
quote to "" and that fixed that. But it still wouldn't run.

So I added some more function to tease out the details about the issue
like this immediately after the DB2_Exec():
if (!$sRes) {
echo db2_stmt_error(). ' - ' . db2_stmt_errormsg();
}

These functions will display the nature of the error and I assume this
might lead you to the source of the problem. Authority. Here is the error
I got:
42501 - Not authorized to object ZTEST in MPAVLAK type *FILE. SQLCODE=-551

I am assuming you created the table using IBM i Navigator. This created
the table under your user profile authority and, most likely, set the
authority for *PUBLIC to *EXCLUDE. As a result, the DB2_Connect string as
you coded could not find the tale as the profile used to resolve the
object is QTMHHTTP when using a zero length string in the DB2_Connect.
Either change the DB2_Connect to use a profile that has adequate authority
or give QTMHHTTP access to the object. I'd recommend the former over the
latter.

Hope this helps!

Mike Pavlak
Cell: (408)679-1011 Office: (708)233-5880

-----Original Message-----
From: WEB400 [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Sent: Friday, December 04, 2015 10:34 AM
To: Web Enabling the IBM i (AS/400 and iSeries)
Subject: [WEB400] Query Fails In PHP db2_exec But Is Good in System I
Navigator.

This is the code...

if ( $oDB = db2_pconnect( '', '', '', array( 'i5_naming' =>
DB2_I5_NAMING_OFF ) ) ) {
// Save Data And Retrieve Auto Incremented ID
$sQry = 'SELECT ID FROM FINAL TABLE( INSERT INTO MyLibrary.zTest (
METHOD, AGENT, IP ) VALUES ( 'GET', 'Mozilla/5.0 (Windows NT 10.0; WOW64;
rv:42.0) Gecko/20100101 Firefox/42.0', '76.187.52.97' ) ) ;' ;

$sRes = db2_exec( $oDB, $sQry ) ;
db2_close( $oDB ) ;
}

All I get in the error log is:

[04-Dec-2015 11:26:24 EST] PHP Warning: db2_exec(): Statement Execute
Failed in /Test.php on line 51


In SiN:

[Fri Dec 04 16:05:26 GMT 2015 ] Run All

SELECT ID FROM FINAL TABLE( INSERT INTO MyLibrary.zTest ( Method,

Agent, IP ) VALUES ( 'GET', 'Mozilla/5.0 (Windows NT 10.0; WOW64;
rv:42.0) Gecko/20100101 Firefox/42.0', '76.187.52.97' ) )

Statement ran successfully (141 ms)

The table was created from:

CREATE TABLE MyLibrary.zTest (
ID bigint GENERATED ALWAYS AS IDENTITY ( START WITH 1,
INCREMENT BY 1, NO CYCLE ),
When timestamp WITH DEFAULT CURRENT TIMESTAMP,
Method varchar(4),
Agent varchar(255),
IP varchar(15),
Request xml,
PRIMARY KEY ( ID )
) ;

I am ready for my beating....

Thanks,
rob

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus