Function sequence error + wrong transaction management

[david-garcia-garcia]

I was able to finally isolate into a simple script the Function Sequence Error issues.

Repro script here:

http://pastebin.com/bYvdzgPN

Using latest PHP7x64 driver 4.1.3 with PHP 7.0.11

Current result:

SQLSTATE[HY010]: [Microsoft][ODBC Driver Manager] Function sequence error
SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]New transaction is not allowed because there are other threads running in the session.

Expected result:

EXECUTION 1 OK
EXECUTION 2 OK

[meet-bhagdev]

@david-garcia-garcia : We are looking into this and seems like it may be by design. Using the client-side cursors allows you to cache the entire result set in memory, which explains why transactions are allowed. On the other hand, when the results are not buffered, the server will prevent transactions because the result set is still active (even with MARS enabled).

For details, please refer to the following blog post
https://blogs.msdn.microsoft.com/cbiyikoglu/2006/11/20/mars-transactions-and-sql-error-3997-3988-or-3983/

Thoughts?

[david-garcia-garcia]

Using the client-side cursors allows you to cache the entire result set in memory, which explains why transactions are allowed.

Does that mean the out of the two samples in the repro script, the first one should be working?

On the other hand, when the results are not buffered, the server will prevent transactions because the result set is still active (even with MARS enabled).

That one seems reasonable, the second sample was just me playing around with the same script without buffered cursors enabled.

I understand then that the expected result for the script should be:

EXECUTION 1 OK
SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]New transaction is not allowed because there are other threads running in the session.

Then that's OK. The specific scenario I am dealing with is Drupal iterating over a a statement result (using it's transversable interface) and doing transactional stuff inside the loop, that corresponds to the first part of the script.

[Hadis-Knj]

@david-garcia-garcia that's right, the execution with buffered set to true should work.

[david-garcia-garcia]

Is this supposed to be fixed in a future release?

[Hadis-Knj]

@david-garcia-garcia The second sample is by design and is not supposed to work. We are not able reproduce the issue in the first sample in our lab, and it is working as expected, do you have an issue with it? Feel free to reopen the issue

[david-garcia-garcia]

We are not able reproduce the issue in the first sample in our lab

This is getting interesting, and might be related to using SQL Native Client vs ODBC.

In our production environments we have Native Client only installed (against SQL Server 2014), and the issue is reproducible.

But on local, I just installed ODBC 13.1 and it does behave as intended. I am now getting this though (Randomly):

[17-Nov-2016 03:21:16 America/Tegucigalpa] PHP Fatal error: No ODBC error was found in D:\d8\index.php on line 84

Could you throw any light on how the driver is internally choosing between ODBC and SQL Native Client, and if there is anyway to control that from the connection string in PDO?