Can you extract JDBC Query Results (columns and values) and Save to Properties For Later Use?

[richie]

Hey!

I'm waiting for DB access to try this out - but my available time is limited so I want to try and get a head start on my approach  - hence the reason I'm asking rather than waiting until I get access and then trying it out.

Simple question - can I execute a JDBC step (e.g. select * from table) to retrieve all columns in a table and pass them into a Properties test step for later use?

Thanks guys - hope I've been clear!

rich

[nmrao]

@richie,
May be, do you like to change the query instead? such as "describe tableName" in oracle which returns the column names.

And select query can result taking more time to fetch huge data, not wise especially without filters / where clause .

[nmrao]

@richie,

May be I misread, thought only columns.

By the way, what do you want to do after getting the response? Compare with some data? That help to have correct steps in the test.

[nmrao]

There is another hurdle here to save the sql query result into properties.

Because, same property and different values for each row. And that can't be put into flat properties.

[richie]

Hey @nmrao 

It's SQL Server, not Oracle so I can't use a DESC on the table like I could in Oracle - if I remember correctly (and I'm going back 10 years on this) - I think there's an sp that you execute to do a DESC on a table in SQLServer.

I can limit the number of rows in the query - I can use select top 1 from table in SQLServer or I think I can use fetch - SQLServer doesn't have the concept of rownum like Oracle so I can put an inline query using rownum to grab a single row in SQLServer - or thinking about it - easiest option is if I just filter the query on the primary key to retrieve a single record.

Ok - I'm rambling a bit.

In summary - I can tailor the query to retrieve only 1 row of the table - with this in mind - do you think I can extract the columns into a Properties step?

It's been a while since I've tested a JDBC step and I remember the 'Transfer to' and 'Assert' button functionality is limited relative to whats available on say a REST step, which is why I'm asking.

thanks man,

rich

[richie]

Hey @nmrao 

Sorry - for some reason I didn't see your last.

I care about the columns AND the values - but I was splitting my task up into little bits thinking I could grab the columns and worry about values later.

Essentially I'm still stuck on my previous problem - I have system to test that provides data for lots of different systems - 4 databases, multiple tables and this will provide these multiple systems with reference data via a GET request where the GET request's QUERY parms are actually table attributes and the values are the table attribute values.

Im trying to think of various methods for my test approach - essentially my GET request will have >=2 attributes from the table to query against - I'm just trying to work out how I can do this - hence the reason I was thinking that the first step of my test could be a JDBC step to return all the attributes of the table and pass all the attribute names into a Properties step, so I can somehow grab the attributes in a subsequent groovy step to build my Query parms on the subsequent REST GET request.

thanks man - appreciate the help!

rich

[nmrao]

@richie,

Couple of things:
1. If you use "top 1", the result may always end up with the same record irrespective of number tries. Isn't it?
2. Instead would you like the result be stored in csv file and later use data source to iterate it and execute GET calls?
3. When you say 4 databases, does it mean different environments such as dev, qa, staging etc ., or different types of database such as sqlserver, oracle, mysql? And you want to be able to execute the tests against all of these?

[richie]

Hey @rao,

Answers as follows.

1. Yep top1 shouldnt change the record retrieved. The data may change over time....each table will include an ID field....perhaps it woule best to filter on that to retrieve only 1 record?
2. Doesnt matter how the data is stored as long as i can get at it later
3. Ive been a little disingenous with the word "database". I used this to imply some hierarchy because there is the concept of datasets (which correspond to tables) within 5 different namespaces but in fact its only a single database and its SQL SERVER.

Nice one

[nmrao]

May be it would be easy if the sql result is available.

[nmrao · Accepted solution]

Using some sample Jdbc result and showing how to save the jdbc response into properties

Again, it is only useful if the jdbc returns single row as result and doesn't help if more than one row as column names are same which becomes property name and can't have duplicate or value will be overriden for each row if multiple.

Script below:

https://github.com/nmrao/soapUIGroovyScripts/blob/master/groovy/CreatePropertiesFromJdbcResult.groov...