Execute Stored Procedures

Executing a stored procedure in a CallableStatement object that represents parameter values as question marks, you get better performance than if you use both question marks and literal values for parameters.

Also, if you mix literals and question marks, you cannot use output parameters with a stored procedure.

This example creates sp_stmt as a CallableStatement object for executing the stored procedure MyProc:

CallableStatement sp_stmt = conn.prepareCall( 
   "{call MyProc(?,?)}");

The two parameters in MyProc are represented as question marks. You can register one or both of them as output parameters using the registerOutParameter methods in the CallableStatement interface.

In this example, sp_stmt2 is a CallableStatement object for executing the stored procedure MyProc2.

CallableStatement sp_stmt2 = conn.prepareCall( 
   {"call MyProc2(?,'javelin')}");

In sp_stmt2, one parameter value is given as a literal value and the other as a question mark. You cannot register either parameter as an output parameter.

To execute stored procedures with RPC commands using name-binding for parameters, use either of these procedures:

Use language commands, passing input parameters to them directly from Java variables using the PreparedStatement class.

// Prepare the statement
System.out.println("Preparing the statement...");
String stmtString = "exec " + procname + " @p3=?, @p1=?";
PreparedStatement pstmt = con.preparedStatement(stmtString);
 
// Set the values
pstmt.setString(1, "xyz");
pstmt.setInt(2, 123);
 
// Send the query
System.out.println("Executing the query...");
ResultSet rs = pstmt.executeQuery();

With jConnect version 6.05 and later, use the com.sybase.jdbcx.SybCallableStatement interface:

import com.sybase.jdbcx.*;

....

// prepare the call for the stored procedure to execute as an RPC

String execRPC = "{call " + procName + " (?, ?)}";
SybCallableStatement scs = (SybCallableStatement)
con.prepareCall(execRPC);

// set the values and name the parameters

// also (optional) register for any output parameters
scs.setString(1, "xyz");
scs.setParameterName(1, "@p3");
scs.setInt(2, 123);
scs.setParameterName(2, "@p1");

// execute the RPC
// may also process the results using getResultSet()
// and getMoreResults()

// see the samples for more information on processing results
ResultSet rs = scs.executeQuery();