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.    ...
  • 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

      • Bishal's avatar
        Bishal
        Contributor
        8 years ago

        I tried it in multiple steps

         

        Step 1 : to create temp table and insert

        Step 2. Selection

        Step 3 : Drop temp table.

         

        It didn't worked for me . Step 1 is fine , but nothing in step 2. At this moment, i moved to groovy script and i am facing a new challenge. Please let me know if you are aware on how to. 

         

        I was trying to send input data ( to SQL query ) from the excel sheet. I did fallowing

         

        1. Get data from DataSoruce ( which is excel sheet )

         

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

         

        2. I tried to see the output in the log output panel to see if this is pulling the data

        log.info (a) 

        log.info (b)

         

        i see the log just fine. Seems ok.

         

        3. Then i am tring to send these DataSource value in the sql parameter so that i can test n number of scnerios. I am not able to data drive the parameter in the groovy. I did fallows.

         

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

         

        --- This doesnt work..

         

        Also, tried this

         

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

         

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

         

        - none of these worked.

         

        Any one has any suggestion on how can i tackle this.