Forum Discussion

Redders's avatar
Redders
New Contributor
7 years ago

Groovy DB Selector script

Hi guys,

 

I have a requirement for the following:

 

  • Potential of using a SQL or Oracle db, the credentials/ connection string differs
  • Connection details should be dynamic as these are locally hosted db's on the testers machines
    • I've currently got it working so it reads from a file on C: which each team member has their own credentials in
  • I'm still in the process of tweaking the connection string for Oracle but the concept can still work as the DBChosen.txt contains 'SQL14'

I've been trying to tweak my script to look for a value in a file located in C: drive and read the value inside which will be either 'SQL14' or 'ORA12'. There are seperate files for both sets of credentials.

 

 

I'm trying to implement an if else statement which says if the value in the file is 'SQL14' use the SQL credentials, else if use the ORA credentials

 

I'm currently getting "groovy.lang.MissingPropertyException: No such property sql for class: error at line 47" which i assume is to do with the if statement having a local scope

 

Not sure if i'm over complicating things here, any help would be appreciated!

 

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"))
context.fileReader = new BufferedReader(new FileReader("C:\\tmp\\GroovyORA.csv"))
firstLine = context.fileReader.readLine()
String[] propData = firstLine.split(",")
String fileContents = new File('C:/tmp/DBChosen.txt').text

//Set Variables

if (fileContents.matches("SQL14")){

String db_server = context.expand('${#Project#DBServer}')
String db_database = context.expand('${#Project#DBDatabase}')
String db_port = context.expand('${#Project#DBPort}')
String db_user = context.expand('${#Project#DBUser}')
String db_password = context.expand('${#Project#DBPassword}')

db_server = propData[0]
db_database = propData[1]
db_port = propData[2]
db_user = propData[3]
db_password = propData[4]

//Connect to the DB

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")

}

else if (fileContents.matches("ORA12")){
 
String connectionUrl = "jdbc:oracle:thin:/@mred_oradb/mred_oradb@//strad:1521:ora123"

sql = Sql.newInstance( connectionUrl, "oracle.jdbc.driver.OracleDriver")

}

  	   //Select data and return values
        sql.eachRow("select VALUE from SP083_SYSTEM_PARAMETERS Where NAME = 'sp100_audit'") { p ->
            log.info "VALUE = ${p.VALUE}"
        }
sql.close()
  • JHunt's avatar
    JHunt
    Community Hero
    7 years ago

    It looks like the problem is along the lines of:

     

    i = 42

    if (i == 2) {
    a = 123
} else if (i == 3) {
    a = 456
}

println a // no such variable a

    i.e. neither of your if statements are evaluating to true.

  • Redders's avatar
    Redders
    New Contributor
    7 years ago

    Hmmm, sorry if its a dumb question but isn't that what i'm doing?

     

     

    String fileContents = new File('C:/tmp/DBChosen.txt').text

if (fileContents.matches("SQL14")){

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")

}

else if (fileContents.matches("ORA12")){
 
String connectionUrl = "jdbc:oracle:thin:/@mred_oradb/mred_oradb@//strad:1521:ora123"

sql = Sql.newInstance( connectionUrl, "oracle.jdbc.driver.OracleDriver")

}

  	   //Select data and return values
        sql.eachRow("select VALUE from SP083_SYSTEM_PARAMETERS Where NAME = 'sp100_audit'") { p ->
            log.info "VALUE = ${p.VALUE}"
        }
sql.close()

    The line it doesn't like and is throwing the "No such property: sql for class" error is the below which is because i thought it was a local scope within the if statement

     

            sql.eachRow("select VALUE from SP083_SYSTEM_PARAMETERS Where NAME = 'sp100_audit'") { p ->
            log.info "VALUE = ${p.VALUE}"

     

  • JHunt's avatar
    JHunt
    Community Hero
    7 years ago

    I just think you need to check what is the actual value for filecontents.

     

    I think what is happening is that you are expecting ORA12 or SQL14 and based on which one you are creating the variable called sql. But if it doesn't match either of those, then the variable is not created and you get that particular error.