none
Return value param from stored procedure is not updated when a value is returned

    Question

  • Another question here has several examples that claim a return code from stored procedure works: http://social.msdn.microsoft.com/Forums/br/sqldriverforphp/thread/56b671ac-0027-41f1-8d92-7f0a0ba1b779

    I have similar code:

     $proc = "{? = call spSMSlog( ?, ?, ?)}";

      // Build params
       $proc_result=10;  // Set as integer
       $params = array(
                  array(&$proc_result, SQLSRV_PARAM_OUT), //pass by ref
                  array($Phone, SQLSRV_PARAM_IN),
                  array($Content, SQLSRV_PARAM_IN),
                  array($DateTimeSentSQL, SQLSRV_PARAM_IN)
                 );
      // Prepare the procedure
      if ($proc_worked = sqlsrv_prepare( $conn, $proc, $params))
      {
          echo "Statement prepared.<br>\n";
      }
      else
      {
          echo "Statement could not be prepared.\n";
          die( print_r( sqlsrv_errors(), true));
      }

      // Execute the procedure
      if ( sqlsrv_execute($proc_worked))
      {
          echo "Statement executed.<br>\n";
      }
      else
      {
          echo "Statement could not be executed.\n";
          die( print_r( sqlsrv_errors(), true));
      }

      /* Display the value of the output parameter  */
      echo "Proc_result: ".$proc_result.'<br>';

     

    The result I get is that the stored procedure has worked as expected with the input parameters as specified. I have confirmed in SQL Mgr Query window that the correct return code is returned by the procedure. However, in PHP the return code parameter is never updated when the stored procedure is executed successfully.

    I have PHP 5.3.5 running Windows Server 2008 R2 with SQLSRV build 2.0.1802 I have run it against two SQL 2008 servers build 10.0.2573 & build 10.50.1600 and both give the same result which leads me to think the issue is in SQLSRV DLL.

    Output produced when above runs:

    Statement prepared.
    Statement executed.
    Proc_result: 10

    If the return value was updated it would be much greater than 10.

    Has anyone got return value coming back from stored procedures in current releases of PHP/SQL?


    Friday, July 15, 2011 3:17 AM

Answers

  • Found a post elsewhere with a clue, the INSERT retuns an dempty rowset which must be consumed to get to the return value. This code works:

       $proc = '{:retval = CALL spSMSLog (:phone, :txt, :stamp) }';
       $stmt = $conn->prepare( $proc );
       $stmt->bindParam(':retval',&$ReturnValue ,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,4);
       $stmt->bindParam(':phone',$Phone,PDO::PARAM_STR);
       $stmt->bindParam(':txt',$Content,PDO::PARAM_STR);
       $stmt->bindParam(':stamp',$DateTimeSent,PDO::PARAM_STR);
     
       // Execute the procedure
           try {
             $stmt->execute();
           }
           catch (PDOException $e) {
             echo 'Execute #2 failed: ' . $e->getMessage();
             die( );
           }
         $stmt->nextRowset(); //skip INSERT result
         $stmt->nextRowset();
         $result = $stmt->fetch(PDO::FETCH_ASSOC);
      /* Display the value of the output parameter  */
           echo "Return value: ".$ReturnValue.'<br>';

    There was also something hinky with param length, seems you must specify length of output param and not specify length of input params. Gave up trying to get a handle on that when it started working.


    Cheers, Mark.
    • Marked as answer by MarkEmery Wednesday, July 20, 2011 6:47 AM
    Wednesday, July 20, 2011 6:47 AM

