How to call a parameterized stored procedure using model in laravel

Question

I made a store procedure in mysql named 'Insertusers' having 8 parameters in it.

Now i want to call this procedure using my model and insert value in table using this procedure.

Here is my Model's code :

<?php
    class Insertuser extends Eloquent {
        protected $guarded = array();

 public static function storedProcedureCall() {
         return DB::statement('Insertusers');
    } 
 public static $rules = array();
            }
?>

Here is my Controllers' code :

public function createUser() {
$clan = new Insertuser;
            $clan->clanname = Input::get('clanname');
            $clan->description = Input::get('tbx_new_clan_Des');
            if (Input::get('chk_new_clan_Status')) {
                $clan->active = true;
            } else {
                $clan->active = false;
            }
            if (Input::get('chk_new_clan_Incoming')) {
                $clan->allow_incoming = true;
            } else {
                $clan->allow_incoming = false;
            }
            if (Input::get('chk_new_clan_key')) {
                $clan->clan_key = Input::get('tbx_new_clan_key');
            }
            $clan->created_by = Session::get('userid');
            $clan->last_updated_by = Session::get('userid');
            $clan->DOMType=1;
            $clan->save();

}

When i tried to create a user, i am getting the following error :

SQLSTATE[42S02]: Base table or view not found.

Please help me out. Thanks.

Is your table name "Insertusers"?

Laurence
– Laurence

2014-06-06 11:14:01 +00:00
Commented Jun 6, 2014 at 11:14 — Laurence
– Laurence, Commented Jun 6, 2014 at 11:14
No, table name is users, InsertUsers is Procedure's name.

deepak.mr888
– deepak.mr888

2014-06-06 11:16:09 +00:00
Commented Jun 6, 2014 at 11:16 — deepak.mr888
– deepak.mr888, Commented Jun 6, 2014 at 11:16

Nikunj Kumar · Accepted Answer · 2014-06-06 12:19:49Z

MSSQL requires that variables within procedures be DECLAREd and folks use the @Variable syntax (DECLARE @TEXT VARCHAR(25) = 'text'). Also, MS allows for declares within any block in the procedure, unlike mySQL which requires all the DECLAREs at the top.

While good on the command line, I feel using the "set = @variable" within stored procedures in mySQL is risky. There is no scope and variables live across scope boundaries. This is similar to variables in JavaScript being declared without the "var" prefix, which are then the global namespace and create unexpected collisions and overwrites.

I am hoping that the good folks at mySQL will allow DECLARE @Variable at various block levels within a stored procedure. Notice the @ (at sign). The @ sign prefix helps to separate variable names from table column names - as they are often the same. Of course, one can always add an "v" or "l_" prefix, but the @ sign is a handy and succinct way to have the variable name match the column you might be extracting the data from without clobbering it.

MySQL is new to stored procedures and they have done a good job for their first version. It will be a pleaure to see where they take it form here and to watch the server side aspects of the language mature.

deepak.mr888 · Accepted Answer · 2014-06-06 12:11:54Z

I got the solution :

Here what i did in my controller :

 $clan_key;
            $clanname=Input::get('clanname');
            $description=Input::get('tbx_new_clan_Des');
            if (Input::get('chk_new_clan_Incoming')) {
                $allow_incoming = true;
            } else {
               $allow_incoming= false;
            }
             if (Input::get('chk_new_clan_key')) {
               $clan_key = Input::get('tbx_new_clan_key');
            }

            $created_by= Session::get('userid');
            $last_updated_by = Session::get('userid');
                    if (Input::get('chk_new_clan_Status')) {
                $active = true;
            } else {
                $active = false;
            }

            $DOMType=1;
            Clan::storedProcedureCall($clanname, $description, $allow_incoming, $clan_key,$created_by, $last_updated_by, $active, $DOMType);

Here is my Model :

class Clan extends Eloquent {
    protected $table = 'clans';

    public static function storedProcedureCall($clanname,$description,$allow_incoming,$clan_key,$created_by,$last_updated_by,$active,$DOMType) {
         return DB::select('call Insertusers(?,?,?,?,?,?,?,?)',array($clanname,$description,$allow_incoming,$clan_key,$created_by,$last_updated_by,$active,$DOMType));
    }
}

Laurence · Accepted Answer · 2014-06-06 11:17:57Z

0

No, table name is users, InsertUsers is Procedure's name.

Well in the code you have posted, you have not told Laravel that. Try this:

class Insertuser extends Eloquent {

   protected $table = 'users';

answered Jun 6, 2014 at 11:17

Laurence

60.2k22 gold badges178 silver badges215 bronze badges

3 Comments

deepak.mr888 Over a year ago

i am able to insert values in 'users' table using this syntax, but i want to insert values in 'users' table using 'Insertusers' procedure.

Laurence Over a year ago

Well you need to check the Insertusers procedure - it probably has the table name there?

deepak.mr888 Over a year ago

Yes, if i just call that procedure in mysql and pass values in that procedure, it is inserting the values in 'users' table.

Collectives™ on Stack Overflow

How to call a parameterized stored procedure using model in laravel

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related