Welcome Guest, Not a member yet? Register   Sign In
CI Migration that defines a MySQL function
#1

[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();
}
}


Messages In This Thread
CI Migration that defines a MySQL function - by El Forum - 10-03-2013, 03:13 PM
CI Migration that defines a MySQL function - by El Forum - 10-03-2013, 03:52 PM



Theme © iAndrew 2016 - Forum software by © MyBB