Fetching a varbinary field as a stream using client buffer in sqlsrv gives "Invalid cursor state" error

[yitam]

Tested in Windows with the latest sqlsrv driver (tested with fields like varbinary(512) or varbinary(max)). Without using client buffer, we can save the output to a jpeg file and then open it with any photo app. However, when using client buffer, the error is

Array
(
    [0] => Array
        (
            [0] => 24000
            [SQLSTATE] => 24000
            [1] => 0
            [code] => 0
            [2] => [Microsoft][ODBC Driver Manager] Invalid cursor state
            [message] => [Microsoft][ODBC Driver Manager] Invalid cursor state
        )

)

This is the repro:

$connectionInfo = array("Database"=>"AdventureWorks2014", "UID"=>$uid, "PWD"=>$pwd);
$conn = sqlsrv_connect($server, $connectionInfo);
$tsql = "SELECT ThumbNailPhoto FROM Production.ProductPhoto WHERE ProductPhotoID = ?";
$productPhotoID = 70;
$params = array($productPhotoID);

$stmt = sqlsrv_prepare($conn, $tsql, $params, array("Scrollable"=>SQLSRV_CURSOR_CLIENT_BUFFERED));
if ($stmt === false) {
    echo "Error in preparing statement.";
    die(print_r(sqlsrv_errors(), true));
}
if (!sqlsrv_execute($stmt)) {
    echo "Error in statement execution.";
    die (print_r(sqlsrv_errors(), true));
}

$readAsStr = false; // if we set this to true, we can see that $field is not empty!
if (sqlsrv_fetch($stmt, SQLSRV_SCROLL_FIRST)) {
    if ($readAsStr) {
        $field = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR));
        var_dump($field);
    } else {
        $image = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
        header("Content-Type: image/jpg");
        if (!fpassthru($image)) {
            print_r(sqlsrv_errors());
        }
    }
} else {
    echo "Error in retrieving data.";
    die (print_r(sqlsrv_errors(), true));
}

[david-puglielli]

The same problem occurs on Linux using the latest unixODBC (2.3.5-pre), which handles the Invalid cursor state error correctly. The cause of this behaviour is that the cursor has been advanced beyond the end of the result set once the call to SQLGetData in sqlsrv_stream_read is made. The reason it has been advanced beyond the end of the result set is that SQLGetData and SQLFetchScroll are called during the process of loading the result set into memory for buffering. Because the purpose of streaming is to handle large amounts of data that would be unsuitable for buffering in the first place, retrieving data as streams from a buffered result set is not something we should support. However the error message does need to more informative, so that will be updated.

[david-puglielli]

Closing issue as it has been fixed.