Bug #9738 Updated: odbc_prepare does implicit SQLSetStmtOption (6,2) (sets to dynamic cursor)

ID: 9738 Comment by: sschadt@openlinksw.com Reported By: sschadt@openlinksw.com Status: Analyzed Bug Type: ODBC related Operating System: HP-UX 10.20 / Linux PHP Version: 4.0.4pl1 New Comment: 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 Previous Comments: ------------------------------------------------------------------------ [2001-07-06 10:38:54] kalowsky@php.net marking this as analyzed as this will hopefully go away too with the cursor fix, but won't disappear until than... ------------------------------------------------------------------------ [2001-06-10 16:05:30] 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-03-14 05:17:27] sschadt@openlinksw.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); ------------------------------------------------------------------------ Edit this bug report at http://bugs.php.net/?id=9738&edit=1