• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Search Query Problem

 
Ranch Hand
Posts: 851
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator



you can see that 2 records exists with lastName "Hello" but it displayed all existing records.

when am running this statement then it displays correct record



but this statement become wrong when I ran with firstName


This query returns two record whereas that should be one record

Now I tried this but throwing exception my java code.


EXCEPTION IS:
==========


Thanks & best regards
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What are you trying to achieve here? Your SQL is doing exactly what you are asking it to do.

Your first SELECT is looking for records which match ANY of the individual OR ... clauses, which is why you get all the records back, because they all match at least one of these OR clauses.

The second SELECT basically looks for records which have LASTNAME = 'Hello' OR [one of the other fields is empty]. You have two records with last name 'Hello', and no records where the other fields are empty, so you get 2 records back.

The third SELECT is looking for records with LASTNAME = 'Hello' OR FIRSTNAME = 'Test' OR [one of the other fields is empty]. You have two records with last name 'Hello', and one of these also has first name 'Test', and you still have no records where the other fields are empty, so you get the same 2 records back.

As for your final example, I can't tell what you're doing here, but your "if (type.equals("3"))" statement means you could have 5 or 6 bind variables in the SQL, so is it going to fall over if the type is "3" and you try to set 6 bind variables?

Here are some very simple rules to follow every time you want to run some SQL from Java:

  • Write the SQL separately.
  • Test it against your database directly via the SQL interpreter e.g. SQL*Plus in Oracle.
  • When you are sure it is working correctly, write the Java to build the corresponding query string.
  • Take the query string you just built in Java and run that via your SQL interpreter to make sure it still does what you want.


  • But of course, the first thing to do is figure out what you want your query to do.
     
    Ranch Hand
    Posts: 241
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    You have some logical mistakes in your SQL statements



    I think you should substitute OR with AND to get the desired effect or even remove the lines with wildcards alltogether.



    You can do the same for your other problematic statement.



    There is no need to list all parameters, just the ones you need to narrow the results:



    This will return just one entry, the first one you added to the table with patient_id 1. If you were to alter the statement and use OR in such a way:


    Two entries would be returned.
    (1,1,'Test','Hello','test@test.com',5); and
    (2,1,'Testing','Hello','test@test.com',4)

    the first one matches the firstName equality and the second one the lastName equality.


    As for your PreparedStatement error, you have to be careful of the types that you're setting to replace your placeholders. Title, and nationality are numbers, and you should be using setInt() method. I think it should look something like this:


    Be careful counting the '?' and what values you assign.


    Hope this helped you.


    Martin
     
    Farakh khan
    Ranch Hand
    Posts: 851
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks webster and Martin.

    The actual problem is:

    My Advance Search Form contains title, first name, last name, email and nationality. Now the code not knows either all the parameters will be received or some of them or just one. In this case the problem arises to handle the SQL query. I used all queries listed above in my first post but none of them is fulfilling my requirement.

    if I am using "OR" in the SQL query then its displaying all the records if I am using "AND" then query displays none. The answer I received from Martin and webster is also not telling me the solution. I am confused a lot as to how I deal with it.

    What I actually want is to get patients Ids by setting query on the basis of search parameters.

    Thanks & best regards
     
    chris webster
    Bartender
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    WHERE title LIKE NVL(?,'%')
    AND firstName LIKE lower(NVL(?,'%'))
    AND lastName LIKE lower(NVL(?,'%'))
    AND email LIKE NVL(?,'%')
    AND nationality LIKE NVL(?,'%')

    Make sure you match the correct number of Java bind variables to the "?" in your SQL.
     
    Farakh khan
    Ranch Hand
    Posts: 851
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Hello,

    Sorry for the late reply as I was still working to fix the problem.


    The above statement is displaying correct results because one field "firstName" retrieved. When I check two fields then record exists but it showing no results



    In the following query I put nothing as firstName and correct lastName. It must show correct results but displaying no results


    The actual problem remains

    Thanks & best regards
     
    chris webster
    Bartender
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    You are doing a LOWER(...) on your column value, but comparing it to a mixed-case literal value such as "Hello".
     
    Farakh khan
    Ranch Hand
    Posts: 851
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks Chris Webster from the bottom of my heart. Now its working fine.

    Best regards
     
    reply
      Bookmark Topic Watch Topic
    • New Topic