One for the MySQL Gurus! 1

[Laeg]

I have a lot of SQL INSERT statements that have been generated dynamically in code. I want to pass this string of generated queries to a stored procedure to run them, BUT within a transaction. Can this be done?? So something like

-- Create the table
CREATE TABLE tblA(a int auto_increment primary key, b int)

-- Create stored procedure
CREATE PROCEDURE `foobar`(IN sSQL varchar(1000))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
BEGIN
     ROLLBACK;
END;

    SET AUTOCOMMIT = 0;

[COLOR=red]    START TRANSACTION;[/color]
          BEGIN
		SET @sSQL = sSQL;
		PREPARE stmt FROM @sSQL;
		[COLOR=red]EXECUTE stmt;[/color]
		COMMIT;
          END;
END;

-- Call procedure with generated mysql
call foobar([b]"declare MyID int;insert into tblA(b)values(100);set MyID = LAST_INSERT_ID();insert into tblA(b)values (MyID);"[/b]);

/*
-- SQL that is being run
DECLARE MyID int;
INSERT INTO tblA(b)values(100);
SET MyID = LAST_INSERT_ID();
INSERT INTO tblA(b)values (MyID);
*/

Is there a better way of executing these dynamic sql statements? I've been at this problem for ages now! Any help appreciated, thanks

 

[r937]

you've been at this problem for ages, but i'm not sure you've defined the problem

by passing a dynamic sql string into the procedure, you take away most of the benefit of a stored procedure

what's wrong with just executing the queries without the bother of the stored procedure?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tsuji]

Refer to "12.7. SQL Syntax for Prepared Statements" (various versions).
quote
[tt][blue]SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by “;” characters).[/blue][/tt]
unquote
It is not supported even in v6.0.
ref

http://dev.mysql.com/doc/refman/6.0/en/sql-syntax-prepared-statements.html

This is also an issue in your other thread:

http://tek-tips.com/viewthread.cfm?qid=1550189&page=1

If you're determined to send to the sp with a bunch of sql statements separated by semi-colon, you would have to split it. Mysql does not have a built-in split() function, you can refer to user comments in the manual such as section 11.4 string functions:
ref

http://dev.mysql.com/doc/refman/6.0/en/string-functions.html

for inspiration.

 

[Laeg]

r937: I need a stored procedure because I want my insert statements to be wrapped in a transaction, plus they are
cascading sql statements, the identity field of one insert
becomes part of the next insert statement.

tsuji: Thanks for the detailed responses, to both threads, if it is unsupported I will have to look at an alternative.

Thanks to both of you.

 

[ingresman]

Does "stand alone" SQL not support transactions ?????

 

[DonQuichote]

Mabye it helps to come up with the real-world example. If there are just two tables, see the combination of INSERT .. ON DUPLICATE KEY and LAST_INSERT_ID(). I don't grasp what exactly you are trying to do...

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)