Calling Stored Procedure with Out Parameter Using Pdo

Calling stored procedure with Out parameter using PDO

It would seem that there is a bug at work here, best solution I've found is this:

http://www.php.net/manual/en/pdo.prepared-statements.php#101993

From the comment at the link above:

$dbh->query("CALL SomeStoredProcedure($someInParameter1, $someInParameter2, @someOutParameter)"); 
$dbh->query("SELECT @someOutParameter");

// OR, if you want very much to use PDO.Prepare(),
// insert "SELECT @someOutParameter" in your stored procedure and then use:

$stmt = $dbh->prepare("CALL SomeStoredProcedure(?, ?)"); 
$stmt ->execute(array($someInParameter1, $someInParameter2));

See also this: https://stackoverflow.com/a/4502524/815386

PHP 7 & MSSQL PDO: Calling a stored procedure with input and output params

You can specify your params following the documentation pages :

http://php.net/manual/en/pdo.prepared-statements.php

http://php.net/manual/en/pdostatement.bindparam.php

Namely using the following code :

<?php
$stmt = $dbh->prepare("CALL MyStoredProc(?, ?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
$value = 'hello';
$stmt->bindParam(2, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n & $value";
?>

This has one out parameter and one in/out paramater, so should be easily adaptable for your needs. They key is simply to setting the correct data_type and length value( arguments 3 & 4 in bindParam() )

Full list of datatypes:

http://php.net/manual/en/pdo.constants.php

Note: this is taken from docs and adapted, but I Haven't tested it, but hopefully at the very least it sets you in the right direction.

API Laravel - Call Store Procedure with IN and OUT parameter using OCI8 or PDO

I had ditch Eloquent and resort to vanilla PDO solution which works without errors.

public function cekLogin(Request $request) {
    $pdo = DB::getPdo();

    $pIsSuccess = "";
    $pMsg = "";
    $pLastScope = "";
    $pParam = "";

    $procedureName = 'myschema.VERIFY_LOGIN';
    
    $userLogin = $request['userLogin'];
    $passLogin = $request['passLogin'];
    $userLogin = $request['userLogin'];
    
    $stmt = $pdo->prepare("begin " . $procedureName . " (:pUserLogin, :pUserPassword, :pIPLogin, :pIsSuccess, :pMsg, :pLastScope, :pParam); end;");
    $stmt->bindParam(':pUserLogin', $userLogin, PDO::PARAM_STR);
    $stmt->bindParam(':pUserPassword', $passLogin, PDO::PARAM_STR);
    $stmt->bindParam(':pIPLogin', $userLogin, PDO::PARAM_STR);
    $stmt->bindParam(':pIsSuccess', $pIsSuccess, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    $stmt->bindParam(':pMsg', $pMsg, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    $stmt->bindParam(':pLastScope', $pLastScope, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    $stmt->bindParam(':pParam', $pParam, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
    
    $stmt->execute();

    return response()->json([
        'IsSuccess' => $pIsSuccess, 
        'Msg' => $pMsg, 
        'LastScope' => $pLastScope, 
        'Param' => $pParam, 
    ], 422);
}

PHP PDO cannot get OUT parameter value

The problem was that the first query that is calling the stored procedure is not considered as finished and closed, and PDO will not execute another query until the previous query is done.

The solution was to add $proc->closeCursor();

The whole working sample is:

$input = 5;
$mydb = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass");
$proc = $mydb->prepare("CALL proc_name($input, @o_code, @o_message)");
$proc->execute();
$proc->closeCursor();

$output = $mydb->query("select @o_code, @o_message")->fetch(PDO::FETCH_ASSOC);
var_dump($output); // array('@o_code'=>value, 'o_message'=>value)

How to call the stored procedure function by passing parameter using PDO

See the php docs for an example:

$stmt = $dbh->prepare("CALL calcArea(?)");
$stmt ->execute(array($s_id));

OR

$stmt = $dbh->prepare("CALL calcArea(?)");
$stmt->bindParam(1, $s_id, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 32)
$stmt->execute();
var_dump($value);//<- result is now in $value

Calling stored procedures with an OUT parameter in PHP

As an example to illustrate it, I will a real world practical example(inserting data into an order table and returning the lastInsertId).

CREATE PROCEDURE `shopping_cart_create_order`(IN `inCartId` INT(11), OUT `newOrderId` INT(11)) BEGIN
    DECLARE newOrder int;
    -- Insert a new record into orders and obtain the new order ID
    INSERT INTO orders (created_on) VALUES (NOW());

    -- Obtain the new Order ID
    SELECT LAST_INSERT_ID() INTO newOrder;

    SET newOrder = newOrderId;
END

At PHP level// Probably at the Model/Entity level First, we need to execute the

shopping_cart_create_order()

stored procedure. Which might probably be in a function.

Second, to get the last order id, we need to query it from the variable

@oid

. It is important that we must call the method

closeCursor()

of the PDOStatement object in order to execute the next SQL statement.

function query($pdo, $sql, $parameters = []){
    $query = $pdo->prepare($sql);
    $query->execute($parameters);
    return $query;
}

function create_order($pdo, $cart_id){
    // Binding the parameters
    $parameters = [':cart_id' => $cart_id];

    // calling stored procedure command
    $sql = 'CALL shopping_cart_create_order(:cart_id)';

    // prepare for execution of the stored procedure, pass value to the command 
    and execute the Stored Procedure
    $query = query($pdo, $sql, $parameters);

   // Then close Cursor. It is important for you to close it.
    $query->closeCursor();

   // execute the second query to get last insert id
    $row = $pdo->query("SELECT @oid AS oid")->fetch();
    return $row;    
}

---

Related Topics