Versions Compared

Old Version 1

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Jenni Wallin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

...

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


Info
titleExample - Using sql_proc

Definition of a Stored Procedure, declared in the database:

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



Note
titleNote!

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.

...

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.

...

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.

...

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.

...

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
titleNote!

The return value must be type casted.


getUpdateCount

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

...

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
Anchor
getError
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.

...

Info
titleExample - Handle error raised by Stored Procedure

Stored Procedure definition:

Code Block
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
Anchor
DBErrorUDR
DBErrorUDR

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

...