Creating a database function within Magento for a module

Question

I have a working module in Magento that is modeled after some custom code we use outside of our install. This module currently add 5 tables to the database to store info and I have extended the Admin to CRUD the info. The ultimate goal here is to move the majority of this custom programming into Magento.

Currently our custom code sits outside Magento and hits a separate database. This database has those same 5 tables, a stored procedure, and 4 functions. What I would like to do now is move the stored procedures and the functions into Magento's database and change the custom code to call all of it's data from Magento's db. However, I can't seem to figure out how the "CREATE FUNCTION" call should be set up for Magento to execute it properly.

The SQL I am using is:

DROP FUNCTION IF EXISTS {$this->getTable('fn_Get_HardinessZone')};
CREATE FUNCTION {$this->getTable('fn_Get_HardinessZone')}(IN ZipCode varchar()) RETURNS integer AS
  DECLARE Result integer;       
BEGIN
  SELECT  MAX(Zone) into Result
  FROM    AMI_zones
  WHERE      (Hfzip <= LEFT(ZipCode, 5)) AND (Htzip >= LEFT(ZipCode, 5));
  if Result is null or Result < 1 or (Result > 11 and Result <> 99) Then
    /*if the left most character is alpha, then set the zone to 98 for Canada*/
   if Left(zipCode, 1) >= 'A' and LEFT(zipcode,1) <= 'Z' THEN
     set result = 98;
   else          
    set Result = 99;
   End if;
  END if;
  RETURN Result;
END;

But this always generates the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'IN ZipCode varchar()) RETURNS'

So what is the proper way to format a SQL call to be run in a module's install/update script to insert a function or stored procedure into Maganeto's database?

Answer 1

The problem is with your SQL statement:

You have an error in your SQL syntax; check the manual ... for the right syntax to use near

'IN ZipCode varchar()) RETURNS'

I would recommend running the SQL through PhpMyAdmin or on the command line until you get it right, then run it through Magento. This man page describes the syntax of CREATE FUNCTION: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html. When testing stored procedures /functions in the mysql client (or PhpMyAdmin) be sure to change the delimiter so that the semicolons in your function body are interpreted correctly.

The below SQL worked for me. The things I changed from your original statement are:

1. IN is not allowed in function declarations (IN ZipCode varchar())
2. I was required to explicitly state length of the varchar
3. The DECLARE belongs inside the function
4. I am guessing that your function is DETERMINISTIC, meaning it will always produce the same results for the same input parameters. If this is not the case, remove DETERMINISTIC from the RETURNS line

Give this a shot:

DROP FUNCTION IF EXISTS {$this->getTable('fn_Get_HardinessZone')};
CREATE FUNCTION {$this->getTable('fn_Get_HardinessZone')} (ZipCode VARCHAR(15))
  RETURNS INTEGER DETERMINISTIC
BEGIN
  DECLARE result INTEGER;
  SELECT  MAX(Zone) INTO result
  FROM    AMI_zones
  WHERE   (Hfzip <= LEFT(ZipCode, 5)) AND (Htzip >= LEFT(ZipCode, 5));
  IF result IS NULL OR result < 1 OR (result > 11 AND result <> 99) THEN
    /* if the left most character is alpha, then set the zone to 98 for Canada */
    IF LEFT(ZipCode, 1) >= 'A' AND LEFT(ZipCode, 1) <= 'Z' THEN
      SET result = 98;
    ELSE
      SET result = 99;
    END IF;
  END IF;
  RETURN result;
END;