Skip navigation links

MySQL Forums :: Connector/Python :: defining triggers through python/MySQLdb not working


Advanced Search

Re: defining triggers through python/MySQLdb not working
Posted by: kao liki ()
Date: December 31, 2011 03:21AM

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

Options: ReplyQuote


Subject Written By Posted
defining triggers through python/MySQLdb not working Arie Kachler 10/18/2011 01:14PM
Re: defining triggers through python/MySQLdb not working kao liki 12/31/2011 03:18AM
Re: defining triggers through python/MySQLdb not working kao liki 12/31/2011 03:21AM
Re: defining triggers through python/MySQLdb not working thang van 01/28/2012 08:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.