Yii return/output from MSSQL Stored Procedures

Yii 1.1 General Discussion
1

I created simple stored Procedure to test:




USE [Test]

GO


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


ALTER PROCEDURE [dbo].[phpTest] 

	@ResultValue int OUTPUT

AS

BEGIN

	SET NOCOUNT ON;


	SET @ResultValue=13

	RETURN 13;

END

And it is working just fine inside Microsoft Studio:


DECLARE @output int

DECLARE @return int


EXEC @return = phpTest @ResultValue = @output OUTPUT


SELECT @return AS 'return', @output AS 'output'

But now I need to get either return or output into the Yii.

Alot of options tested:




	    $sql = "DECLARE @output int;DECLARE @return int;EXECUTE @return = phpTest @ResultValue = @output OUTPUT"; // either variables or question marks or :variable

	     

	    $connection = Yii::app()->db;

	    $command = $connection->createCommand($sql);

		$command->bindParam(1,$returnVariable,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,2); //with or without this

		$command->bindParam(':returnVariable',$returnVariable,PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,100); // or with :variable


		$command->execute(); //with or without this

	    $result = $command->queryScalar();


	    pr($returnVariable);die;

Anyone that has any working option?

2

Hallo Constantiff

i have been solved like your case.

you can try my code to call return output from stored procedure.

this code work for me.




  public function actionCreate() {

        $model = new Transactions;

        if (isset($_POST['Transactions'])) {


            $custId = Yii::app()->user->mysite;

            $transactionNumber = null;

            $conn = new PDO(Yii::app()->db->connectionString, Yii::app()->db->username, Yii::app()->db->password);


            $sqlHeader = $conn->prepare("declare @transactionNumber nvarchar(11); exec spSetCustomerTransaction ?, @transactionNumber output; select ? = TransactionNumber from CustomerTransactions where TransactionNumber = @transactionNumber;");

            $sqlDetail = $conn->prepare("exec spSetCustomerTransactionDetails ?, ?, ?;");

            $sqlToken = $conn->prepare("exec spSetToken ?");

            $sqlDelsessionCart = $conn->prepare("exec spDelSessionCart ?, ?");


            $waiting = true;

            while ($waiting) {

                try {

                    $conn->beginTransaction();

                    $sqlHeader->bindParam(1, $custId, PDO::PARAM_STR);

                    $sqlHeader->bindParam(2, $transactionNumber, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 2100);

                    $valid = $sqlHeader->execute();




                    $total = count($_POST['ProdId']);

                    $i = 0;

                    for ($i = 0; $i <= $total; $i++) {

                        if (isset($_POST['ProdId'][$i])) {

                            $ModelLooping = new Transactions();

                            $ModelLooping->ProdId = $_POST['ProdId'][$i];

                            $ModelLooping->Quantity = $_POST['Quantity'][$i];


                            $sqlDetail->bindParam(1, $transactionNumber, PDO::PARAM_STR);

                            $sqlDetail->bindParam(2, $ModelLooping->ProdId, PDO::PARAM_STR);

                            $sqlDetail->bindParam(3, $ModelLooping->Quantity, PDO::PARAM_INT);

                            $valid = $sqlDetail->execute() && $valid;

                        }

                    }

                    $sqlToken->bindParam(1, $transactionNumber, PDO::PARAM_STR);

                    $valid = $sqlToken->execute() && $valid;


                    $total = count($_POST['ProdId']);

                    $i = 0;

                    for ($i = 0; $i <= $total; $i++) {

                        if (isset($_POST['ProdId'][$i])) {

                            $Model2 = new Transactions();

                            $Model2->ProdId = $_POST['ProdId'][$i];

                            $Model2->Quantity = $_POST['Quantity'][$i];


                            $sqlDelsessionCart->bindParam(1, $custId, PDO::PARAM_STR);

                            $sqlDelsessionCart->bindParam(2, $Model2->ProdId, PDO::PARAM_STR);

                            $valid = $sqlDelsessionCart->execute() && $valid;

                        }

                    }




                    if ($valid) {

                        $conn->commit();

                        $this->redirect(Yii::app()->createUrl('/b2b/order/val/id/' . $transactionNumber));

                        #$this->redirect(Yii::app()->createUrl('/b2b/order/ver/id/' . $custId));

                    } else {

                        $this->redirect(array('create'));

                    }


                    $waiting = false;

                } catch (PDOException $e) {

                    if (stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) {

                        $conn->commit();


                        usleep(250000);

                    } else {

                        $conn->rollBack();

                        throw $e;

                        $this->redirect(array('create'));

                    }

                }

            }

        }

        $this->render('create', array(

            'model' => $model,

        ));

    }