multiple sql statements in a prepare? 1

[Laeg]

I have some sql statements that I am preparing in code which I pass to a stored procedure to execute via the prepare statement but I keep getting an error, how can I get around this?

create table tblA(a int auto_increment primary key, b int)

CREATE PROCEDURE `foobar`(IN sSQL varchar(1000))
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
begin

SET @sSQL = sSQL;
PREPARE stmt FROM @sSQL;
EXECUTE stmt;

end;

call foobar("insert into tblA(b)values(100);insert into tblA(b)values (200);");

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 ';insert into tblA(b)values (200)' at line 1

 

[r937]

declare a DELIMITER before, and reset it to the semicolon after

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

 

[Laeg]

Sorry I'm not sure I follow, I presume I would be adding this in the stored procedure itself, where exactly would I put this, is it just saying DELIMITER ; before the PREPARE so that it knows to break it up?

 

[r937]

please check the example in da manual --

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

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

 

[Laeg]

Thanks, doesn't seem to work though - I followed the example

Running MySQL 5.0.45-community-nt

delimiter //
create procedure foobar(IN sSQL varchar(1000))
begin

SET @sSQL = sSQL;
PREPARE stmt FROM @sSQL;
EXECUTE stmt;

end;
//
delimiter ;

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 //
create procedure foobar(IN sSQL varchar(1000))
begin

SET @sSQL' at line 1

 

[r937]

i don't understand why you're getting that error -- it seems that mysql doesn't like the DELIMITER command

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

 

[Laeg]

Can anybody else help me here? It seems like the DELIMITER keyword only works from the command line utility, what if I wanted to call this stored procedure from code, anybody any ideas?

Thanks for replies

 

[tsuji]

[tt]create table tblA(a int auto_increment primary key, b int)[highlight];[/highlight]
[red]
DELIMITER //[/red]
[green]set sql_mode=''//[/green]

CREATE PROCEDURE `foobar`(IN sSQL varchar(1000))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
begin

SET @sSQL = sSQL;
PREPARE stmt FROM @sSQL;
EXECUTE stmt;
[red]DEALLOCATE PREPARE stmt;[/red]
end;
[red]//
DELIMITER ;[/red]
[red]-- [/red]call foobar("insert into tblA(b)values(100);insert into tblA(b)values (200);");
[blue]call foobar("insert into tblA(b)values(100);");
call foobar("insert into tblA(b)values(200);");[/blue]
-- or taking advantage of specific insert into syntax
[blue]call foobar("insert into tblA(b) values (110), (220), (330);");[/blue]
[green]call foobar("select * from tblA;");[/green]
[/tt]

 

[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.

 

[DonQuichote]

Can anybody else help me here? It seems like the DELIMITER keyword only works from the command line utility

Correct. DELIMITER is not an SQL command, it is an internal command of the command-line utility. To be consistent, some database front-ends understand this command also, but this is program-specific. You should look in the documentation of the program or library you use for how to separate statements.


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