Service Virtualization

Expand all | Collapse all

Executing multiple SQL queries in single JDBC step

Jump to Best Answer
  • 1.  Executing multiple SQL queries in single JDBC step

    Posted 07-10-2018 07:35 AM

    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.



  • 2.  Re: Executing multiple SQL queries in single JDBC step
    Best Answer

    Posted 07-10-2018 10:53 AM

    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 



  • 3.  Re: Executing multiple SQL queries in single JDBC step

    Posted 07-11-2018 02:12 AM

    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?



  • 4.  Re: Executing multiple SQL queries in single JDBC step

    Posted 07-11-2018 09:24 AM

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



  • 5.  Re: Executing multiple SQL queries in single JDBC step

    Posted 07-11-2018 10:09 AM

    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.