Using SQL Server Operations

The database operations used in the SQL Server Adapter are used to access the SQL Server database. Database operations are either accessed through activities in BPEL, or through methods called from a Java Collaboration Definition.

• SQL Server Adapter Database Operations (BPEL)

• SQL Server Adapter Database Operations (JCD)

SQL Server Adapter Database Operations (BPEL)

The SQL Server Adapter uses a number operations to query the SQL Server database. Within a BPEL business process, the SQL Server Adapter uses BPEL activities to perform basic outbound database operations, including:

• Insert

• Update

• Delete

• SelectOne

• SelectMultiple

• SelectAll

In addition to these outbound operations, the SQL Server Adapter also employs the inbound activity ReceiveOne within a Prepared Statement OTD.

Activity Input and Output

The Java CAPS Business Rules Designer includes Input and Output columns to map and transform data between activities displayed on the Business Process Canvas.

Figure 1-6 displays the business rules between the FileClient.write and otdSQLServer.Db_employeeDelete activities. In this example, the whereClause attribute appears on the Input side.

Figure 1-6 Input and Output Between Activities

The following table lists the expected Input and Output of each database operation activity.

Table 1-5 SQL Server Operations

SQL Server Adapter Database Operations (JCD)

The same database operations are also used in the JCD, but appear as methods to call from the Collaboration.

Tables, Views, and Stored Procedures are manipulated through OTDs. Methods to call include:

• insert()

• insertRow()

• update(String sWhere)

• updateRow()

• delete(String sWhere)

• deleteRow()

• select(String where)

  ---

  Note - Refer to the Javadoc for a full description of methods included in the SQL Server Adapter.

  ---

The Table

A table OTD represents a database table. It consists of fields and methods. Fields correspond to the columns of a table while methods are the operations that you can apply to the OTD. This allows you to perform query, update, insert, and delete SQL operations in a table. The ability to update via a resultset is called Updatable Resultset, which is a feature supported by this adapter. By default, the Table OTD has UpdatableConcurrency and ScrollTypeForwardOnly. Normally you do not have to change the default setting.

The type of result returned by the select() method can be specified using:

• SetConcurrencytoUpdatable

• SetConcurrencytoReadOnly

• SetScrollTypetoForwardOnly

• SetScrollTypetoScrollSensitive

• SetScrollTypetoInsensitive

To Perform a Query Operation on a Table

