New PHP 7 PDO seems not to support client buffered queries

[david-garcia-garcia]

I just gave the new driver a test drive and this is the first thing I came accross:

The server reports the following message: SQLSTATE[IMSSP]: The value passed for the 'Scrollable' statement option is invalid..

// It creates a cursor for the query, which allows you to iterate over the result set
// without fetching the whole result at once. A scrollable cursor, specifically, is one that allows
// iterating backwards.
// https://msdn.microsoft.com/en-us/library/hh487158%28v=sql.105%29.aspx
$pdo_options[PDO::ATTR_CURSOR] = PDO::CURSOR_SCROLL;

// Lets you access rows in any order. Creates a client-side cursor query.
$pdo_options[PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE] = PDO::SQLSRV_CURSOR_BUFFERED;

These are the exact PDO options that reach the statement before throwing the exception:

[meet-bhagdev]

@david-garcia-garcia thanks for the quick report. We will investigate this. Are you using the 64 bit variant or the 32 bit variant?

[david-garcia-garcia]

Using x64. Please confirm you can reproduce. If not I'll make a repro script.

I identified other bugs along the way , but I'd rather deal with them one at a time, at least until I can get a basic D8 up and running with the current driver that works with PHP 5.x.

I have a close release soon and did not count on having this ready. It will be awesome to launch on PHP7.

[meet-bhagdev]

@david-garcia-garcia We were able to confirm and will aim to fix this in the coming releases. Happy to look into any other bugs you may have as well.

[david-garcia-garcia]

Another issue is that now it is impossible to perform binary bindings:

Uncaught PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. in D:\d8\index.php:128 Stack trace: #0 D:\d8\index.php(128): PDOStatement->execute() #1 {main} thrown in D:\d8\index.php on line 128

See the repro script from:

#63

Besides the original issue reported, 2 new bugs appear on the repro script from that issue.

Just comment out these two lines (so skip the Scroll cursor type bug):

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

grom the repro script to make the binary issue happen.

[david-garcia-garcia]

BTW I really hope you are building a nice and complete test suite while working on this driver....

[meet-bhagdev]

@david-garcia-garcia we will look into this! We are adding all these repro scripts to our test suite :)

[meet-bhagdev]

@david-garcia-garcia This error for binary bindings is from a bug in PDO that is fixed in the PHP source but not out as an official release yet :)

From our Readme:
The $driver_options (for specifying encoding) in PDOStatement::bindParam does not work due to a bug in the PDO extension source code. A fix for this bug now exists but it hasn't made it into an official PHP release yet.

Let me know if you have more questions around it.

Cheers,
Meet

[david-garcia-garcia]

@meet-bhagdev Thanks por pointing that out.

I was wondering why the Drupal 8/MySQL driver was not using bindParam() - that's why they have not run into this bug - and it looks like the MySQL PDO driver does not need to explictly bind BLOB/Binary columns. You just send the raw data - for example strings - and it will push it through to the BLOB column in MySQL.

That's some nice magic that could help me massively simplify the Drupal 8/MSSQL integration :)

[meet-bhagdev]

@david-garcia-garcia : A fix for $driver_options (for specifying encoding) is included in PDOStatement::bindParam in the PHP 7.0.6 release.
Give our latest preview driver with the PHP 7.0.6 release and let us know what your thoughts are.

[david-garcia-garcia]

Client buffered queries seem to work now. Time to look at all the old driver bugs and see if they are still there.

The original concern of this issues has been addressed thanks.

[meet-bhagdev]

Awesome! Will go ahead and close this one.