Create a SQL query from workflow to query a mySQL database using Orchestration

[Sajan3]

Hi all,

We have a requirement where we will get requester details from a request item and build a sql query to query a database and check if the user record exists in a table on the database. Can this be implemented using the JDBCProbe activity; and if yes, can someone help us with the way it works/the way it needs to be designed.

We have already activated the Orchestration plugin in our instance. 

Can someone help with suggestions on how this can be implemented using Orchestration.

Thanks in advance,

Sajan

[Kamal17]

Hi Sajan,

I have done couple of orchestrations using custom JDBC activitiy. For your requirement you can try below steps,

1. Create a custom JDBC activity with necessary JDBC connection and credential details.

2. Write an SQL statement. For your case it could be a SELECT query since you want to verify if user account is present in a db table. Something like Eg: SELECT COLUMN FROM TABLE WHERE USER_NAME=12345 should do the job. This is just an example actual query might vary based on your need.

3. In the post-processing script, you can parse the output and take necessary action based on the value.

You can refer this docs link for more details.

Mark Correct if this solves your issue. Hit Like/Helpful based on the impact.

Regards,

Udhay

[Sajan3]

Hi Udhay,

I have built the necessary JDBC connection, credentials and also created the JDBC activity; the connection and credentials were validated and tested successfully. When i try and test the connection on the activity, the message displays as: "Connection looks good". But when i test the inputs, i get the following error message:

I have modified the MID server to accept all IP ranges, included the capability JDBC, included ALL and Orchestration in the mid server supported applications; but still i am not able to proceed beyond this step. I had created the following test inputs:

The Execute command section is as follows:

Can you please suggest me steps on how to rectify this error or finding the root cause of it; also if possible, provide further significant steps to follow. Many thanks in advance.

Regards,

Sajan Allan Roy

[Kamal17 · Accepted solution]

Hi Sajan,

From your error message i see there are two possible issues.

Error 1: Invalid input, i.e your hostname "LON-HCSQLD13.mtvne.ad.viacom.com" could be incorrect.

-From your command prompt, ping this hostname and see if you are getting response. If you get response, then it is a valid hostname. In this case instead of using the complete name as input, just use the hostname "LON-HCSQLD13" without domain info and check.

-If the above set doesn't help then find out the IP Address of this host and use that as input instead of hostname and see if you are getting the same error

I hope when you do one of these you should be able to fix the issue or identify the root cause based on new error message.

Error 2: MID Server null/down

-Make sure your MID Server is up, validated and running. Also, ensure it has been selected as your default integration/orchestration MID Server.

-If everything looks ok then probably you need to verify connectivity between MID Server and your host. To do that, login to your MID server and open command prompt. Run the command, telnet hostname. See if you are getting message connected successfully. If no then probably firewall port needs to be opened between MID server and host to allow connection.

Let me know if you see any new errors after trying these steps. If you have already fixed this issue, you can share your solution and root cause.

Mark Correct if this solves your issue. Hit Like/Helpful based on the impact.

Regards,

Udhay