webMethods JDBC Adapter

Release Notes


webMethods JDBC Adapter Version 6.5

Software AG is pleased to introduce version 6.5 of the webMethods JDBC Adapter. This is a major release of the adapter that simplifies the management of your JDBC Adapter components, as well as provides you greater flexibility in designing and maintaining your JDBC Adapter integrations. This release provides features to help you better manage your adapter connections and polling notifications, as well as several features that will make users of the adapter with Oracle databases more productive. This release includes the following major enhancements.

Connection Management Enhancements

Ability to Programmatically Configure Adapter Connections

If you need to define a large number of adapter connections at one time, the process can be time consuming and prone to error. To streamline this process, the adapter now provides a new public service that you can use to read in adapter connection properties from an XML file and automatically create a set of adapter connections.

Ability to Specify Username and Password Credentials Dynamically at Runtime

All adapter services and notifications are associated with a specific adapter connection when they are defined. Previous versions of the adapter provided the ability to change the connection associated with a service at design time, and also the ability to dynamically associate a different connection with a service at runtime. However, in both of these cases you must define an adapter connection that can be swapped out for the original adapter connection. In some cases this can mean that you must create a large number of possible adapter connections that might or might not be used frequently. To simplify the management of adapter connections, and to provide better visibility into which user initiates a service, this release introduces the ability to dynamically specify just the username and password credentials for a connection at runtime.

Utility Service to Programmatically Update Passwords for Existing Adapter Connections

Previously, the only way to update the password for an adapter connection was to manually edit it in the adapter's Edit Connection screen in the Integration Server Administrator. However, this process can be time consuming for users who have many adapter connections, especially if they have to update the passwords regularly for compliance reasons. This release of the adapter provides a new public service that allows you to programmatically update passwords for adapter connections, enabling you to automate the process of updating multiple adapter connection credentials quickly.

Ability to Obtain Adapter Connections from the JDBC Adapter Connection Pool for Your Custom Services

This release of the adapter provides a new adapter service called the Execute Service that enables you to obtain JDBC Adapter connection objects from the adapter's connection pool and use them for user-defined services. In earlier versions of the adapter, there was no public service available that allowed you to obtain connection objects from the connection pool. With this new adapter service, you can now write your own custom Java services and execute them against the database within the Adapter Runtime framework.

Polling Notification Enhancements

Ability to Enable Update Notifications to Publish Old Values as Well as New Values

In previous releases of the adapter, Update polling notifications always published the updated values for event columns. For example, if a notification monitored a column where the value changed from "3" to "5," the notification would publish the new value of "5." The adapter did not have the ability to publish the original value of "3." However, users want the flexibility to capture and publish the original value and/or the new value. This release of the adapter enhances the Update polling notifications by adding a new column in the notification template called "Output Value Type". In this column you can choose either "Old" or "New," depending on what data you want the notification to publish.

Ability to Configure Polling Notifications to Always Publish Locally

You can now configure a polling notification to always publish its documents to the local Integration Server, even if the Integration Server is configured with a Broker. In earlier releases of the adapter, polling notifications would publish to a Broker if there was one configured for the Integration Server. If there was no Broker configured, then the polling notification would publish directly to the hosting Integration Server. This behavior could potentially cause performance problems if you have multiple Integration Servers hosting similar applications and all of these Integration Servers are configured with a central Broker. If a polling notification always invokes a service on the local Integration Server, then the document would first be published to the Broker, and then would be published back to the local Integration Server, potentially impacting performance. This feature enables you to avoid this potential performance problem.

Improved Error Handling when Migrating Polling Notifications

When migrating a polling notification between Integration Server environments (for example, between a development or staging environment and a production environment), the polling notification must be in the same state (Disabled or Suspended) on both Integration Servers. If the notifications are in different states, the polling notifications could potentially encounter problems where the notification's database objects (that is, the buffer tables and triggers) might not be created or dropped correctly. This feature improves the migration of polling notifications by providing better error handling messages when migrating notifications that are in non-suspended states.

Support for WHERE Clauses with Basic Notifications

Basic Notifications now provide the ability to configure a WHERE clause, enabling users to specify more sophisticated event criteria. For example, you can now use a Basic Notification to monitor a table of supplier company names and only publish events for company names that match a specified first letter or prefix, as defined by the WHERE clause.

Ability to Configure Basic Notifications to Not Delete Records upon Processing

