Hi all,
Can anybody please tell me, Why WmDB package is deprecated???

Thanks and Regards,
Raghu

WmDB package is deprecated as the concept of Database is changed in Wm6.0. Wm6.0 uses a concept called Connection polling.

Hi Ram,
Will u please explain me in detail. What concept WmDB package uses.
Can u relate WmDB package to WM5 Adapter.

Thanks and Regards,
Raghu

Hi,

WmDB package was developed using JDBC connection with an inbuild database given for Trading Networks, where as in wm6.0 JDBC Connection Pooling concept is used and there is no inbuit database. Once has to create the database for which scripts are supplied. You can use WmDB package in Wm6.0 but u need to include this package along with yours.

I donot have an idea about WM5 Adapter.

The WmDB package could also be used to execute custom SQL statements. In wM6, you should use the JDBC adapter when doing SQL integration instead of the WmDB package. The 6.0 JDBC adapter is an IS adapter, which means it installs as a package on your IS. This differs from the 4.6/5.0 Enterprise JDBC adapter in that there is not a separate adapter runtime environment. Lots of information on exactly how the 6.0 JDBC adapter works is available on Advantage - look at the Admin and Developer guides for the adapter for more information.

The JDBC Adapter has the following things that WmDB does not have:

1. user interface for service creation integrated into Developer.
2. support for database notifications.
3. better support for transactionality.
4. written specifically to us the 6.x Adapter runtime.
5. has a specific (short) list of DBMSs that it supports and it was tested against each of those DBMSs.

The 6.0 JDBC Adapter is basically the Enterprise JDBC Adapter 4.2 rewritten to work in the 6.0 Adapter runtime. The prior post lists many of the benefits of the new JDBC Adapter.

The WmDB package is the original implimentation of database functionality in the webMethods environment. It is still part of the 6.0 Architecture to support backwards compatibility with existing integrations that have been built on the 3.x and 4.x architecture.

The WmDB package works with a concept called Database Aliases, and not with a Connection Pool. Inorder to impliment some of the same functionality that you have with Notifications in the JDBC Adapter, it requires alot more work to be done manually by the developer such as creating a scheduled service to handle database polling, creating triggers on database tables, and mapping output data from SQL execution to an output document.

One of the major benefits that the WmDB package has, that I have not been able to reproduce in the JDBC Adapter framework, is to create a service that can dynamically support different database connections. For Example, connecting to Database1 and executing the SQL statement, and then from code, changing the connection so it connects to Database2 and runs the same SQL. In the WmDB package, you could write the SQL once, and change the DB it executes against just by changing the DB Alias it uses.

There is one thing I really find disturbing in the use of the JDBC adapter. With WmDB you can just make a service and feed the SQL query to it dynamically. With the JDBC adapter you need to create a separate adapter for every single SQL query you want to execute on the DB. This (in most cases) gives you a very large amount of adapter services, which is not always easy to handle…
But for the rest it is fine

Actually, there is a JDBC adapter service type called Dynamic SQL that is used to do exactly this. Basically you create a single service and pass the SQL that you want to run as a pipeline variable. You still can’t specify dynamic input and output fields, but it works very well for statements with no return values (like inserts and updates). See the JDBC adapter documentation for more details.

Hi Franklin,
But u can’t change connections dynamically through JDBC adapter service, which can be done with WmDB by assign the alias runtime.
Please correct me if i am wrong.

Regards,
Raghu.

I have implemented a JDBC adapter service using Dynamic SQL that is assembled in the pipeline editor, as Skip Franklin suggested above. I am running 6.01 and attempting to connect to Oracle 9i. Anyway, I am not sure if I have run into a bug. Inside the SQL window in the adapter I have simply added the reference to the input string as so: ${sqlString}

My SQL string is: SELECT aSequence.NEXTVAL FROM DUAL

This is exactly as it appears in the results pane in the pipeline editor.

