Dear WM-Users,

My integration have around 300 custom sql adapter services. I want to update custom sql database table schema’s dynamically.

is there any possibility for updating db schema dynamically for custom adapters services?

Where we can find adapter services in IS back-end ?

Thanks for Advise,

Do you mean changing the connection of your customSql ?

you can change the connection with “WmART/pub.art.service:setAdapterServiceNodeConnection”

Thanks for your replay.

But I want to change custom adapter service database schema name dynamically not connection.

When we create the adapter service, it actually creates node.ndf in
file:///C:/webMethods6/IntegrationServer/packages/AS/ns/customAdapterService/
folder.

This is a xml file, it contains IRTNODE_PROPERTY tag stores properties about JDBC adapter connection and database operation properties. The value of this field is stored as encrypted string. I want to decrypt and change the operation properties then encrypt and place the file in same folder.

Do you have any idea about how can we decrypt and encrypt the IRTNODE_PROPERTY tag value?

What’s mechanism webMethods use to encrypt the IRTNODE_PROPERTY tag value?

Thanks for your advise,
KSK

The JDBC adapter supports another type of adapter service (Dynamic SQL). This would work if you have the same table structures in different schemas within the same database. Say you wanted to perform a simple insert like the following, only changing the schema from ‘default’ via an input to your service:

insert into default.mytable (value1, value2) values (‘foo’,‘bar’)

You would enter a SQL statement like this into the dynamic SQL adapter service like so:

insert into ${myschema}.mytable (value1, value2) values (?,?)

Then you would finish the setup of the dynamic SQL service (i.e. defining input parameters for the two ‘?’ inputs). After this if you looked at the input signature for your adapter service you would see the following inputs (assuming you named the variable inputs value1 and value2):

doc > myInsertServiceName
doc > overrideCredentials
string > $dbUser
string > $dbPassword
string > myschema
string > value1
string > value2

I’m not sure from your question if this is what you were trying to achieve; however, this method will work to allow you to dynamically change the schema you interact with at runtime, provided the table structure is the same across schemas.

Thanks for your replay.

I have around 300 above custom adapter services and I have to change the schema every quarterly in QAT environment. So if I use dynamic SQL means I need to write 300 dynamic SQL services. Am I right? Apart form this I can go and change the schema manually.

Is it possible to update all the custom adapter services with one dynamic SQL service?

Thanks for your information,
KSK

So you have 300 customSQL, and in the SQL field you provide a Sql’request with a schema define in front of the names of the table ? basically “select colum from SCHEMA_HARDCODED.table_name” ?

so you want to change this hardcoded URL field ?

Yes, I need to change the schema define in front of the names of the table. I give you some examples below for clear understanding.

select col1,col2… from schemaName.tableName
insert into schemaName.tableName values (value1, value2…);

I want to update above schema name. Any suggestions would be really useful for me.

Thanks,
KSK

I got the solution, I have de-serialized IRTNODE_PROPERTY tag value by using IDataBinCoder class.

Thanks for all about your support - KSK

KSK,

Would you mind sharing the code that you used to decode the file?

Thanks!

pub.jdbcAdapter:updateServiceSchema
pub.jdbcAdatper:updateNotificationSchema

These can be used to programmatically change the schema name of services and notifications. Refer to the JDBC Adapter docs.

Alas, this won’t work for SQL within custom JDBC adapter services.

Hi Smile,

If you don’t mind can u plse share the procedure how you changed the schema name for a custom sql adapter.

Thanks,