title: Laravel 5.1 How do you create a MySQL stored procedure with migration
body: <p>Hello I'm trying to create a stored procedure using Laravel 5.1 migrations. So far I tried with DB::connection()->getPdo()->exec(), DB::raw() and DB::unprepared(), but no luck. Here's how my code looks like: </p>
  
  <p>
  
  <pre><code>use Illuminate\Database\Schema\Blueprint;
  use Illuminate\Database\Migrations\Migration;
  use Wryttnapp\Models\User;
  
  
  class AddCentroidFieldsToUsersTable extends Migration
  {
      /**
       * Run the migrations.
       *
       * @return void
       */
      public function up()
      {
          DB::connection()-&gt;getPdo()-&gt;exec("
              ALTER TABLE `users` 
              ADD COLUMN centroid POINT NULL 
              AFTER `avatar`;");
  
          DB::unprepared("SET @OLD_SQL_MODE=@@SQL_MODE;
  
              DELIMITER ;;
  
              DROP PROCEDURE IF EXISTS UPDATE_USER_CENTROID;;
  
              CREATE PROCEDURE UPDATE_USER_CENTROID(userId INT)
                NOT DETERMINISTIC
              BEGIN
                DECLARE bDone INT;
  
                DECLARE tempLatLon VARCHAR(100);
                DECLARE counter INT;
                DECLARE firstLatLon VARCHAR(100);
                DECLARE polygonDefinition TEXT;
                DECLARE geometry POINT;
  
                DECLARE curs CURSOR FOR SELECT DISTINCT CONCAT(`latitude`, ' ', `longitude`) FROM `user_locations` WHERE `user_id` = userId ORDER BY `id` ASC;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  
                OPEN curs;
  
                SET bDone = 0;
                SET counter = 0;
                SET polygonDefinition = ',';
                FETCH curs INTO tempLatLon;
  
                WHILE NOT bDone DO
                  SET polygonDefinition = CONCAT(polygonDefinition, ',', tempLatLon);
  
                  IF counter = 0 THEN
                        SET firstLatLon = tempLatLon;
                  END IF;
                  SET counter = counter + 1;
  
                  FETCH curs INTO tempLatLon;
                END WHILE;
  
                CLOSE curs;
  
                SET polygonDefinition = CONCAT(polygonDefinition, ',', firstLatLon);
                SET polygonDefinition = SUBSTRING(polygonDefinition, 3);
                SET polygonDefinition = CONCAT('POLYGON((', polygonDefinition, '))');
  
                SET geometry = ST_Centroid(ST_GeomFromText(polygonDefinition));
  
                UPDATE `users` SET centroid = geometry WHERE id = userId;
              END;;
  
              DELIMITER ;
              ");
      }
  
      /**
       * Reverse the migrations.
       *
       * @return void
       */
      public function down()
      {
          DB::connection()-&gt;getPdo()-&gt;exec("
              ALTER TABLE `users` DROP COLUMN centroid;
              DROP INDEX centroid_index ON `users`;
              DROP FUNCTION IF EXISTS `UPDATE_USER_CENTROID`;");
      }
  }
  </code></pre>
  
  <p>Please help! Thanks!</p>