MS SQLSRV Driver and Output Parameters never updated with INSERT

[mark.emery]

My problem is very siimilar to that described in this post: http://www.phpfreaks.com/forums/index.php?topic=292507.0

 

What we have found is that if there is an INSERT statement in the stored procedure, it never returns output to the caller eihter as a return value or an output parameter. Which is a major blow to our project since doing something as obvious as this in the the procedure doesn't work:

 

 

INSERT INTO dbo.tblSMSLog

            (SMSMobile,  SMSContent,  SMSDateTime)    VALUES  (@SMSMobile, @SMSContent, @SMSDateTime);

SET @newLogID = (SELECT SCOPE_IDENTITY() );

RETURN @newLogID

 

Running the procedure in SQL query window confirms the procedure works and returns the value. Running the procedure from PHP the record is inserted with the correct values passed as parameters but nothing is passed back. If the INSERT is removed and a SELECT is done instead a value is passed back.

 

Adding 'SELECT @newLogID as newLogID' before the RETURN also returns a recordset in query window but does not return a recordset to PHP.

 

It appears the presense of an INSERT statement inhibits all output to the caller. Has anyone seen this? Got a clue? or working code that does an INSERT and returns a value?

[mark.emery]

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.