StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreating a database function within Magento for a module
text
Body
copied!<p>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. </p> <p>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. </p> <p>The SQL I am using is: </p> <pre><code>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; </code></pre> <p>But this always generates the following error: </p> <pre><code>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' </code></pre> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload