Clever Geek Handbook

How do I add quotes to a sql where clause in a Groovy script?

Colleagues, please help me with sql in Groovy. In my SOAP frontend groovy script, I have an sql query:

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

Everything was fine until I worked without quotes in the where clause. After adding UPPER (Country) = "JAPAN" I get the spike:

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

How to rewrite a quoted query in where where?

+3


source to share


2 answers


Or use a different quote character in your 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 lines marked with:

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


source


I prefer parameters over literals, especially because it works correctly 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'])
+1


source






More articles:


All Articles