CLI0005W from query with join in PHP script

[Larry Menard]

Folks,

I have a PHP script that does:

$statement = 'SELECT tbl1.col1, tbl2.col2 FROM tbl1, tbl2';
$result = odbc_prepare($dbconn, $statement);

It succeeds, but always generates warning:

PHP Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in C:\temp\cli0005w.php on line 28

Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in C:\temp\cli0005w.php on line 28

I think I understand what the error means. When I use the Microsoft ODBC driver it says:

Warning: odbc_result() [function.odbc-result]: SQL error: [Microsoft][ODBC Cursor Library] Positioned request cannot be performed because result set was generated by a join condition, SQL state SL002 in SQLGetData in C:\My Server\gallery2\test-non-adodb.php on line 83

So I presume the CLI0005W means CLI is invoking some extra hoops in order to execute the statement, for which I am grateful, but I don't want PHP to report it.

I've tried modifying the error_reporting() in the PHP script, but can't seem to get rid of the warning.

Is there any way to suppress (or prevent) these warnings at either level, DB2 or PHP?

Thanks.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

[Knut Stolze]

Larry Menard wrote:

Folks,

I have a PHP script that does:

$statement = 'SELECT tbl1.col1, tbl2.col2 FROM tbl1, tbl2';
$result = odbc_prepare($dbconn, $statement);

It succeeds, but always generates warning:

PHP Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W
Option value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in
C:\temp\cli0005w.php on line 28

Warning: odbc_prepare(): SQL error: [IBM][CLI Driver] CLI0005W Option
value changed. SQLSTATE=01S02, SQL state 01S02 in SQLPrepare in
C:\temp\cli0005w.php on line 28

I think I understand what the error means. When I use the Microsoft
ODBC driver it says:

Warning: odbc_result() [function.odbc-result]: SQL error:
[Microsoft][ODBC Cursor Library] Positioned request cannot be performed
because result set was generated by a join condition, SQL state SL002 in
SQLGetData in C:\My Server\gallery2\test-non-adodb.php on line 83

So I presume the CLI0005W means CLI is invoking some extra hoops in
order to execute the statement, for which I am grateful, but I don't
want PHP to report it.

I've tried modifying the error_reporting() in the PHP script, but can't
seem to get rid of the warning.

Is there any way to suppress (or prevent) these warnings at either
level, DB2 or PHP?

If you don't add an explicit FOR READ ONLY at the end of the query, PHP
automatically adds a FOR UPDATE. So it tries to give you an updatable
cursor on a joined table, and that cannot work. Add the FOR READ ONLY and
you should by fine. I found that adding this clause wherever possible is a
good idea when PHP is involved.

Oh, and if you use PHP with BLOBs, you might hit quite a few problems...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Dan Scott]

The Unified ODBC extension for PHP uses scrollable cursors for every
query in DB2. Worse, it uses a kind of scrollable cursor that DB2
doesn't support, so the client and server go back and forth negotiating
a supported scrollable cursor level _for every row you request_. Which
means that retrieving data from a remote server is crazy slow.

All of this, along with workarounds for the problem, is well-documented
here:
http://www-128.ibm.com/developerwork...ott/index.html

I see you're using ADODB; we really need to get the ibm_db2 extension
for PHP (http://php.net/ibm_db2) supported in ADODB, because until
that's available anybody using ADODB to connect to DB2 through Unified
ODBC is going to think that the database is the bottleneck. The ibm_db2
extension does things right (including BLOB support, defaulting to
forward-only cursors but enabling you to use scrollable cursors on a
statement by statement basis, and support for OUT/INOUT parameters in
stored procedures).

Dan

[Larry Menard]

Thanks, Knut & Dan.

That was the problem, thanks. I had forgotten that particular nastiness,
and didn't realize this was a case of it.

My simple test described below was not using ADOdb, but Dan knows that
the original application (from which I rendered the testcase) is. On one
hand I await support for ibm-db2 in ADOdb anxiously, but on the other hand I
know that no change is ever without its new problems. ;-)

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

The Unified ODBC extension for PHP uses scrollable cursors for every
query in DB2. Worse, it uses a kind of scrollable cursor that DB2
doesn't support, so the client and server go back and forth negotiating
a supported scrollable cursor level _for every row you request_. Which
means that retrieving data from a remote server is crazy slow.

All of this, along with workarounds for the problem, is well-documented
here:
http://www-128.ibm.com/developerwork...ott/index.html

I see you're using ADODB; we really need to get the ibm_db2 extension
for PHP (http://php.net/ibm_db2) supported in ADODB, because until
that's available anybody using ADODB to connect to DB2 through Unified
ODBC is going to think that the database is the bottleneck. The ibm_db2
extension does things right (including BLOB support, defaulting to
forward-only cursors but enabling you to use scrollable cursors on a
statement by statement basis, and support for OUT/INOUT parameters in
stored procedures).

Dan

[Larry Menard]

Turns out I'm still getting the CLI0005W from a lot of queries. One
example is a query that does a select from a LONG VARCHAR column.

Is this the same problem Knut mentioned regarding BLOBs? In Clara Liu's
article she mentions LOBs getting CLI0005W, but her solution is to use FOR
READ ONLY. I'm now using that clause, and it isn't helping.

Any other suggestions?

Thanks.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Dan Scott" <de*****@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

The Unified ODBC extension for PHP uses scrollable cursors for every
query in DB2. Worse, it uses a kind of scrollable cursor that DB2
doesn't support, so the client and server go back and forth negotiating
a supported scrollable cursor level _for every row you request_. Which
means that retrieving data from a remote server is crazy slow.

All of this, along with workarounds for the problem, is well-documented
here:
http://www-128.ibm.com/developerwork...ott/index.html

I see you're using ADODB; we really need to get the ibm_db2 extension
for PHP (http://php.net/ibm_db2) supported in ADODB, because until
that's available anybody using ADODB to connect to DB2 through Unified
ODBC is going to think that the database is the bottleneck. The ibm_db2
extension does things right (including BLOB support, defaulting to
forward-only cursors but enabling you to use scrollable cursors on a
statement by statement basis, and support for OUT/INOUT parameters in
stored procedures).

Dan

[Dan Scott]

Larry:

Retrieval of LOBs and (I believe) LONG VARCHARs in scrollable cursors
is not supported by DB2, ergo warnings or errors are to be expected
with Unified ODBC. :(

Dan