Re: defining triggers through python/MySQLdb not working

[kao liki]

Arie Kachler Wrote:
-------------------------------------------------------
> Hello,
> I have the following string:
>
> >>> sql
> "DELIMITER
> $$\nCREATE\nDEFINER='root'@'localhost'\nTRIGGER
> `Starling`.`request_node-beforeinsert`\nBEFORE
> INSERT ON `Starling`.`request_node`\nFOR EACH
> ROW\nBEGIN\n DECLARE `var-request_node_id`
> int(10) unsigned;\nDECLARE `var-is_active`
> enum('true','false');\nDECLARE `var-sequence`
> int(10) unsigned;\nDECLARE `var-parent_node_id`
> int(10) unsigned;\nDECLARE `var-type`
> enum('folder','template');\nDECLARE
> `var-content_manager_id` int(10)
> unsigned;\nDECLARE `var-created`
> datetime;\nDECLARE `var-modified`
> datetime;\nDECLARE `var-request_template_id`
> int(11) unsigned;\nDECLARE `var-request_folder_id`
> int(11) unsigned;\n DECLARE `var-_revision`
> BIGINT UNSIGNED;\n DECLARE revisionCursor
> CURSOR FOR SELECT `request_node_id`, `is_active`,
> `sequence`, `parent_node_id`, `type`,
> `content_manager_id`, `created`, `modified`,
> `request_template_id`, `request_folder_id` FROM
> `_revision_request_node` WHERE
> `_revision`=`var-_revision` LIMIT 1;\n \n IF
> NEW.`_revision` IS NULL THEN\n INSERT INTO
> `_revision_request_node` (`_revision_comment`,
> `_revision_user_id`, `_revision_timestamp`) VALUES
> (NEW.`_revision_comment`, @auth_uid, NOW());\n\t
> SET NEW.`_revision` = LAST_INSERT_ID(); \n
> ELSE\n SET `var-_revision`=NEW.`_revision`;\n
> OPEN revisionCursor;\n FETCH
> revisionCursor INTO `var-request_node_id`,
> `var-is_active`, `var-sequence`,
> `var-parent_node_id`, `var-type`,
> `var-content_manager_id`, `var-created`,
> `var-modified`, `var-request_template_id`,
> `var-request_folder_id`;\n CLOSE
> revisionCursor;\n \n SET
> NEW.`request_node_id` = `var-request_node_id`,
> NEW.`is_active` = `var-is_active`, NEW.`sequence`
> = `var-sequence`, NEW.`parent_node_id` =
> `var-parent_node_id`, NEW.`type` = `var-type`,
> NEW.`content_manager_id` =
> `var-content_manager_id`, NEW.`created` =
> `var-created`, NEW.`modified` = `var-modified`,
> NEW.`request_template_id` =
> `var-request_template_id`, NEW.`request_folder_id`
> = `var-request_folder_id`;\n END IF;\n \n
> SET NEW.`_revision_comment` = NULL;\n END;$$\n"
>
> if I print the string, of course, newlines are
> shown as newlines:
>
> >>> print sql
> DELIMITER $$
> CREATE
> DEFINER='root'@'localhost'
> TRIGGER `Starling`.`request_node-beforeinsert`
> BEFORE INSERT ON `Starling`.`request_node`
> FOR EACH ROW
> BEGIN
> DECLARE `var-request_node_id` int(10)
> unsigned;
> DECLARE `var-is_active` enum('true','false');
> DECLARE `var-sequence` int(10) unsigned;
> DECLARE `var-parent_node_id` int(10) unsigned;
> DECLARE `var-type` enum('folder','template');
> DECLARE `var-content_manager_id` int(10)
> unsigned;
> DECLARE `var-created` datetime;
> DECLARE `var-modified` datetime;
> DECLARE `var-request_template_id` int(11)
> unsigned;
> DECLARE `var-request_folder_id` int(11) unsigned;
> DECLARE `var-_revision` BIGINT UNSIGNED;
> DECLARE revisionCursor CURSOR FOR SELECT
> `request_node_id`, `is_active`, `sequence`,
> `parent_node_id`, `type`, `content_manager_id`,
> `created`, `modified`, `request_template_id`,
> `request_folder_id` FROM `_revision_request_node`
> WHERE `_revision`=`var-_revision` LIMIT 1;
>
> IF NEW.`_revision` IS NULL THEN
> INSERT INTO `_revision_request_node`
> (`_revision_comment`, `_revision_user_id`,
> `_revision_timestamp`) VALUES
> (NEW.`_revision_comment`, @auth_uid, NOW());
> SET NEW.`_revision` = LAST_INSERT_ID();
> ELSE
> SET `var-_revision`=NEW.`_revision`;
> OPEN revisionCursor;
> FETCH revisionCursor INTO
> `var-request_node_id`, `var-is_active`,
> `var-sequence`, `var-parent_node_id`, `var-type`,
> `var-content_manager_id`, `var-created`,
> `var-modified`, `var-request_template_id`,
> `var-request_folder_id`;
> CLOSE revisionCursor;
>
> SET NEW.`request_node_id` =
> `var-request_node_id`, NEW.`is_active` =
> `var-is_active`, NEW.`sequence` = `var-sequence`,
> NEW.`parent_node_id` = `var-parent_node_id`,
> NEW.`type` = `var-type`, NEW.`content_manager_id`
> = `var-content_manager_id`, NEW.`created` =
> `var-created`, NEW.`modified` = `var-modified`,
> NEW.`request_template_id` =
> `var-request_template_id`, NEW.`request_folder_id`
> = `var-request_folder_id`;
> END IF;
>
> SET NEW.`_revision_comment` = NULL;
> END;$$
>
> But when I execute this, it's not working. The
> error is very generic but I suspect is being
> caused by the embedded newlines:
>
> >>> cursor.execute(sql)
> Traceback (most recent call last):
> File "", line 1, in
> File
> "build/bdist.macosx-10.6-universal/egg/MySQLdb/cur
> sors.py", line 174, in execute
> File
> "build/bdist.macosx-10.6-universal/egg/MySQLdb/con
> nections.py", line 36, in defaulterrorhandler
> _mysql_exceptions.ProgrammingError: (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 'DELIMITER
> $$\nCREATE\nDEFINER='root'@'localhost'\nTRIGGER
> `Starling`.`request_node-' at line 1")
>
> I know the syntax of the trigger definition is
> correct.
>
> Any ideas how to pass the string to
> cursor.execute() so that newlines are expanded as
> newlines and not as \n inside the string?
>
> Thanks for any insight you can give me.
>
> Arie Kachler


nice