This release enhances Basic Notifications to not delete records in the notification's associated table when an event has been processed. Basic Notifications are provided for users to manage their own database tables and triggers, rather than having the adapter manage them. Basic Notifications are designed to delete a record from the database table after the record has been processed. However, in some cases users might want to use the Basic Notification's database table as a way to persist the records, or users might want to associate a Basic Notification directly with a production table and therefore do not want the Basic Notification to delete the record from the database. Rather than delete an event, users want the ability to update an event record to indicate that it has been processed.

Utility Service to Clean up Database Objects for Polling Notifications

This release of the adapter provides a new public utility service that cleans up a polling notification's associated database objects if they are not dropped correctly. This service allows you to build logic directly in your flow or Java services if the notification encounters a problem. In earlier releases of the adapter, if the notification encountered a problem with its associated database objects (for example, if an administrator accidentally deleted a database trigger but not a buffer table), the notification may fail to drop all of the database objects. In this case, the adapter cannot enable the notification until an administrator manually drops the existing database objects. To prevent this type of manual intervention, this utility service automatically drops a polling notification's associated database objects.

Utility Service to Identify Database Object Information for Notifications

Polling notifications automatically create objects (that is, triggers, buffer tables, sequences) on the database to manage notification data. This release of the adapter introduces a new public utility service that enables you to easily obtain a list of all database objects associated with your JDBC Adapter notifications. You can then use this list to simplify or even automate the management of those objects.

Oracle-Specific Enhancements

Support for the Oracle CLOB and BLOB Data Types

The adapter now supports the Oracle-specific CLOB and BLOB data types (java.sql.Clob and java.sql.Blob) as input parameter data types for use in adapter services.

Streaming Support of BLOB and CLOB Data with Oracle Databases

In earlier versions of the adapter, the adapter only supported the byte array and String data types when retrieving BLOB or CLOB fields. However, users sometimes encountered out of memory errors when converting large BLOB data into bytes. This version of the adapter introduces two new data types, java.io.Reader for CLOB and java.io.InputStream for BLOB, to support the streaming of byte data when working with Oracle databases.

Support for ARRAY and STRUCT Data Types with Oracle Databases

The adapter now supports the ARRAY and STRUCT data type objects as input and output parameters in adapter services. In earlier versions of the adapter there was no way to insert or retrieve a record or list as an input or output to an adapter service. To provide this capability, the adapter now supports the java.sql.STRUCT and java.sql.ARRAY data types as input and output parameters for adapter services.

Ability to Process Oracle Nested Cursors in a Result Set

The Stored Procedure adapter service has been enhanced to fully process result sets from Oracle databases that contain nested cursors. If a Stored Procedure adapter service contains an Oracle cursor, the adapter now checks for the presence of nested cursors in a service's ResultSet and processes them recursively. In previous versions of the adapter, the service only allowed the processing of Oracle cursors that were returned at the top level.

Other Notable Features

Ability to Provide the Size of Result Set Data for Select Statements

This release of the adapter enables you to include the size of a select statement's result set in the output signature of an adapter service. You can now use this data when defining your application logic within your flow or Java services.

Ability to Set the Transaction Isolation Level in an Adapter Connection

The adapter now allows you to specify a database driver-specific transaction isolation level in your adapter connections.

Ability to Retrieve Row Count Data for Insert and Update Adapter Services

For Input and Update adapter services, the adapter now provides the ability to retrieve the total number rows of data that have been inserted or updated in the database and make this data available in the output signature of the services. You can now use this data within the application logic of your flow or Java services.

Ability to Not Require Output Signature Configuration for CustomSQL and DynamicSQL Adapter Services

In earlier versions of the adapter you had to specify the output columns for Custom SQL and Dynamic SQL adapter services at design time. However, you might have a SQL statement that selects a large number of columns, or, for a Select SQL, you might not know all of the columns that the statement will return. To simplify the configuration of the Custom SQL and Dynamic SQL services if you do not want to configure the output signature at design time, the adapter now inspects the result set metadata and constructs the output signature.

Support for New Object Data Type

This release adds a new data type called "Object." This data type is useful when you want to configure an adapter service to use a database driver's setObject method instead of its setString method for CHAR, VARCHAR, and LONGVARCHAR data types because the performance of the setObject method can be faster than when calling the setString method.

Ability to Set a Query Timeout Limit at the Individual Adapter Service and Notification Level

The adapter now includes a query timeout parameter that allows you to configure a query timeout limit at the individual adapter service and notification level. This parameter, if specified, overrides the global watt.adapter.JDBC.QueryTimeout property.

