PHP PDO returning no records but SSMS does for the same SQL query

Question

I have a conundrum that appears to defy logic, involving Lumen, PHP, PDO, and SQL Server.

I have a controller which contains an action, that executes a stored procedure on a QL Server instance before returning the results as a JSON string. Nothing special is happening but for certain parameters, I do not get any response.

Right, some code. Here's the PHP/PDO prepared statement.

        // Prepare our query.
        $query = $pdo->prepare("
            EXEC [dbase].[dbo].[myStoredProc]
                @A = :A,
                @B = :B,
                @C = :C,
                @D = :D,
                @E = :E,
                @F = :F,
                @G = :G
        ");

        // Bind the parameters and execute the query.
        $query->bindParam(':A', $A);
        $query->bindParam(':B', $B);
        $query->bindParam(':C', $C);
        $query->bindParam(':D', $D);
        $query->bindParam(':E', $E);
        $query->bindParam(':F', $F);
        $query->bindParam(':G', $G);
        $query->execute();

        // Uncomment the following line for debugging purposes.
        $query->debugDumpParams();

        // Lets get all of the data.
        $data = $query->fetchAll(\PDO::FETCH_ASSOC);

        print_r($data);

Perfectly normal as I said. If I use POSTMAN and pass in the parameters as follows:

A    'C_ICPMS_06'
B    'AQC1'
C    '726'
D    NULL
E    '2021-08-30 00:00:00'
F    '2021-11-30 23:59:59'
G    NULL

I get a list of results as expected, both from POSTMAN and PHP as well as through SSMS (using the output from the debug statement).

Now if I change parameter C from '726' to '728', I do not get any output from POSTMAN and PHP, but still, get output from SSMS.

Thinking that there could be some text within the output that is breaking the FETCHALL function, I amended the stored procedure to return a single record, all columns containing 1's. Once more the parameter of 726 works, 728 does not.

I added a VAR_DUMP command to ensure that the parameter isn't being molested on its way to the controller, both parameter values report that they are strings with 3 characters in length.

if I change the prepared statement as below, I still don't get any results seen within POSTMAN/PHP.

        // Bind the parameters and execute the query.
        $query->bindParam(':A', $A);
        $query->bindParam(':B', $B);
        //$query->bindParam(':C', $C);
        $query->bindValue(':C', '728');
        $query->bindParam(':D', $D);
        $query->bindParam(':E', $E);
        $query->bindParam(':F', $F);
        $query->bindParam(':G', $G);
        $query->execute();

The debug SQL statement is identical to before (using the param as opposed to value).

If I change the stored procedure, such that regardless of what value is passed in for parameter C, it is hardcoded to 728, it works as intended (obviously it does not matter what the parameter is set within POSTMAN). So I get values within POSTMAN and SSMS, therefore, it is safe to assume that the whole problem is being caused by the parameter and value '728'.

Further digging at this issue, I find that if the parameter has a value of '72F' or '70W', also returns no results via POSTMAN/PHP but does from within SSMS. I've checked and cannot see any error messages being produced.

I added the below lines to the controller to see if I can see an issue, but nothing was seen (not on screen nor within error files).

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

In trying to figure this out, I created a temporary database table in order to capture the input parameters and the number of records as found within the SP. This should show where the problem lies, i.e. within PHP or within SQL Server. It now gets stranger.

Calling the SP from within SSMS, it populates with an expected record, including the number of records the initial search returned. However, calling it from POSTMAN using the controller, everything is the same in terms of parameters, but the number of records found is 0!

So I know something very weird is going on, but cannot put my finger on what and therefore how to fix it. If anyone has any ideas or has come across a similar problem, please let me know. this is bugging me now. No doubt when I get this working, it'll be a silly error and I'll end up kicking myself.

Answer 1

The issue was that a temporary table that was being created couldn't hold a specific value being assigned to it. The column was designated as a TINYINT but should have been a SMALLINT since the value could go negative.

Why SSMS never reported that as an issue and happily allowed it through, God only knows. But when called externally, it failed to insert any records within the temporary table, returning no records as a result.

There go 1.5 days of my life never to be seen again.