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