JDBC How to check adapter service template (automatically)?

Hello,

Is there any way to automatically check Adapter Service Template?

For example:
I have a thousand of adapter services (all types: select, insert, update etc.). And now I want to find only CustomSQL.

I’ve found a very userful service in WmART package - pub.art.service:listAdapterServices
and now I’m able to list all my adapter services.
Unfortunately the output contains only “serviceNodeName” (name of adapter service) and “packageName”.
I need a info what kind of template is using…

Hi Tomasz,

you will have to introspect the node.ndf of each AdapterService.

I am not sure if it is in the node.ndf directly or if this is part of the base64 encoded tag.

Regards,
Holger

Thanks Holger for reply,

Yes, information about template is available in node.ndf file as base64

<value name="IRTNODE_PROPERTY">...base64 encoded tag...</value>

after decoded:
…serviceTemplateName(com.wm.adapter.wmjdbc.services.CustomSQL…

But this is the same level of solution as using SAG Designer.
I may open every service adapter and check “Adapter Settings” tab.

I’m looking for any tips how to write an automatic script. The results should be the list of service adapters with CustomSQL template. Any ideas?

How did you decode it? If with a program, then just apply it to all the node.ndf files. You’ll have to figure out how to find node.ndf for JDBC adapters (and not other assets). You could derived the file names from the full names of the adapter services.

I’m using only webpage tool https://www.base64decode.org/

Hi Tomasz,

there is a Built-In-Service pub.string:base64decode.

Some Pseudo-code:


list all JDBC AdapterServices
LOOP over list of services
 convert service name to path and add "/node.ndf" to the end (replace all "." and ":" with "/" and add a "packages/<package name>/ns/" at the start)
 pub.file:getFile for node
 pub.xml:queryXML for IRTNODE_PROPERTY
 pub.string:base64decode IRTNODE_PROPERTY
 search for "CustomSQL"
 BRANCH
  CustomSQL: log serviceName
  $default: do nothing
END LOOP

See IS Built-In-Services Reference for further informations on these services.

Regards,
Holger

1 Like

Based on Holger answer (thank you very much) and my tests I resolved this issue.

Step 1: list of all adapter services
WmART -> pub.art.service:listAdapterServices (this method takes only one input parameter - adapterTypeName. If you don’t know the name you may use pub.art:listRegisteredAdapters method to find it)

Step 2: loop over list of adapter services

LOOP
Step 3: prepare path to node.ndf file (java service) (input params: nodeName, packageName from current iteration of loop)


public static final void preparePath(IData pipeline) throws ServiceException {
// pipeline
IDataCursor pipelineCursor = pipeline.getCursor();
String nodeName = IDataUtil.getString( pipelineCursor, "nodeName" );
String packageName = IDataUtil.getString( pipelineCursor, "packageName" );
pipelineCursor.destroy();

//convert : and . to /
String adapterPath = nodeName.replace(".", "/");
String adapterPath2 = replace.replace(":", "/");
		
//create relative path - recommended
String path = "packages/" + packageName + "/ns/" + adapterPath2 + "/node.ndf";

//version to fullpath, not recommended because of possibility of different IS and OS settings
//String path = "E:/SoftwareAG/IntegrationServer/instances/default/packages/" + packageName + "/ns/" + adapterPath2 + "/node.ndf";
		
// pipeline out
IDataCursor pipelineCursor_1 = pipeline.getCursor();
IDataUtil.put( pipelineCursor_1, "path", path );
pipelineCursor_1.destroy();			
}

Step 4: WmPublic -> pub.file:getFile (input parameter: loadAs -> String)

Step 5: convert string to xml
WmPublic -> pub.xml.xmlStringToXMLNode

Step 6: find IRTNODE_PROPERTY node in xml
WmPublic -> pub.xml.queryXMLNode
(input parameters: node -> from step 5 node, fields: name -> myVariable, resultType -> String, query -> /Values/value[@name=‘IRTNODE_PROPERTY’], queryType -> XQL

Step 7: decode base64 token
WmPublic -> pub.string.base64Decode

Step 8: convert to string
WmPublic -> pub.string.bytesToString

Step 9: clear string from non-letters (java service)

 public static final void clearString(IData pipeline) throws ServiceException {
// pipeline
IDataCursor pipelineCursor = pipeline.getCursor();
String	inputStr = IDataUtil.getString( pipelineCursor, "inputStr" );
pipelineCursor.destroy();
		
StringBuffer sb = new StringBuffer();
//loop over all char in string	
for(int i = 0; i < inputStr.length(); i++){
			
boolean isLett = Character.isLetter(inputStr.charAt(i));
//if is a letter -> append to string buffer object			
if (isLett)
	sb.append(inputStr.charAt(i));	
}
		
String outputStr = null;
//search "CustomSQL" in string	
if (sb.toString().indexOf("CustomSQL") > 0 ){
	outputStr = "1";
}else{
	outputStr = "0";
}
		// pipeline
	IDataCursor pipelineCursor_1 = pipeline.getCursor();
	IDataUtil.put( pipelineCursor_1, "outputStr", outputStr );
	pipelineCursor_1.destroy();	
}

Step 10: branch on step 9 result. if outputStr == 1 -> append to string list (or document list).
END LOOP

Thats all! Ten steps “only” for listing CustomSQL adapter services.

1 Like

Hi Tomasz,

congratulations for resolving pseudo code into real code.

Small addendum to this:
Just use the relative path without the “E:/SoftwareAG/IntegrationServer/instances/default/” part and this will run on every OS supported by webMethods and regardless on the installation directory “SoftwareAG”.
This willl also make the code backwards compatible for platforms (prior to wM 9.6) where there is only one IS instance per installation possible, thus not having “instances/default” folders.

Regards,
Holger

Really a good solution provided by Holger and Tomasz, but you can implement this at max 5 steps without the need of the custom-built java services and no XML query node.

I have used one non-public API and you can use it proper planning and governance (with less risk) and also this service will not be used heavily you can go ahead with this API. See the below screenshot (with comments) if you are interested in it.

Any questions?

One ‘rookie question’. How to make wm.art.ns:queryAdapterServiceData visible in Designer?

@Holger,

I tried to use relative path to node.ndf file start with “/packages/…”.
It didn’t work because of “file not exists” error so I decided to change to full path.

Now I know that it should be without slash at the beginning

String path = "packages/" + packageName + "/ns/" + adapterPath2 + "/node.ndf";

I’ve edited my previous posted code already.

Hi,

esp. on Unix any path starting with “/” is referred to as absolute.

Removing the leading “/” turns the into a relative path, relative to the IS instance (default in most of the cases).

Regards,
Holger

They are hidden for some reason and you can call the service namespace " wm.art.ns:queryAdapterServiceData" by dragging an INVOKE step and copy the full-service namespace. I have provided the working code screenshot if you have any further question you can reach me on email.

1 Like