How To Call Oracle Stored Procedure In Yii

Yii 1.1 General Discussion
1

[b]i create a procedure in oracle db with parameters and how can call this procedure in YII

please help me ?[/b]

2

Hi

try this


$command = Yii::app()->db->createCommand('call yourproc(param)');

$command->execute();

//OR

$command = Yii::app()->db->createCommand('SELECT * FROM yourproc(param)');

$command->queryRow();
3

Thanks

4

Please tell us the results!

Other members may know if it works! :)

Thanks

5

how to get return parameter in my function?

iam create a procedure in oracle such as,


create or replace procedure Proc1(p_out out number)

      is

      begin

        p_out := 123;

        

        

   end;

 /

how can get p_out value in my page ?

6

See if example 4 on this page helps.

7

I didn’t try, but you could check that


$valrefer=null;

$command = Yii::app()->db->createCommand('call yourproc(:valrefer)');

$command->bindParam(":valrefer",$valrefer,PDO::PARAM_STR);

$command->execute();

echo $valrefer;
8

thank you for your response

i tried this code but an error occurred like this,




CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 3131 OCIStmtExecute: ORA-03131: an invalid buffer was provided for the next piece

(ext\pdo_oci\oci_statement.c:148). The SQL statement executed was: call Proc1(:valrefer)
9

[b]

Finally i got a solution for procedure calling , thanks for all your valuable response, my code is

Hope anyone to help this code Thank you

[/b]




    $pvStatus=''; //out 

        $pvPassword='***';  // In

        $pvUsername='***';  //  In

        $command = Yii::app()->db->createCommand('call LoginAction(:pvUsername,:pvPassword,:pvStatus)');


        $command->bindParam(":pvUsername",$pvUsername,PDO::PARAM_STR);

        $command->bindParam(":pvPassword",$pvPassword,PDO::PARAM_STR);


        $command->bindParam(":pvStatus",$pvStatus,PDO::PARAM_STR, 10); 


        $command->execute();


        echo $pvStatus;
10

hello friend,

I am new to yii framework, I’m trying to implement the code but can not I write this code in the model.

want to run the procedure from a button on the view and return me a message and takes me to index my application

know as I do?

urgent please

thank you very much :(

11

create a function in your controller and put your procedure call code

like this





$command = Yii::app()->db->createCommand

            ('call Your_Function_Name (:nId,:pvDatabase,:pvUsername,:pvPassword)');// call the procedure

                $pvId= $_POST['LoginForm']['database'];

                $command->bindParam(":nId",$pvId,PDO::PARAM_INT,15); // bind parameter

                $command->bindParam(":pvDatabase",$get_dbsname,PDO::PARAM_STR,100); // out parameter

                $command->bindParam(":pvUsername",$get_dbuname,PDO::PARAM_STR, 100); // out parametermust have enter size exc(10)

                $command->bindParam(":pvPassword",$get_dbpass,PDO::PARAM_INT, 100); // out parameter

                $command->execute(); // execute the cammand

just print the $command variable , you will get the out variable values of the procedure

then redirect to the view page




$this->redirect(array('create','any_parameter'=>$your_value));

you can get $your_value variable in view

12

Thanks for your prompt help (Rathilesh).

want to call the function that has the procedure from a CLinkColumn and send the post id. like this:

code in admin.php

array(

‘class’=>‘CLinkColumn’,

‘header’ => ‘Aprobar’,

‘imageUrl’=>Yii::app()->baseUrl.’/images/approved.png’,

‘urlExpression’=>‘Yii::app()->controller(“actionCallProcedure”,array(“id”=>$data->ID_MENSAJES))’,

‘htmlOptions’=>array(‘style’=>‘text-aling:center’, ‘onclick’=>‘prueba();’),

),

I think the (‘urlExpression’=>‘Yii::app()->controller->createUrl(“actionCallProcedure”,array(“id”=>$data->ID_MENSAJES))’,) not so good

you see I’m trying to call this function in my controller called “actionCallProcedure” which has this code:

code in controller

public function actionCallProcedure($id){

 $command = Yii::app()->db->createCommand


 ('call EttcPkDaMensajSigem.atenderMensaje (:id)');// call the procedure


 $command->bindParam(":id",$id,PDO::PARAM_INT,15); // bind parameter


 $command->execute(); // execute the cammand


        $this->renderPartial('admin', array('model'=>$model)); //redirect to admin

}

error: The system is unable to find the requested action “CallProcedure”. <_<

appreciate your help :(