CODEWITHSUNDEEP
phpsqldb2
Geoff_S
Geoff_S

Reputation: 5105

Return variable from stored procedure call as output value for variable

I'm currently running a stored procedure, which runs successfully, but after the call to a separate procedure I'd like to return the value of V_R_ID as an output value to be stored in PHP

So in this example:

P1 : BEGIN ATOMIC

DECLARE V_H_ID INTEGER DEFAULT 0 ;
DECLARE V_R_ID VARCHAR ( 20 ) ;
DECLARE V_TIMESTAMP TIMESTAMP ;

DECLARE C_NAME VARCHAR ( 45 ) DEFAULT 'EMAIL' ;

SELECT CAST ( NEXT VALUE FOR SCHEMA . H_ID_SEQUENCE AS VARCHAR ( 20 ) )
INTO V_R_ID
FROM SYSIBM . SYSDUMMY1 ;

CALL PROCEDURESCHEMA . INSERT_TYPE ( 
V_R_ID
, C_NAME
, IN_DATA
, NULL
, NULL ) ;

/*Here I'd like to declare V_R_ID as an output of RETURN_ID */

END P1

How can I properly do this so that after calling this procedure in PHP I can then set the RETURN_ID as a php variable $returnID?

Upvotes: 1

Views: 1943

Answers (1)

Parfait
Parfait

Reputation: 107767

Consider declaring an output parameter which from DB2 docs:

Output-only parameters, which return values from the stored procedure to the calling program.

CREATE PROCEDURE myProc (OUT V_R_ID  VARCHAR (20))

   LANGUAGE SQL 

P1 : BEGIN ATOMIC

   DECLARE V_H_ID INTEGER DEFAULT 0 ;
   DECLARE V_R_ID VARCHAR ( 20 ) ;
   DECLARE V_TIMESTAMP TIMESTAMP ;

   DECLARE C_NAME VARCHAR ( 45 ) DEFAULT 'EMAIL' ;

   SELECT CAST ( NEXT VALUE FOR SCHEMA . H_ID_SEQUENCE AS VARCHAR ( 20 ) )
   INTO V_R_ID
   FROM SYSIBM . SYSDUMMY1 ;

   CALL PROCEDURESCHEMA . INSERT_TYPE ( 
      V_R_ID
    , C_NAME
    , IN_DATA
    , NULL
    , NULL ) ;

END P1

Then in PHP, using PDO prepared statement, call the procedure with ? placeholder and bind it to a PHP variable:

$dbh = new PDO("ibm:DRIVER={IBM DB2 ODBC DRIVER};DATABASE=****;" .
               "HOSTNAME=##.##.##.###;PORT=####;PROTOCOL=TCPIP;", "user", "pass");

$stmt = $dbh->prepare("CALL myProc(?)"); 
$stmt->bindParam(1, $return_id, PDO::PARAM_STR, 20); 
$stmt->execute();

echo $return_id;

Similarly but slightly different, with PHP's IBM DB2 module connection bind parameter to a string name of variable:

$conn = db2_connect($database, $user, $password);

$stmt = db2_prepare($conn, "CALL myProc(?)");
db2_bind_param($stmt, 1, "return_id", DB2_PARAM_OUT);
db2_execute($stmt);

echo $return_id;

Upvotes: 1

Related Questions