CODEWITHSUNDEEP
phpsqlsqlsrv
xevidos
xevidos

Reputation: 21

PHP sqlsrv_fetch_array only loops through half of the results

When looping through the result set of ( 60 rows ) PHP only loops through a few of the results. If I do not sort the results it returns four. If I sort the results by their ID it returns 31 results. I believe it may have something to do with the size of the data in each row but I cannot be sure. The view I am selecting from has 50 columns about a quarter of which are HTML code and I believe those have a datatype of varchar(max). I am using the following specs and code.
Microsoft SQL SERVER 2008
PHP 7.2
ODBC 17
PHP Execution time is 900 seconds
PHP Memory limit is 4096MB

$connection_info = array();
$connection_info["Database"] = "database";
$connection_info["UID"] = "username";
$connection_info["PWD"] = "password"
$connection = sqlsrv_connect( "server", $connection_info );
result = sqlsrv_query( $connection, "SELECT * FROM server.[database].[dbo].[table]", array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ) );

while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ) ) {
    echo $row["id"] . " " . var_dump( sqlsrv_errors() ) . "<br>";
    }
echo var_dump( sqlsrv_errors() );

The last check of sqlsrv_errors() returns an error of:

array(1) { [0]=> array(6) { [0]=> string(5) "01004" ["SQLSTATE"]=> string(5) "01004" [1]=> int(0) ["code"]=> int(0) [2]=> string(71) "[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation" ["message"]=> string(71) "[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation" } }

Upvotes: 2

Views: 1873

Answers (1)

Luke Price
Luke Price

Reputation: 70

I had a similar issue.

I found the loop would stop seemingly randomly. This is the way I fixed my issue. When the loop reaches a point where the server produces an error (which can be displayed using 'sqlsrv_errors')

while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ) ) {  
echo $row["id"] . " " . var_dump( sqlsrv_errors() ) . "<br>"; 
}

This is the way I avoided the issue:

$params = array();
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$result = sqlsrv_query($conn, $query, $params, $options);
for ($i = 0; $i < sqlsrv_num_rows($result); $i++) {
    $obj = sqlsrv_fetch_array($newq, SQLSRV_FETCH_NUMERIC, SQLSRV_SCROLL_ABSOLUTE, $i);
    echo $row[0]; 
}

I think the issue occurs because it doesn't know how to handle the error it is presented with and for this reason quits the loop.

This is the documentation that describes the issue

Upvotes: 1

Related Questions