Expansion of Update Schema/Notification Services to Additional Services and Notifications

Earlier versions of the adapter provided services to update the schema for adapter services and connections. However, those services only provided support for the Insert, Update, Delete, and Select services and notifications. This release of the adapter expands the UpdateServiceSchema and UpdateNotificationSchema services to support adapter services and notifications that were not previously supported by these utility services.

For a list of all the changes and issues resolved since the last release, see the readme file for the webMethods JDBC Adapter on the Software AG Documentation Web site or in the directories in which you installed the webMethods JDBC Adapter.

For information about previous releases of the webMethods JDBC Adapter, see the release notes for those releases, below.


webMethods JDBC Adapter Version 6.0.3 Feature Pack 1

This feature pack contains the following new features:

StoredProcedureWithSignature - This template simplifies configuring a stored procedure service by providing automatic signature introspection. The user no longer needs to reference and enter the parameters. Signatures of in, out, inout, and oracle cursor parameter types and return values are automatically listed in the configuration tab.

StoredProcedureNotificationWithSignature - This template simplifies configuring stored procedure notifications by providing automatic signature introspection. The user no longer needs to reference and enter the parameters. Signatures of in, out, inout, and oracle cursor parameter types and return values are automatically listed in the configuration tab.

UpdateServiceSchema - This service programmatically changes the schema settings for an adapter service.

UpdateNotificationSchema - This service programmatically changes the schema settings for an adapter notification.


webMethods JDBC Adapter Version 6.0.3

The webMethods JDBC Adapter is an add-on to the webMethods Integration Platform that provides seamless and real-time integration with many relational database systems that support and provide JDBC drivers.

This adapter supports not only standard database operations, such as Insert, Update, and Delete, but also provides extensive support for stored procedures and notifications.

Summary of Major Features for Version 6.0.3

Additional Database and Driver Support

The JDBC Adapter now supports the following additional databases running on the operating systems supported by the database vendors:

JDBC Adapter 6.0.3 also adds support for DataDirect Connect for JDBC, edition 3.3, where appropriate. See the webMethods JDBC Adapter Installation Guide for a detailed list of supported operating systems and drivers.

Batch Services

The JDBC Adapter now supports two new services for performing faster Inserts and Updates of large amounts of data:

Template

Description

BatchInsertSQL

Inserts new information into a database table in a batch manner, where a COMMIT does not occur until all data has been inserted successfully.

BatchUpdateSQL

Updates existing information in a database table in a batch manner, where a COMMIT does not occur until all update operations have completed successfully.

These templates include a mapping for an output field to reflect the number of affected rows. The services return either the number of rows or an integer that indicates successful execution.


webMethods JDBC Adapter Version 6.0.2

Additional Database and Driver Support

The JDBC Adapter now supports the following additional databases running on the operating systems supported by the database vendors.

JDBC Adapter 6.0.2 also adds support for Solaris 2.9 and DataDirect Connect for JDBC, edition 3.2, where appropriate. See the webMethods JDBC Adapter Installation Guide for a detailed list of supported operating systems and drivers.

Support for Ordered Notifications

The JDBC adapter can now publish notification data for multiple Insert, Update, and Delete operations on multiple tables in the order in which the events occurred. An Ordered Notification template is provided in the webMethods Developer.

Enhanced Logging Support

The JDBC Adapter uses the Integration Server’s logging mechanism to capture messages and write them to the Audit, Error, or Server logs. Information for the JDBC Adapter’s error codes is also now provided in the webMethods JDBC Adapter User's Guide.

Enhanced Adapter Service Template Scalability

Users can now provide a schema list to be used as a table filter as a better performing alternative to the default behavior of automatic introspection of all database objects when creating an adapter service or notification for a large database.

Upgrade Utility Support

The webMethods JDBC Adapter Installation Guide references a utility available for assisting users upgrading from the Enterprise JDBC Adapter Version 4.6 to the webMethods JDBC Adapter Version 6.0.2.


webMethods JDBC Adapter Version 6.0.1

Support for Notifications

The JDBC Adapter supports notifications, which are changes of interest that have taken place in the database. The adapter supports notifications that result from Insert, Update and Delete operations, as well as those prompted through stored procedure calls. There are Automatic, Basic, and Stored Procedure Notifications. Automatic notifications create triggers on a database and buffer tables to hold data.

Support for Multiple Transaction Types

