cancel
Showing results for 
Search instead for 
Did you mean: 

[SOLVED]LIKE operator in SQL

Usha_Kodali
Frequent Contributor

[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 3

Re: LIKE operator in SQL

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


Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Usha_Kodali
Frequent Contributor

Re: LIKE operator in SQL

thank you so much...its working
Usha_Kodali
Frequent Contributor

Re: LIKE operator in SQL

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")
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors