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

[Bishal]

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>

[nmrao]

If you are ok, then groovy script can also be used to execute the sql.

Please see below thread:
http://www.schibsted.pl/blog/groovy-sql-an-easy-way-to-database-scripting/

[groovyguy]

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]

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]

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]

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 = 😛

- none of these worked.

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

[nmrao]

It appears that you are trying to execute PL/SQL. So, that did not work.

Please see if the below thread helps.
https://stackoverflow.com/questions/31119400/how-to-run-below-pl-sql-code-from-groovy-againts-oracle...

[Bishal]

My query works in the groovy script if i don't data drive it. For example;

sql.eachRow ('''Declare @PortfolioOldCode varchar(3) = '113',
@EndDate Date = '4/30/2017'

if i send the parameter value like above the script works pretty well. However, i need to parameterize the sql input so that i can send multiple test data from the excel DataSource. I tried with getdata function but it didn't work for me. Therfore, wondering if there are other solution

[nmrao]

Can you confirm if it is Pl/sql?

Have you looked at the above link from previous reply?

[Bishal]

This is T-SQL.  Yes i did, but not much luck.