The JDBC Adapter supports Automatic (NO_TRANSACTION), Controlled (LOCAL_TRANSACTION), and 2-phased (XA_TRANSACTION) transactions. In Automatic transaction mode, the adapter handles the transaction boundaries. In Controlled transaction mode, users have control over the transactions. Using XA JDBC drivers, the adapter can support 2-phase transactions executed across multiple databases. The adapter works with the webMethods Integration Server to act as the transaction manager for 2-phase transactions.

Support for Connection Pooling

The JDBC Adapter supports connection pooling, which provides for efficient use of connections. The connections are held by the various flow services while they are actively processing database transactions and returned to the pool as soon as transactions are over. This is particularly efficient in cases where the flow service does several things besides doing transactions with a database.

Support for Exactly Once Notification

The JDBC Adapter supports exactly once notification in association with the Integration Server configuration for transient storage. This feature ensures that duplicate documents are not forwarded to the Integration Server even under fault/failure situations.

Template-Based Integrations

The JDBC Adapter provides service templates and notification templates for interactions with databases. This makes it easy to develop flow services to interact with a database without having to type the SQL.

Service templates and notification templates represent specific tasks for doing work within a database. For example, a JDBC adapter service that deletes data from a database might be based on the DeleteSQL service template included with the adapter. You can create specific adapter services using the templates and execute them using parameters specified when the adapter runs. These parameters include the actual data required by the adapter to perform the operation, such as a table name.

The JDBC Adapter supports the following templates:

Template

Description

CustomSQL

Executes free format SQL on a database.

DeleteSQL

Deletes rows from a table and includes a mapping for an output field that stores the number of affected rows.

InsertSQL

Inserts new information into a database table.

SelectSQL

Retrieves specified information from a database table.

StoredProcedure

Calls a stored procedure to perform database operations.

UpdateSQL

Updates existing information in a database table and includes a mapping for an output field that stores the number of affected rows.

DynamicSQL

Configures a dynamic SQL statement, part of which is set at runtime using the Input field.

BasicNotification

Polls a database table for data using a SELECT operation.

DeleteNotification

Publishes notification of DELETE operations on a database table.

InsertNotification

Publishes notification of INSERT operations on a database table.

StoredProcedureNotification

Publishes notification data by calling a stored procedure on a database.

UpdateNotification

Publishes notification of UPDATE operations on a database table.

Platform Support for JDBC Adapter 6.0.1

The JDBC Adapter supports the following databases and operating systems:

Database & Version

Operating System

Oracle 8.0.5, 8.1.7, 9.0.1, 9.2.0

  • Windows NT 4.0
  • Windows 2000 Professional
  • Windows 2000 Server
  • Windows 2000 Advanced Server
  • Windows XP Professional
  • Oracle Solaris 2.7, 2.8
  • HP-UX 11, 11i
  • AIX 4.3.3, 5.1
  • Linux 2.2
  • Mac OS X

Microsoft SQL Server 7.0 & 2000

  • Windows NT 4.0
  • Windows 2000 Professional
  • Windows 2000 Server
  • Windows 2000 Advanced Server
  • Windows XP Professional

The JDBC Adapter supports the following JDBC drivers:

Database & Version

Driver & Version

Oracle 8.0.5, 8.1.7, 9.0.1, 9.2.0

Oracle JDBC Thin Driver and OCI Driver

Microsoft SQL Server 7.0

DataDirect Connect for JDBC, edition 3.1

Microsoft SQL Server 2000

Microsoft SQL Server 2000 Driver for JDBC, version 2.2.0019

Note: For a complete listing of the combinations of databases, operating systems, and drivers the JDBC Adapter supports, see the webMethods JDBC Adapter Installation Guide Version 6.0.1.


Copyright © 2012  Software AG, Darmstadt, Germany and/or Software AG USA, Inc., Reston, VA, United States of America, and/or their licensors.

Detailed information on trademarks and patents owned by Software AG and/or its subsidiaries is located at http://documentation.softwareag.com/legal/.

Use of this software is subject to adherence to Software AG’s licensing conditions and terms. These terms are part of the product documentation, located at http://documentation.softwareag.com/legal/ and/or in the root installation directory of the licensed product(s).

This software may include portions of third-party products. For third-party copyright notices and license terms, please refer to "License Texts, Copyright Notices and Disclaimers of Third Party Products." This document is part of the product documentation, located at http://documentation.softwareag.com/legal/ and/or in the root installation directory of the licensed product(s).

ADAPTER-JDBC-RN-65-20121015