php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #9738 odbc_prepare does implicit SQLSetStmtOption (6,2) (sets to dynamic cursor)
Submitted: 2001-03-14 05:17 UTC Modified: 2002-01-21 09:03 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: sschadt at openlinksw dot com Assigned:
Status: Closed Package: ODBC related
PHP Version: 4.0.4pl1 OS: HP-UX 10.20 / Linux
Private report: No CVE-ID: None
 [2001-03-14 05:17 UTC] sschadt at openlinksw dot com
Since some drivers don't like to perform certain queries with the default setting of the dynamic cursor, it might be nice to use something like "odbc_setoption" to manually change the type of Statement that the SQL will be prepared on.  Problem is, currently when changing a statement option this way (argument of "2" for SQLSetStmtOption), you have to already have prepared a SQL statement:

	$result = odbc_prepare ($conn, $sql);
	odbc_setoption ($result, 2, 6, 3);
	odbc_execute ($result);  

This implicitly sets the statement option to set a dynamic cursor first, as the following ODBC trace shows:

SQLAllocStmt hDbc=DBD0002
phstmt=40073138
SQLAllocStmt: returning SQL_SUCCESS

SQLGetInfo hDbc=DBD0002
fInfoType=8 rgbInfoValue=7B03CD68 cbInfoValueMax=4 pcbInfoValue=0
SQLGetInfo: returning SQL_SUCCESS

SQLSetStmtOption hStmt=DBD0003
fOption=6 vParam=00000002
SQLSetStmtOption: returning SQL_SUCCESS_WITH_INFO

SQLPrepare hStmt=DBD0003
szSqlStr=400746F8 cbSqlStr=-3
  [SELECT target.description, target_data.layer1_key,target_data.layer2_key, target_data.layer3_key,target_data.layer4_key,target_data.layer5_key,target_data.layer6_key,target_data.company_code,target_data.location_code FROM dw_user_targets  JOIN target ON target.target_code = dw_user_targets.target_code JOIN target_data ON target_data.target_code = dw_user_targets.target_code AND target_data.company_code=2 AND target_data.year=1998 AND target_data.period_no=7 WHERE dw_user_targets.dw_userid='mark']
SQLPrepare: returning SQL_ERROR

Here's sample script that could reproduce the above.  (Just substitute the above query and connect to a Progress 8.3B database with a similar schema to get even closer)

<?
putenv("ODBCINI=/usr/local/openlink/odbc.ini");
$dsn="DSN=OracleLocal"; // this is a valid DSN set up in the above odbc.ini file, tested in odbctest
$user="scott"; //default user for the demo Oracle database
$password="tiger"; //default password for demo Oracle database
 
$sql="SELECT * FROM EMP";  
// directly execute mode 
if ($conn_id=odbc_connect("$dsn","","")){
    echo "connected to DSN: $dsn";
    if($result=odbc_prepare($conn_id, $sql)) {
         odbc_execute($result)
    }else{
        echo "can not execute '$sql' ";
    }
    echo "closing connection $conn_id";
    odbc_close($conn_id);
}else{
    echo "cannot connect to DSN: $dsn ";
}
?>

So, I propose making a new prepare function such as "odbc_prepare_clean" which doesn't do the implicit SetStmtOption(2,6) for the dynamic cursor.  There shouldn't be a problem without the implicit SetStmtOption as the driver will default to what it supports anyway.

E.g:
	$result = odbc_prepare_clean ($conn, $sql);
	odbc_setoption ($result, 2, 6, 3);
	odbc_execute ($result);  


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2001-06-10 16:05 UTC] kalowsky@php.net
patch sent to you for verification.  awaiting response :)
http://www.deadmime.org/~dank/odbc.c.diff
http://www.deadmime.org/~dank/odbc.h.diff

if you didn't get them AGAIN in email... i think it's your 
mail server btw.. ;-)


 [2001-07-06 10:38 UTC] kalowsky@php.net
marking this as analyzed as this will hopefully go away too with the cursor fix, but won't disappear until than...
 [2002-01-16 13:12 UTC] sschadt at openlinksw dot com
Hi Dan,

I realize it's been a while on this one, but the issue is still causing problems for our users.  I took the patched files you gave me last summer, but they were causing other problems I couldn't pinpoint (see emails in July or Aug of '01).

The simplest way to get rid of this is by changing the following line in ext/odbc/php_odbc.c :

 if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC)

 to

 if (SQLSetStmtOption(result->stmt, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY)

Can you let me know if this would be possible in future releases? 

-Stephen
 [2002-01-21 09:03 UTC] kalowsky@php.net
Bugs bugs bugs.  I hate bugs.

As soon as my CVS connection can be completely established this change will be commited to CVS.  

I still don't like this as the final solution, but it will get things working again.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sat May 15 12:01:23 2021 UTC