Forum Discussion

richie's avatar
richie
Community Hero
6 years ago

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

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...
nmrao's avatar
nmrao
Champion Level 3
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's avatar
richie
Community Hero
6 years ago

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's avatar
    richie
    Community Hero
    6 years ago

    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's avatar
      nmrao
      Champion Level 3
      6 years ago
      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's avatar
        richie
        Community Hero
        6 years ago
        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