How to read dynamic variable name and value from mysql table so that they can be used in SOAPUI Request

Question

I'm trying to read in a set of user defined property names and values from a table in a mysql database.

I'm having trouble with the groovy syntax of how to retrieve the property values and reference them in the request payload.

I'd like to have all the dynamic properties defined and values extracted in the setup script so that they can be dynamically used in a request payload of my project.

my table looks likes this

dynamic_variables table

  variable_id |project_id | variable_name | variable_value
  1           |126        | Make          | Porsche
  2           |126        | Model         | 911

sample JSON request that gets triggered in the test suite and uses the property references

 {
  "username" : "my_username",
 "password" : "my_password",
  "cars" : {
  "carSpecs" : [
     {
        "make" : "${make}",
        "model" : "${model}"
     }
  ]
 }
 }

Now here is my script

//declare arrays to handle property name and values
def dynamicVariables = []
def varPropNames = []
def varPropValues = []

//read all properties from table and store results in dynamicVariable array
def sql =     Sql.newInstance("jdbc:mysql://localhost:3306/automation_v2","root", "",     "com.mysql.jdbc.Driver")
sql.eachRow("SELECT * FROM dynamic_variables where project_id = 126") {
     row -> 
     variable_name = row.variable_name
     variable_value = row.variable_value
     dynamicVariables.add(variable_name+","+variable_value)
   }

 sql.close()

//set properties so that all steps in test case can access and use them
def a =1
dynamicVariables.each(){
    def (propertyName, propertyValue) = it.split(',')
    varPropNames.add(propertyName)
    varPropValues.add(propertyValue)

    testRunner.testCase.testSuite.setPropertyValue( propertyName, propertyValue)
        testRunner.testCase.testSteps["setProperties"].setPropertyValue( propertyName, propertyValue)     
    propertyName = testRunner.testCase.testSuite.getPropertyValue(propertyName)
    log.info(propertyName)
    a++
   }

So when propertyName is printed out to the console, i can see Porsche and 911 "make" and "model" printed out in each row. But how can i reference them dynamically in the JSON example request using ${make} and ${model} property syntax?

THanks!

Is it right that you need to execute the REST request for all the rows? May be you can check this one and see if that is helpful - http://stackoverflow.com/questions/40471259/groovy-script-to-read-an-xml-file-and-update-next-step-request-with-file-content/40480207#40480207 — Rao, Nov 30 '16 at 13:09

score 0 · Answer 1 · answered Dec 01 '16 at 03:42

I figured this one out. :) :)

I had to use context.put to extract the property value so that it could access it dynamically for my test requests.

This was the line of code that got me there:

   propertyNameArray.add(testRunner.testCase.testSuite.getPropertyValue(propertyName))
   context.put('${varPropNames}'+[a],propertyNameArray[a])

So my updated code now looks like:

def dynamicVariables = []
def varPropNames = []
def varPropValues = []
def propertyNameArray = []


    def sql2 = Sql.newInstance("${db_env}","root", "", "com.mysql.jdbc.Driver")
    sql2.eachRow("SELECT * FROM dynamic_variables where project_id = 126") {
         row -> 
         variable_name_1 = row.variable_name
         variable_value_1 = row.variable_value
         dynamicVariables.add(variable_name_1+","+variable_value_1)
    }

sql2.close()

def propertyNameArrayValue=[]
def a =1
    dynamicVariables.each(){
        def (propertyName, propertyValue) = it.split(',')
        varPropNames.add(propertyName)
        varPropValues.add(propertyValue)


        testRunner.testCase.testSuite.setPropertyValue( propertyName, propertyValue)
            testRunner.testCase.testSteps["setProperties"].setPropertyValue( propertyName, propertyValue)     
        propertyNameArray.add(testRunner.testCase.testSuite.getPropertyValue(propertyName))
        context.put('${varPropNames}'+[a],propertyNameArray[a])

        //log.info(propertyName)
        a++
    }

sql.close()

How to read dynamic variable name and value from mysql table so that they can be used in SOAPUI Request

1 Answers1