php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #13838 Default ODBC statement concurrency option does not allow some queries on MSSQL
Submitted: 2001-10-26 10:55 UTC Modified: 2021-05-26 19:18 UTC
Votes:3
Avg. Score:3.7 ± 0.9
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: f dot glasner at feldmann-mg dot com Assigned:
Status: Wont fix Package: ODBC related
PHP Version: * OS: *
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: f dot glasner at feldmann-mg dot com
New email:
PHP Version: OS:

 

 [2001-10-26 10:55 UTC] f dot glasner at feldmann-mg dot 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.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-01-06 12:22 UTC] kalowsky@php.net
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.  
 [2010-12-20 13:31 UTC] jani@php.net
-Package: Feature/Change Request +Package: ODBC related -PHP Version: 4.3.2 -> +PHP Version: *
 [2017-10-23 00:10 UTC] kalle@php.net
-Status: Assigned +Status: Open -Assigned To: kalowsky +Assigned To:
 [2021-05-26 19:18 UTC] krakjoe@php.net
-Status: Open +Status: Wont fix
 [2021-05-26 19:18 UTC] krakjoe@php.net
I think it's safe to assume that the implementation of this feature request that was being worked on 18 years ago didn't pan out.

Since no other fixes have been proposed in the time since, I'm going to close this at wont fix, as that seems to more accurately represent this bugs actual status.
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Wed Jul 28 07:01:23 2021 UTC