How do I execute stored procedures?
Created May 4, 2012
Andreas Schaefer Here is an example on how to execute a stored procedure with JDBC (to use this in a servlet is the same the only thing is that you create the connection and callable statement in the init() of the servlet):
package DBTest; import java.sql.*; public class JdbcTest { private String msDbUrl = "jdbc:odbc:ms"; private String msJdbcClass = "sun.jdbc.odbc.JdbcOdbcDriver"; private Connection mcDbAccess; private CallableStatement msProcedure; public JdbcTest() { try { Class.forName( msDbUrl ).newInstance(); mcDbAccess = DriverManager.getConnection( msJdbcClass, "milestone", "milestone" ); msProcedure = mcDbAccess.prepareCall( "{? = call sp_sav_Bom_Header( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" ); msProcedure.registerOutParameter( 1, java.sql.Types.VARCHAR ); msProcedure.setInt( 2, -1 ); msProcedure.setInt( 3, 39 ); msProcedure.setString( 4, "format" ); long ltTest = new java.util.Date().getTime(); System.out.println( "Today: " + ltTest ); msProcedure.setTimestamp( 5, new Timestamp( ltTest ) ); msProcedure.setString( 6, "type" ); msProcedure.setString( 7, "submitter" ); msProcedure.setString( 8, "email" ); msProcedure.setString( 9, "phone" ); msProcedure.setString( 10, "comments" ); msProcedure.setString( 11, "label" ); msProcedure.setInt( 12, 52 ); msProcedure.setBoolean( 13, true ); msProcedure.setBoolean( 14, false ); msProcedure.setInt( 15, 53 ); msProcedure.setString( 16, "runtime" ); msProcedure.setString( 17, "configuration" ); msProcedure.setBoolean( 18, true ); msProcedure.setBoolean( 19, false ); msProcedure.setString( 20, "special instructions" ); msProcedure.setInt( 21, 54 ); ResultSet lrsReturn = null; System.out.println( "Execute: " + (lrsReturn = msProcedure.executeQuery() ) ); while( lrsReturn.next() ) { System.out.println( "Got from result set: " + lrsReturn.getInt( 1 ) ); } System.out.println( "Got from stored procedure: " + msProcedure.getString( 1 ) ); } catch( Throwable e ) { e.printStackTrace(); } } public static void main(String[] args) { new JdbcTest(); } }I also tried it by using a native JDBC driver (i-net) and it also works fine. The only problem we encounter with JDBC-ODBC bridge is that a stored procedure pads spaces to the full length of a VARCHAR but the native JDBC behaves right. Therefore I suggest to use JDBC native drivers.
The above example uses the MS SQL Server.