Forum Discussion

_ivanovich_'s avatar
_ivanovich_
Frequent Contributor
6 years ago

How to simplify groovy code for several sql request in different table ?

Hi,

how can we simplify a groovy code for several sql request which are not same.

i'm querying different table for different data so i have at least 50 sql queries and for each query i set propertis with data retrieved from table.

 

My problem is:

i have 50 sql queries.

Is there a way to simplify this code.

I cannot use loop because all tables are differents.

 

example of query:

 

StringBuilder builder = new StringBuilder()
def tableValues = sql.eachRow("select type from order where id = "abc123"") { row -> 
builder.append( "${row.type}" )
}
//Set properties
String myvalue = builder.toString()
testRunner.testCase.setPropertyValue( "type", type)



StringBuilder builder2 = new StringBuilder()
def tableValues2 = sql.eachRow("select name from product where desc = "yzx"") { row -> 
builder.append( "${row.name}" )
}
//Set properties
String myvalue2 = builder2.toString()
testRunner.testCase.setPropertyValue( "name", name)

 

  • I would probably just put the results into the test run context.

    context.type = sql.firstRow('select type from order where id = ?', ["abc123"]).type
    context.name = sql.firstRow('select name from product where desc = ?', ["yzx"]).name

    That form uses prepared statements, which you should be doing and is not much more difficult.

     

    To get the values in a request's body:

    ${type}
    ${name}

    To get the values in another groovy script:

    context.type
    context.name

     

  • JHunt's avatar
    JHunt
    Community Hero

    I would probably just put the results into the test run context.

    context.type = sql.firstRow('select type from order where id = ?', ["abc123"]).type
    context.name = sql.firstRow('select name from product where desc = ?', ["yzx"]).name

    That form uses prepared statements, which you should be doing and is not much more difficult.

     

    To get the values in a request's body:

    ${type}
    ${name}

    To get the values in another groovy script:

    context.type
    context.name

     

    • _ivanovich_'s avatar
      _ivanovich_
      Frequent Contributor

      Thank you so much JHunt.

      in the case i have this sql request:

       

      select type from order where number = "789" and id = "abc123";

       

      i tried this:

      context.type = sql.firstRow('select type from order where number ="789" and id = ?', ["abc123"]).type

      it returns error