Stored Procedure

[Laeg]

I want to add a record to tblA and insert the identity field generated into tblB. If there are 2 successful INSERTs then I'd like a true/false boolean returned. How do I do this in mysql with a stored procedure, in/out parameters and transactions?

tblA
A int auto_increment primary key NOT NULL

tblB
B int

 

[DonQuichote]

From the top of my head:

BEGIN;
INSERT INTO tblA() VALUES();
SET @Id=LAST_INSERT_ID();
INSERT INTO tblB(B) VALUES(@Id);
COMMIT;

Note that I do not return TRUE. I could do that with SELECT TRUE, but it would be a bit odd. If there is any error (insufficient rights, for instance) the commit will never occur and you will get an error. Without knowing which program or driver you use to feed MySQL the queries, I cannot tell you how exactly errors are passed back. You can instruct drivers or programs to ignore errors and continue, but you'd have to look in the appropriate documentation.

The INSERT statement for table tblA may seem odd without any fields, but it is perfectly valid.

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

 

[Laeg]

That did not work. Maybe there is some MySQL level setting that says don't commit after a statement until you see the commit command but for me when I added a column name error to the tblB INSERT statement my tblA INSERT statement had worked and had not been rolled back.

 

[dbNuge]

first of all, I'm using MySQL 5.1.32 on my Apple Mac with Leopard.

give this a try...

CREATE TABLE a (aid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10));
CREATE TABLE b (bid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, aid INT NOT NULL);

DELIMITER //

CREATE PROCEDURE testx()
BEGIN
INSERT INTO a (name) VALUES ('AAA');
SET @id=LAST_INSERT_ID();
INSERT INTO B (aid) VALUES (@id);
COMMIT;
END;
//

DELIMITER ;

CALL testx();

Best of luck and I hope this helps!
nuge