Callable Statements
Callable Statements enable usage of Stored Procedures with output parameters.
The Callable Statement functions are used execute stored procedures and to manage the results. The DBErrorUDR is used to handle SQL errors.
Note
Please refer to the Notes section on the Database Functions page for details on allowed database data type.
The following functions for Database Callable Statement described here are:
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) )Parameter | Description |
|---|---|
| Name of the database, including the folder name |
| 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. |
| 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 |
isFunction | Optional parameter that indicates that the call will be made for a stored function. This parameter is set to |
inclResultParam | Optional parameter that you can set to true to apply a result parameter of |
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)Parameter | Description |
|---|---|
| The Callable Statement Identifier that is returned from the |
| The values expected by the stored procedure declared in the 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) Parameter | Description |
|---|---|
| The Callable Statement Identifier that is returned from the |
| The values expected by the stored procedure declared in the 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)Parameter | Description |
|---|---|
| The Callable Statement Identifier that is returned from the |
| The values expected by the stored procedure declared in the 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) Parameter | Description |
|---|---|
| The Result Identifier that is returned from the |
| Index of the requested parameter from the stored procedure (type |
Returns | The value of the out parameter Note! The return value must be type casted. |
getUpdateCount
This function returns the number of rows that were affected by the executeUpdate function.
int CallableStatement.getUpdateCount(any resultIdentifier);Parameter | Description |
|---|---|
| The Result Identifier that is returned from the |
Returns | For Oracle databases, it returns this statement: The number of rows in the database that were affected by the call. If an update exists, -1 will be returned. For MySQL and PostgreSQL databases, it returns this statement: The number of rows in the database that were affected by the update. |
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);Parameter | Description |
|---|---|
| The Result Identifier that is returned from the |
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);
...
}
}If the executeUpdate function generates an SQL error, the getError function will generate a DBErrorUDR.
The following fields are included in the DBErrorUDR:
Field | Description |
|---|---|
| The SQL error code. |
| The SQL error message. |