However, the error I am getting is that Oracle cannot execute the string: “SELECT aSequence.NEXTVAL FROM DUAL”. "

I have re-tested this several times, yet something keeps concatenating a period, a space, and an additional double quote to the end of the string I am passing to the adapter.

Has anyone seen this or know what may be causing this?

Thanks in advance.
-Glenn

Glenn,

I tried with “SELECT aSequence.NEXTVAL FROM DUAL” stmt. It is getting value from database. Make sure that the seq exists in oracle.
Also verify your jdbcoutput type in adapterservice.

Experiencing the same issue with the JDBC Dynamic SQL adapter service that I had encountered in my previous posting, above. However, this occurred in a completely different Dynamic SQL. The adapter is performing all of the dynamic substitution successfully, for all 5 input variables I am using, but once again, the SQL fails to execute because the query SQL has the following garbage appended to the very end:

. "

Hence query string looks like this (simplistic view) when the adapter throws the error:

“SELECT x FROM y WHERE y.fieldName = value”. "

This is causing an Oracle ORA-01006: bind variable does not exist error. I suspect Oracle is expecting a bind variable to be referenced after the period, but the statement is just a simple select.

I have verified my output types and they are all varchar as expected.

So, if anyone has a clue as to why the . " (period, space, double quote) gets appended to the end of my SQL String inside the JDBC adapter service, any help is greatly appreciated.

Thanks in advance.

-Glenn

A few weeks ago, a WM staffer informed me that the WmDB package was “un-deprecated” in 6.1 due to negative feedback WM received when they deprecated it in 6. So WmDB is not going to remove it apparently.

A few weeks ago, a WM staffer informed me that the WmDB package was “un-deprecated” in 6.1 due to negative feedback WM received when they deprecated it in 6. So WmDB is apparently not going to be removed.

Our sales rep told me the exact same thing yesterday. Lot’s of negative feedback so the deprecation has been rolle back during release 6 of the product.

Hello Glenn,

I know its a while ago. But I was wondering how did you solved the issue with the Dynamic SQL with the JDBC adapter that throws the error
“SELECT x FROM y WHERE y.fieldName = value”. "

I am trying to run a dynamic sql in webMethods 6.1 using the JDBC.

I need to find the easiest way of producing an Dynamic SQL. Did you use the WmDB package. If so can you use it whilst using the JDBC adapter.

Regards

Paul Pannu
Thanks

Paul Pannu

Paul,

You can do dynamic sql using the JDBC adapter. wmDB is legacy technology and the JDBC adapter is where webMethods is focusing their development efforts. So using the JDBC adapter is the preferred approach.

Assuming Oracle…

Create a Dynamic SQL adapter service. In the dynamic sql field, place the SQL you want to dynamically substitute inside ${}. For example:
SELECT empno,ename,job from ${table} WHERE ${pred}

table and pred become service inputs automatically. (Don’t try to specify them as input fields.

You will need to supply appropriate output fields, 1 for each column in your select list. So if you are used to doing dynamic sql in a 3gl, it’s not quite that “dynamic”. This is a compromise imposed so that when you add the invoke step for your adapter service, you have predefined output fields that you can map from.

Although the number of select list items must be predefined, you could dynamically choose what to select at runtime.

You can supply input fields (i.e. Bind Variables) and these will also become part of the service input. Place a question mark in the SQL text or inside the text you are substituting with a ${expression}. As with other adapter services, the order of the input fields you declare corresponds to the order in which the ? symbols are encountered in the final SQL statement. Remember that in Oracle, bind variables only help you when the SQL statement you are submitting exactly matches a previous SQL statement previously submitted. Otherwise, the SQL statement will still have to be re-parsed. If you are doing dynamic sql, this is probably not likely. You should only consider bind variables for short running queries.

If you are going to be doing a long-running query, it would be better to avoid bind variables because they prevent the optimizer from using column histogram statistics in generating a query plan.