How do I read an output param whose type is uniqueidentifier?

Question

I'm trying to execute a stored procedure on SQL Server using PDO. Everything runs fine, but when I try to read the output parameter (whose type is UNIQUEIDENTIFIER) the only thing I get is a NULL.

I've tried running my script on Debian 9 with PHP 7.0 and Ubuntu 18.10 with PHP 7.2 and changing the PDO type of my parameter, with no success.

$order_uid = null;
$sql = "EXEC spInsertOrder ?, ?, ?, ?...";
$stmt = $db->prepare($sql);

$stmt->bindParam(29, $order_uid, PDO::PARAM_INPUT_OUTPUT | PDO::PARAM_STR, 50);
if ($stmt->execute() === false) {
    echo $stmt->errorCode();
    print_r($stmt->errorInfo());
}

I expect to get the UUID that SQL Server emits, instead this error raises:

Fatal error: Uncaught PDOException: SQLSTATE[IMSSP]: An invalid type for parameter 5 was specified. Only booleans, integers, floating point numbers, strings, and streams may be used as parameters.

Answer 1

Before you read the value of your OUTPUT parameter, you need to consider the following:

• If your stored procedure executes SELECT statements, you need to consume all results with PDOStatement::nextRowset, before accessing the value of your output parameter.
• If your statement executes INSERT or UPDATE statements, put SET NOCOUNT ON as first line in your procedure to stop SQL Server to return the count of the affected rows as a resultset.
• set your PHP variable to null.

Working example (tested with PHP 7.1.12 and PHP Driver for SQL Server (PDO) 4.3.0+9904):

T-SQL:

CREATE PROCEDURE [dbo].[sp_UID]
    @id UNIQUEIDENTIFIER OUTPUT
AS BEGIN
    SET NOCOUNT ON
    SET @id = NEWID()
END

PHP:

<?php
# Connection info
$server   = 'server\instance,port';
$database = 'database';
$uid      = 'uid';
$pwd      = 'pdw';

# Connection
try {
    $dbh = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
    die("Error connecting to SQL Server. ".$e->getMessage());
}

# Stored procedure
try {
    $sql = "{CALL sp_UID(?)}";
    $uid = null;

    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(1, $uid, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 36);
    $stmt->execute();
    // If your procedure returns result set, you need to fetch result and then get the value for your output parameter
    /*
    do {
        while ($row = $stmt->fetch( PDO::FETCH_ASSOC )) {
        }
    } while ($stmt->nextRowset());  
    */
} catch( PDOException $e ) {
    die( "Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;

# End
$dbh = null;
echo $uid;
?>

Output:

F689A035-C3DB-4D4E-88FB-52F5DA133FA8