DC 12.3 | JDBC Stored Procedure Invoker

User Guide > Process Steps and Components > Invoker Step > JDBC Stored Procedure Invoker

Was this helpful?

JDBC Stored Procedure Invoker

The JDBC Stored Procedure Invoker connects to a target database, runs the specified stored procedure, and returns either an XML file or WebRowSet data in the format of a response schema.

To use this invoker, you must have the following:

• Database experience

• Java Database Connectivity (JDBC) knowledge

• Scripting knowledge

• XML knowledge

The main features of the JDBC Stored Procedure Invoker are as follows:

• Supports any database with type 4 drivers only

• Executes the selected stored procedure on the database

• Returns an XML instance containing information on the stored procedure that was run, including all input and output parameters. In addition, it returns all result sets from the stored procedure.

To connect to a database using the JDBC Stored Procedure Invoker, you must have information about the repository home, database connection, and required type 4 JDBC drivers. The relevant type 4 JDBC-compliant driver .jar files must be on your computer. For example, if you are using Oracle9i, you must have the ojdbc14.jar file present on your computer.

Design-Time Behavior

The JDBC Stored Procedure Invoker performs the following during design-time:

1. When the procedure or optional package information is selected in the step properties, the component reads the connection information, loads the drivers, and connects to the target database using the host URL, with a user ID and password.

2. Parses the request XML file to check for occurrences of the <ServiceInfo> tag.

3. Component does one the following:

• If the request XML file contains the tag, then the JDBC Stored Procedure Invoker uses the connection information specified within the tag to connect to the database.

• If the request XML file does not contain the tag, then the JDBC Stored Procedure Invoker uses the information specified in the instance properties (drivers, JAR location, and so on) to connect to the database.

4. Component connects to the target database.

5. Displays the stored procedures in the database based on the search criteria.

6. Invoker creates the request and response schema for the selected stored procedure in the repository home folder.

7. Disconnects from the target database.

Note: The request and response schemas are used only at design-time and not at run-time.

Run-Time Behavior

JDBC Stored Procedure Invoker performs the following during run-time:

1. Reads the connection information, loads the drivers, and connects to the target database using the Host URL, User ID, and password.

2. Parses the request XML file to check for occurrences of the <ServiceInfo> tag.

3. JDBC Stored Procedure Invoker does one the following:

• If the request XML file contains the tag, then JDBC Stored Procedure Invoker uses the connection information specified within the tag to connect to the database.

• If the request XML file does not contain the tag, then JDBC Stored Procedure Invoker uses the information specified in the instance properties (drivers, .jar file location, and so on) to connect to the database.

4. Connects to the target database.

5. (Optional) Starts a transaction based on steps used in the process.

6. Executes the selected stored procedure.

7. Returns the data in the format of a response schema containing information on the stored procedure.

8. (Optional) If a transaction was started, performs rollback or commit.

9. Disconnects from the target database.

Note: The request and response schemas are used at design time only, not at run time.

Procedure Schema

This parameter narrows the search for stored procedures in the database schema.

• If this parameter is empty, all procedures from all schemas are returned.

• If this parameter contains a valid value, only procedures in the named schema are returned.

The step property Stored Procedure contains the schema name. The database returns schema.procedure.

DB2 Limitations

The client and server version must be compatible.

Version Mismatch Errors

The following errors indicate a DB2 version mismatch:

DB2 SQL error: SQLCODE: -1109, SQLSTATE: , SQLERRMC: SYSIBM.
SQL0805N Package "NULLID.SYSSH200"
Not found SQLSTATE=51002.

Understanding a Request XML

Before you can use the JDBC Stored Procedure Invoker, you must understand how the request schema relates to the XML instance passed into the component at run time.

The schema defines the elements and attributes in the XML request and is generated based on the metadata from the procedure. The procedure parameter names become the element names and the properties of the parameters become the attributes.

The following example shows the pertinent portions of a request schema for an Oracle procedure with input of a number and REF CURSOR output:

<xsd:element name="ACNO" type="ACNO Type"/>
<xsd:element name="CV" type="CV_Type"/><xsd:choice>
<xsd:complexType name="ACNO_Type">
<xsd:simpleContent>
<xsd:extension base="xsd:integer">
<xsd:attribute name="Direction" fixed="IN"/>
<xsd:attribute name="SQLType" fixed="3"/>
<xsd:attribute name="DBTypeName" fixed="number"/>
<xsd:attribute name=IsNullable" fixed="true"/>
<xsd:attribute name="Remarks" fixed=""/>
<xsd:attribute name="NullAllowed"/>
</xsd:extension>
</xsd:simpleContent>
</xsd:complexType>
<xsd:complexType name="CV_Type">
<xsd:attribute name="Direction" fixed="OUT">
<xsd:attribute name="SQLType" fixed="1111"/>
<xsd:attribute name="DBTypeName" fixed="REF CURSOR"/>
<xsd:attribute name="IsNullable" fixed="true"/>
<xsd:attribute name="Remarks" fixed=""/>
<xsd:attribute name="NullAllowed"/>
<xsd:complexType>

Generate the request manually as a string in your script or by using the XML connector in the map. If you use a script to generate the XML, remember to escape the double quotes for the attributes when building the string. The following example shows a request XML instance created based on the schema:

