Calling a SQL stored procedure: the callAction( ) method

The callAction( ) method calls the stored procedure inoutproc:

create proc inoutproc @id int, @newname varchar(50), @newhome Address,
 		@oldname varchar(50) output,  @oldhome Address output as
 
 	select @oldname = name, @oldhome = home from xmp where id=@id
 	update xmp set name=@newname, home = @newhome where id=@id

This procedure has three input parameters (@id, @newname, and @newhome) and two output parameters (@oldname and @oldhome). callAction( ) sets the name and home columns of the row of table xmp with the ID value of @id to the values @newname and @newhome, and returns the former values of those columns in the output parameters @oldname and @oldhome.

The inoutproc procedure illustrates how to supply input and output parameters in a JDBC call.

callAction( ) executes the following call statement, which prepares the call statement:

CallableStatement cs = con.prepareCall("{call inoutproc (?, ?, ?, ?, ?)}");

All of the parameters of the call are specified as parameter markers (?).

callAction( ) supplies values for the input parameters using JDBC setInt( ), setString( ), and setObject( ) methods that were used in the doSQL( ), updatAction( ), and selectAction( ) methods:

        cs.setInt(1, id);

        cs.setString(2, newName);

        cs.setObject(3, newHome);

These set methods are not suitable for the output parameters. Before executing the call statement, callAction( ) specifies the datatypes expected of the output parameters using the JDBC registerOutParameter( ) method:

        cs.registerOutParameter(4, java.sql.Types.VARCHAR);

        cs.registerOutParameter(5, java.sql.Types.JAVA_OBJECT);

callAction( ) then executes the call statement and obtains the output values using the same getString( ) and getObject( ) methods that the selectAction( ) method used:

        int res = cs.executeUpdate();

        String oldName = cs.getString(4);

        Address oldHome = (Address)cs.getObject(5);