Executing multiple SQL queries in single JDBC step

[SKavitha]

Hi,

 

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.

[Prem Bairoliya]

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 

[SKavitha]

Hi Prem,

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?

[Premalatha Gadde]

Do not select "Use Connection Pool" or "Keep Connection Open" options and each JDBC step will make a new connection.

[SKavitha]

Prema,

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.