Forum Discussion

Bishal's avatar
Bishal
Contributor
8 years ago

How to execute a SQL query which includes temp table ( creates and inserts ) - JDBC Step

 

I have a JDBC test step with a SQL Query which  has a temp table on it. I am not able to execute this query. Every time i try to execute i get below XML response. Anyone has any idea? Thanks. 

 

 

 

<Results>
<UpdateCount>124</UpdateCount>
</Results>

  • Bishal's avatar
    Bishal
    8 years ago

    This is solved, i used

     

    def a = context.expand( '${DataSource#a}' )
    def b = context.expand( '${DataSource#b}' )

     

    sql.eachRow("""Declare @PortfolioOldCode varchar(3) = ${a},
    @EndDate Date = ${b},

     

    insted of 

     

    sql.eachRow("""Declare @PortfolioOldCode varchar(3) = context.expand( '${DataSource#a}' ),
    @EndDate Date = context.expand( '${DataSource#b}' )

     

    or

     

    sql.eachRow("""Declare @PortfolioOldCode varchar(3) = a,
    @EndDate Date = b

     

     

    -- Thanks for helping in guys 

     

     

     

     

  • groovyguy's avatar
    groovyguy
    Community Hero
    8 years ago

    Based on what I know of JDBC test steps and queries, this would be the expected result of a SQL query that creates/inserts. What are you expecting to see in return? 

    • Bishal's avatar
      Bishal
      Contributor
      8 years ago

      So basically the query Declares some parameters, create temp table, inserts into that table from Stored proc . Then it does some data manipulation on that temp table and selects some columns to display. It should return the results with columns that are selected ( which it does in sql server ). But in JDBC step on SOAP UI it doesn't. Also, if i try to run any-other query which doesn't have create and insert statement in query it works fine. 

       

      • PaulMS's avatar
        PaulMS
        Super Contributor
        8 years ago

        If you want to avoid using groovy script, is it possible to separate the SQL query into a few JDBC request test steps?

         

        Step 1 create temp table, inserts etc

        Step 2 selects some columns to display

        Step 3 drop table if necessary