Use a out parameter in an insert into statement 1

[steve1rm]

Hello,

I have a stored procedure that inserts a new passenger into a database. The fields are name and email. The passengerID is a auto increment. The database I am using is mySQL.

The problem is that I want to return the passengerID when the stored procedure is called. I am not sure if that is possible. Here is my code:
Store procedure in mysql

DELIMITER $$

DROP PROCEDURE IF EXISTS `flightdb`.`spInsertNewPassenger`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertNewPassenger`(_name varchar(50), _email varchar(5) )
BEGIN
	INSERT INTO Passengers ( name, email )
	VALUES ( _name, _email );	
END$$

DELIMITER ;

The stored procedure is called from a java program below:

try
		{
			CallableStatement cs = conn.prepareCall( "{ spInsertNewPassenger( ?, ?) }" );
			
			cs.setString( 1, name );
			cs.setString( 2, email );
			
			cs.executeUpdate(); //I want to return the ID here
			
		}

Can anyone help change the above code so that i can return the ID number.

Thanks in advance,

Steve

 

[KarveR]

this link here is probably appropriate for you.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

You could use:
[tt]
DELIMITER $$

DROP PROCEDURE IF EXISTS flightdb.spInsertNewPassenger $$

CREATE DEFINER=root@localhost PROCEDURE spInsertNewPassenger
(IN _name VARCHAR(50), IN _email VARCHAR(50), OUT id INT)
BEGIN
INSERT passengers (name,email) VALUES (_name,_email);
SELECT LAST_INSERT_ID() INTO id;
END $$

DELIMITER ;
[/tt]
You could then call the procedure with:
[tt]
CALL spInsertNewPassenger('Joe Bloggs','joe@bloggs.com',@id);

SELECT @id;
[/tt]

 

[steve1rm]

Thanks for your help,

Just 1 question.

When retrieve the integer value. Will the code below do?

CallableStatement cs = conn.prepareCall( "{ spInsertNewPassenger( ?, ?, @ID) }" );
            
            cs.setString( 1, name );
            cs.setString( 2, email );
            
            IDNumber = cs.executeUpdate(); //Return the ID number

Thanks in advance,

Steve

 

[TonyGroves]

No, the procedure does not return the id number, it stores it into the variable @id. You then need to run the last query shown to get the value of @id.

 

[steve1rm]

Hello TonyGroves,

Maybe I am just being stupid, but I don't seem to understand what I am supposed to do with this output parameter.

In my code I have this:

try
		{
			CallableStatement cs = conn.prepareCall( "spInsertNewPassenger( ?, ? )" );
			
			cs.setString( 1, name );
			cs.setString( 2, email );
			
			cs.executeUpdate(); 
			
			select id; //Error here		
		}

I don't think don't think this is right. As i as a compile error when I run the above code.

Please could you write me some code. The store procedure in mySql is ok, that work fine. But is the java code I am getting confused about.

Thanks very much,

Steve

 

[TonyGroves]

I'm not a Java user, but I'm quite sure "select id;" is not valid Java code. I gave "SELECT @id" as SQL code, not Java. It should be treated as a normal SQL select query, which will return one record containing one field, "@id", containing the ID value in question.

 

[steve1rm]

Hello,

Thanks for all your help, the problem is solved and the code is below.

DELIMITER $$

DROP PROCEDURE IF EXISTS `flightdb`.`spInsertNewPassenger`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spInsertNewPassenger`(IN _name varchar(50), IN _email varchar(50), OUT IDNumber int)
BEGIN
	
	INSERT INTO Passengers ( passengerName, email ) VALUES ( _name, _email );	
	
	SELECT LAST_INSERT_ID() INTO IDNumber;
END$$

DELIMITER ;

int passengerID = 0;
		
		try
		{
			CallableStatement cs = conn.prepareCall( "{ call spInsertNewPassenger( ?, ?, ? ) }" );
			
			cs.setString( 1, passengerName );
			cs.setString( 2, email );
			cs.registerOutParameter(3, Types.INTEGER );
			cs.execute(); 
			
			passengerID = cs.getInt( 3 );
		}
		catch( SQLException ex )
		{
			System.out.println( ex.getMessage() );
		}
		
		return passengerID;

Steve