sqlsrv_has_rows() doesn't roll back statement cursor correctly

[v-mabarw]

Hi, sqlsrv_has_rows() function seems to interfere with the statement cursor
position; assuming to work with an SQL statement wich retrieves a single
record (e.g.: SELECT myField FROM myTable WHERE myIDField =
someExistingValue), try this:

$stmt = sqlsrv_query($conn,$sql);
if (sqlsrv_has_rows($stmt)) {
    while ($row = sqlsrv_fetch_array($stmt)) {
        echo "first field value: " . $row[0];
    }
}
// no output will be produced, because the while cycle will be never entered:

Also repeated calls passing the same (populated) statement fatally makes the
returned value change from TRUE to FALSE. Assuming again to work with an SQL
statement wich retrieves a single record, try this:

$stmt = sqlsrv_query($conn,$sql);
echo "<br>Rows?" . (sqlsrv_has_rows($stmt) ? " Yes!" : " NO!") . "<br>";
echo "<br>Rows?" . (sqlsrv_has_rows($stmt) ? " Yes!" : " NO!") . "<br>";
// the output will be:
// Rows? Yes!
// Rows? No!

thanks in advance

Work Item Details

Original CodePlex Issue: Issue 22426
Status: Proposed
Reason Closed: Unassigned
Assigned to: Unassigned
Reported on: Jan 8 at 6:04 PM
Reported by: ideattiva
Updated on: Jan 9 at 9:24 AM
Updated by: robertjohnson

[v-mabarw]

On 2014-01-09 17:24:53 UTC, robertjohnson commented:

It's a bug, it only works properly when called once and once-only per result.

Internally sqlsrv_has_rows simply calls sqlsrv_fetch, so you can do the same.

If you open a scrollable cursor, you can rewind it. If it's a forward cursor, just remember that you fetched the first row.

[yitam]

The first problem no longer exists but I'm able to reproduce the second problem with the following repro script (happens in both Windows and Linux):

// this table has three rows
$stmt = sqlsrv_query( $conn, "select name from fruit" );
if (! $stmt) {
	echo "Query failed\n";
	die (print_r(sqlsrv_errors(), true));
}

// if we skip the next three calls it's fine
echo "Has Rows?" . (sqlsrv_has_rows($stmt) ? " Yes!" : " NO!") . "\n";
echo "Has Rows?" . (sqlsrv_has_rows($stmt) ? " Yes!" : " NO!") . "\n";
echo "Has Rows?" . (sqlsrv_has_rows($stmt) ? " Yes!" : " NO!") . "\n";

// but after calling sqlsrv_has_rows() three times, the following 
// if statement will return false, so no data is fetched  
if (sqlsrv_has_rows($stmt)) {
	while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))  
	{  
		var_dump($row);
	}
}

Expected Output:

Has Rows? Yes!
Has Rows? Yes!
Has Rows? Yes!
array(1) {
  [0]=>
  string(5) "apple"
}
array(1) {
  [0]=>
  string(6) "banana"
}
array(1) {
  [0]=>
  string(9) "blueberry"
}

Actual Output:

Has Rows? Yes!
Has Rows? Yes!
Has Rows? Yes!

[yitam]

Fixed by #256