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

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

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