Copyright © Cloud Software Group, Inc. All Rights Reserved

JDBC Call Procedure

Activity

The JDBC Call Procedure activity calls a database procedure using the specified JDBC connection.

If this activity is not part of a transaction group, the SQL statement is committed after the activity completes. 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. See TIBCO ActiveMatrix BusinessWorks Process Design for more information about creating groups for transactions.

If you want to override the default behavior of transaction groups for certain JDBC activities in a transaction group, you can check the Override Transaction Behavior field 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 Refresh button on this activity allows you to synchronize the activity with the contents of the database. This is useful if you make a change to the database while you are editing a process definition containing this activity in TIBCO ActiveMatrix BusinessWorks.

When using the jConn or Data Direct 3.3 driver and connecting to a Sybase database, stored procedures that are supposed to return multiple result sets do not return any data. To avoid this problem, you can either use the sun.jdbc.odbc.JdbcOdbcDriver instead or you can add set nocount on to your stored procedure definition.

Configuration

The Configuration tab has the following fields.

Field	Global Var?	Description
Name	No	The name to appear as the label for the activity in the process definition.
Description	No	Short description of the activity.
JDBC Connection	Yes	Path to the shared configuration resource containing the JDBC connection information. See JDBC Connection for more information about JDBC resources.
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. The Select Schema button allows you to choose a schema from the database using the specified JDBC Connection.
Catalog/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 the selected procedure with the same name. This field is only applicable to databases that have more than one catalog and package. See your database documentation for more information about catalogs and packages. The Select Catalog/Package button allows you to choose 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.
Procedure/ Function Name	Yes	Name of the database procedure or user-defined function to call. You can use the Select Procedure/Function button to query the database for available procedures or functions once the JDBC Connection field is specified. The list of available procedures and functions will be limited by the values supplied in the Schema and Catalog/Package fields. Note: The input parameters and output parameters are displayed when you use the Select Procedure/Function button. TIBCO Designer retrieves the signature of each stored procedure/function from the database once. If you change the signature while editing your project, you must click the Refresh button to retrieve the changes from the database.
Timeout (sec)	Yes	Time to wait for the procedure call to complete. If the call does not complete in the given time limit, an error is returned.
Maximum Rows	Yes	The maximum number of rows to retrieve. To retrieve all rows, specify 0.
Parameter Types	No	This field displays the parameters for the procedure and allows you to override the parameter type (IN, OUT, INOUT) for each parameter.

Advanced

The Advanced tab has the following fields.

Field	Global Var?	Description
Override Transaction Behavior	Yes	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. If this check box is selected, this activity is not part of the transaction group and is committed when it completes. Selecting this option uses a separate database connection to perform the activity and commit the SQL statement.
Interpret Empty String as Null	No	Specifies how empty strings in the activity’s input elements should be handled. When the field is checked, nulls are sent to the database where empty strings are supplied. When the field is unchecked, empty strings are treated as zero-length strings. Whether you use this field or not, you can still use XPath to set input elements explicitly to null. Note: Many databases treat empty strings and nulls as the same, so this field will not affect how the database interprets empty strings.
Resultsets Use Schema	No	Specifies that the design-time must try to fetch the schema when a resultset is expected from a stored procedure call. When selected, designer 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 designer does not get any metadata, the results will be displayed as ’unknown resultsets’.

Input

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

Input Item	Datatype	Description
ServerTimeZone	string	Specifies the time zone for the database server. See Time Zone Construction for more information about how time zones are constructed using this field. You can use the Constants tab in the XPath Formula Builder to supply a valid time zone name. The list of time zone names is generated from java.util.TimeZone.getAvailableIDs(). See Time Zone Names for the list of valid time zone names.
inputSet	complex	The input parameters of the database procedure. The datatype contained in this element varies depending upon the parameters specified for the database procedure.
timeout	number	Time (in seconds) to wait for the procedure call to complete. If the call does not complete in the given time limit, an error is returned.
maxRows	number	The maximum number of rows to retrieve. To retrieve all rows, specify 0.

Output

The output for the activity is dependent 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.
unknownResultSets	complex	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, then use the Parse XML activity to parse the XML based on the schema. You can then use the parsed output in subsequent activities. See TIBCO ActiveMatrix BusinessWorks Release Notes for a more detailed description of this procedure.
Elapsed Time	long	This field displays the time taken for the execution of the activity in milliseconds. This is an optional element that appears only when the property java.property.bw.activity.output.stats.elapsedTime.JDBCCallActivity is set to true in designer.tra. For run-time, set the property to true in the bwengine.tra file. By default, the property is set to false. If set in the design-time, this property must be carried forward to the run-time; else it can cause run-time errors. Enabling this property causes additional performance overhead. Hence, it is recommended that you use this property only when this information is to be consumed by some other ActiveMatrix BusinessWorks activity. Also, it is recommended that this property be used only in a non-production environment as a diagnostic tool and the property be turned off at run-time. You can turn off the property and not calculate the elapsed time at run-time by setting the property java.property.bw.activity.output.stats.elapsedTime.turnoff in the bwengine.tra file. However, note that setting this property will turn off the calculation of elapsed time at run-time for all the JDBC activities.

Error Output

The Error Output tab lists the possible exceptions that can be thrown by this activity. See TIBCO ActiveMatrix BusinessWorks Error Codes for more information about error codes and corrective action to take.

Exception	Thrown When...
JDBCDriverLoadingException	An error occurred when loading the specified JDBC driver.
JDBCConnectionNotFoundException	An error occurred when attempting to connect to the specified database.
InvalidTimeZoneException	An invalid timezone was specified. See Time Zone Names for a list of valid timezones.
JDBCSQLException	A SQL error occurred.
JDBCTimedOutException	The operation has timed out.
LoginTimedOutException	A timeout has occurred while attempting to connect to the database.

Copyright © Cloud Software Group, Inc. All Rights Reserved