Querying in JDBC to oracle DB

[123456.kiran]

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.

[Looserette]

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 ...

[Tolls]

Can you show us your actual code for this:

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

[Looserette]

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:

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

[Looserette]

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 :(

[Tolls]

What driver?

[Tolls]

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