1. Execute the select() method with the where clause specified if necessary.
2. Loop through the ResultSet using the next() method.
3. Process the return record within a while() loop.

   For example:

   package prjSQLServer_JCDjcdALL;
   
   
   public class jcdTableSelect
   {
   
       public com.stc.codegen.logger.Logger logger;
   
       public com.stc.codegen.alerter.Alerter alerter;
   
       public com.stc.codegen.util.CollaborationContext collabContext;
   
       public com.stc.codegen.util.TypeConverter typeConverter;
   
       public void receive( com.stc.connector.appconn.file.
   FileTextMessage input, dtd.otdOutputDTD1325973702.
   DB_Employee otdOutputDTD_DB_Employee_1, otdSQLServer.OtdSQLServerOTD
   otdSQLServer_1, com.stc.connector.appconn.file.FileApplication FileClient_1 )
           throws Throwable
       {
           FileClient_1.setText( "Selectiong records from db_employee table via
   Table Select........" );
           FileClient_1.write();
           otdSQLServer_1.getDb_employee().select( input.getText() );
           while (otdSQLServer_1.getDb_employee().next()) {
               otdOutputDTD_DB_Employee_1.setEmpNo( typeConverter.shortToString(
   otdSQLServer_1.getDb_employee().getEMP_NO(), "#", false, "" ) );
               otdOutputDTD_DB_Employee_1.setLastname(
   otdSQLServer_1.getDb_employee().getLAST_NAME() );
               otdOutputDTD_DB_Employee_1.setFirstname(
   otdSQLServer_1.getDb_employee().getFIRST_NAME() );
               otdOutputDTD_DB_Employee_1.setRate(
   otdSQLServer_1.getDb_employee().getRATE().toString() );
               otdOutputDTD_DB_Employee_1.setLastDate(
   typeConverter.dateToString( otdSQLServer_1.getDb_employee().getLAST_UPDATE(),
   "yyyy-MM-dd hh:mm:ss", false, "" ) );
               FileClient_1.setText( otdOutputDTD_DB_Employee_1.marshalToString() );
               FileClient_1.write();
           }
           FileClient_1.setText( "Table Select Done." );
           FileClient_1.write();
       }
   
   }

To Perform an Insert Operation on a Table

1. Execute the insert() method. Assign a field.
2. Insert the row by calling insertRow()

   This example inserts an employee record.

   package prjSQLServer_JCDjcdALL;
   
   
   public class jcdInsert
   {
   
       public com.stc.codegen.logger.Logger logger;
   
       public com.stc.codegen.alerter.Alerter alerter;
   
       public com.stc.codegen.util.CollaborationContext collabContext;
   
       public com.stc.codegen.util.TypeConverter typeConverter;
   
       public void receive( com.stc.connector.appconn.file.FileTextMessage input,
   otdSQLServer.OtdSQLServerOTD otdSQLServer_1,     dtd.otdInputDTD_1206505729.
   DB_Employee otdInputDTD_DB_Employee_1, com.stc.connector.appconn.file.
   FileApplication FileClient_1 )
           throws Throwable
       {
           FileClient_1.setText( "Inserting records in to db_employee table......" );
           FileClient_1.write();
           otdInputDTD_DB_Employee_1.unmarshalFromString( input.getText() );
           otdSQLServer_1.getDb_employee().insert();
           for (int i1 = 0; i1 < otdInputDTD_DB_Employee_1.countX_sequence_A(); i1 += 1) {
               otdSQLServer_1.getDb_employee().setEMP_NO( typeConverter.stringToShort(
    otdInputDTD_DB_Employee_1.getX_sequence_A( i1 ).getEmpNo(), "#", false, 0 ) );
               otdSQLServer_1.getDb_employee().setLAST_NAME( otdInputDTD_DB_Employee_1.
   getX_sequence_A( i1 ).getLastname() );
               otdSQLServer_1.getDb_employee().setFIRST_NAME( otdInputDTD_DB_Employee_1.
   getX_sequence_A( i1 ).getFirstname() );
               otdSQLServer_1.getDb_employee().setRATE( new java.math.BigDecimal(
    otdInputDTD_DB_Employee_1.getX_sequence_A( i1 ).getRate() ) );
               otdSQLServer_1.getDb_employee().setLAST_UPDATE( typeConverter.
   stringToTimestamp( otdInputDTD_DB_Employee_1.getX_sequence_A( i1 ).getLastDate(),
    "yyyy-MM-dd hh:mm:ss", false, "" ) );
               otdSQLServer_1.getDb_employee().insertRow();
           }
           FileClient_1.setText( "Insert Done." );
           FileClient_1.write();
       }
   
   }

To Perform an Update Operation on a Table

1. Execute the update() method.
2. Using a while loop together with next(), move to the row that you want to update.
3. Assign updating value(s) to the fields of the table OTD.

   .

4. Update the row by calling updateRow().

   package prjSQLServer_JCDjcdALL;
   
   
   public class jcdUpdate
   {
   
   public com.stc.codegen.logger.Logger logger;
   public com.stc.codegen.alerter.Alerter alerter;
   public com.stc.codegen.util.CollaborationContext collabContext;
   public com.stc.codegen.util.TypeConverter typeConverter;
   
       public void receive( com.stc.connector.appconn.file.FileTextMessage input,
    otdSQLServer.OtdSQLServerOTD otdSQLServer_1, com.stc.connector.appconn.file.
   FileApplication FileClient_1 )
           throws Throwable
       {
           FileClient_1.setText( "Updating the Rate and Last_update fields .. " );
           FileClient_1.write();
           otdSQLServer_1.getDb_employee().update( input.getText() );
           while (otdSQLServer_1.getDb_employee().next()) {
           otdSQLServer_1.getDb_employee().setLAST_NAME( "Krishna" );
           otdSQLServer_1.getDb_employee().setFIRST_NAME( "Kishore" );
           otdSQLServer_1.getDb_employee().updateRow();
           }
           FileClient_1.setText( "Update Done." );
           FileClient_1.write();
       }
   
   }

To Perform a Delete Operation on a Table

• Execute the delete() method.

  In this example DELETE an employee.

  package prjSQLServer_JCDjcdALL;
  
  public class jcdDelete
  {
  public com.stc.codegen.logger.Logger logger;
  public com.stc.codegen.alerter.Alerter alerter;
  public com.stc.codegen.util.CollaborationContext collabContext;
  public com.stc.codegen.util.TypeConverter typeConverter;
  
      public void receive( com.stc.connector.appconn.file.FileTextMessage
   input, otdSQLServer.OtdSQLServerOTD otdSQLServer_1, com.stc.connector.
  appconn.file.FileApplication FileClient_1 )
          throws Throwable
      {
          FileClient_1.setText( "Deleting record............" );
          FileClient_1.write();
          otdSQLServer_1.getDb_employee().delete( input.getText() );
          FileClient_1.setText( "Delete Done." );
          FileClient_1.write();
      }
  }

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. Remember that while in the Collaboration Editor you can drag and drop nodes from the OTD into the Collaboration Editor.

Executing Stored Procedures

The OTD represents the Stored Procedure “LookUpGlobal” with two parameters, an inbound parameter (INLOCALID) and an outbound parameter (OUTGLOBALPRODUCTID). These inbound and outbound parameters are generated by the DataBase Wizard and are represented in the resulting OTD as nodes. Within the Transformation Designer, you can drag values from the input parameters, execute the call, collect data, and drag the values to the output parameters.

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
    {
        //@map:employeedb_with_top_db_employee_1.unmarshalFromString(Text)
        employeedb_with_top_db_employee_1.unmarshalFromString( input.getText() );

        //@map:Copy java.lang.Integer.parseInt(Employee_no) to Employee_no
        insert_DB_1.getInsert_new_employee().setEmployee_no( java.lang.Integer.
parseInt( employeedb_with_top_db_employee_1.getEmployee_no() ) );

        //@map:Copy Employee_lname to Employee_Lname
        insert_DB_1.getInsert_new_employee().setEmployee_Lname(
 employeedb_with_top_db_employee_1.getEmployee_lname() );

        //@map:Copy Employee_fname to Employee_Fname
        insert_DB_1.getInsert_new_employee().setEmployee_Fname(
 employeedb_with_top_db_employee_1.getEmployee_fname() );

        //@map:Copy java.lang.Float.parseFloat(Rate) to Rate
        insert_DB_1.getInsert_new_employee().setRate(
 java.lang.Float.parseFloat( employeedb_with_top_db_employee_1.getRate() ) );

        //@map:Copy java.sql.Timestamp.valueOf(Update_date) to Update_date
        insert_DB_1.getInsert_new_employee().setUpdate_date(
 java.sql.Timestamp.valueOf( employeedb_with_top_db_employee_1.getUpdate_date() ) );

        //@map:Insert_new_employee.execute
        insert_DB_1.getInsert_new_employee().execute();

        //@map:insert_DB_1.commit
        insert_DB_1.commit();

        //@map:Copy "procedure executed" to Text
        FileClient_1.setText( "procedure executed" );

        //@map:FileClient_1.write
        FileClient_1.write();
    }

}

