I need to execute multiple SQL queries in single JDBC step(SQL Database Execution) step. I separated each query with ; but not getting expected response. Query 1 does not have any result set and query 2 returns return set.
Query used in JDBC step:
alter session set current_schema = ENVI;select number from policy where polnum='123';
Kindly guide me on this.
AFAIK the JDBC step is designed to execute a single SQL at a time.
Please try with two JDBC steps, one with each SQL statement. Select Use Connection Pool to share the connection between the two steps.
SQL Database Execution (JDBC) - DevTest Solutions - 10.3 - CA Technologies Documentation
I added 2 JDBC steps and checked the "Use Connection Pool " option. In this case I am not getting the expected response. Instead I checked "Keep Connection Open" in first JDBC step . In this case I am receiving the response but the connection is open for next request also. How to close the connection once SV responds and always have a new connection for each request?
Do not select "Use Connection Pool" or "Keep Connection Open" options and each JDBC step will make a new connection.
For our scenario ( shared the SQL queries in first post), the second query runs only based on first query getting passed. So the session for the both query needs to be same.
I am able to achieve same session only by selecting "Keep Connection Open". but the session is active even after the execution of second query.
--- My need is same session should be there for both queries and session should be closed after the execution of second query.