Results 1 to 5 of 5
Like Tree1Likes
  • 1 Post By doWhile

Thread: Executing query from java program

  1. #1
    ashu000 is offline Member
    Join Date
    Jun 2011
    Posts
    1
    Rep Power
    0

    Default Executing query from java program

    Hi All,

    I am frustrated with a problem. I have created so many queries for DB2 and successfully executed it.
    I am facing problem a query which uses IN keyword.

    I have a text box on page where user can enter any number of comma separated values. I need to query DB using this parameter as

    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN (:stateAbb);

    Here, stateAbb value I will pass from my java program.
    Lets say user has entered in text box two values - DC, AL.

    In my java program I am taking these values one by one and making it as 'DC' and 'AL' and again appending these as 'DC', 'AL' because in query it must be in single quotes.
    So, my query became (printed in console):
    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ('DC', 'AL');


    Now, when I send it and execute this, I get the following error :
    nested exception is java.sql.SQLException: An undefined column name was detected

    The same query when I run directly on sql page ..it runs successfully.

    Kindly help me as I am not understanding where things are wrong and I tried almost every option.
    Thanks in advance.

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    6

    Default

    DarrylBurke likes this.

  3. #3
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,189
    Rep Power
    19

    Default

    Thanks doWhile.

    db

  4. #4
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    4

    Default

    Quote Originally Posted by ashu000 View Post
    The same query when I run directly on sql page ..it runs successfully.
    It says "UNDEFINED COLUMN NAME", when you run it in sql page did you copy paste the printout in your console which is
    "SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ('DC', 'AL');", if not try to do this because you might have type the correct
    column name when you are on sql page.

    This is the only thing that I could say because I do not see your code and you shorten/conluded the error message.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,442
    Rep Power
    18

    Default

    Quote Originally Posted by ashu000 View Post
    Hi All,

    I am frustrated with a problem. I have created so many queries for DB2 and successfully executed it.
    I am facing problem a query which uses IN keyword.

    I have a text box on page where user can enter any number of comma separated values. I need to query DB using this parameter as

    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN (:stateAbb);

    Here, stateAbb value I will pass from my java program.
    Lets say user has entered in text box two values - DC, AL.

    In my java program I am taking these values one by one and making it as 'DC' and 'AL' and again appending these as 'DC', 'AL' because in query it must be in single quotes.
    So, my query became (printed in console):
    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ('DC', 'AL');


    Now, when I send it and execute this, I get the following error :
    nested exception is java.sql.SQLException: An undefined column name was detected

    The same query when I run directly on sql page ..it runs successfully.

    Kindly help me as I am not understanding where things are wrong and I tried almost every option.
    Thanks in advance.
    OK.
    I'm guessing you;re doing something like this (since you hadn't bothered to give us any relevant code):
    Java Code:
    String firstThing = "'DC'";
    String secondThing = "'AL'";
    String inClause = firstThing + "," + secondThing;
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM STATE WHERE STATE_ABBREVIATION IN (:stateAbb)");
    ps.setString(":stateAbb", inClause);
    Now, that'll give you:
    SELECT * FROM STATE WHERE STATE_ABBREVIATION IN ("'DC', 'AL'");
    as your query, which is clearly wrong.

    You'll need to build the query string based on the number of parameters you need in the IN clause.
    Appending an additional ? (the easiest way).
    Then use that to get your preparedstatement.
    Then loop around your parameters assigning them to their relevant positions.

    Or use a stored procedure.

Similar Threads

  1. Problem executing SELECT query
    By mike_ledis in forum JDBC
    Replies: 2
    Last Post: 04-26-2011, 05:30 AM
  2. Executing java program using windows scheduler
    By kanitha in forum New To Java
    Replies: 0
    Last Post: 03-21-2009, 09:16 AM
  3. Executing an external java program
    By arunsubramanian in forum Advanced Java
    Replies: 5
    Last Post: 02-06-2009, 07:49 AM
  4. Replies: 2
    Last Post: 07-18-2008, 01:13 PM
  5. Error When Executing Query
    By radz in forum New To Java
    Replies: 5
    Last Post: 06-26-2008, 03:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •