'PDOException' with message 'SQLSTATE[HY010]: [Microsoft][ODBC Driver Manager] Function sequence error' in [no active file]:0

[david-garcia-garcia]

When performing insert operations having buffered queries turned on, you get this exception:

SQLSTATE[HY010]: [Microsoft][ODBC Driver Manager] Function sequence error

This happens when running INSERT's and preparing the statement like this:

$pdo_options[PDO::ATTR_CURSOR] = PDO::CURSOR_SCROLL; $pdo_options[PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE] = PDO::SQLSRV_CURSOR_BUFFERED;

return PDO::prepare($query, $pdo_options);

This is happening in 3.0 and 3.1 version of the driver.

[ghst-46]

Hi David-garcia-garcia,

Thank you for taking the time to report this bug and helping to make our Microsoft 3.1 SQL Server Driver better.

We’ve attempted to reproduce this issue, have not been able to do so, and would like your help with more information if possible.

Could you please provide the exact steps to reproduce the bug including
• The INSERT statement that fails, and
• The table definition including data type fields
It would be best if you could provide us with a complete PHP script that reproduces the bug.

Thanks!

[david-garcia-garcia]

-- ghst-46 I've been busy lately collaborating with --E from the Wincache PHP extension and had no time to look into this.

I am also maintainer for the SQL Server Driver for Drupal. A few months ago I tried to revise the driver to make use of the prefetching capabilities of the driver (otherwise we are emulating prefetching in PHP in Drupal which is a massive performance hog).

Please take a look at the discussion regarding moving prefetch away from PHP and usings the PDO's implementation:

https://www.drupal.org/node/1471506

I have prepared a sample script that demonstrates two of the problems I came accros: null terminated strings and the "Function sequence error".

There is a third problem with nested statements that I am not sure you guys can look into (see comment #8 in the thread).

The thing here is that either of all these issues are solved, or you are out of game in the major CMS's due to performance. We are a traditional MS shop but customers and government projects impose nowadays to use one of the major Open Source CMS's for projects, we are seriously trying to stick to MS but the pieces needed to glue PHP + MS SQL + IIS aren't simply taken care of as frequently as they should. Should there be a bug, it hangs around for years in a low level C extension that is no fun to dive into. (Praise to the Wincache team that have done a great job improving and updating the extension during these last year).

You can download the sample scripts here:

https://www.dropbox.com/s/l3xj5303zd03p00/democode.zip?dl=0

If needed you can get in touch with me through my Drupal.org profile contact form:

https://www.drupal.org/u/david_garcia

Greetings and thanks, hope to see the PHP driver moving forward (and getting compiled for PHP 5.6 soon).

[david-garcia-garcia]

I took my time to add another sample code for the one remaining endemic and really annoying issue when consuming the MS SQL pdo driver for PHP. The comments in the code briefly explain the issue.

https://www.dropbox.com/s/bovl9lspsjwg4qs/democode2.zip?dl=0

[ghst-46]

Thank you David-garcia-garcia, your response has been valuable.

[david-garcia-garcia]

I hope valuable means you could replicate with ease :) There's no worse nightmare than not being able to replicate a bug.

Also hope that you could push the "lack of support" message a little bit up in the command chain and MS decides to put more resources into IIS-FastCGI+PHP+MSSqlPDO before more MS shops move to Linux+mySQL+Apache/NginX.

Unluckily, it is not our choice anymore as it was some years ago. For strategic reasons, customers are now telling us to use this or that open source technology otherwise they go to another provider. Even governments have jumped into this wagon.

Greetings!

[SumoLion]

Valuable does mean we recreated. Thank you.

MS is indeed boosting priority to open source and has additional Microsoft Drivers for PHP for SQL Server releases coming… 5.6 and beyond.

[david-garcia-garcia]

Great that sounds like buzz lightyear taking off.

Just joking, thanks for the feedback and the effort. Hope to see soon native prefetching working on Drupal instead of having to emulate it from PHP.

[SumoLion]

Resolved with release 3.2.0.0

[david-garcia-garcia]

@SumoLion please send a big thanks to the team, we have been waiting for such an update like water in the dessert.

I debugged through the test script to find out that some of the issues have not been solved (sorry for the crap test script).

These are the different issues from the test script:

[1] PDO Exception under some circumstances when issuing updates. [FIXED]

[2] Prefetch statements are returning NULL terminated strings. Indeed, type mapping is messed up to such a point that it is imposible to retrieve a float field from the database when using prefetch. [NOT FIXED]

[3] "error occurred translating string to UCS-2" it is not happening now, but retested against old version and still not happening. Probably the part of the script that tests this is broken. [PROBABLY FIXED, BUT CAN'T CONFIRM]

2 out of 3 is very good news, just wanted to confirm with you that it was these were the issues that you could identify and confirmed to be fixed (just in case I'm testing them wrong).

Also if you ackowledge the existence of the null terminated strings problem and the type mapping issues.

I wouldn't mind working on a more elaborate demo script for the null terminated strings issue and mapping problems if required.

Greetings and thanks again!

[SumoLion]

@david-garcia-garcia

We looked at 1 (this issue, #40) and fixed it as you have seen. We looked into 2, (also issue #41 ) but haven't committed to changing this behaviour as of yet. If you do find a repro for 3, please let us know. To my knowledge we couldn't repro it here either.

[david-garcia-garcia]

"but haven't committed to changing this behaviour as of yet"

Does this mean that #41 is indeed a bug, or and expected behaviour that you might consider changing?

I just want to have real expectations on when to overhaul the Drupal SQL Server driver, and I'm waiting for prefetching to work properly before doing so.

Thanks!

[SumoLion]

@david-garcia-garcia

#41 is expected behaviour that we would consider changing in time to coincide with our next release. Can't comment on exact timing, but more is coming for this driver.