Forum Discussion

Scripter's avatar
Scripter
Occasional Contributor
9 years ago
Solved

Groovy script for database manipulation using project property

I want to execute simple delete query with the schema name taken from project properties, but I am not sure how to pass the value to it, current way doesn't work for me.

 

import groovy.sql.Sql
import com.eviware.soapui.support.GroovyUtils.*
def dbURL = context.expand('${#Project#MSSQLUrl}')
def dbUser = context.expand('${#Project#MSSQLUser}')
def dbPass = context.expand('${#Project#MSSQLPassword}')
def dbDriver = context.expand('${#Project#MSSQLDriver}')
def dbschema1 = context.expand('${#Project#Schema1}')
def dbschema2 = context.expand('${#Project#Schema2}')
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver(dbDriver)

log.info (dbschema1) //logs EMP_SCHEMA
def sql = Sql.newInstance(dbURL, dbUser, dbPass, dbDriver)
sql.execute("DELETE FROM ['$dbschema1'].[dbo].[JOB] WHERE EMPNAME = 'NAME1'"); //doesnt work, com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ''EMP_SCHEMA'.dbo.JOB'. error at line: 14
sql.execute("DELETE FROM [EMP_SCHEMA].[dbo].[JOB] WHERE EMPNAME = 'NAME1'"); //works

Dont know how to pass the schema value to SQL query