Need help with DB queries using groovy
Can anyone please help with this request ?
I want to use one Data source to create Oracle DB connection and use this connection in multiple responders under extension tool using groovy to create custom queries and return results for processing further . How do I achieve this ?
Answers
-
Hi,
Have you tried looking at what the DB tools can do? It's possible that you could use those instead of an extension tool.
0 -
For few simple responders I can utilize DB tool , but for complex responders where multiple queries are executed and processed require scripting , do you have a sample script that you can paste here on how to invoke the connected from datasource and write a query in groovy ?
0 -
Hi,
Does the script just need to build the query? If the script can build the query string it might be easier to data bank the returned query string from the script and parameterize the db tool to execute the query.
The challenge with executing the query from the script will be that it will have to manage all the jdbc sql connection code to execute the query and that might be a significant effort compared to generating the query string and using the DB tool.
0 -
Hello William , thank you for your response . No, the script has more complex logic than just building a query string . Here is some more information , requirement is to extract multiple attributes from incoming request , create custom query based on various possible combinations of data collected from incoming request , execute queries from different tables and use the result set data to create blocks of XML/JSON response , concatenate and then create a final response. Similar logic is applicable to 100s of other responders with data required to query from multiple tables . If there is a functionality to create connection at 1 place and invoke the same connection in various extension tool scripts within various responders it reduces maintaining connection strings and multiple datasources.
0 -
Hi,
The connection created by the DB tool or the DB data source isn't available to scripts. From the description, I would suggest using Data Banks to extract values from the incoming request. Then use variable references, e.g. ${columnName}, in the SQL queries in the DB tool to execute the query using the values from the Data Bank.
Is the queryable to perform the joins of the tables on the server side? Or is the script combining data on the client side?
0 -
Script is combining data on client side only for the reason that there are too many tables and responders on a large scale. Creating more logic on server side adds complexity.
0 -
Hi,
Since the data source and db tool don't make the connection they manage available to scripts an alternative might be to make the scripts "Global Tools" so that they can be reused on multiple responders. That is assuming that many responders will need to perform the same scripted query.
The script would have to do all the jdbc/connection management but at least as a global tool then it could then be added to multiple responders. The connection wouldn't be shared and creating any type of pooling would be difficult. Depending on the database the performance of creating a new connection all the time can become a performance bottleneck.
An alternative might be to write a java class that manages a static pool of connections and has all the desired query code and then add a jar file with that class to the system properties so that responders could call the class/method they need.
0 -
Thank you William for the suggestion !
0