Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Notifications | Configuring StoredProcedureNotificationWithSignature | Creating a StoredProcedureNotificationWithSignature
 
Creating a StoredProcedureNotificationWithSignature
You configure Adapter for JDBC notifications using Designer. For more information about adapter notifications, including what you need to know before you configure and manage them, see Before Configuring or Managing Notifications.
*To configure a StoredProcedureNotificationWithSignature
1. In Designer, right-click the package in which the notification should be contained and select New > Adapter Notification.
2. Select the parent namespace, type a name for the adapter notification, and click Next.
3. Select Adapter for JDBC as the adapter type and click Next.
4. Select the StoredProcedureNotificationWithSignature template and click Next.
5. Select the appropriate Adapter Connection Name and click Next.
The name of the publishable document associated with this notification is displayed.
6. Click Finish.
For more information about adapter notifications and publishable documents, see Adapter Notifications. For more details about the Integration Server publishable documents, see the Publish-Subscribe Developer’s Guide for your release.
7. Designer creates the notification, and the editor for the adapter notification appears.
a. You can select the Adapter Settings tab at any time to confirm adapter notification properties such as the Adapter Name, Adapter Connection Name, and Adapter Notification Template, as necessary.
b. In the Publish Document section, you can specify how you want the notification document to be published:
*To publish documents to Broker, select Broker/Local. This is the default option.
*To publish documents to a JMS provider, select JMS Provider, and provide values for the following input fields:
Field
Description/Action
Connection alias name
Name of the JMS connection alias configured on Integration Server.
If the connection alias is a Broker Cluster configured with Multisend Guaranteed policy, you must add the watt property watt.art.notification.jmsSend.usePublicService and set it to true.
Note:
Adapter Runtime does not support LOCAL_TRANSACTION and XA_TRANSACTION type JMS connection alias.
Destination name
Name of the destination from which you want the JMS trigger to receive messages.
Destination type
Specify whether the destination is a Queue (default) or a Topic.
The information from the Permissions tab appears in the Properties panel.
8. Select the Call tab to specify the stored procedure to call. Provide values for the following call parameters:
Field
Description/Action
Catalog Name
Name of the catalog. The default for the catalog name is current catalog.
Schema Name
Name of the schema. The default for the schema name is current schema.
Note:
You can disable the lookup performed on the database for schema name by configuring the Schema Name parameter in the Configuration tab.
Procedure Name Pattern
To save time, you can type all or part of the procedure name in this field to narrow your search. This is helpful when dealing with a large database that has a long list of procedures. Use % as a multi-character wildcard and _ (underscore) as a single character wildcard.
Note:
With DB2 databases, functions do not appear in the list of procedure names. Only procedures appear in the list.
Note:
If you disable the lookup performed on the database for stored procedure name by configuring the Procedure Name parameter in the Configuration tab, the value in the Procedure Name Pattern field is not taken into consideration OR is ignored.
Procedure Name
Type or select the stored procedure name, depending on how you set the Procedure Name Pattern field. If you select <All Procedures>, this field lists all of the procedures in the selected catalog and schema.
Note:
You can disable the lookup performed on the database for stored procedure name by configuring the Procedure Name parameter in the Configuration tab.
Publish Locally
Specifies whether to publish the notification's publishable document to the local Integration Server. By default, this option is not selected, that is, if the Broker is configured to Integration Server, the publishable document is published to the Broker; otherwise the publishable document is published to the local Integration Server. Selecting the Publish Locally option reduces performance problems, if Integration Server is connecting to a remotely located Broker that is in turn triggering a service on the local Integration Server.
a. The top table on the Call tab based on the signature for the stored procedure you specified:
Field
Description/Action
Parameter Name
Stored procedure parameter name.
SQL Type
SQL data type of the database column.
JDBC Type
JDBC data type of the stored procedure parameter.
Parameter Type
Defines the parameter type as IN, INOUT, or OUT. If you select IN or INOUT, you may also set the input expression in the Expression field.
Expression*
Sets the input for the IN or INOUT parameter types only. The RETURN or OUT parameters will appear automatically on the Call tab's bottom table. For a list of the allowable expression settings by parameter type, and how each parameter will map to the input or output fields, see the following table.
The following table shows valid expressions by parameter type:
Parameter Type
Expression
Input or Output Mapping?
RETURN
Empty (default)
Output field
OUT
Empty (default)
Output field
IN
Default is empty, but it needs to be updated with a fixed value.
No mapping
INOUT
Empty (default)
Output field
ORACLE CURSOR (INOUT)
Empty (default)
Output field. Set the parameters in the ResultSet tab as described later in the procedure.
b. The bottom table on the Call tab lists the following output parameters and values for the stored procedure that will map to the output of the notification's publishable document and returns the value of OUT or INOUT parameters:
Field
Description/Action
Output Parameter Name
Stored procedure output parameter name.
SQL Type
SQL data type of the database column.
JDBC Type
JDBC data type of the output parameter.
Output Field
Name of the output parameter.
Output Field Type
Output parameter Java data type. For a list of JDBC to Java data type mappings, see JDBC Data Type to Java Data Type Mappings.
c. Specify the query time out value of the StoredProcedureNotificationWithSignature notification you are configuring in the following field:
Field
Description/Action
Query Time Out
Query time out value in seconds. This value is the amount of time Adapter for JDBC waits for the notification to execute before stopping the SQL operation. The default value is -1. Use the default value to have the notification use the value indicated on the watt.adapter.JDBC.QueryTimeout property as the time out. If you specify a value equal to 0, or if the watt.adapter.JDBC.QueryTimeout property is not set, the notification executes without a time out. If you specify a value greater than 0, the notification executes with the specified value as the time out.
Note:
-1 is the only permissible negative value for this field.
For more information about the watt.adapter.JDBC.QueryTimeout property, see Forcing a Timeout During Long-Running SQL Operations in Services and Notifications.
9. Select the Configuration tab to configure the lookup parameters for stored procedure. Use the following fields to set the parameters and values:
Field Name
Use Lookup
Schema Name
Performs a lookup on the database for schema name. Possible values are:
*true. Default. Performs a lookup on the database for schema name.
*false. Skips the lookup on the database for schema name. The Schema Name field is now editable and you can enter the value.
Procedure Name
Performs a lookup on the database for procedure name. Possible values are:
*true. Default. Performs a lookup on the database for procedure name.
*false. Skips the lookup on the database for procedure name. The Procedure Name field is now editable and you can enter the value.
Note:
The value in the Procedure Name Pattern field is not taken into consideration/ignored.
10. If the procedure returns a result set, select the ResultSet tab to specify result set parameters using the fields in the following table.
This type of notification can support multiple results sets. Use the icon to create additional result sets as needed.
Note:
While all the tables in the Call tab are updated automatically if the selected stored procedure changes, the ResultSet tab information is not updated automatically. To update this information, you must manually update the fields in the ResultSet tab.
Provide values for the following parameters:
Field
Description/Action
Result Set Index
An index is automatically assigned to each result set. The first row default value is 1.
Result Set Name
The name of the result set you want to create.
Result Set Name(from second row)
Select result set name.
Column Name
The name of the column of the result set.
JDBC Type
The JDBC data type of the result column.
Output Type
The Java data type of the result column. For a list of JDBC to Java data type mappings, see JDBC Data Type to Java Data Type Mappings.
11. The information about using the Permissions tab to assign an access control list (ACL) to an element appears in the Properties panel.
12. From the File menu, select Save.
13. You must schedule and enable the notification using Integration Server Administrator before you can use it. For details, see Managing Polling Notifications.