[SOLVED] How can I add stored procedures to mysql

[cve]

Hi guys, I'm working with my new module and I need to "inject" stored procedures during install process of module into mysql database... I have that code:
(my $tableName is cms_module_sklep_nested_category and $this->db is $this->GetDb(); in my construct which invoke parent contruct())

Code: Select all

if (!isset($gCms)) exit;
$tableName = $this->_getTableName();
$sql[] = 'DELIMITER ;';

$sql[] = 'DROP PROCEDURE IF EXISTS REMOVE_NODE_ONLY;';

$sql[] = 'DELIMITER $$';
$sql[] = '
CREATE PROCEDURE REMOVE_NODE_ONLY(IN $name VARCHAR(20))
BEGIN
	START TRANSACTION;
	SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1, @myParent := parent FROM '.$tableName.' WHERE name = $name;
	DELETE FROM '.$tableName.' WHERE lft = @myLeft;
	UPDATE '.$tableName.' SET rgt = rgt - 1, lft = lft - 1, parent = @myParent WHERE lft BETWEEN @myLeft AND @myRight;
	UPDATE '.$tableName.' SET rgt = rgt - 2, parent = @myParent WHERE rgt > @myRight;
	UPDATE '.$tableName.' SET lft = lft - 2, parent = @myParent WHERE lft > @myRight;
	COMMIT;
END $$
';
$sql[] = 'DELIMITER ;';
foreach($sql as $q) {
    $this->db->Execute($q);
}

My tables and other stuff was added, but stored procedures doesn't... MySql ver. is 5.1.4. What is wrong... :/ I don't know...

[calguy1000]

a: Don't use $this->db it may disappear in the future.
$db = cmsms()->GetDb();
but the $db variable is given to you in an a modules install method, so you don't even have to do that

b: Trap the result of the execute statement, and if it failed display the error message from mysql:

Code: Select all

$db = cmsms()->GetDb();
foreach($sql as $q) {
    $dbr = $db->Execute($q);
    if( !$dbr )
      {
          die('Error executing query: '.$db->sql.'<br/>'.$db->ErrorMsg());
      }
}

[cve]

Thanks calguy1000 for your prompt, that was very helpful. It works without DELIMITER syntax...

Code: Select all

$sql[] = 'CREATE PROCEDURE ADD_CHILD(IN $container VARCHAR(20), IN $name VARCHAR(20))
BEGIN
	START TRANSACTION;
	SELECT @myLeft := lft FROM '.$tableName.' WHERE name = $container;
	UPDATE '.$tableName.' SET rgt = rgt + 2 WHERE rgt > @myLeft;
	UPDATE '.$tableName.' SET lft = lft + 2 WHERE lft > @myLeft;
	INSERT INTO '.$tableName.'(name, lft, rgt, parent) VALUES($name, @myLeft + 1, @myLeft + 2, $container);
	COMMIT;
END';