IBM FileNet P8, Version 5.2            

DbExecute system function

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

Before you run the workflow with the DbExecute system function, use Process Configuration Console to define the DbExecute connection in the workflow system properties.

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

Database stored procedures can throw exceptions, so you should 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 (one hour).


Last updated: October 2013

© Copyright IBM Corporation 2014.
This information center is powered by Eclipse technology. (