Fetching a varbinary field as a stream using client buffer in sqlsrv gives "Invalid cursor state" error #570
Description
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));
}