Manipulating the ResultSet and Update Count Returned by Stored Procedure

For Stored Procedures that return ResultSets and Update Count, the following methods are provided to manipulate the ResultSet:

• enableResultSetOnly

• enableUpdateCountsOnly

• enableResultSetandUpdateCounts

• resultsAvailable

• next

• getUpdateCount

• available

SQL Server 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 PreparedStatementAgent class, simplifies the whole process of determining whether any results, be it 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.

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

Collaboration Usability for a Stored Procedure ResultSet

The Column data of the ResultSets can be dragged-and-dropped from their XSC nodes to the Business Rules. Below is a code snippet that can be generated by the Collaboration Editor:

while (getSPIn().getSpS_multi().resultsAvailable())
{
if (getSPIn().getSpS_multi().getUpdateCount() > 0)
  {
    System.err.println("Updated "+getSPIn().getSpS_multi().getUpdateCount()+" rows");
  }

  if (getSPIn().getSpS_multi().getNormRS().available())
  {
    while (getSPIn().getSpS_multi().getNormRS().next())
    {
      System.err.println("Customer Id   = "+getSPIn().getSpS_multi().getNormRS().
getCustomerId());
      System.err.println("Customer Name = "+getSPIn().getSpS_multi().getNormRS().
getCustomerName());
      System.err.println();
    }
    System.err.println("===");
  }
  else if (getSPIn().getSpS_multi().getDbEmployee().available())
  {
    while (getSPIn().getSpS_multi().getDbEmployee().next())
    {
      System.err.println("EMPNO    = "+getSPIn().getSpS_multi().
getDbEmployee().getEMPNO());
      System.err.println("ENAME    = "+getSPIn().getSpS_multi().
getDbEmployee().getENAME());
      System.err.println("JOB      = "+getSPIn().getSpS_multi().
getDbEmployee().getJOB());
      System.err.println("MGR      = "+getSPIn().getSpS_multi().
getDbEmployee().getMGR());
      System.err.println("HIREDATE = "+getSPIn().getSpS_multi().
getDbEmployee().getHIREDATE());
      System.err.println("SAL      = "+getSPIn().getSpS_multi().
getDbEmployee().getSAL());
      System.err.println("COMM     = "+getSPIn().getSpS_multi().
getDbEmployee().getCOMM());
      System.err.println("DEPTNO   = "+getSPIn().getSpS_multi().
getDbEmployee().getDEPTNO());
      System.err.println();
    }
    System.err.println("===");
  }
}

