PHP sqlsrv_query method giving strange blank results

[Popdog]

I have a stored procedure that simply returns 1 result and only 1 row. The output when run through studio looks like the attached image.

 

I have written local VB applications that execute the stored procedure and output the data on screen without issue.

 

But when I use the below PHP app it acts as if though the stored procedure did not return any rows. All I get on the screen is the "Statement Executed" echo. Now other stored procedures work fine when I use this same code, but I know this SP is returning a row with a value (see attached screenshot) in it, so I can't understand why PHP would see only this SP as not returning a value.

$connectionInfo = array( "UID"=>$uid,                              
                         "PWD"=>$pwd,                              
                         "Database"=>$databaseName);   
    
/* Connect using SQL Server Authentication. */    
$conn = sqlsrv_connect( $serverName, $connectionInfo);    
    
$tsql = "Exec GetBalanceForBalanceSite '1008'";  
    
/* Execute the query. */    
    
$stmt = sqlsrv_query( $conn, $tsql);    
    
if ( $stmt )    
{    
     echo "Statement executed.<br>\n";    
}     
else     
{    
     echo "Error in statement execution.\n";    
     die( print_r( sqlsrv_errors(), true));    
}    
    
/* Iterate through the result set printing a row of data upon each iteration.*/    
    


while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC))    
{    
     echo $row[0];    
}    
    

[ginerjm]

Looking at the code it would appear that your query is returning no rows.

 

turn on error checking to be sure nothing is causing an error. Or check your error log.

[Popdog]

That is exactly the issue. It appears as if though no rows are being returned but only when called using a PHP or Javascript app. No errors are returned when turning on error checking. It just sees no data. In Microsoft Studio it does return a row using the exact same statement though. Also when I call it using an ADODB connection in VB it returns data just fine. I can't understand how this method could see a different result than any other method of running the same exact statement?

[mac_gyver]

afaik, your query statement needs to assign the value to the stored procedure input parameter name. something like - 

$tsql = "Exec GetBalanceForBalanceSite @your_input_parameter_name_here=1008";

and if this is an integer value, you shouldn't have single-quotes around it.

[Popdog]

afaik, your query statement needs to assign the value to the stored procedure input parameter name. something like - 

$tsql = "Exec GetBalanceForBalanceSite @your_input_parameter_name_here=1008";

and if this is an integer value, you shouldn't have single-quotes around it.

 

Ok, I tried this just to make sure but for this stored proc it is not a requirement. Both "exec GetBalanceForBalanceSite '1008'" and "exec GetBalanceForBalanceSite @scid=1008" return the same output. 1 row from studio and 0 rows from PHP 

[mac_gyver]

this isn't mentioned specifically in the php/sqlsrv documentation, but stored procedures in the other database types/drivers return two result sets. i think the first one is a result set for the query itself (should you for example be selecting something in the sql statement) and the second one is the output from the stored procedure. i also just found references to doing this upon searching for sqlsrv stored procedure information.

 

add the following before the start of your while(){} loop and see if it starts working as expected - 

sqlsrv_next_result($stmt); // skip over the first result set

[Popdog]

 

this isn't mentioned specifically in the php/sqlsrv documentation, but stored procedures in the other database types/drivers return two result sets. i think the first one is a result set for the query itself (should you for example be selecting something in the sql statement) and the second one is the output from the stored procedure. i also just found references to doing this upon searching for sqlsrv stored procedure information.

 

add the following before the start of your while(){} loop and see if it starts working as expected - 

sqlsrv_next_result($stmt); // skip over the first result set

 

I test this out with no luck. I would expect that all stored procedure calls would require this if that was the case though. I can call any other stored procedure without a problem, it is just this one procedure that returns no rows. I would normally expect the procedure is bad, but it is not bad, it returns a row every time I test it. That is where I am baffled. It is tough for me to post the procedure on a SQL forum for help when it works perfectly fine. It is as if though the way the procedure is returning the data is somehow hiding it.

 

Here is my vbscript app that works just fine calling the same exact SP.

strSQL = "exec GetBalanceForBalanceSite "&chr(34)&"5404"&chr(34)


conn.Open ConnectionDetails
rs.open strSQL, conn, 3,3


If NOT rs.EOF Then
rs.MoveFirst
   MsgBox rs("Balance")
End If

[mac_gyver]

you need to start digging into what you are getting back.

 

any chance you have multiple databases and the one you selecting on the connection doesn't have any matching data?

 

what does adding the following show - 

var_dump(sqlsrv_num_rows($stmt));

do you have php's error_reporting/display_errors set to report and display all php detected errors?

 

echo some literal string inside the while(){} loop so that you will know if it is running - echo 'in loop';  also, use var_dump($row); inside the loop to see what exactly is in $row, assuming the while loop is running.

 

if there's no apparent output from your page, what does a 'view source' in your browser show?

 

and is that all of your actual code in the file? with nothing later in the file that could be hiding/discarding the output, such as a header() redirect and nothing removed between where the query is ran and where you are looping over the result?

 

