Forum Discussion

Redders's avatar
Redders
New Contributor
7 years ago
Solved

Groovy - setPropertyValue for a value in a file and using it in the same script

Hi folks,

 

I've been trying to read values from a CSV via a script and use them within the same script to login to a db.

 

I've got the db login and SQL query running when hard coded the DB login details and not using this CSV file, however lots of different people will be using this script who all have different properties etc so i would like to make it dynamic rather than having to manually change it every time. Each person will have a csv file with 5 values in which this script will refer to.

 

I'm quite new to this and here is what i have so far but i'm getting "groovy.lang.MissingPropertyException: No Such property: DBServer for class"

 

The fileReader code i have is taken from another script i have which does work when referencing in a soap request, however i want to try do a similar thing but refer in the same groovy script

 

 

import groovy.sql.Sql
import java.sql.*

com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver")

context.fileReader = new BufferedReader(new FileReader("C:\\tmp\\GroovySQL.csv"))
firstLine = context.fileReader.readLine()
String[] propData = firstLine.split(",")
testRunner.testCase.testSuite.project.setPropertyValue("DBServer",propData[0])
testRunner.testCase.testSuite.project.setPropertyValue("DBDatabase",propData[1])
testRunner.testCase.testSuite.project.setPropertyValue("DBPort",propData[2])
testRunner.testCase.testSuite.project.setPropertyValue("DBUser",propData[3])
testRunner.testCase.testSuite.project.setPropertyValue("Password",propData[4])


//Connect to the DB

String db_server = "{$DBServer}"
String db_database = "{$DBDatabase}"
String db_port = "{$DBPort}"
String db_user = "{$DBUser}"
String db_password = "{$DBPassword}"
String connectionUrl = "jdbc:sqlserver://" + db_server + ":" + db_port +
";database=" + db_database +
";user=" + db_user +
";password=" + db_password;

sql = Sql.newInstance( connectionUrl, "com.microsoft.sqlserver.jdbc.SQLServerDriver")



//Read data
sql.eachRow('select USER_ID from SP085_USERS') { p ->
log.info "First Name = ${p.USER_ID}"
}

sql.close()

 

Any help would be appreciated!

  • At this line, DBServer isn't among your Groovy variables.

     

    String db_server = "{$DBServer}"

    Instead, DBServer was the name of the property set on the Project level. You can just get it in the same way that you set it, as in...

     

    String db_server = testRunner.testCase.testSuite.project.getPropertyValue("DBServer")

    or use this alternative:

     

    String db_server = context.expand('${#Project#DBServer}')

    However, I'm not sure that your users would appreciate their credentials being set into the SoapUI project file that they might save and share with others later. Instead, just keep it in your scripts. If it's all in the one script, just create the variables directly:

     

    db_server = propData[0]
    db_database = propData[1]
    db_port = propData[2]
    db_user = propData[3]
    db_password = propData[4] String connectionUrl = "jdbc:sqlserver://" + db_server + ":" + db_port + ";database=" + db_database + ";user=" + db_user + ";password=" + db_password;

    Or, if its in a different script (but still in the same test case), you can do essentially the same thing using the test run context, which persists during the test run:

     

    context.db_server = propData[0]
    context.db_database = propData[1]
    context.db_port = propData[2]
    context.db_user = propData[3]
    context.db_password = propData[4] String connectionUrl = "jdbc:sqlserver://" + context.db_server + ":" + context.db_port + ";database=" + context.db_database + ";user=" + context.db_user + ";password=" + context.db_password;

     

2 Replies

  • JHunt's avatar
    JHunt
    Community Hero

    At this line, DBServer isn't among your Groovy variables.

     

    String db_server = "{$DBServer}"

    Instead, DBServer was the name of the property set on the Project level. You can just get it in the same way that you set it, as in...

     

    String db_server = testRunner.testCase.testSuite.project.getPropertyValue("DBServer")

    or use this alternative:

     

    String db_server = context.expand('${#Project#DBServer}')

    However, I'm not sure that your users would appreciate their credentials being set into the SoapUI project file that they might save and share with others later. Instead, just keep it in your scripts. If it's all in the one script, just create the variables directly:

     

    db_server = propData[0]
    db_database = propData[1]
    db_port = propData[2]
    db_user = propData[3]
    db_password = propData[4] String connectionUrl = "jdbc:sqlserver://" + db_server + ":" + db_port + ";database=" + db_database + ";user=" + db_user + ";password=" + db_password;

    Or, if its in a different script (but still in the same test case), you can do essentially the same thing using the test run context, which persists during the test run:

     

    context.db_server = propData[0]
    context.db_database = propData[1]
    context.db_port = propData[2]
    context.db_user = propData[3]
    context.db_password = propData[4] String connectionUrl = "jdbc:sqlserver://" + context.db_server + ":" + context.db_port + ";database=" + context.db_database + ";user=" + context.db_user + ";password=" + context.db_password;

     

  • Redders's avatar
    Redders
    New Contributor

    Brilliant, that resolves it! a great start to my week thank you!