How to add quotes into sql where clause in Groovy script?

Question

Collegues, please help me with sql in Groovy. In my SOAP UI groovy script i have sql query:

sql.eachRow('select top 1 '+     
              'Country, '+    
   'from dbo.Address where UPPER(Country) = "JAPAN" ORDER BY NEWID()')  

Everything was fine till i work without quotes in where clause. After I add UPPER(Country) = "JAPAN" i receive an axception:

com.microsoft.sqlserver.jdbc.SQLServerException: Ivalid column name 'Japan'

How to rewrite the query with quotes in where clause?

Answer 1

Or use a different quote character in the Groovy code:

sql.eachRow("select top 1 " +     
            "Country, " +    
            "from dbo.Address where UPPER(Country) = 'JAPAN' ORDER BY NEWID()")

or multi-line strings:

sql.eachRow('''select top 1
               Country, 
               from dbo.Address where UPPER(Country) = 'JAPAN' ORDER BY NEWID()''')

or multi-line strings with a margin:

sql.eachRow('''select top 1
              | Country, 
              | from dbo.Address where UPPER(Country) = 'JAPAN' ORDER BY NEWID()'''.stripMargin())

Answer 2

I prefer parameters over literals, especially because it works properly if the value comes from user input or if the value itself contains quotes:

sql.eachRow('''select .... from dbo.Address 
     where UPPER(Country) = :country ...''', 
   [country: 'Japan'])