Versions Compared

Version Old Version 1 New Version Current
Changes made by

Jenni Wallin

Former user

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 resultresults.  The The DBErrorUDR is used to handle SQL errors.

Note
titleNote

Please refer to the Notes section on the 9. Database Functions page for details on allowed database data type.

prepareCall

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

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


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.

...

Code Block
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.

...

Code Block
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.

...

Code Block
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.

Code Block
any CallableStatement.get
 (any resultIdentifier, 
 int spIndex)


ParameterDescription

resultIdentifier

The Result Identifier that is returned from the executeUpdate function

int

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 were affected by the the executeUpdate function.

Code Block
int CallableStatement.getUpdateCount(any resultIdentifier);


ParameterDescription

resultIdentifier

The Result Identifier that is returned from the executeUpdate function

Returns

For Oracle databases this will return the following statement: 

The number of rows in the database that

was

were affected by the call. If an update exists -1 will be returned.

For MySQL and PostgreSQL databases this will return the following statement: 

The number of rows in the database that were affected by the update. 


getError
Anchor
getError
getError

...

Code Block
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.


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); 
     ...
  }
}


...

The following fields are included in the DBErrorUDR:

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