Why do some tables return rows, while others don't?

Question

Since I switched my project from the mssql_* library to the sqlsrv_* library, some tables (apparently) contain no rows when I do a SELECT on them.

Both types of table (the ones that return rows and the ones that don't) are in the same database, so it's not a connection problem. My query works if I copy and paste it into SQL Server.

I get no error message, no exception, nothing. Just no rows when there ARE rows in that table.

Here's the code I use for getting rows from the database:


---

class db
{
  // ...

  static function query_filter($database, $query)
  {
    $connection = db::connect($database);
    $query_result = sqlsrv_query($connection, $query);
    
    if( $query_result === false )
    {
      throw new db_Sql_Exception($query);
    }

    $result = array();
    while( $row = sqlsrv_fetch_array($query_result) ) 
    {
      $result[] = $row;
    }
    return $result;	
  }

  // ...
}

---


And here's the test script I used to make sure I wasn't crazy:


---

require "db.php";

$query = "
  SELECT * FROM STA_LogInterfacesOperateursCompteursVitesses
";

$rows = db::query_filter('TestDB', $query);

foreach( $rows as $row )
{
  print "<p>" . print_r($row) . "</p>";
}

---


If I switch the table name in my query, everything suddenly works, assuming I pick one of the tables that the SQL Server driver likes.

I'm using the 1.1 / October 2009 version of the driver. I've been trying to fix this since yesterday, so any help would be appreciated.

Answer 1

Wow, this is insane. This query returns rows, as it should:
---

  SELECT logvitesseinterfacesoperateurs_id,
    DATEPART(hour, creation) AS hour, 
    DATEPART(minute, creation) AS minute
  FROM STA_LogInterfacesOperateursCompteursVitesses 

---
As soon as I select another column (SQL Server datatype "real"):
---

  SELECT logvitesseinterfacesoperateurs_id,
    DATEPART(hour, creation) AS hour, 
    DATEPART(minute, creation) AS minute, 
    valeur AS speed 
  FROM STA_LogInterfacesOperateursCompteursVitesses 

---
BOOM! No rows, no exception, no error. Does the driver not support the "real" type? If so, an error message would be useful here, instead of a silent failure.

Answer 2

We do support the “real” type. To investigate further, we need additional information.

What is the return value of sqlsrv_fetch_array? If it is null, then indeed there are no rows. However, if the return value is FALSE, then the call failed and the error should be available through sqlsrv_errors. 

Please note that the driver does no throw an exception on error, but it fails the call and makes the errors available through a subsequent call to sqlsrv_errors.

Thank you,
Serban

 

---

Serban Iliescu - MSFT

Answer 3

The return value of sqlsrv_fetch_array is false. This is the error message:

Array ( [0] => Array ( [0] => 22003 [SQLSTATE] => 22003 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Numeric value out of range [message] => [Microsoft][SQL Server Native Client 10.0]Numeric value out of range ) )

How can the query "succeed", but then fail when I try to look at a row?

How can the value be out of range? This is nothing like an edge case. ANY real column gives me this error, and my database contains entirely ordinary numbers like 2.1 or 0.495 or 0.006666.

Answer 4

Please check that SQL Server Native Client 10 (SNAC 10) installed is version 10.00.2531 or later. Version 10.00.1600 had a bug on handling “real” numbers (very similar to the behavior reported in your post).

The SNAC 10 version required is the one included in the Microsoft SQL Server 2008 Feature Pack, April 2009.

http://www.microsoft.com/downloads/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

Hope this helps,

Serban

 

 

---

Serban Iliescu - MSFT

Answer 5

Thanks, this solved the problem.

However, I downloaded that broken version from Microsoft's website very recently. Maybe it's just me, but I wouldn't consider that version production-ready.

It should be removed or at least carry a warning, I spent a few hours on this, thinking the problem was in my code at first.

Again, thanks for the help.