After calling resultsAvailable(), 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, it is recommended that for maximum portability, all of the results in a ResultSet object should be retrieved before OUT parameters are retrieved. Therefore, you should 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:

• The method resultsAvailable() implicitly calls getMoreResults() when it is called more than once. You should not call both methods in your Java code. Doing so may result in skipped data from one of the ResultSets when more than one ResultSet is present.

• The methods available() and getResultSet() can not be used in conjunction with multiple ResultSets being open at the same time. Attempting to open more the one ResultSet at the same time closes the previous ResultSet. The recommended working pattern is:

  • Open one Result Set (ResultSet_1) and work with the data until you have completed your modifications and updates. Open ResultSet_2, (ResultSet_1 is now closed) and modify. When you have completed your work in ResultSet_2, open any additional ResultSets or close ResultSet_2.

  If you modify the ResultSet generated by the Execute mode of the Database Wizard, you need to assure the indexes match the stored procedure. By doing this, your ResultSet indexes are preserved.

• Generally, getMoreResults does not need to be called. It is needed if you do not want to use our enhanced methods and you want to follow the traditional JDBC calls on your own.

The Database Wizard Assistant expects the column names to be in English when creating a ResultSet.

Prepared Statement

A Prepared Statement OTD represents a SQL statement that has been compiled. Fields in the OTD correspond to the input values that users need to provide.

Prepared statements can be used to perform insert, update, delete and query operations. A prepared statement uses a question mark (?) as a place holder for input. For example:

insert into EMP_TAB(Age, Name, Dept No) value(?, ?, ?)

To execute a prepared statement, set the input parameters and call executeUpdate() and specify the input values if any.

getPrepStatement().getPreparedStatementTest().setAge(23);
getPrepStatement().getPreparedStatementTest().setName(”Peter Pan’);
getPrepStatement().getPreparedStatementTest().setDeptNo(6);
getPrepStatement().getPreparedStatementTest().executeUpdate();

Batch Operations

To achieve better performance, consider using a bulk insert if you have to insert many records. This is the “Add Batch” capability. The only modification required is to include the addBatch() method for each SQL operation and then the executeBatch() call to submit the batch to the database server. Batch operations apply only to Prepared Statements.

getPrepStatement().getPreparedStatementTest().setAge(23);
getPrepStatement().getPreparedStatementTest().setName(”Peter Pan’);
getPrepStatement().getPreparedStatementTest().setDeptNo(6);
getPrepStatement().getPreparedStatementTest().addBatch();

getPrepStatement().getPreparedStatementTest().setAge(45);
getPrepStatement().getPreparedStatementTest().setName(”Harrison Ford’);
getPrepStatement().getPreparedStatementTest().setDeptNo(7);
getPrepStatement().getPreparedStatementTest().addBatch();
getPrepStatement().getPreparedStatementTest().executeBatch();

Result Sets

The SQL Server Adapter supports the following 3 types of Result Sets:

• Forward Only

• Scroll Insensitive

• Scroll Sensitive

By default, the SQL Server Adapter uses Forward only. To traverse backward, you must explicitly set the result set type as scroll sensitive or scroll insensitive according to the requirement.

Type_Forward_Only

The result set is nonscrollable; its cursor moves forward only, from top to bottom. The view of the data in the result set depends on whether the DBMS materializes results incrementally.

Type_Scroll_Insensitive

The result set is scrollable: Its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to its current position. The result set generally does not show changes to the underlying database that are made while it is open. The membership, order, and column values of rows are typically fixed when the result set is created.

Type_Scroll_Sensitive

The result set is scrollable; its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to its current position. The result set is sensitive to changes made while it is open. If the underlying column values are modified, the new values are visible, thus providing a dynamic view of the underlying data. The membership and ordering of rows in the result set may be fixed or not, depending on the implementation.

Using Result Sets with Stored Procedures

In order to scroll back in a result set returned from a stored procedure, the <OTD>.setScrollTypeToScrollInsensitive() method should be called before any other methods for the OTD.

Example:

Sch_StoredProcedures.setScrollTypeToScrollInsensitive();
Sch_StoredProcedures.getUsp_GetAppointment().setPatientID( PatientID );
Sch_StoredProcedures.getUsp_GetAppointment().setAppointmentDate( ApointmentDateTime );
Sch_StoredProcedures.getUsp_GetAppointment().execute();
Sch_StoredProcedures.getUsp_GetAppointment().enableResultSetsOnly();

If the <OTD>.setScrollTypeToScrollInsensitive() method is not called, then any attempt to move back in the resultset will fail.

Example:

Executing Sch_StoredProcedures.getUsp_GetAppointment().get$usp_GetAppointmentResultSets0().first(); will fail with the following exception: Unsupported method: ResultSet.first.