Forum Discussion

Scripter's avatar
Scripter
Occasional Contributor
9 years ago

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

 

 

 

database
Groovy
SoapUI
sql
  • rupert_anderson's avatar
    rupert_anderson
    9 years ago

    Hmm.. ok, maybe its a clash of placeholder syntax, what if we try an java style way of adding the schema string e.g.

    "DELETE FROM ["+dbschema1+"].[dbo].[JOB] WHERE EMPNAME = 'NAME1'"

    ?

     

     

  • rupert_anderson's avatar
    rupert_anderson
    Valued Contributor
    9 years ago

    Hi,

     

    Sorry if I'm not understanding your needs, but don't you just need to remove the '' e.g.

     

    sql.execute("DELETE FROM [$dbschema1].[dbo].[JOB] WHERE EMPNAME = 'NAME1'")

    gives string of DELETE FROM [EMP_SCHEMA].[dbo].[JOB] WHERE EMPNAME = 'NAME1'

     

    • Scripter's avatar
      Scripter
      Occasional Contributor
      9 years ago

      Nope, it then gives error, I already checked some of these but doesn't work.

         com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
   	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
   	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)
   	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:910)
   	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:935)
   	at groovy.sql.Sql.setObject(Sql.java:3655)
   	at groovy.sql.Sql.setParameters(Sql.java:3620)
   	at groovy.sql.Sql.getPreparedStatement(Sql.java:3881)
   	at groovy.sql.Sql.getPreparedStatement(Sql.java:3928)
   	at groovy.sql.Sql.execute(Sql.java:2287)
   	at groovy.sql.Sql.execute(Sql.java:2360)
   	at groovy.sql.Sql$execute.call(Unknown Source)
   	at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45)
   	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108)
   	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
   	at Script40.run(Script40.groovy:16)
   	at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:92)
   	at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:141)
   	at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:250)
   	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
   	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   	at java.lang.Thread.run(Unknown Source)
      • rupert_anderson's avatar
        rupert_anderson
        Valued Contributor
        9 years ago

        Hi,

         

        OK, thats strange, the syntax looks OK, for example this works fine for me:

         

        1. def invoice = db.firstRow("select * from invoices where id =
       $requestInvoiceNo")

        I don't have your setup / db etc, I just tested to see if I could get the same string that you said works:

         

        def dbschema1 = 'EMP_SCHEMA'

log.info "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

        Gives:

        Wed Jun 29 14:06:15 BST 2016:INFO:DELETE FROM [EMP_SCHEMA].[dbo].[JOB] WHERE EMPNAME = 'NAME1'

         

        And you say that 

        context.expand('${#Project#Schema1}')

        is definitely returning EMP_SCHEMA? Can you print out the query string after the substitution of dbschema1 has taken place?

         

        Regards,

        Rupert