IBM FileNet P8, Version 5.2.1            

DbExecute system function

Use the DbExecute system function to run a stored procedure in a specified database.

Start of changeDefine the DbExecute system function for the workflow system. For information, see Setting DbExecute connections. Before you create a new DbExecute connection, you must first configure XA and non-XA JDBC providers for the database type of the DBExecute database on the application server to ensure that the appropriate JDBC drivers are configured.End of change

To specify a DbExecute system function:

  1. From the General System Palette, drag a DbExecute step onto the workflow map.
  2. Enter the database name.
  3. Enter the name of the stored procedure to execute.
    Restriction: The name that is used to create a stored procedure determines how it must be called. Process Designer does not allow quoted stored procedure names for the DBExecute system function, so the names of stored procedures must not be enclosed in quotes.
  4. Any database stored procedure that will be called by a System step must have all of its parameters declared as follows:
    • out - SQL Server
    • in out - Oracle
    • inout - DB2®

    This means that the stored procedure can update the value of the parameter. The stored procedure will return the (possibly updated) values of all parameters to the caller. The updated values will be reflected in values stored in the data fields that were passed in as parameters to the stored procedure.

    Define data fields in the workflow properties and specify the appropriate data fields as parameters for the various steps.

    The datatypes must match the types that are specified in the stored procedure and can be of the following types:

    Designer Param Type Oracle Param Type SQL Server Param Type DB2 Param Type
    String varchar varchar varchar
    Integer number int int
    Boolean number bit number
    Float number float float
    Time date datetime timestamp

Parameters for a stored procedure cannot be arrays. For example, you can pass in fifteen separate string parameters, but you cannot pass in an array of fifteen strings as a single parameter. You can use a single element in an array by specifying its index. For example, the syntax for specifying the first element in an array is fieldname[1].

Handling parameters returned as null from stored procedures

  Oracle SQLServer DB2
Maximum number of parameters in procedure 1024 1024 1024
String parameter maximum characters 4000 4000 4000

Because database stored procedures can throw exceptions, consider handling exceptions in the stored procedure or in an exception map in the workflow.

If you have upgraded from FileNet® P8 version 3.5.2 or earlier and you are using Microsoft SQL Server, you might see exception in delete or update statements that you did not see before. The database will return an exception when a delete or update statement that contains a Where clause does not select any records. For example, if you are deleting all records where Name=Don, if there are no records where Name=Don, the stored procedure receives an error from the database. If you don't catch these exception in the stored procedure, then the work object receives the exception and goes to either your exception map or to the Malfunction queue.

If a problem occurs in a running workflow at the DbExecute system function, you can find information about the error in the system message log.

If the stored procedure does not return a message within the time limit, the timeout expires and the work object gets an exception and goes to the Conductor queue. The default timeout value is 3600 seconds (1 hour).



Last updated: October 2015
bpfdh101.htm

© Copyright IBM Corporation 2015.