#13838 [Opn->Ana]: Default ODBC statement concurrency option does not allow some queries on MSSQL

ID: 13838 Updated by: kalowsky@php.net Reported By: f.glasner@feldmann-mg.com -Status: Open +Status: Analyzed Bug Type: ODBC related Operating System: WinNT4 SP5 -PHP Version: 4.0.6 +PHP Version: 4.3.0 New Comment: This is true for all versions of PHP really. The ODBC env needs to be delt with differently for this to work. I've been working on a fix for this for awhile. Previous Comments: ------------------------------------------------------------------------ [2003-01-06 11:57:04] kalowsky@php.net Nicos this hasn't been changed, thus the non-status change in it. ------------------------------------------------------------------------ [2003-01-02 19:40:34] nicos@php.net Please try using this CVS snapshot: http://snaps.php.net/php4-latest.tar.gz For Windows: http://snaps.php.net/win32/php4-win32-latest.zip ------------------------------------------------------------------------ [2001-10-26 10:55:48] f.glasner@feldmann-mg.com I am using PHP 4.0.6 on WinNT4 SP5. The database is MSSQL 7.0. With the default ODBC statement concurrency option set by PHP/ODBC some queries are not executed by SQL Server. $conn = odbc_connect("MyDSN", "user", "pass"); odbc_exec($conn, "SELECT * FROM test1 WITH(UPDLOCK HOLDLOCK) WHERE ID=1"); results in this error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot specify UPDLOCK or TABLOCKX with READ ONLY or INSENSITIVE cursors. Workaround: Manually set a default ODBC statement option that results in a cursor that is not read only: $conn = odbc_connect("MyDSN", "user", "pass"); odbc_setoption($conn, 1, SQL_CONCURRENCY, SQL_CONCUR_VALUES); odbc_exec($conn, "SELECT * FROM test1 WITH(UPDLOCK HOLDLOCK) WHERE ID=1"); The drawback of the workaround is that it does not work for persistent connections. But it would be nice to have persistent connections also. So, I propose some extensions on the arguments of odbc_connect/odbc_pconnect: int odbc_connect (string dsn, string user, string password [, int cursor_type[, array default_statement_options]]) So it could be used like this: $dso = array(SQL_CURSOR_TYPE=>SQL_CURSOR_DYNAMIC, SQL_CONCURRENCY=>SQL_CONCUR_VALUES); $conn = odbc_connect("DSN", "user", "pass", SQL_CUR_USE_DRIVER, $dso); You could either set the defaults with SQLSetConnectOption after SQLConnect or do a SQLSetStmtOption after allocating an ODBC statement handle for all the items of the array. Maybe it would be possible to change the signature of odbc_connect/odbc_pconnect to: int odbc_connect (string dsn, string user, string password [, mixed odbc_options]) where mixed means the (current) int or the proposed array. Advantages: - Persistent connections could be made to accept some statement options because odbc_pconnect has all the information for discriminating and selecting an appropriate connection. There seems to be a similar problem reported with bug id #9738 (http://bugs.php.net/bug.php?id=9738). But instead of providing some sort of "odbc_prepare_clean" I would prefer to extend the capability of setting ODBC statement options in a much more flexible way that can be used for persistent connections also. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=13838&edit=1