Callable Statements(3.2)

Owned by Jenni Wallin
Jun 12, 2023
5 min read

Callable Statements enable usage of Stored Procedures with output parameters.

The Callable Statement functions are used execute stored procedures and to manage the result. The DBErrorUDR is used to handle SQL errors.

prepareCall

To prepare a call with an out parameter, the Stored Procedure must be defined with the prepareCall function.

any CallableStatement.prepareCall
    ( string  dbProfile , 
      string  sqlProc(?,?) , 
      boolean  captureSQLError  (optional) ,
	  boolean isFunction (optional) ,
	  boolean inclResultParam (optional) )
ParameterDescription

dbProfile

Name of the database, including the folder name

sqlProc(?,?)

Name of the stored procedure, including a question mark for each parameters it requires


Example - Using sql_proc

Definition of a Stored Procedure, declared in the database:

create or replace procedure sql_proc(i int, OUT o char) is   
    begin
      . . .
      o:='value for o';
    end;
/

Note!

The number of question marks must match the number of defined parameters in the Stored Procedure.

captureSQLError

Optional parameter that controls error handling. If the parameter's value is set to true, any SQL error gets captured, without disrupting the execution. For more information about how to fetch the SQL error code and message, see the section below, getError. This parameter is set to false by default.

isFunction Optional parameter that indicates that the call will be made for a stored function. This parameter is set to false by default.
inclResultParamOptional parameter that you can set to true to apply a result parameter of ?= on the JDBC API stored procedure SQL escape syntax. If the isFunction parameter is set to true, the inclResultParam will be set to true by default.

Returns

Callable Statement Identifier. This object is threadsafe and is used when executing calls towards the stored procedure.

execute

The execute function maps to the corresponding JDBC API and could differ slightly depending on the JDBC driver.

The function is used for questions and lookups. If the database should be updated, use the function executeUpdate instead.

execute can handle several OUT parameters including a table format.

Note!

The table format OUT parameter is applicable only when using a PostgreSQL database.


To execute a call with the parameters expected by the stored procedure, the parameters must be specified in correct order.

any CallableStatement.execute
   (any csi, 
	any param1, ..., any paramN)
ParameterDescription

csi

The Callable Statement Identifier that is returned from the prepareCall function

paramN

The values expected by the stored procedure declared in the prepareCall function. Parameters registered as out parameters in the stored procedure must be omitted.

The parameters must have the same type as defined in the stored procedure.

Returns

The returned value is the Result Identifier of the execution. A new object is returned for every call executed.

executeQuery

The executeQuery function maps to the corresponding JDBC API and could differ slightly depending on the JDBC driver.

The function is used for questions and lookups. If the database should be updated, use the function executeUpdate instead.

executeQuery can handle several OUT parameters except a table format.

To execute a call with the parameters expected by the stored procedure, the parameters must be specified in correct order.

any CallableStatement.executeQuery
 (any csi, 
 any param1, ..., any paramN)
ParameterDescription

csi

The Callable Statement Identifier that is returned from the prepareCall function

paramN

The values expected by the stored procedure declared in the prepareCall function. Parameters registered as out parameters in the stored procedure must be omitted.

The parameters must have the same type as defined in the stored procedure.

Returns

The returned value is the Result Identifier of the execution. A new object is returned for every call executed.

executeUpdate

The executeUpdate function maps to the corresponding JDBC API and could differ slightly depending on the JDBC driver.

The function is used for updating the database.

To execute a call with the parameters expected by the stored procedure, the parameters must be specified in correct order.

any CallableStatement.executeUpdate
 (any csi, 
 any param1, ..., any paramN)
ParameterDescription

csi

The Callable Statement Identifier that is returned from the prepareCall function

paramN

The values expected by the stored procedure declared in the prepareCall function. Parameters registered as out parameters in the stored procedure must be omitted.

The parameters must have the same type as defined in the stored procedure.

Returns

The returned value is the Result Identifier of the execution. A new object is returned for every call executed.

get

The get function is used to retrieve the result from the executed call.

any CallableStatement.get
 (any resultIdentifier, 
 int spIndex)
ParameterDescription

resultIdentifier

The Result Identifier that is returned from the executeUpdate function

spIndex

Index of the requested parameter from the stored procedure (type int). The first parameter has index 1.

Returns

The value of the out parameter

Note!

The return value must be type casted.

getUpdateCount

This function returns the number of rows that was affected by the executeUpdate function.

int CallableStatement.getUpdateCount(any resultIdentifier);
ParameterDescription

resultIdentifier

The Result Identifier that is returned from the executeUpdate function

Returns

The number of rows in the database that was affected by the call. If an update exists -1 will be returned.

getError


This function will capture potential SQL errors from the executeUpdate function and return a UDR that contains both the error code and the error message.

DatabaseFunctions.DBErrorUDR = CallableStatement.getError(any resultIdentifier);
ParameterDescription

resultIdentifier

The Result Identifier that is returned from the executeUpdate function.

Returns

Returns an error UDR. For more information about the error UDR, see the section below, DBErrorUDR.

If no error occurred, null will be returned.

Example - Handle error raised by Stored Procedure

Stored Procedure definition:

create or replace procedure upd_item(id int, amount int) is
  begin
    if amount > 110000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Amount is to high!');
    end if;                
    ...
  end;
/
APL Code:
any s;
DatabaseFunctions.DBErrorUDR error;
initialize {
  s = CallableStatement.prepareCall("p.db","UPD_ITEM(?,?)",true);
}
consume {
  ...
  any result = CallableStatement.executeUpdate(s, id, amount);
  error = CallableStatement.getError(result);
  if (error != null) {
     //handle error
     if (error.ErrorCode == -20001) {
        udrRoute(input, "adjust_amount")
     } else {
        abort(error.ErrorCode + error.ErrorMessage);
     }
  } else {
     //no error -let's proceed 
     int cnt = CallableStatement.getUpdateCount(result); 
     ...
  }
}

DBErrorUDR

If the executeUpdate function generates an SQL error, the getError function will generate a DBErrorUDR.

The following fields are included in the DBErrorUDR:

FieldDescription
ErrorCode (int)The SQL error code.
ErrorMessage (string)The SQL error message.