Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Notifications | Configuring StoredProcedureNotifications | Creating a StoredProcedureNotifications
 
Creating a StoredProcedureNotifications
You configure notifications using Designer.
Be sure to review the section Before Configuring or Managing Notifications before you configure notifications.
For details and important considerations when using a StoredProcedureNotification, see Stored Procedure Notifications.
*To configure a StoredProcedureNotification
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 StoredProcedureNotificaton 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
The 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
The name of the destination from which you want the JMS trigger to receive messages.
Destination type
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 which stored procedure to use with the notification. Use the icon and set the Call parameters as follows:
Field
Description/Action
Catalog Name
The name of the catalog. The default for the catalog name is current catalog.
Schema Name
The name of the schema. The default for the schema name is current schema.
Enable Procedure Lookup (Optional)
To type in the Procedure Name, set this field to False.To select the Procedure Name from a list, set this field to True. The default is False. Set this value to False if you know the name of the procedure and you are working with a large database that has a long list of procedures.
Procedure Name
Type or select the stored procedure name, depending on how you set the Enable Procedure Lookup field.
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.
JDBC Type
The JDBC type of the corresponding Return Field Name.
Return Field Name
Name of the return field of the stored procedure.
Query Time Out
Specify the 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. Use the Parameter tab to specify stored procedure parameters. Use the icon (or the icon to fill in all rows of the table) to create new parameters for the stored procedure.
Field
Description/Action
ParamJDBCType
The JDBC type of the stored procedure parameter. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
ParamName
Stored procedure parameter name.
ParamType
Select OUT as the parameter type because StoredProcedure Notifications do not accept input parameters.
Expression
Keep the default value of ? because StoredProcedure Notifications do not accept input parameters.
Output Name
Name of any output parameters of the stored procedure, if any. For information about output fields for stored procedures, see Stored Procedure Notifications.
Output Type
Output parameter Java type. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
10. StoredProcedure notifications can support one result set (or one Oracle REF CURSOR). The result set can contain nested cursors.
Note:
When using the result set that contains nested cursors, the performance of Adapter for JDBC could degrade. Since the nested cursors are recursively processed, Adapter for JDBC may also return data that may not be required.
If the procedure returns a result set, select the ResultSet tab to specify result set parameters using the fields in the following table:
Field
Description/Action
Result Set Index
An index is automatically assigned to each result set. The first row default value is 1.
Note:
When using for Oracle database, this field is not required.
Result Set Name
Type the name of the result set you want to create. For information about result sets, see Stored Procedure Notifications.
Note:
When using for Oracle database, this field is not required.
Result Set Name (from second row)
Select a valid result set name.
Column Name
Name of column of the result set.
JDBC Type
The JDBC type of the result set column.
Output Type
The Java type of the result column. For a list of JDBC type to Java 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.