Results 1 to 7 of 7
  1. #1
    123456.kiran is offline Member
    Join Date
    Mar 2011
    Posts
    1
    Rep Power
    0

    Default Querying in JDBC to oracle DB

    Hi,

    I have done a JDBC connection to an oracle database and trying to run update and select statements using prepared statements.

    The Problem is when i write a select * from tablename; i am getting the correct result but when i do select * from tablename where columnname=? and preparedStatement.setString(1, value.toUpperCase);

    i am getting 0 rows when i did a analysis and using TOAD found that the query going to database is like this

    select * from tablename where coulmnname= :1 ;

    Let me know if anyone has resolved this issue.

  2. #2
    Looserette is offline Member
    Join Date
    Feb 2011
    Posts
    12
    Rep Power
    0

    Default

    Hello,

    I am not sure, but I think I have somehow the same kind of error...
    Here are a few more details, in case that might help:

    In java, I'm building directly the String query, with:
    query = "select * from "+Table+" where slt='"+Slot+"' and por='"+Port+"' and chn='"+Channel+"'";

    When I run this query, I get no results.
    Right before running the query, I print the query, and run it manually in Oracle SQL Dev, and it works fine (ie: I get the expected results).

    As the OP, I tried to use a preparedStatement, and get the same issue.

    For fun, I printed one of my queries (that does not work):
    => select * from myTABLE where slt='003' and por='0007' and chn='51'

    Then, in my java code, I did:
    Slot="003";
    Port="0007";
    Channel="51";
    query = "select * from "+Table+" where slt='"+Slot+"' and por='"+Port+"' and chn='"+Channel+"'";
    => And it works perfectly fine !



    In short: Slot, Port and Channel are String. I can print them, do substitutes, do replace...etc. In Java, they behave like String.
    But when I put them in a query, it just fails.

    I also tried many things like:
    Slot = Slot.toString();
    Slot = Slot.replace('0','0');
    to force those things into String, but no luck


    that does not make any sense to me :(
    heeeeelp






    edit: in case that helps (by being clearer), I tried also:
    query = "select * from "+Table+" where slt=? and por=? and chn=?";
    PreparedStatement stm = connection.prepareStatement(query);
    stm.setString(1,Slot);
    stm.setString(2,Port);
    stm.setString(3,Channel);
    note: System.out.println("TEST:"+Slot+","+Port+","+Chann el+";"); gives: TEST:003,0007,51;
    => does not work

    Then, I do:
    query = "select * from "+Table+" where slt=? and por=? and chn=?";
    PreparedStatement stm = connection.prepareStatement(query);
    stm.setString(1,"003");
    stm.setString(2,"0007");
    stm.setString(3,"51");
    and it works fine ...



    edit again:
    I did a snoop -x 0 on the connection port, and found:
    select * from myTABLE where slt='003.' and por='0007.' and chn='51.'
    I am not sure where the "." is coming from ... especially since I print the query before, and they are not there ...
    Last edited by Looserette; 03-31-2011 at 11:03 AM.

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Can you show us your actual code for this:
    Java Code:
    query = "select * from "+Table+" where slt=? and por=? and chn=?";
    PreparedStatement stm = connection.prepareStatement(query);
    stm.setString(1,Slot);
    stm.setString(2,Port);
    stm.setString(3,Channel);
    Include the execute, and the code for cycling through the result set, and the print out code for the parameters (sys.out.println()).

    Also, what driver are you using (in case)?

  4. #4
    Looserette is offline Member
    Join Date
    Feb 2011
    Posts
    12
    Rep Power
    0

    Default

    I don't think the code has any error, since, as said above, the whole thing is working fine when I manually set the variables to:
    Slot="003";
    Port="0007";
    Channel="51";

    but since I'm often wrong, here it is:

    Java Code:
    query = "select * from "+Table+" where slt=? and por=? and chn=?";
    PreparedStatement stm = connection.prepareStatement(query);
    stm.setString(1,Slot);
    stm.setString(2,Port);
    stm.setString(3,Channel);
    // works fine with below values:
    //stm.setString(1,"003");  
    //stm.setString(2,"0007");
    //stm.setString(3,"52");
    
    
    ResultSet rs = stm.executeQuery();
    
                            while (rs.next()) {
                                    CIR_ID = rs.getString(1);
                                    STS_CIR = rs.getString(2);
                                    CUS_NME = rs.getString(3);
                                    STS_POR = rs.getString(4);
                                    SOR_TYP = rs.getString(5);
                                    SOR_CAT = rs.getString(6);
    System.out.println("FOUND for channel:"+Channel+":"+CIR_ID);
                            }
                            rs.close();
                            stm.close();


    As edited in the 1st post (in case you missed it):
    I did a snoop -x 0 on the connection port, and found:
    select * from myTABLE where slt='003.' and por='0007.' and chn='51.'
    I am not sure where the "." is coming from ... especially since I print the query before, and they are not there ...
    Note: after trying some query=query.replaceAll("\\.",""); and other things like that, I can conclude that the "." is not a real ".", but something else ... no idea what, though :)
    Last edited by Looserette; 03-31-2011 at 11:36 AM.

  5. #5
    Looserette is offline Member
    Join Date
    Feb 2011
    Posts
    12
    Rep Power
    0

    Default

    I found a solution I don't like at all:
    I'm doing a regexp right before, to capture ([0-9]+) from Slot, Port and Channel...
    and it works afterward!

    all this must come from the way I retrieve those variables ...

    I'll keep on looking into that, but maybe my issue was not connected to the OP :(

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    What driver?

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    There must be something else in your Strings.
    A hidden character or something.
    Check the length using String.length for each of them.

Similar Threads

  1. JDBC with Netbeans/Oracle
    By sehudson in forum Advanced Java
    Replies: 1
    Last Post: 03-11-2010, 09:26 AM
  2. JDBC Oracle Connection
    By Bean in forum New To Java
    Replies: 13
    Last Post: 11-06-2009, 08:06 PM
  3. oracle JDBC java
    By silia_motor in forum JDBC
    Replies: 4
    Last Post: 05-10-2009, 09:37 AM
  4. Jdbc Driver For Oracle
    By Swamipsn in forum New To Java
    Replies: 0
    Last Post: 08-14-2007, 04:31 AM
  5. Oracle and JDBC
    By Eric in forum JDBC
    Replies: 3
    Last Post: 08-11-2007, 08:49 PM

Posting Permissions

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