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

From: sschadt@openlinksw.com Operating system: HP-UX 10.20 / Linux PHP version: 4.0.4pl1 PHP Bug Type: Feature/Change Request Bug description: odbc_prepare does implicit SQLSetStmtOption (6,2) (sets to dynamic cursor) 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 Bug report at: http://bugs.php.net/?id=9738&edit=1