All replies

  • I have also tried this same exercise using PDO inspired by this post http://social.msdn.microsoft.com/Forums/en-CA/sqldriverforphp/thread/aac57e8a-4232-45b1-98c6-83491fc7c49d

    with identicial results:

       $proc = "{? = call spSMSlog( ?, ?, ?)}";
       $stmt = $conn->prepare( $proc );

      // Build params
       $proc_result=10;  // Set as integer
       $stmt->bindParam(1,$proc_result,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,100);
       $stmt->bindParam(2,$Phone,PDO::PARAM_STR);
       $stmt->bindParam(3,$Content,PDO::PARAM_STR);
       $stmt->bindParam(4,$DateTimeSentSQL,PDO::PARAM_STR);
       try {
        $stmt->execute();
       }
       catch (PDOException $e) {
         echo 'Execute failed: ' . $e->getMessage();
         die( );
       }

    The procedure runs as expected and works fine but the return value param is still not updated.

    FetchAll and nextRowset methods did not update the output parmeters . The stored procedure does not return a resultset so there should be nothing to consume anyway.

    We've been day and a half trying to over come this one issue that has crippled the development project.

    Any clues anyone how to get the return value?

     

    Friday, July 15, 2011 6:04 AM
  • I have since confirmed that no output parameters return a value from called procedures when the procedure works as expected otherwise.

    The PDO DLL php_pdo_sqlsrv_53_nts_vc9 is version 2.0.1802.200.

    Any idea where I should look for the source of this problem?



    Monday, July 18, 2011 12:30 AM
  • Starting to suspect a bug here. We have found that putting a RETURN with a value in the stored procedure at the start of the procedure returns the value correctly. If the RETURN statement is moved to after the database has been accessed (INSERT) and the RETURN value is indicating how that went, the variable in the calling progam is not updated even though a value is returned.

    This matches the sample code examples in these forums that show trival procedures returning a value without any database activity. We've confrmed these trivial examples work. Where it fails is when the stored procedure has done something useful. Then it does not return a value to the caller.

    So we have confirmed out code to call the procedure works proviided the procedure is trivial. We've confirmed our non-trivial procedure returns the correct value when run. What's left is a bug in between not passing the return value through.


    Cheers, Mark.
    • Edited by MarkEmery Tuesday, July 19, 2011 2:57 AM
    Monday, July 18, 2011 4:11 AM
  • Also found the when the stored procedure returns a rowset with SELECT the corresponding PDO->FETCH statement receives a null value instead of an array with the select result.

    It appears the problem is quite general in that no output is returned to the calling program when database activity occurs in the procedure.

    Anyone got real live instances of this code returning values with latest versions of PHP/SQLSRV  ?


    Cheers, Mark.
    Monday, July 18, 2011 5:02 AM
  • Further investigation reveals that a stored procedure that ONLY contains a SELECT statement can return information to the caller. If there is an INSERT statement present no data is returned to the caller at all, neither return value nor recordset from a SELECT that follows the INSERT.

    I'm declaring this a bug, I see no way forward writing a viable application using PHP with SQL server on Windows platform.


    Cheers, Mark.
    Tuesday, July 19, 2011 2:03 AM
  • Found a post elsewhere with a clue, the INSERT retuns an dempty rowset which must be consumed to get to the return value. This code works:

       $proc = '{:retval = CALL spSMSLog (:phone, :txt, :stamp) }';
       $stmt = $conn->prepare( $proc );
       $stmt->bindParam(':retval',&$ReturnValue ,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,4);
       $stmt->bindParam(':phone',$Phone,PDO::PARAM_STR);
       $stmt->bindParam(':txt',$Content,PDO::PARAM_STR);
       $stmt->bindParam(':stamp',$DateTimeSent,PDO::PARAM_STR);
     
       // Execute the procedure
           try {
             $stmt->execute();
           }
           catch (PDOException $e) {
             echo 'Execute #2 failed: ' . $e->getMessage();
             die( );
           }
         $stmt->nextRowset(); //skip INSERT result
         $stmt->nextRowset();
         $result = $stmt->fetch(PDO::FETCH_ASSOC);
      /* Display the value of the output parameter  */
           echo "Return value: ".$ReturnValue.'<br>';

    There was also something hinky with param length, seems you must specify length of output param and not specify length of input params. Gave up trying to get a handle on that when it started working.


    Cheers, Mark.
    • Marked as answer by MarkEmery Wednesday, July 20, 2011 6:47 AM
    Wednesday, July 20, 2011 6:47 AM
  • Glad you were able to figure it out.

    As for the insert returning an empty row set, it's actually returning a row count which must be consumed/skipped.  If you wish to not have the row count returned, you can use "SET NOCOUNT ON" and row counts from operations such as INSERT, UPDATE, and DELETE will not be returned.

    As for output parameters requiring a length, that is how PDO works according to the docs in PDOStatement::bindParam, specifically the instructions on the length parameter.  If you don't think it's working as described and have a code sample, I'd like to see it.  I wouldn't want a bug in binding parameters.

    Thanks for your feedback and let us know if there is anything else we can look at or help with (though admittedly you helped yourself on this issue :)).

    Jay

     


    Jay Kint MSFT
    Wednesday, July 20, 2011 3:53 PM
    Moderator
  • Thanks for the hint on SET NOCOUNT I've updated my code to be a bit simpler. The documentation on how all this works leaves a very great much to be desired. There is no way I could have ever guessed this is how it works from the documentation available at present. Believe me, I've spent two whole days scouring it.

    The whole time we were trying to work out what was going on here the called procedure was storing records with the parameters supplied, except when we were playing around with bindings to see if that was the issue. When parameter sizes are specified the procedure does not store records and the exception handler is not tripped.

       $stmt->bindParam(':retval',&$ReturnValue ,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,4);
       $stmt->bindParam(':phone',$Phone,PDO::PARAM_STR,10);
       $stmt->bindParam(':txt',$Content,PDO::PARAM_STR,160);
       $stmt->bindParam(':stamp',$DateTimeSent,PDO::PARAM_STR,255);

    The STR sizes specified are the actual sizes of the NVARCHAR fields in the database. One thing I did notice in SQL profiler trace was that when a field size is specified on INPUT params they appear to be changed to OUTPUT params, at least on the SQL server side.


    Cheers, Mark.




    Wednesday, July 20, 2011 11:35 PM