Ask a Question

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

SOLVED
Bishal
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>

11 REPLIES 11
nmrao
Community Hero

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.
msiadak
Community Hero

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!

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
Super Contributor

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

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
Community Hero

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.

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
Community Hero

Can you confirm if it is Pl/sql?

Have you looked at the above link from previous reply?


Regards,
Rao.

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

cancel
Showing results for 
Search instead for 
Did you mean: