Forum Discussion

Usha_Kodali's avatar
Usha_Kodali
Frequent Contributor
15 years ago

[SOLVED]LIKE operator in SQL

Hi,

I have the following query and it gives me error.
Can you please help how to solve it?

def performer_name = 'Bill'
("select  p.performer_id, p.performer_name, p.gender from artst_performers p where p.performer_first_name like '${performer_name}%' ")

the above statement gives me error when i use it in groovy script.
If i replace performer_name in the query with Bill it works fine. Its just with parameter name in the query. I am new to groovy so dont know how to escape the characters
   
Error:

Tue Sep 29 17:42:19 EDT 2009:ERROR:java.sql.SQLException: Invalid column index
   java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
    at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5328)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8870)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8811)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9533)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9516)
    at groovy.sql.Sql.setObject(Sql.java:1468)
    at groovy.sql.Sql.setParameters(Sql.java:1434)
    at groovy.sql.Sql.eachRow(Sql.java:624)
    at groovy.sql.Sql.eachRow(Sql.java:652)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoCachedMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:229)
    at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:52)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:117)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:129)
    at Script24.run(Script24.groovy:101)
    at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:59)
    at com.eviware.soapui.support.scripting.groovy.SoapUIProGroovyScriptEngineFactory$SoapUIProGroovyScriptEngine.run(SourceFile:50)
    at com.eviware.soapui.impl.wsdl.teststeps.WsdlGroovyScriptTestStep.run(WsdlGroovyScriptTestStep.java:148)
    at com.eviware.soapui.impl.wsdl.panels.teststeps.GroovyScriptStepDesktopPanel$RunAction$1.run(GroovyScriptStepDesktopPanel.java:273)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

3 Replies

Sort By
  • SmartBear_Suppo's avatar
    SmartBear_Suppo
    SmartBear Alumni (Retired)
    15 years ago
    Hi Usha,

    Try it this way:

    def performer_name = 'Bill%'
    ("select  p.performer_id, p.performer_name, p.gender from artst_performers p where p.performer_first_name like ${performer_name} ")


    Regards,

    /Dragica
    eviware.com
  • Usha_Kodali's avatar
    Usha_Kodali
    Frequent Contributor
    15 years ago
    script i used

    def performer_first_name = context.expand( '${DataSource#performer_first_name}' )
    def first_name = "%${performer_first_name}%"
    first_name = first_name.toUpperCase()

    sql.eachRow("select  p.performer_id, p.performer_name, p.gender from artst_performers p where UPPER(p.performer_first_name) like '${first_name}' and p.deleted <>1")