Return a value from a insert store procedure 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

 

[Diancecht]

A stored procedure returns a ResultSet like any other JBDC call: here you have an example of how to retrieve the info:

http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html

Cheers,
Dian

 

[sedj]

You don't need a resultset (or cursor) in the db, just declare an 'out' parameter in the SP, then something like :

out_id number;

select id into out_id
from table_name t
where name = t.name
and email = t.email


and then in the java side, do a :

stmt.registerOutParameter(3, Types.NUMBER)


--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[steve1rm]

Hello

Thanks for you respone.

However, I am using a insert into not a select statement.

When I insert a name and e-mail, I would like to return the auto incremented ID number.

Thanks for your help,

Steve

 

[sedj]

Yes, so like I said, you put the value of the auto-increment value into an out parameter, and bind the statement by registering the out parameter in java.

No idea in MySQL, but in Oracle this would look like :

	procedure example_proc( 	in_name in varchar2,
					in_addr in varchar2,
					out_seq_code out number)
	is
	begin

		
		select seq_code.nextval into out_seq_code from dual;
	
		insert into my_table values (	out_seq_code,
						in_name,
						in_addr);						
		 						
		commit;
						
						
	end;

try
        {
            CallableStatement cs = conn.prepareCall( "{ call example_proc(?,?,?) }" );
            
            cs.setString( 1, name );
            cs.setString( 2, email );
            cs.registerOutParameter(3, Types.NUMBER);
            cs.execute();

            // your sequence code is here
            int iSeqCode = cs.getInt(3);
            
        }

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[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

 

[sedj]

Cool, Thanks for posting back the MYSQL solution.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk