Laravel 5.1 How to create a MySQL stored procedure with migration

Question

Hello, I am trying to create a stored procedure using Laravel 5.1 migration. So far I have tried with DB :: connection () → getPdo () → exec (), DB :: raw () and DB :: unprepared (), but no luck. This is what my code looks like:

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()->getPdo()->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()->getPdo()->exec(" ALTER TABLE `users` DROP COLUMN centroid; DROP INDEX centroid_index ON `users`; DROP FUNCTION IF EXISTS `UPDATE_USER_CENTROID`;"); } } 

Please, help! Thanks!

Answer 1

Here is how I did the procedures in the migrations so far ... I believe that I had problems setting the delimiters, so I threw them and used different unprepared() for each statement.

 $procedure = " CREATE PROCEDURE `procedure_name`(procedure_param_1 TEXT, procedure_param_2 TEXT) BEGIN // Your SP here END "; DB::unprepared("DROP procedure IF EXISTS procedure_name"); DB::unprepared($procedure); 

This seems to be due to: Laravel raw sql memory transfer error