Trouble with ZfcUser and db2_prepare()

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

Trouble with ZfcUser and db2_prepare()

Postby eric6141 on Mon Jul 06, 2015 4:55 pm

Working on implementing ZfcUser, but having trouble with what may be a simple error to resolve. The function db2_prepare() continues to come back false. When I look at the SQL statement that is generated I can see why it fails.

The error that comes back is "SQLSTATE=42601 SQLCODE=-104 Token . was not valid. Valid tokens: + - AS <IDENTIFIER>." The statement is 'SELECT user.* FROM user WHERE email = ?'

Is there a way to remove the "user." so all that's left is the wildcard * from the select statement? Is this handled by another special option that needs to be set for the DB2 db adapter?
eric6141
 
Posts: 5
Joined: Mon Jul 06, 2015 4:42 pm

Re: Trouble with ZfcUser and db2_prepare()

Postby mark397 on Mon Jul 06, 2015 8:56 pm

In this simple of a statement you should be able to remove the 'user' table qualifier. I have had luck with aliased tables, but have not tested using the table name itself as the prefix. This may be a different issue, though, if you don't have the I5_NAMING constant on. On an IBM i that doesn't have it on you'll need to prefix the table name with the schema name, e.g. 'mylibrary.user' or 'mylibrary/user'. The slash only works with I5_NAMING, but the period works regardless, so I tend to use that. What we're migrating to is auto-resolution using library lists, which is what it seems you're attempting to do, but again that only works with I5_NAMING.
mark397
 
Posts: 88
Joined: Thu May 30, 2013 6:12 pm

Re: Trouble with ZfcUser and db2_prepare()

Postby eric6141 on Mon Jul 06, 2015 9:35 pm

Thanks for that suggestion, and I'll check out if aliasing the information helps out any.

If it helps to know i5 naming is set on. Below is how it is currently setup. When creating tables we use the RUNSQLSTM command and set the naming parameter to *SQL (instead of *SYS). I also tried creating this table with *SYS and having the i5 naming set on and off, but still get the same error.

Hopefully the example below helps someone else dealing with a DB2 on i connection issue. Another helpful setting was the line for platform_options when trying to implement ZfcUser.

'db' => array(
'driver' => 'IbmDb2',
'db' => '*LOCAL',
'platform_options' => array('quote_identifiers' => false),
'driver_options'=> array('i5_commit' =>DB2_I5_TXN_READ_UNCOMMITTED,
'autocommit'=>DB2_AUTOCOMMIT_ON,
'i5_naming'=>DB2_I5_NAMING_ON,
'i5_lib'=>'mylib',
'i5_libl'=>'mylib',
),
),
eric6141
 
Posts: 5
Joined: Mon Jul 06, 2015 4:42 pm


Return to PHP Questions / Hints

Who is online

Users browsing this forum: No registered users and 2 guests