Use the DbExecute system function to execute a stored procedure in a specified database.
Before you run the workflow with the DbExecute system function, be sure to define the database connection using vwtool. See dbconfig in the online Help for vwtool.
To specify a DbExecute system function
See About system steps for information about including multiple system functions in a single step.
NOTE 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.
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].
Oracle | SQL Server | DB2 | |
---|---|---|---|
Maximum number of parameters in procedure | 1024 | 1024 | 1024 |
String parameter maximum characters | 4000 | 255 | 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. See FileNet message log files.
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). See Process Task Manager Advanced Usage Technical Notice. To download this document from the IBM support page, see Accessing IBM FileNet Documentation.