|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #22971 Error in odbc_prepare or in odbc_connect?
Submitted: 2003-03-30 22:38 UTC Modified: 2003-04-09 06:37 UTC
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: anton at esrr dot ru Assigned:
Status: No Feedback Package: ODBC related
PHP Version: 4.3.1 OS: Linux RedHat 8
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2003-03-30 22:38 UTC] anton at esrr dot ru
Sorry for my english.
I've php + Db2.(./configure --with-ibm-db2=/home/db2inst1/sqllib)

Now. For exaple, i'm trying to execute such script:

<?php function dbconnect($verbose) {
 $dbname = "SAMPLE";
 $username = "db2inst1";
 $password = "ibmdb2";
 // odbc_connect returns 0 if the connection attempt fails;
 // otherwise it returns a connection ID used by other ODBC functions
 $dbconn = odbc_connect($dbname, $username, $password);
 if (($verbose == TRUE) && ($dbconn == 0))
 echo("Connection to database failed.");
 $sqlerror = odbc_errormsg($dbconn);
 else echo "connection successfull\n";
 $q="Select count(*) from SYSIBM.SYSTABLES";
 echo "preparing query";

And I'm getting next result:

db2inst1@redcell4 htdocs]$ php ./tdb.php
connection successfull
preparing query
Warning: SQL error: [IBM][CLI Driver][DB2] SQL0228N FOR UPDATE clause
specified for read-only cursor "SQLCUR4 ".  SQLSTATE=42620
, SQL state 42620 in SQLPrepare in /usr/local/apache/htdocs/tdb.php on line

[db2inst1@redcell4 htdocs]$

[db2inst1@redcell4 htdocs]$ db2 "? SQL0228N"

 SQL0228N FOR UPDATE clause specified for read-only cursor

Explanation:  Cursor "<cursor-name>" is defined as INSENSITIVE
SCROLL, but the corresponding SELECT statement contains a FOR
UPDATE clause.

The statement cannot be processed.

User Response:  To define a read-only cursor, specify INSENSITIVE
on DECLARE CURSOR, but do not specify the FOR UPDATE clause as
part of the SELECT statement of the cursor.

sqlcode:   -228

sqlstate:  42620

I think that in function odbc_prepare cursor is created.

So I'm trying to add parameter SQL_CUR_USE_ODBC  to connect string. That is "$dbconn = odbc_connect($dbname, $username,
$password,SQL_CUR_USE_ODBC )".

Result is changed, but it is wrong result:

php ./tdb.php

Warning: SQL error: [IBM][CLI Driver] CLI0150E  Driver not capable.
SQLSTATE=S1C00, SQL state S1C00 in SQLSetConnectOption in
/usr/local/apache/htdocs/tdb.php on line 10
Connection to database failed.
Warning: odbc_errormsg(): supplied argument is not a valid ODBC-Link
resource in /usr/local/apache/htdocs/tdb.php on line 14
preparing query
Warning: odbc_prepare(): supplied argument is not a valid ODBC-Link resource
in /usr/local/apache/htdocs/tdb.php on line 20

[db2inst1@redcell4 htdocs]$ db2 "? CLI0150E"

 CLI0150E Driver not capable.

Explanation:  The operation is valid but not supported by either
the driver or the data source.

User Response:  Specify a valid operation.

[db2inst1@redcell4 htdocs]$

Where is problem in odbc_connect, odbc_prepare or in my mind? HELP PLEASE!


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2003-04-02 18:23 UTC] anton at esrr dot ru
 [2003-04-02 18:41 UTC]
You had the correct category before, IBMs DB2 uses ODBC.

Try calling odbc_set_option and changing your CURSOR to SQL_CURSOR_FORWARD_ONLY, this should fix a few things for your DB2 install.
 [2003-04-02 20:07 UTC] anton at esrr dot ru
>You had the correct category before, IBMs DB2 uses ODBC.
>Try calling odbc_set_option and changing your CURSOR to
>SQL_CURSOR_FORWARD_ONLY, this should fix a few things for >your DB2

Ok. I'll try. Thank you.
But on this moment the positive result has achieved by commenting out strings containing #define HAVE_SQL_EXTENDED_FETCH 1 in php_odbc_includes.h, as described in Bug #9869 ( I commented out all lines with this string, but I think that DB2 CLI part enough.
 [2003-04-03 04:57 UTC]
Dan: Would the real fix be to disable that  HAVE_SQL_EXTENDED_FETCH for DB2 altogether? 

 [2003-04-03 06:31 UTC]
Sniper, the real fix would be not to use the native interface to IBM DB2 and to use unixODBC as per the DB2 recommendations.

Unfortunately I haven't a DB2 machine to test with and cannot comment much more on that.  What I do know is that DB2 doesn't like the SQL_CURSOR_DYNAMIC that is used by PHP, and in fact claims it's leaking memeory.  Removing the EXTENDED_FETCH will discontinue the cursor use, but this is also why I suggested changing the cursor.  
 [2003-04-09 06:37 UTC]
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Open". Thank you.

 [2003-11-24 12:21 UTC] maxi at paoloastori dot com
accessing DB2 via odbc (tried both unixODBC --with-unixODBC and DB2 CLI --with-ibm-db2) was extremely slow from PHP.

Once disabled EXTENDED_PREFETCH in ext/odbc/odbc.h queries started flying and randomly error messages all disappeared.

I really suggest to disable EXTENDED_PREFETCH if accessing DB2 using ODBC.
Simply changing from SQL_CURSOR_DYNAMIC to SQL_CURSOR_STATIC  in function SQLSetStmtOption inside php_odbc.c is not enough.
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun May 26 12:01:29 2024 UTC