cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC like statement

Highlighted
Occasional Contributor

JDBC like statement

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 property in this query so it's  select * from frc where service like :ser% (I've tried * and ? , and even '%')

any ideas or hints ?
Thnx in advance

Tags (2)
7 REPLIES 7
Highlighted
Community Hero

Re: JDBC like statement

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
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
Occasional Contributor

Re: JDBC like statement

hi,

 

I was just issuing an example here with my query, sorry for being misleading Smiley Wink
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

Highlighted
Community Hero

Re: JDBC like statement

hi,

 

I just had a quick play with one of my soapui projects.

 

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', value = '12345'

 

Originally I had the following query (which Ive simplified quite a bit) to

 

select * from Session where SessionNumber = '${Properties#SessionNumber}'

I changed the query above to the following:

 

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

 

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 '_'  e.g. '_${Properties#SessionNumberWithoutTheS}' instead of the % symbol

 

i had a look around - noticed your post on stackoverflow! Smiley Happy - and I noticed the syntax sometimes included a colon ':' and other times didn't.

 

I haven't' nailed down why some posts use this.

 

Where are you storing the property?  As you can see from above - I was storing the Property in the properties step and the above syntax works

 

hope this helps!

 

richie 

 

 

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
Occasional Contributor

Re: JDBC like statement

Hmm maybe I forgot the ': ' in my post at the other place. I'll try your way right on!

 

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 ${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)

Highlighted
Community Hero

Re: JDBC like statement

I'm struggling to visualise the problem.

 

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!)

 

 

worst case if you have a secure project  - 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?

 

Cheers,

 

richie

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
Occasional Contributor

Re: JDBC like statement

I'm afraid I can't publish the project here but here's screenshot form the querysoccheck2.jpg

 

I get my data for subscriber and soc from an xml command above inside this testcase. and since it somehow (maybe because of some format problem or spaces or I don't know what it won't get anything with '=' my thing would be the "like". 

 

**************************

EDIT: I've found a workaround for my problem.
Instead of getting the data directly from the request I built a property- transfer with concat ([my value] ,%) that way I can query it with the property value in the like statement

Highlighted
Community Hero

Re: JDBC like statement

Hi,

 

yeah - so I think you use a colon ':' if the property your calling in your SQL is a property on the JDBC step (never done this before) - not sure - but that seems to be the pattern.

 

(If anyone is reading this and can tell me what the colon char is supposed to do in this regard - that would be helpful!)

 

I can't find a way around it with the colon - it's a reserved character in SQL so I thought escaping it would work - but I've given it a shot and I couldn't get it to work - escaping should work - perhaps my escaping is incorrect.

 

I was going to suggest what your workaround was.  It looks like the colon is used to specify 'this test step' when specifying the  property or something similar.  If you move the property you need to do the like on outside the JDBC test step (like my example with the property in a properties step) - this means you can remove the colon and then the use of the wildcard works with normal property transfer syntax.

 

So - sorry I couldn't help - but it sounds like you've got it sorted more or less now anyway.

 

cheers,

 

richie

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta