StoredProcedureWithSignature ." JDBC adapter problem in webMethods Integration Server 8.2.2.0

Hello friends.

I have the following problem: when JDBC adapter of type StoredProcedureWithSignature is changed (even by simply locking and saving it) or DB is restored from backup or similar changes are made, it stops working. Suddenly it stops seeing its procedure/function name, which is for example <.“ESBW.CHANGE_PRODUCT_ADMIN_DATA”>. During the call the underlying DBMS or adapter machinery complains:

ESBW is an Oracle package name.

The problem seems to occur due to double dot at the beginning. If I manage to change the procedure name to <ESBW.CHANGE_PRODUCT_ADMIN_DATA> (by making Procedure Name Pattern empty and putting my own procedure name), after adapter service save, the procedure parameters are wiped out, most likely because ESB adapter no longer sees such procedure name, which leads to this error:

That most likely means the procedure gets improperly called due to unwillingly emptied signature.

The only solution I have found so far is to create new adapter service of type StoredProcedure (without signature this time) and manually put IN/OUT/RETURN parameters, and put procedure name without this fancy <.">. The problem with this solution is that at the end of the day nobody wants to recreate hundreds of services, it would impose a considerable burden and would be error prone and require lots and lots of regression tests as well.

To put it simply, I wonder why ESB JDBC adapter service sees all DB procedures in <.“xxx”> format (in Procedure Name box), but if it is invoked, it fails. Also, how to make it work without huge effort.

I use com.wm.dd.jdbcx.oracle.OracleDataSource driver, but the problem occurs also in some other drivers I’ve tested.

Please help.

Product webMethods Integration Server
Version 8.2.2.0
Updates IS_8.2_SP2_Core_Fix7
Build Number 228
SSL Strong (128-bit)
Java Version 1.6.0_27 (50.0)
Java VM Name Java HotSpot™ 64-Bit Server VM
Java Build Info 20.2-b06, mixed mode
Java Vendor Sun Microsystems Inc.

Can you share your Oracle Version as well as the version of the database driver?

Additionally provide us the Fixlevel for the JDBCAdapter.
There have been quite some issues related to StoredProcedure in the last Fixes for JDBC Adapter.

I am not sure if the DataDirect Driver (com.wm.dd.jdbcx.oracle.OracleDataSource) will work due to license restrictions. It might be better to obtain an Oracle driver from OTN (Oracle TechNet).

Regards,
Holger

Here you are.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
“CORE 11.2.0.2.0 Production”
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


Description JDBC Adapter
Adapter Version 6.5 Release Notes
Updates WmJDBCAdapter_6.5_Fix28
JDBC_6.5_Fix42
JCA Spec Version 1.0
Vendor Name webMethods, Inc.

I don’t know how to check the version of the database driver.

You may check driver version from the jar file you placed in the IS class path like ojdbc6.jar

HTH,
RMG

Lukaz,

Can you please recreate the same procedure with diff name, use StoredProcedurWitSignature Adapter service and check if it is working or not.If not there is a issue in the storedProcedure.

Thanks,

Lukaz,

Can you please recreate the same procedure with diff name, use StoredProcedurWitSignature Adapter service and check if it is working or not.If not there is a issue in the storedProcedure.

Thanks,

Thanks for the reply.

No, it still does not work. In fact, this problem happens in most (all?) SPWS adapters I have, regardless of the DB connection (DB instance/schema/catalog).

?ukasz

Please share us the first paragraph of the META-INF/MANIFEST.MF file (a text file) from the ojdbc.jar you are using.
The x indicates the minimum JVM-Version for which this jar will be working.

For the currently supported Oracle Databases there are only ojdbc5.jar (up to Oracle 11), ojdbc6.jar and ojdbc7.jar (introduced with Oracle 12) available.

Latest available Fix for the JDBC-Adapter is JDBC_6.5_Fix50.
Latest IS-Core Fix available is IS_8.2_SP2_Fix16 and for the AdapterRunTime this is WAR_8.2_SP2_Fix6.

You can open the jar with any packaging program like 7Zip, WinZIP etc. as JARs are merely zips, except that extension might be bounded for some additional functionality.

I.e. if there is a Main-Class declared in the Jar you can run it by double-clicking it on windows.
On Unix you can run them with "java -jar " to achieve the same.

The Unix-Installer for SAG-Components work this way (as an example).

Regards,
Holger

Lukaz,

Try to follow steps given by Holger and also kindly raise a ticket with SAG to find out the fix to the earliest. Please update the forum with the latest news.

Thanks,

I have multiple ojdbc*.jar files in my /WmJDBCAdapter/code/jars/static folder.

ojdbc6.jar

[i]Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.5
Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC
Implementation-Version: 11.2.0.2.0
Repository-Id: JAVAVM_11.2.0.2.0_LINUX_100812.1
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver
sealed: true

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false[/i]

ojdbc14.jar

[i]Manifest-Version: 1.0
Specification-Title: Oracle JDBC driver classes for use with JDK14
Sealed: true
Created-By: 1.4.2_08 (Sun Microsystems Inc.)
Implementation-Title: ojdbc14.jar
Specification-Vendor: Oracle Corporation
Specification-Version: Oracle JDBC Driver version - “10.2.0.1.0”
Implementation-Version: Oracle JDBC Driver version - “10.2.0.1.0”
Implementation-Vendor: Oracle Corporation
Implementation-Time: Wed Jun 22 11:19:45 2005

Name: oracle/sql/converter/
Sealed: false

Name: oracle/sql/
Sealed: false

Name: oracle/sql/converter_xcharset/
Sealed: false

[/i]

I also have there:

javaxsql.zip
jconn3.jar
jtds-1.2.2.jar
ojdbc14.jar
ojdbc6.jar
oraclepki.jar
osdt_cert.jar
osdt_core.jar
postgresql-8.1-404.jdbc3.jar

Please remove the ojdbc14.jar completely as it is outdated and not really compatible with an Oracle 11g Database.

Additional (regarding to the Adapters Administrators and Users Guide) most of these jars can be placed under WmJDBCAdapter/code/jars instead of WmJDBCAdapter/code/jars/static, thus making it possible to exchange these jars without restarting IS, but simply reloading the WmJDBCAdapter-Package.

Only if the driver addtionally uses native libraries and the jar is only the JNI-Wrapper for those, the jar should be placed under WmJDBCAdapter/code/jars/static, to get the libraries correctly loaded during startup of IS.

Additionally you should check if SCG_8.2_SP2_DataDirect42_Fix1 is applied and configured to your IS.
The resulting Jar is located at common/lib/ext/sl53_cj42.jar and additionally under MWS/lib/* (it is named sl53_cj42_driver.jar here and needs to be manually copied here after the application of the Fix.
IF there are older version of this driver (others than ending in ‘42’) you can remove them.
Remember to edit the IntegrationServer/bin/ini.cnf in this case.
For MWS you need to call “MWS/bin/mws.sh -s default update” to adjust the classpath in the start.jar.

Regards,
Holger