error when trying to use stored procedure

posted 10 years ago

i'm using mysql and a stored procedure. my model looks like this

class TaskAssignment extends Eloquent
{
public static function getTaskAssignmentByUserId($user_id=0)
  {
    return DB::statement('CALL sp_get_task_assignment('.$user_id.')');
  }
}

my controller looks like this

class DashboardController extends BaseController
{
    public function index()
    {
      $my_tasks = TaskAssignment::getTaskAssignmentByUserId(Auth::User()->id);
      return View::make('dashboard.index', compact('my_tasks));
    }
}

i get the following error

SQLSTATE[42000]: Syntax error or access violation: 1370 execute command denied to user ... for routine 'sp_get_task_assignment' (SQL: CALL sp_get_task_assignment(8))

i can run "CALL sp_get_task_assignment(8)" from the error message on mysql workbench and it works as it should. i even game the mysql user all privileges and it still gives me that error. any ideas?

Last updated 2 years ago.

w1n78

replied 10 years ago

Solution

found my error. i was querying the wrong database so it did exist but my app was using a different database where the stored procedure didn't exist.

i also switched out DB::statement for DB::select. with DB::statement i get a true/false result instead of a resultset.

Last updated 2 years ago.

hemantshekhawat

replied 10 years ago

Hey,

I'm using Eloquent ORM along with SLIM.

This is my AppModel

use Illuminate\Database\Eloquent\Model as Eloquent;

/**
 * Description of GetPostProcedure
 *
 * @author hemant
 */
class GetPostProcedure extends Eloquent {

    public function getPost($user_id, $post_id) {

        $SQL = 'get_view_user_post(' . $post_id . ',' . $user_id . ',5.5)';

//        return $this->select('call ' . $SQL, [$post_id, $user_id]); // call type 1

        return DB::select('call ' . $SQL); // call type 2
    }

}

in Call type 1, i get back an object with no data at all. and in type 2, i get error

Fatal error: Class 'DB' not found in /var/www/html/new-mentor/app/models/GetPostProcedure.php on line 26

what can be done here??

Moderators