JDBC CustomSQL, DynamicSQL - Schema

, ,
1

I’m using WM 6.1 and SQL SERVER 2000 and have several JDBC customSQL adapter services that I’ve created.

The problem is that I can’t run them without the fully qualified schema name in the actual SQL statement:

For example:
SELECT IMITM FROM TESTDTA.F4102 (WORKS)
SELECT IMITM FROM F4102 (DOES NOT WORK) (Error: Invalid object)

Question: How do you setup the JDBC adapter so that you eliminate the need to have the fully qualified schema name?

Environment will require developing in test environment then promote to production database. There are also multiple schemas per database.

TESTDTA (Development Business Data)
TESTCTL (Development control tables)

CRPDTA (Test Business Data)
CRPCTL ( Test control tables)

PRODDTA (Production Business Data)
PRODCTL (Production control tables)

Tried using currentSchema=TESTDTA but that didn’t seem to do anything.

This is a very important issue for the client and if I can’t solve this issue the client may stop using webMethods.

Any help or examples would be greatly appreciated.

2

you have to use Dynamic SQL and retrieve “Schema” value at runtime by using config files.

Before invoking the adapter service retrieve the “Schema” value from config file at run time .

Your dynamic SQL will look like below and value of “Schema” will be replaced at run time

SELECT IMITM FROM ${Schema}.F4102

3

Thanks sai for your response. I can see how this would work for Dynamic SQL but how would you get the CustomSQL services to work?

Also, there is suppose to be a property on the JDBC Adapter currentSchema=YourCurrentSchema that the services would default to if the service is not able to validate an object but that doesn’t seem to work.

Thanks

4

I am not aware of such property in CustomSQL

Documentation on CustomSQL says that.

“Because an adapter service that uses custom SQL provides no error checking, be sure that
your SQL statement works correctly. You can verify SQL statement accuracy using your
vendor’s SQL utility”

I believe that you have to provide the correct SQL for it to work.

5

Thanks sai.
The SQL does work properly and was tested before the posting.

The setting in the IS for JDBC adapters other properties states in the manual that currentSchema=YourCurrentSchema should work as I stated before but I can’t find any examples are someone who has used that property before.

If you have more than one schema I guess you have to set up an adapter for each schema owner.

Thanks for the reply