PHP PDO Not Finding OUTPUT Parameter From MSSQL Stored Procedure Call

Question

Been working at this for 3 days and tried every possible example instructions I could find in those 3 days. I have to ask for help in trying to understand what is not functioning here with regards to the OUTPUT parameter. This is a machine to machine operation, no terminal or webpage human interaction and only exists on an internal private subnet. Caller--> Cisco IVR-->PHP-->MSSQL DB-->PHP-->Cisco IVR--->Caller. I have included my entire PHP working script but I want to focus on why my parameter 3 statement does not function. My MSSQL stored procedure code 1st.

ALTER PROCEDURE [dbo].[pIOGetEligSSNDOB]
    
    @SSN NVARCHAR(9),
    @DOB NVARCHAR(8),
    @RCODE INT OUTPUT 
    
AS
BEGIN   

    SELECT * FROM ZASMasterDB.dbo.Eligibility WHERE SSN = @SSN AND DOB = @DOB
    IF @@ROWCOUNT >= 1
    SET @RCODE = 3
    ELSE 
    SET @RCODE = 5
       SELECT 'ReturnCode' = @RCODE
       RETURN @RCODE
       
END


****My working PHP version 7.4.10 script using PDO****


$ID = $_GET['MEMBER_ID']; $DOB = $_GET['DOB'];
$start_time             = microtime(true);

$sth = $pdo->prepare("exec dbo.pIOGetEligSSNDOB ?, ?, ? "); // I am using 2 INPUT and 1 OUTPUT

$RCODE = 0;
$row_count = 0;

$sth->bindParam(1, $ID);     // Members social security number as INPUT to SQL server
$sth->bindParam(2, $DOB);    // Members date of birth as INPUT to SQL server
$sth->bindParam(3, $RCODE, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,10); //$RCODE to hold the OUTPUT integer 

$sth->execute();
    
$time                   = round( (microtime(true) - $start_time), 4);           
$col_count              = 0;

//ARRAYS TO HELP ORGANIZE THE RESULTS
$column_name_array      = array();
$rows_array             = array();

// THE LOOP!

while($row = $sth->fetch(PDO::FETCH_ASSOC)){
    
    $row_count++;                         // A MUST for the IVR to know how many records returned 
    $col_count          = count($row);
    $column_name_array  = array_keys($row); 
    $row = array_map('trim',$row);      
    $rows_array[]       = join("·", array_values($row));  //Delineation for the IVR
} 

    $sth = null;       
    $pdo = null;
 
 
?>   



    
    
    
              // This should be the value of the OUTPUT parameter from SQL DB
    
       
    
    
    ·

This is the result of the above code. It is perfect, except no OUPUT integer from SQL server to the $RCODE which would then display between the code elements.

This is the result of executing it in SQL Management Studio

I tried, changed the int value and still nothing. My PHP wont allow (? OUPUT) in the prepare line as the 3rd thing, doesn't like the ? mark. More research on here shows someone saying you need a DECLARE. So I made my line:

 $sth = $pdo->prepare("DECLARE @RCODE INT; exec pIOGetEligSSNDOB ?,?, @RCODE OUTPUT; ");

and still no return value but PHP didn't complain, got my regular data back still. The I tried to use the exact same thing in my prepare line as I use manually when in SQL mgt studio.

 $sth = $pdo->prepare("DECLARE @RCODE INT; exec pIOGetEligSSNDOB ?,?, @RCODE = @RCODE OUTPUT;");

and no $RCODE result.

So I had read about the whole "nextRowset" thing while I as pulling my hair out days ago, but then forgot about it while still trying to get something to work. Thank you for reminding me Zhorov!!. So I added the do to my original while and then added the while at the end of all the do, just like in those examples.

do {
    while($row = $sth->fetch(PDO::FETCH_ASSOC)){

    $row_count++;
    $col_count          = count($row);

    $column_name_array  = array_keys($row); 
    
    $row = array_map('trim',$row);      
    
    $rows_array[]       = join("·", array_values($row));
} 
}  while ($sth->nextRowset());

And now my final XML output is a little differnet, and I do see the integer number 7 but it has been added at the end of my regular data fetch results rather than where I need it to go, and that is between the code elements. Then instead of all my regular column names that usually would display between the column name elements, it now shows the name 'ReturnCode', which I dont want to see. So I definitly have a formatting problem now. I did follow the examples of the nextRowset code exactly like they are explained, but somehow somethings wrong with the final output. This is very difficult when you are still trying to learn PHP and don't quite understand the placements and syntaxes of everything, but I am closer now than I was before.

So I believe I solved my own question here. It seems the 'Free TDS driver' I am using for my PHP PDO code does not support the use of OUTPUT parameters from MSSQL when first having the PHP execute the MSSQL stored procedure that returns the expected result set. The notes I found to support this was right here on Stack, but the link in those notes explaining why the OUTPUT parameter is not supported say that Microsoft did away with the OUTPUT parameter in MSSQL version 7 and up? Ver7 came out in 1998 I think. I don't know if that part is that true, but I can assure you, in my problem here, the OUTPUT parameter would not function at all. So the alternative was to use the nextRowset command in PHP, but NOT in the way that so many examples show. I had to figure it out myself, trial and error. I do not need a do..while loop and I do not need a while at the end of my original loop. Had to make the SQL code look like this.

CREATE PROCEDURE [dbo].[pIOGetEligSSDOB]
    
    @SSN nvarchar(10),
    @DOB nvarchar(10)   
    
AS
BEGIN
    DECLARE @RCODE int
    SELECT * FROM ZASMasterDB.dbo.Eligibility WHERE SSN = @SSN AND DOB = @DOB
    IF @@ROWCOUNT >= 1
    SET @RCODE = 7
    ELSE
    SET @RCODE = 8
       SELECT @RCODE      

END

Then in PHP I keep my original working loop exactly the way it is and I added the 2 lines after the loop code. I first had to advance to the nextRowset like all of the example code showed, but I then have to go into that next rows column and pull out the single digit return code and put it into my $rcode to finally display on my XML output in the elements.

while($row = $sth->fetch(PDO::FETCH_ASSOC)){
    
    $row_count++;                         
    $col_count          = count($row);
    $column_name_array  = array_keys($row); 
    $row = array_map('trim',$row);      
    $rows_array[]       = join("·", array_values($row));  
} 

   $sth ->nextRowset();
   $rcode = $sth->fetchColumn();

So final output looks like:

PHP PDO Not Finding OUTPUT Parameter From MSSQL Stored Procedure Call

This is the result of the above code. It is perfect, except no OUPUT integer from SQL server to the $RCODE which would then display between the code elements.

This is the result of executing it in SQL Management Studio

Answers (1)

Related Questions