cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Contributor

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>

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Contributor

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

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 

 

 

 

 

View solution in original post

11 REPLIES 11
Highlighted
Community Hero

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

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/


Regards,
Rao.
Highlighted
Community Hero

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

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? 




---

Click the Accept as Solution button if my answer has helped, and remember to give kudos where appropriate too!
Highlighted
Contributor

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

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. 

 

Highlighted
Super Contributor

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

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

Highlighted
Contributor

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

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. 

Highlighted
Community Hero

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

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


Regards,
Rao.
Highlighted
Contributor

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

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

 

Highlighted
Community Hero

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

Can you confirm if it is Pl/sql?

Have you looked at the above link from previous reply?


Regards,
Rao.
Highlighted
Contributor

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

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

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors