JDBC Call Procedure

JDBC Call Procedure is an asynchronous activity that calls a database procedure or function using the specified JDBC connection. If this activity is not part of a transaction group, the SQL statement is committed after the activity completes.

General

If this activity is part of a transaction group, the SQL statement is committed or rolled back with the other JDBC activities in the group at the end of the transaction.

To override the default behavior of transaction groups for certain JDBC activities in a transaction group, select the Override Transaction Behavior check box on the Advanced tab. This specifies that the activity is outside of the transaction and the SQL statement is committed when the activity completes, even if the activity is in a transaction group.

The General tab has the following fields.

Field Literal Value/Process Property/Module Property Description
Name None The name to be displayed as the label for the activity in the process.
JDBC Shared Resource Yes The path to the shared resource containing the JDBC connection information.

For more information about JDBC resources, see JDBC Connection.

Timeout(sec) Yes The time (in seconds) to wait for the procedure call to complete. If the call does not complete in the specified time limit, it returns an error.
Maximum rows Yes The maximum number of rows to retrieve. To retrieve all rows, specify 0.

Description

Provide a short description for the activity in this field.

Procedure

The Procedure tab has the following fields.

Field Literal Value/Process Property/Module Property Description
Schema Yes The schema in which the procedure resides. This name is used to resolve naming conflicts, if more than one schema in the database has the selected procedure with the same name.

Configure the Schema field to use a Process Property or a Module Property if a schema has been defined for the project, and you know the schema name. To search for a schema, configure the field to use a Literal Value, and click the Browse button to select a schema from the database that is using the specified JDBC Connection.

Catalog or Package Yes The catalog or package in which the procedure resides. This name is used to resolve naming conflicts if more than one catalog or package in the database has selected procedures with the same name.

Configure the Catalog or Package field to use a Process Property or a Module Property if a catalog or package has been defined for the project, and you know their names. To search for a catalog or package, configure the field to use a Literal Value, click the Browse button, and select a catalog or package from the database using the specified JDBC Connection. If a schema is specified in the Schema field, only catalogs or packages in the specified schema are listed.

This field is only applicable to databases with more than one catalog and package.

For more information about catalogs and packages, see your database documentation.

Procedure / Function No The name of the database procedure or user-defined function to call.

Click the Browse button to select the database for available procedures or functions after specifying the JDBC Connection field.

Click the Refresh button to retrieve the changes from the database, if you change the parameters or signatures while editing your project.

The list of available procedures and functions is limited by the values supplied in the Schema and Catalog or Package fields.

Parameters No This field displays the parameters for the procedure and you can override the data type (IN, OUT, IN/OUT) for each parameter in the Direction column.

The parameters listed in the table show the Parameter Name, Data Type, and the Direction (IN, OUT, IN/OUT) of the each parameter. All IN and IN/OUT parameters listed in the Parameters table are shown on the Input tab of the activity for the user to provide values.

Advanced

The Advanced tab has the following fields.

Field Description
ThreadPool SharedResource Specifies a queue of threads available to run a queue of tasks. Thread pools are used to improve performance when executing large numbers of asynchronous tasks by reducing per task invocation overhead, provide a means of bounding, and managing the resources consumed when executing a collection of tasks.
Override Transaction Behavior Overrides the default behavior of a transaction group. If this activity is in a transaction group, the activity is normally committed or rolled back with the other transactional activities.

Select this check box to specify that this activity is not part of the transaction group and is committed when it completes. Also, select this check box to use a separate database connection to perform the activity and commit the SQL statement.

Override JDBC Connection Selecting this option overrides the default datasource connection, and allows for SQL queries to be executed against the optional datasource.

After selecting this option, the sharedResourceName field is enabled under the Input > Data Source tab. From the sharedResourceName field, type the complete path with the name of the optional datasource, which can be found under the Resources folder. For example, if a JDBC shared resource is located at Resources > Package_Name > JDBC_SR_Name, then the value you enter should be Package_Name.JDBC_SR_Name. The value you enter in the XPath Expression field is case sensitive and must be a string.

To avoid mapping issues, ensure the optional datasource maintains a structure that is identical to the default database, and that both datasources are using the same datatypes.
Note: Transactions between multiple datasources is not supported.
Interpret Empty String As Null Specifies how empty strings in the activity’s input elements should be handled. Selecting this check box sends the nulls to the database where empty strings are supplied.

Empty strings are treated as zero-length strings, if this check box is not selected. Whether you use this option or not, you can still use XPath to set input elements explicitly to null.

ResultSets Use Schema Specifies that the design-time must try to fetch the schema when a resultset is expected from a stored procedure call.

When selected, TIBCO Business Studio for BusinessWorks tries to invoke the stored procedure with default values and get the metadata for the resultset.

If the check box is not selected, or if TIBCO Business Studio for BusinessWorks does not get any metadata, the results are displayed as ’unknown resultsets’.

Interpret Invalid XML Chars

Specifies how invalid characters should be handled. If the check box is clear and invalid characters are sent from the database to ActiveMatrix BusinessWorks, the call procedure execution fails.

On the other hand, if invalid characters are sent, and the check box is selected, ActiveMatrix BusinessWorks executes it successfully.

Override S/A DataType Schema Select the check box to override the schema appended to the object type parameter. The schema in the Schema field on the Procedure tab overrides the schema in the Parameters field at run time.
Use Nil Specifies whether NULLs are represented as optional schema elements or whether each item that can contain a NULL has subitems.

Input

The input for this activity is dependent upon the input parameters of the database procedure.

Input Item Datatype Description
inputs complex The input parameters of the database procedure. The datatype contained in this element varies depending upon the parameters specified for the database procedure.

The IN and IN/OUT parameters displayed in the Parameters table on the Procedure tab are displayed here for the user to set values.

You can see inputSet once you select Procedure in the General tab.

ServerTimeZone string Specifies the time zone for the database server.
timeout number The time (in seconds) to wait for the procedure call to complete. The call if not completed in the specified time limit, returns an error.
maxRows number The maximum number of rows to retrieve. To retrieve all rows, specify 0.

Output

The output of the activity is depends upon the output parameters of the database procedure.

Output Item Datatype Description
outputSet complex The output of the database procedure. The datatype contained in this element varies depending upon what the database procedure returns.
UnresolvedResultSets string The output of the database procedure may return other result sets. These result sets are returned in this output item as an XML string. Create a schema resource to describe these result sets, and then use the Parse XML activity to parse the XML based on the schema. You can then use the parsed output in subsequent activities.

Fault

The Fault tab lists the possible exceptions generated by this activity. For more information about error codes and the possible corrective action to take, see the TIBCO ActiveMatrix BusinessWorks™ Error Codes guide.

Fault Generated When..
JDBCConnectionNotFoundException An error occurred when attempting to connect to the specified database.
InvalidTimeZoneException An invalid time zone was specified.
JDBCSQLException An SQL error occurred.
LoginTimedOutException A timeout occurred while attempting to connect to the database.
ActivityTimedOutException A timeout has been reached.