Out parameters in MySQL Stored Procedure help

Trampas

Hi,

I am using MySQL 5.0 along with SQLyog 5.0.22

I have created the following sp

DELIMITER $$

DROP PROCEDURE IF EXISTS `travelagg`.`spInsertBooking`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertBooking`(in intFlightJourneyID int, in datDeptDate int, in datRetDate int, in intAdultNo int, in intChildNo int, in intInfantNo int, out Booking int)
BEGIN
INSERT INTO
booking (FlightJourneyID, DeptDate, RetDate, AdultNo, ChildNo, InfantNo)
VALUES
(intFlightJourneyID, datDeptDate, datRetDate, intAdultNo, intChildNo, intInfantNo);
select MAX(BookingID) into Book from booking;
END$$

DELIMITER ;

The BookingID is an autoincrement column and everytime i enter in a new record I am looking to retrieve back the new BookingID.

I am running the sp by

call spInsertBooking ( 1, 20070305 , 20070308 , 2 , 1 , 1,@Book) ;

But everytime i run the command i get 0 rows affected.

Can someone please help me fix this problem please.

Thanks for your help and time
Gareth

cython

Trampas wrote:

Hi,

I am using MySQL 5.0 along with SQLyog 5.0.22

I have created the following sp

DELIMITER $$

DROP PROCEDURE IF EXISTS `travelagg`.`spInsertBooking`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertBooking`(in intFlightJourneyID int, in datDeptDate int, in datRetDate int, in intAdultNo int, in intChildNo int, in intInfantNo int, out Booking int)
BEGIN
INSERT INTO
booking (FlightJourneyID, DeptDate, RetDate, AdultNo, ChildNo, InfantNo)
VALUES
(intFlightJourneyID, datDeptDate, datRetDate, intAdultNo, intChildNo, intInfantNo);
select MAX(BookingID) into Book from booking;
END$$

DELIMITER ;

The BookingID is an autoincrement column and everytime i enter in a new record I am looking to retrieve back the new BookingID.

I am running the sp by

call spInsertBooking ( 1, 20070305 , 20070308 , 2 , 1 , 1,@Book) ;

But everytime i run the command i get 0 rows affected.

Can someone please help me fix this problem please.

Thanks for your help and time
Gareth

Well I think I can see an error in the prodedure anyway. I think the line where you retrieve the new booking_id should read:

select MAX(BookingID) into Booking from booking;

Do you mind me asking why you are doing this in a procedure? I assume that you are going to be calling this from within another program, yes? What languge is that written in? I ask because some have built in functions to retrieve the value of an auto-increment index generated by an INSERT, in which case you could use a regular insert and one of them to do this more simply

seamus

After you call the procedure, are you running

SELECT @Book

?

Also, best practice would dictate that you don't use

select MAX(BookingID) into Book from booking;

use

select LAST_INSERT_ID();

instead. Reason being that if you insert the row, then immediately someone else inserts another row, the value for MAX(BookingID) will give you the wrong row.

Trampas

Hi,

I am using VB.NET to call the store procedure.

If I use the

select LAST_INSERT_ID();

It gets the inserted record but if i go

select LAST_INSERT_ID() into Book;

I get nothing.

I am running this through the SQLyog software.

DELIMITER $$

DROP PROCEDURE IF EXISTS `travelagg`.`spInsertBooking`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertBooking`(in intFlightJourneyID int, in datDeptDate int, in datRetDate int, in intAdultNo int, in intChildNo int, in intInfantNo int, out Book int)
BEGIN
 INSERT INTO
 booking (FlightJourneyID, DeptDate, RetDate, AdultNo, ChildNo, InfantNo)
 VALUES
 (intFlightJourneyID, datDeptDate, datRetDate, intAdultNo, intChildNo, intInfantNo);
select LAST_INSERT_ID() into Book;
END$$

DELIMITER ;

Thanks for your help.

Trampas

Hi,

I am using VB.NET to call the store procedure.

If I use the

select LAST_INSERT_ID();

It gets the inserted record but if i go

select LAST_INSERT_ID() into Book;

I get nothing.

I am running this through the SQLyog software.

DELIMITER $$

DROP PROCEDURE IF EXISTS `travelagg`.`spInsertBooking`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertBooking`(in intFlightJourneyID int, in datDeptDate int, in datRetDate int, in intAdultNo int, in intChildNo int, in intInfantNo int, out Book int)
BEGIN
 INSERT INTO
 booking (FlightJourneyID, DeptDate, RetDate, AdultNo, ChildNo, InfantNo)
 VALUES
 (intFlightJourneyID, datDeptDate, datRetDate, intAdultNo, intChildNo, intInfantNo);
select LAST_INSERT_ID() into Book;
END$$

DELIMITER ;

Thanks for your help.

seamus

If this works,

select LAST_INSERT_ID()

Then why are you trying to

select LAST_INSERT_ID() into Book

?

And shouldn't that be @Book?

Trampas

Hi Semaus ad thanks for our help

I need to get the new ID out of the stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `travelagg`.`spInsertBooking`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertBooking`(in intFlightJourneyID int, in datDeptDate int, in datRetDate int, in intAdultNo int, in intChildNo int, in intInfantNo int, out Book int)
BEGIN
	INSERT INTO
		booking (FlightJourneyID, DeptDate, RetDate, AdultNo, ChildNo, InfantNo)
	VALUES
		(intFlightJourneyID, datDeptDate, datRetDate, intAdultNo, intChildNo, intInfantNo);
	
	SELECT LAST_INSERT_ID() into @Book;
    END$$

DELIMITER ;

call spInsertBooking ( 1, 20070305 , 20070308 , 2 , 1 , 1, @Book ) ;

Gives me nothing. Is there something wrong with my code??

seamus

The very last line,

After
call spInsertBooking ( 1, 20070305 , 20070308 , 2 , 1 , 1, @Book ) ;

Try
select (@Book);

Trampas

Got that working now. Thaks for your help Seamus