How to retrieve JDBC adapter connection parameters on service?

Hi

In one of our interface, the input data needs to be inserted into a target DB table; In the table there is a field/column name “CreatedBy”, where they want to pass the username used in the JDBC connection.

Is there a way to read the JDBC connection parameters from IS service based on the connection name?

Thanks for your time and inputs.

Yes, it is possible, review the services in WmART and WmJDBC packages.

Hi Ravindran,

Ca you describe your use case a bit more in detail please?

What is your database vendor?

I checked IS Built-In-Services Guide (for WmART) as well as JDBC Adapter Users Guide and did not find any services which would have returned the username from the connection.
Even introspecting the packages for non-public services did not lead to a result.

Regards,
Holger

There is one present in WmART - wm.art.admin.connection:getResourceConfiguration which will get the connection props for a specific connection alias.

How many connections do you have currently and in the future? There will be another feasible way to do this task, let us know more details.

Hi,

but this one is not visible in Designer due to missing List ACL privileges (even for Administrators), it is only visible in the package management in IS Admin UI in the list of services for the WmART package.

Regards,
Holger

but this one is not visible in Designer due to missing List ACL privileges (even for Administrators),

Yes, same with me. Can you please help us on how to use these services on our flow?

Also can we use the services under wm.admin package on our normal interface flow? Is it recommended or do we have some restrictions on this usage?

Hi,

you need to place an empty invoke step in your flow and then fill in the service name to invoke by copy&paste or typing manually.

Check the output of the service to see how exactly the variables are structured and named.

As the service does not have an appropriate List ACL it will not be possible to call or select it directly in IS Admin UI and Designer, only indirectly by creating a flow service invoke step as described above.

Due to these restrictions you should use this service with caution as it might change in future releases without any notice.
This is true for all services starting with “wm." instead of "pub.”, even if they are visible in Designer or can be selected & tested in IS Admin UI.

When ever possible such services should not be used directly by any means, but you are encouraged to check if there is a way to do this with services being publicly available.

Regards,
Holger

By now you must be able to invoke the service that I mentioned earlier and be cautious while updating or applying fixes as this service may tend to change anytime.

You should be able to use a pseudo column to obtain values to set such columns, such as UID or USER if you’re using an Oracle DB. Other DB types have similar facilities I think.

If there is control of the DB objects, the even better approach is to have the DB default the column or use a trigger to set the user. That way, you don’t need to set anything at all. Your DB logged in user will be used automatically.

Trying to extract the username from the adapter connection pool can be done but I’d advise against it. wm.art.admin.connection:getResourceConfiguration is the service that can get all the details (as Mahesh notes earlier). Use that in conjunction with pub.document.documentListToDocument to convert the list from name/value pairs (“systemName” and “value”) into a document with usable var names. Use the debugger and trial-and-error to get things into a format you can easily use. But again, I advise against this approach and would instead recommend using the pseudocolumns or the DB setup instead.

1 Like

Hi,

I agree with Rob.
Whenever possbbile this should be left up to the database system to fill such columns, i.e. by using triggers “before insert”.

This “CreatedBy” column would only make sense when it is possible to insert into the schema/table by using different users.

Regards,
Holger