Call a stored procedure

You can use the CallableStatement interface of OceanBase Connector/J to call stored procedures.

CallableStatement provides the following methods for you to call stored procedures: executeUpdate(), executeQuery(), and execute(). execute() is the most flexible method, because it does not require advance information on whether the stored procedures will return result sets.

In the following example, the stored procedure obSp returns the inOutParam value incremented by 1, and the string passed in by using inputParam as ResultSet.

CREATE PROCEDURE obSp(IN inputParam VARCHAR(100),
                        INOUT inOutParam INT)
BEGIN
    DECLARE n INT;
    SET n = inOutParam + 1;
    SET inOutParam = n;
    SELECT inputParam;
    SELECT CONCAT('zhang', inputParam);
END

To call the obSp stored procedure in the example, perform the following steps:

1. Prepare a callable statement by using the Connection.prepareCall() method.

   Here is an example:

   import java.sql.CallableStatement;
   ...
   
       CallableStatement cSt = conn.prepareCall("{call obSp(?, ?)}");
   
       cSt.setString(1, "asdfg");
   

2. Register output parameters, if any.

   To retrieve the values of the output parameters (the OUT or INOUT parameters specified when the stored procedure was created), OceanBase Connector/J requires that the values of the output parameters be specified by using the registerOutputParameter() method in the CallableStatement interface. Here is an example:

   import java.sql.Types;
   ...
       cSt.registerOutParameter(2, Types.INTEGER);
   
       cSt.registerOutParameter("inOutParam", Types.INTEGER);
   ...
   

3. Set input parameters, if any.

   Input and input/output parameters are set in the same way as the PreparedStatement object. Here is an example:

        cSt.setString(1, "asdfg");
   
        cSt.setInt(2, 1);
   ...
   

4. Execute CallableStatement and retrieve any result sets or output parameters.

   Here is an example:

   ...
       boolean obResults = cSt.execute();
   
          while (obResults) {
           ResultSet rs = cSt.getResultSet();
           ...
           obResults = cSt.getMoreResults();
       }
       int outputValue = cSt.getInt(2); 
       outputValue = cSt.getInt("inOutParam"); 
   ...