CodeIgniter Forums

Full Version: CI Migration that defines a MySQL function
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Unknown[/eluser]
We are using database migrations for our project and I need to define a MySQL function as part of the migration. I am getting a syntax error when I try to change the delimiter as is common when defining a function in SQL. The error is:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$' at line 1"

I don't care how I have to do it, I just need this code to execute as a migration. Any suggestions are welcome.

Would it be possible to keep the SQL in some "migration.sql" file and use CI to tell MySQL to read from that file?

Here is the raw SQL from mysql_dump that needs to be executed:

Code:
DELIMITER $$
DROP FUNCTION IF EXISTS `default_workload`$$
CREATE FUNCTION `default_workload`(`uid` INT, `eid` INT) RETURNS int(11)
  READS SQL DATA
  BEGIN
  DECLARE n integer;
  SELECT U.id into n
  FROM default_workloads_users U
  INNER JOIN (SELECT workload_type
    FROM default_workloads_category
    WHERE exercise_category_id = get_parent_category(eid)
    ORDER BY id ) C ON U.type = C.workload_type
    where U.user_id=uid
    LIMIT 1;
  RETURN (n);
   END$$


Here is what my migration looks like (which gives me a syntax error on the DELIMITER line):


Code:
class Migration_Workloads_Changes extends CI_Migration
{
public function up()
{
  $this->db->trans_start();
  $SQL[] = ' DELIMITER $$';
  $SQL[] = ' DROP FUNCTION IF EXISTS `default_workload`$$ '
  $SQL[] =
<<<SQL
  CREATE FUNCTION `default_workload`(`uid` INT, `eid` INT) RETURNS int(11)
  READS SQL DATA
  BEGIN
  DECLARE n integer;
  SELECT U.id into n
  FROM default_workloads_users U
  INNER JOIN (SELECT workload_type
    FROM default_workloads_category
    WHERE exercise_category_id = get_parent_category(eid)
    ORDER BY id ) C ON U.type = C.workload_type
    where U.user_id=uid
    LIMIT 1;
  RETURN (n);
  END$$
SQL;
  
      foreach($SQL as $query) {
       $this->db->query($query);
      }
  $this->db->trans_complete();
}
}

El Forum

[eluser]Unknown[/eluser]
Got it, just had to get rid of the DELIMITER lines altogether. Apparently the mysqli driver is ok defining stored procedures with lots of ';'s everywhere...