JDBC like statement

Question

Hi community,I've been struggling with this issue for some time now but I found some workarounds, but now I need it and can't really find a good answer for me. I need to use in a jDBC query the 'like' statement and with all combos I could think of or find I always end up in an error.So select * from frc where service like 'CLI%' works out fine,the problem might be that I use a&nbsp;property in this query so it's&nbsp;&nbsp;select * from frc where service like :ser%&nbsp;(I've tried * and ? , and even '%')any ideas or hints ?Thnx in advance

richie · Answer

hi,can you confirm the query you are trying to exe ute?can you please provide the stqck trace of the error that is generated please?have you already confirmed you can execute normal (non-like) queries successfully?* an ? wont work as a wildcard for like. % is the multi character wildcard and _ is the single character wildcard for standard ANSI SQL.in your post you mention "select * from database where....". is this a typo? are you aware its the tablename, not the database name that is required at this point in your query? you can prefix the table in your query by doing the following "select * from database.table where...." but it isnt necessary unless your db connection is to a different db/schema to the one you are querying.also. are you sure the field you are trying to use in your "like" clause a char varchar type field? "like" doesnt work against numeric fields.hope this helps!richie

st-pat · Answer

hi,&nbsp;I was just issuing an example here with my query, sorry for being misleading ;)The like statement works perfectly fine if I put it hardcoded in the query so select * from FRC where soc like 'CLI%';but I have the soc stored as a test property because it gets read out from an xml statement above. so the select * from FRC where soc like :soc% fails

richie · Answer

hi,&nbsp;I just had a quick play with one of my soapui projects.&nbsp;I already had a Property created in the Properties step with name = SessionNumber, value = 'S12345'I just created a second property in the Properties step with the name 'SessionNumberWithoutTheS',&nbsp;value = '12345'&nbsp;Originally I had the following query (which Ive simplified quite a bit) to&nbsp;select * from Session where SessionNumber = '${Properties#SessionNumber}'I changed the query above to the following:&nbsp;select * from Session where SessionNumber like '%${Properties#SessionNumberWithoutTheS}'So the multicharacter wildcard is at the front (in my example but you could move it to the other side of the propertyexpansion if necessary) and i successfully retrieved the Session record I was searching for&nbsp;It took several seconds - because I'm using a like rather than an =, you can speed this up slightly by using the single character wildcard '_'&nbsp; e.g. '_${Properties#SessionNumberWithoutTheS}' instead of the % symbol&nbsp;i had a look around - noticed your post on stackoverflow! :) - and I noticed the syntax sometimes included a colon ':' and other times didn't.&nbsp;I haven't' nailed down why some posts use this.&nbsp;Where are you storing the property?&nbsp; As you can see from above - I was storing the Property in the properties step and the above syntax works&nbsp;hope this helps!&nbsp;richie&nbsp;&nbsp;&nbsp;

st-pat · Answer

Hmm maybe I forgot the ': ' in my post at the other place. I'll try your way right on!&nbsp;Somehow I don't get it done...I've stored the property in the property list of the jdbc query so I call it with :soc, if I put the '%' now in front it say illegal argument.Also when I try to call the variable directly from the xml like&nbsp;${brianSisAddService#Request#d....}it won't take the '%', there it won't even accept the = because it says invalid value (for the name of the service)

richie · Answer

I'm struggling to visualise the problem.&nbsp;Is there any chance you can publish your soapui project and I'll have a look to see if I can sort it? (no promises!)&nbsp;&nbsp;worst case if you have a secure project&nbsp; - could you create a separate project without any of the requests/domain/cred details - all I really need are the test case hierarchy, the test steps your using and the property details etc. just so I can see what you are doing?&nbsp;Cheers,&nbsp;richie

Forum Discussion

JDBC like statement

7 Replies

Recent Discussions

Smart Assertion default of "Received Data"

Vulnerable jetty files

RunTestCase input and return proeprties of the same name

Related Content

Multiple sql statements in one JDBC step with Oracle

NameMapping issues after IF Object statement returns false?

Ready API 1.4.0 clearing SQL statement in JDBC Data Sources