Note: The Request and Parameter elements always start the XML request instance.

The first parameter in this example is defined in the XML instance as ACNO. ACNO becomes the first element in the request. The parameter name also has a corresponding type definition. The values in the type definition become the attributes. ACNO has a type definition named ACNO_Type. In the definition of ACNO_Type, there are attributes with values. The attributes, Direction, SQLType, DBTypeName, and IsNullable are all attributes with the values they contain in the schema. If the parameter is an input or in-out parameter, it is passed in as a data element of the parameter name. In this example, ACNO has 10019 as its value.

The second parameter is called CV and is a REF CURSOR. The attributes for the CV_Type are defined in the schema. They are the same as for ACNO, but the data values are different. The data values are Direction, SQLType, DBTypeName, and IsNullable. No data value is passed, since the parameter is an output parameter. This is captured by the component and put in the WebRowSet XML response.

JDBC Stored Procedure Invoker Properties

Property	Description
Repository Home	Directory where all schema files are generated and stored. For every stored procedure that you select, the invoker generates a corresponding request and response schema file. These files are stored in the repository home. The value of the User ID property and a hard-coded "SP" directory are used to create a directory under Repository Home. If the User ID is not specified, then SP is created in a directory called No_UserId. Tip... If you do not know the path of your temporary directory, locate it by typing %tmp% at the command prompt. Example #1 Repository Home = %tmp% User Id = tester The request and response schemas are created in %tmp%\tester\sp. Example #2 Repository Home = %tmp% User Id = (blank) The request and response schemas are created in the %tmp%\NO_USERID\sp Note: The Repository Home directory has a generated subdirectory called spInvokerBaseSchemas, which contains the base schemas: invokerBase.xsd, invokerRequest.xsd, and invokerResponse.xsd.
Recreate Schema	Indicates whether to recreate the request and response schema for every database session. By default, Recreate Schema is set to FALSE. This ensures that the JDBC Stored Procedure Invoker does not recreate the request and response schema for every database session. If the procedure has changes, set the value to TRUE. This indicated that the component must generate a new request and response for the procedure if the schemas already exist.
Procedure Schema	(Optional) If a procedure is contained within a particular schema, specify the schema in which to search for the procedure. If this field is not specified, all schemas in the database are searched.
Driver	JDBC driver to load and use. For example: oracle.jdbc.driver.OracleDriver
Host URL	JDBC URL connection to the database. For example: jdbc:oracle:thin:@dbserver1:1521:mydbname
User ID	Username for the database session. Note: Provide the user ID for the database session only if it is configured for your database.
Password	The password for the database session.
Jar Location	Directory that contains the JAR files that are required to load the JDBC driver. Note: You cannot append multiple paths to the value. All driver JAR files must be located in the same folder. Caution! Always use the latest JAR files for a particular database version.
Global Transaction	Select this option to make it a global.

Supported Actions

Action	Description
Execute	Executes the selected stored procedure. Requires two DJMessage objects, one that contains the XML request and one to capture the XML response. The maximum size limit for DJMessage is 2 GB.
Connect	Opens a persistent connection to the database
Disconnect	Closes an existing database connection and performs any clean up actions required.
BeginTransaction	Starts an implicit transaction for the session that can be committed or rolled back.
CommitTransaction	Commits any insert, update, or delete operations made on the database.
RollBackTransaction	Rolls back any SQL statements that have been sent to the database using the WebRowSet XML format.

Tip... Call Connect and Disconnect explicitly in your process flow.

Supported Action Parameters

Action	Parameter	Description
Execute	SourceMessage	Request XML message used to execute the procedure.
Execute	TargetMessage	Response XML message returned from executing the procedure. The target message is in WebRowSet XML format.

Supported Action Properties

Action	Property	Description
Execute	Stored Procedure	Stored procedure that the JDBC Stored Procedure Invoker should invoke. For example: StoredProcedure=SP_RS. Select a stored procedure from the list. Based on your selection, the JDBC Stored Procedure Invoker automatically creates a request and response schema in the repository home folder. Note: The spInvokerBaseSchemas contains the base schemas invokerBase.xsd, invokerRequest.xsd, and invokerResponse.xsd. The user interface displays a reference to the request and response schemas.
Execute	Source Schema	Name of the schema created based on metadata from the stored procedure. The source schema is used to define the structure of the request that is passed in at run time. The response schema file is located in the repository home folder. The JDBC Stored Procedure Invoker automatically maps the path to the request schema in the repository home.
Execute	Target Schema	Name of the response schema file created by the JDBC Stored Procedure Invoker, based on the chosen stored procedure. The response schema file is located in the repository home folder. JDBC Stored Procedure Invoker automatically maps the path to the response schema based on your entry in the Repository Home instance property.

Errors

Error Code	Error Name	Description	Reason
19	ERR_OPENERR	Error while connecting to the database	Returned when an exception occurs while connecting to the specified database
27	ERR_CLOSERR	Error while disconnecting from the database	Returned when an exception occurs while disconnecting from the specified database
98	ERR_TRANSACT	Error while using transaction method like commit, rollback.	Returned when an exception occurs while using transaction methods like commit and rollback
89	ERR_EXECUTE	Error while running the stored procedure	Returned when an exception occurs while running the stored procedure (Execute method)

Last modified date: 12/03/2024