Software AG is pleased to introduce the latest release, 9.0 of the webMethods Adapter for JDBC. This is a major release of the adapter that makes your JDBC integration much richer and simpler with new capabilities. Changes to this release include:
The adapter now allows you to choose synonyms in all the adapter services, including select, insert, update, and delete. The adapter templates now display synonyms in addition to tables and views, in the Designer.
You can now configure and access new data types introduced in the latest versions of database servers without using an adapter fix or patch. Also, the property files enable you to change the data type of any existing database object type and receive them on Integration Server.
CustomSQL service template generates variables automatically, by parsing queries and fetching the correct data type from the database server. The input and output variables are automatically generated by the adapter template in a single click, making your development faster, simpler and easier.
webMethods Adapter for JDBC 9.0 is upgraded to be compliant with JDBC 4.0 and 4.1.
The adapter has been refactored with the latest JDBC constructs for faster and reliable performance.
The additional log information provides details on the cause and time at which the adapter notifications were disabled.
For latest information on the software and operating systems that the adapter supports, see the webMethods Adapters System Requirements document on the documentation website (http://documentation.softwareag.com/).
webMethods 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.
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.
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 run time. 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 run time.
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.
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 Run Time framework.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The adapter now allows you to specify a database driver-specific transaction isolation level in your adapter connections.
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.
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.
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.
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.
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.html file for the webMethods JDBC Adapter on the webMethods software download 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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. |
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 |
|
| Microsoft SQL Server 7.0 & 2000 |
|
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 © 2015 Software AG, Darmstadt, Germany and/or Software AG USA Inc., Reston, VA, USA, and/or its subsidiaries and/or its affiliates and/or their licensors.
The name Software AG and all Software AG product names are either trademarks or registered trademarks of Software AG and/or Software AG USA Inc. and/or its subsidiaries and/or its affiliates and/or their licensors. Other company and product names mentioned herein may be trademarks of their respective owners.
Detailed information on trademarks and patents owned by Software AG and/or its subsidiaries is located at http://softwareag.com/licenses.
This software may include portions of third-party products. For third-party copyright notices, license terms, additional rights or restrictions, please refer to "License Texts, Copyright Notices and Disclaimers of Third Party Products". For certain specific third-party license restrictions, please refer to section E of the Legal Notices available under "License Terms and Conditions for Use of Software AG Products / Copyright and Trademark Notices of Software AG Products". These documents are part of the product documentation, located at http://softwareag.com/licenses and/or in the root installation directory of the licensed product(s).
ADAPTER-JDBC-RN-90-20150415a