Forum Discussion

st-pat's avatar
st-pat
Occasional Contributor
7 years ago

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

JDBC
SoapUI Pro
  • richie's avatar
    richie
    Community Hero
    7 years ago
    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's avatar
      st-pat
      Occasional Contributor
      7 years ago

      hi,

       

      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's avatar
        richie
        Community Hero
        7 years ago

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