edit: also, try the above things both with and without the sqlsrv_next_result($stmt); line, but change that line to be var_dump(sqlsrv_next_result($stmt)); so that you can tell if there actually is a next result set (this particular stored procedure may be returning multiple result sets for some reason.)

Edited May 1, 2015 by mac_gyver

[Popdog]

 

you need to start digging into what you are getting back.

 

any chance you have multiple databases and the one you selecting on the connection doesn't have any matching data?

 

what does adding the following show - 

var_dump(sqlsrv_num_rows($stmt));

do you have php's error_reporting/display_errors set to report and display all php detected errors?

 

echo some literal string inside the while(){} loop so that you will know if it is running - echo 'in loop';  also, use var_dump($row); inside the loop to see what exactly is in $row, assuming the while loop is running.

 

if there's no apparent output from your page, what does a 'view source' in your browser show?

 

and is that all of your actual code in the file? with nothing later in the file that could be hiding/discarding the output, such as a header() redirect and nothing removed between where the query is ran and where you are looping over the result?

 

edit: also, try the above things both with and without the sqlsrv_next_result($stmt); line, but change that line to be var_dump(sqlsrv_next_result($stmt)); so that you can tell if there actually is a next result set (this particular stored procedure may be returning multiple result sets for some reason.)

 

 

The loop is not being run at all. I guess because there is no rows.

 

var_dump(sqlsrv_num_rows($stmt)); outputs "Bool(False)"

var_dump(sqlsrv_next_result($stmt)); outputs "Bool(True)"

 

The "Statement Executed" echo is always working. Just no further echos afterwards. There is no other code other than the Variable population for credentials and cleanup. If I change only the stored procedure name to another SP in the database, the webpage outputs the first row without any issue.

 

I have the following at the top of the code also now.

 

error_reporting(E_ALL);

ini_set('display_errors', '1');

Edited May 1, 2015 by Popdog

[mac_gyver]

var_dump(sqlsrv_next_result($stmt)); outputs "Bool(True)"

 

 

this indicates that there is more than one result set.

 

put 3-4 of the var_dump(sqlsrv_next_result($stmt)); lines in a row to see how many result sets there are. true values indicate a result set. a null value indicates no more result sets.

 

at this point, i would guess that your stored procedure is running several queries (insert/update/delete/select), each stacking a result into the returned data. if this is the expected operation of the procedure and not due to a logic problem, you will need to call enough sqlsrv_next_result($stmt); statements to get to the actual one holding the data.

 

---

 

based on the default cursor type usage, it looks like the false value for the num_rows() statement is the expected.

Edited May 1, 2015 by mac_gyver

[Popdog]

this indicates that there is more than one result set.

 

put 3-4 of the var_dump(sqlsrv_next_result($stmt)); lines in a row to see how many result sets there are. true values indicate a result set. a null value indicates no more result sets.

 

at this point, i would guess that your stored procedure is running several queries (insert/update/delete/select), each stacking a result into the returned data. if this is the expected operation of the procedure and not due to a logic problem, you will need to call enough sqlsrv_next_result($stmt); statements to get to the actual one holding the data.

 

---

 

based on the default cursor type usage, it looks like the false value for the num_rows() statement is the expected.

 

 

OMG, you found it! I have to run next result 4 times before I get my output. The SP has multiple select statements into temporary tables but this has never caused any additional outputs when accessed from other methods.

[mac_gyver]

these result sets correspond to each of the insert queries for the temp tables.

 

if you echo sqlsrv_rows_affected($stmt); before each of the sqlsrv_next_result($stmt); lines, you should get an integer value that's the number of rows that were inserted by each query.

 

i don't know if there's a way of suppressing/resetting this. perhaps in the ms sql stored procedure documentation.

[mac_gyver]

here's an edit to the above (the forum software cut my edit time limit off mid edit) -

 

edit: here's trick that should work. you can use sqlsrv_num_fields($stmt) to find if the result set contains any field/column definition, from a SELECT/SHOW query, regardless of there being any rows in the result set. you could loop while sqlsrv_next_result($stmt) is true and if sqlsrv_num_fields($stmt) is true, there's a SELECT/SHOW result set that you can fetch row(s) from.

[Popdog]

here's an edit to the above (the forum software cut my edit time limit off mid edit) -

 

edit: here's trick that should work. you can use sqlsrv_num_fields($stmt) to find if the result set contains any field/column definition, from a SELECT/SHOW query, regardless of there being any rows in the result set. you could loop while sqlsrv_next_result($stmt) is true and if sqlsrv_num_fields($stmt) is true, there's a SELECT/SHOW result set that you can fetch row(s) from.

 

Very cool. Thanks!

[Fever]

This drove me crazy too!   Make sure you put:  

 

SET NOCOUNT ON 

 

at the beginning of your stored procedure.  If not you will have to loop through all outputs to get to your result.  I spent an entire day on this before I got it!!
 

/=ever