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:

From the General System Palette, drag a DbExecute step onto the workflow map.
Enter the database name.
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.

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

If a string parameter is set to null by a stored procedure, the work object data item will receive an empty string.
If a date/time, integer, or float parameter is set to null by a stored procedure, an exception will be thrown. (there is no representation of a null for these data types). In addition, an explanatory message is logged.
If a boolean parameter is set to null, an exception will be thrown. In addition, an explanatory message is logged.

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