test

Using Database Operations

The Stored Procedure

A Stored Procedure OTD represents a database stored procedure. Fields correspond to the arguments of a stored procedure while methods are the operations that you can apply to the OTD. It allows you to execute a stored procedure. In the Collaboration Editor you can assign values to the input parameters, execute the call, collect data, and retrieve the values from output parameters.

Executing Stored Procedures

The OTD used in the example below, contains a Stored Procedure with input parameters. These input parameters are generated by the Database OTD Wizard and are displayed in the Collaboration Editor as subnodes of the OTD.

Below are the steps for executing the Stored Procedure:

  1. Specify the input values.

  2. Execute the Stored Procedure.

  3. Retrieve the output parameters if any.

For example:


package Storedprocedure;

public class sp_jce
{

    public com.stc.codegen.logger.Logger logger;

    public com.stc.codegen.alerter.Alerter alerter;

    public void receive( com.stc.connector.appconn.file.FileTextMessage
 input,com.stc.connector.appconn.file.FileApplication FileClient_1,employeedb.
Db_employee employeedb_with_top_db_employee_1,insert_DB.Insert_DBOTD insert_DB_1 )
    throws Throwable
    {
        employeedb_with_top_db_employee_1.unmarshalFromString( input.getText() );

insert_DB_1.getInsert_new_employee().setEmployee_no( java.lang.Integer.parseInt(
 employeedb_with_top_db_employee_1.getEmployee_no() ) );

        insert_DB_1.getInsert_new_employee().setEmployee_Lname(
 employeedb_with_top_db_employee_1.getEmployee_lname() );

        insert_DB_1.getInsert_new_employee().setEmployee_Fname(
 employeedb_with_top_db_employee_1.getEmployee_fname() );

        insert_DB_1.getInsert_new_employee().setRate(
 java.lang.Float.parseFloat( employeedb_with_top_db_employee_1.getRate() ) );

        insert_DB_1.getInsert_new_employee().setUpdate_date(
 java.sql.Timestamp.valueOf( employeedb_with_top_db_employee_1.getUpdate_date() ) );

        insert_DB_1.getInsert_new_employee().execute();

        insert_DB_1.commit();

        FileClient_1.setText( "procedure executed" );

        FileClient_1.write();
    }

}

Manipulating the ResultSet and Update Count Returned by Stored Procedure

The following methods are provided for using the ResultSet and Update Count when they are returned by Stored Procedures:

Oracle stored procedures do not return records as ResultSets; instead, the records are returned through output reference cursor parameters. Reference Cursor parameters are essentially ResultSets.

The resultsAvailable() method, added to the OTD, simplifies the whole process of determining whether any results, whether they are update Counts or ResultSets, are available after a stored procedure has been executed. Although JDBC provides three methods (getMoreResults(), getUpdateCount(), and getResultSet()) to access the results of a stored procedure call, the information returned from these methods can be quite confusing to the inexperienced Java JDBC programmer and they also differ between vendors. You can simply call resultsAvailable() and if Boolean true is returned, you can expect either a valid Update Count when getUpdateCount() is called and/or the next ResultSet has been retrieved and made available to one of the ResultSet nodes defined for the Stored Procedure OTD, when that node’s available() method returns true.

Update Counts information that is returned from Stored Procedures is often insignificant. Process returned ResultSet information and avoid looping through all of the Update Counts. The following three methods control exactly what information is returned from a stored procedure call. The enableResultSetsOnly() method, added to the OTD allows only ResultSets to be returned and thus every resultsAvailable() called only returns Boolean true if a ResultSet is available. Likewise, the enableUpdateCountsOnly() method causes resultsAvailable() to return true only if an Update Count is available. The default case of the enableResultSetsAndUpdateCount() method enables both ResultSets and Update Counts to be returned.

Collaboration usability for a Stored Procedure ResultSet

You can use your mouse to drag and drop the Column data of the ResultSets from their OTD nodes to the Business Rules. Below is a code snippet that can be generated by the Collaboration Editor:


// resultsAvailable() true if there’s an update count and/or a result set available.
// note, it should not be called indiscriminantly because each time the results
pointer is advanced via getMoreResults() call.
while (getSPIn().getSpS_multi().resultsAvailable())
{
    // check if there’s an update count
    if (getSPIn().getSpS_multi().getUpdateCount() > 0)
    {
        logger.info("Updated "+getSPIn().getSpS_multi().getUpdateCount()+" rows");
    }
// each result set node has an available() method (similar to OTD’s) that tells the
// user whether this particular result set is available. note, JDBC does support access
// to more than one result set at a time, i.e., cannot drag from two distinct result
// sets simultaneously
    if (getSPIn().getSpS_multi().getNormRS().available())
    {
    while (getSPIn().getSpS_multi().getNormRS().next())
    {
    logger.info("Customer Id = "+getSPIn().getSpS_multi().getNormRS().getCustomerId());
    logger.info("Customer Name = "+getSPIn().getSpS_multi().getNormRS().
getCustomerName());
    }
    if (getSPIn().getSpS_multi().getDbEmployee().available())
    {
    while (getSPIn().getSpS_multi().getDbEmployee().next())
    {
    logger.info("EMPNO = "+getSPIn().getSpS_multi().getDbEmployee().getEMPNO());
    logger.info("ENAME = "+getSPIn().getSpS_multi().getDbEmployee().getENAME());
    logger.info("JOB = "+getSPIn().getSpS_multi().getDbEmployee().getJOB());
    logger.info("MGR = "+getSPIn().getSpS_multi().getDbEmployee().getMGR());
    logger.info("HIREDATE = "+getSPIn().getSpS_multi().getDbEmployee().getHIREDATE());
    logger.info("SAL = "+getSPIn().getSpS_multi().getDbEmployee().getSAL());
    logger.info("COMM = "+getSPIn().getSpS_multi().getDbEmployee().getCOMM());
    logger.info("DEPTNO = "+getSPIn().getSpS_multi().getDbEmployee().getDEPTNO());
    }
}
Note –

resultsAvailable() and available() cannot be indiscriminately called because each time they move ResultSet pointers to the appropriate locations.

Once the "resultsAvailable()" method has been called, the next result (if available) can be either a ResultSet or an UpdateCount, if the default "enableResultSetsAndUpdateCount()" was used.

Because of limitations imposed by some DBMSs, SeeBeyond recommends that for maximum portability, all of the results in a ResultSet object should be retrieved before OUT parameters are retrieved. Therefore, you must retrieve all ResultSet(s) and update counts first, followed by retrieving the OUT type parameters and return values.

The following list includes specific ResultSet behavior that you may encounter: