tables info from a JDBC Adapter service

, ,
1

Hi,

How to retrive the tables information programatically from a JDBC Adapter service?did anybody have an idea how to achieve this. I do not see any of the services in WmART package provided this information.

Thanks in advance,
Venkat.

2

You may have to use a custom SQL service to do this.Check your database provider to different queries on metadata.

3

Venkat can you please let us know what type of table information you need to retrieve …

Thanks in Advance
Gouse.

4

Hi,

basically i need to get the list of table names used by all the JDBC Adapter services i have in my webMethods environment. How can i achive this using the flow or java services in webMethods.

Thanks,
venkat123.

5

This is a tough thing to do. With the different types of JDBC services available, there are several places that can hold a table name. And Custom SQL services make it even harder to be certain that you have the complete list. I assume by “webMethods” you mean just Integration Server.

Might I inquire as to the end result you’re trying trying to get? Perhaps there are alternatives.

6

Hello Rob,

we are using IS 6.5 with JDBC Adapter 6.0.3.x. Basically my requirement is to create some kind of ‘depends on’ relationship in MetaData repository between tables and their calling parent flow services(web services).so i basically if i can get the list of tables used by adapter services in package and followed by web services, then i can build this relationship and automate this process.

Thanks,
Subba.

7

Hi Venkat,

I am not sure you can get this , as we doesn’t have any idea which Adapter service is using which Table as in an alternative way
you should implement a service which will be used by all other services which inserts the details in tables what you want ( tablename, which package/service called, webservice etc) so you can have a common repository which gives you all the details what you want.

Rob,

Any Comments/Suggesstions on this.

Thanks in Advance
Gouse.

8

Here are a few steps that can get you part of the way there.

  1. Get the list of packages using wm.server.packages:packageList
  2. Loop over the package list. Ignore packages that start with Wm.
  3. For each package, call wm.server.packages:packageInfo to get the list of services within that package.
  4. Loop over the list of services.
  5. For each service, get the service detail (this is described separately below).
  6. If the service is an “AdapterService” type, then you have a service of interest (assuming that the JDBC adapter is the only adapter you have installed on your IS–if not, you’ll need to refine how you determine that you have a JDBC adapter service).
  7. Use wm.art.ns:queryAdapterServiceData to get the service meta data.
  8. Depending on the type of adapter service, the meta data available will vary. adapterServiceData/templateProperties will have various fields. You can conditionally evaluate them to determine what table names are used. Field names I’m familiar with are “sql” (custom SQL I believe), “tables.columnInfo” (a list), and “tables.tableName” (a list). There are likely to be others.

To get the details of service:

  1. Given a package and service name, retrieve the node.ndf file contents. Place the contents into a document you define that contains fields such as name, description, svc_type, and any other service attributes you want to retrieve. You can use something like this (uses undocumented wM classes):

[highlight=java]
import com.wm.app.b2b.server.Resources;
import com.wm.lang.ns.NSName;

IDataCursor idc = pipeline.getCursor();
String pkg = IDataUtil.getString(idc, “package”);
String service = IDataUtil.getString(idc, “service”);

Resources r = new Resources(System.getProperty(“watt.server.homeDir”, “.”), false);
java.io.File f = new java.io.File(r.getNSNodeDir(pkg, NSName.create(service)), “node.ndf”);

IDataUtil.put(idc, “serviceFile”, f.getPath());
idc.destroy();[/highlight]
2. Get the file contents. Call xmlStringToXMLNode. Call xmlNodeToDocument.
3. Loop over the nodeData/Values/value list. For each entry, place the value/*body into the output document field you created above. For example, if value/@name is “svc_type” put value/*body in the svc_type field of your output document.
4. You can use the fields within the output document to help with the processing steps above.

Hope this is enough to get you started. Good luck!

9

Hi Rob,

Thanks for your Quick reply.

I have some clarification please clear from which package we can get this service wm.server.packages:packageList (which version) and we have [B][FONT=Arial Narrow][SIZE=4]

pub.art.service:listAdapterServices but where we can get wm.art.ns:queryAdapterServiceData.

Thanks in Advance
Gouse.

[/b][/size][/font]

10

The services you cannot find by browsing in Developer are undocumented/unpublished services. Officially, you’re not supposed to use them. If you do, wM will not support any code that does.

One technique to call these services within a FLOW step is to insert an invoke step, calling any arbitrary service. Then on the properties panel in the upper-right hand of Developer, edit the service name to set it to the undocumented service you want to call.