PHP 4.0 Bug #9738 Updated: odbc_prepare does implicit SQLSetStmtOption (6,2) (sets to dynamic cursor)
| From: | sschadt at openlinksw dot com | Date: | Wed, 14 Mar 2001 04:27:35 +0000 |
| Subject: | PHP 4.0 Bug #9738 Updated: odbc_prepare does implicit SQLSetStmtOption (6,2) (sets to dynamic cursor) | ||
| Groups: | php.dev | ||
| Request: | Send a blank email to php-dev+get-48870@lists.php.net to get a copy of this message | ||
ID: 9738
User Update by: sschadt@openlinksw.com
Status: Open
Old-Bug Type: Feature/Change Request
Bug Type: ODBC related
Description: odbc_prepare does implicit SQLSetStmtOption (6,2) (sets to dynamic cursor)
Previous Comments:
---------------------------------------------------------------------------
[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);
---------------------------------------------------------------------------
Full Bug description available at: http://bugs.php.net/?id=9738