Executing stored procedures

• If you execute 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. Further, if you mix literals and question marks, you cannot use output parameters with a stored procedure.

  The following 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 the following 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 the following procedures.

  • Use language commands, passing input parameters to them directly from Java variables using the PreparedStatement class. This is illustrated in the following code fragment:

    // 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 5.x and later, use the com.sybase.jdbcx.SybCallableStatement interface, illustrated in this example:

    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();
    

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF