Is there a way to generate stored MYSQL procedures in a Laravel 4 migration?
For example, here's a simple procedure generation query stored as a string (via a Heredoc)
$query = <<<SQL
DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test()
BEGIN
INSERT INTO `test_table`(`name`) VALUES('test');
END$$
DELIMITER ;
SQL;
DB:statement(DB::RAW($query));
When Running this in a migration's up() function I get this error:
There are two major problems with your code
DELIMITER is not a valid sql statement. It's just a MySql client command. So just don't use it. BTW the error you get tells you exactly that.DB::statement to execute CREATE PROCEDURE code because it uses prepared statement source code for Connection. You can use PDO exec() DB::connection()->getPdo()->exec() insteadThat being said a sample migration for imaginary tags table might look like this
class CreateTagsTable extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('tags', function($table){
$table->increments('id');
$table->string('name')->unique();
});
$sql = <<<SQL
DROP PROCEDURE IF EXISTS sp_insert_tag;
CREATE PROCEDURE sp_insert_tag(IN _name VARCHAR(32))
BEGIN
INSERT INTO `tags`(`name`) VALUES(_name);
END
SQL;
DB::connection()->getPdo()->exec($sql);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
$sql = "DROP PROCEDURE IF EXISTS sp_insert_tag";
DB::connection()->getPdo()->exec($sql);
Schema::drop('tags');
}
}