wood burning stoves 2.0*
The moose likes JDBC and the fly likes Prepared Statement Issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Prepared Statement Issue" Watch "Prepared Statement Issue" New topic
Author

Prepared Statement Issue

Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11


This works fine when i hard code a value for the wild card, however for some reason will not work with the prepared statement. When debugging it shows that the prepared statement has the parameter set but does still not work.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

You say "will not work". Do you have anything more specific you could tell us? (If you think about it, you'll realize that phrase by itself doesn't tell other people much.)
Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11
Sorry I meant returns an empty result set
Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11
When a value is hard coded the result set is not empty values are same in both cases
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

1) Show us both versions of the code (with hard-coded values as well as with binds).
2) Use System.out.println(universityId) to obtain the value of the variable and post it here.
3) Tell us the database type of the stu_master.stuno column.
4) Run the SQL command with hard-coded values in an SQL client and post the result as well.

That is, TellTheDetails. Then we should be able to help you.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Martin Vajsar wrote:2) Use System.out.println(universityId) to obtain the value of the variable and post it here.


Actually, print the value in such a way that you can tell whether it has leading or trailing white space.
Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11
As requested with the id number escaped.



1)

2)System.out.println("|" + universityId + "|"); = |0066xxxx|
3)oracle char (not varchar2 its a vended product don't know why the schema is like it is)
4)Works in sql client returns the single row I required same as what happens when I hard code the value in #1.

Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11
The code I ran in sql editor:

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

One common reason for this kind of problem is that the two environments (SQL editor vs Java code) are configured to look at different databases with different contents.
Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11
Yep thought about that and thats why I did the hard code example to make sure I'm getting the same result in both the editor and my application. I get the same result with the same time stamp and everything.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

The code snippets you posted are not real, right? I mean they are not copied&pasted from a working program. The hard-coded part is missing a closing apostrophe, as does the parametrized one, which moreover does not contain the question mark. And, according to the comments, neither of them returns any row. In other words, we still don't know which code you're actually executing and therefore cannot reliably tell you anything about it.

The CHAR is probably part of the problem. Please use DESC stu_master in SQLplus to obtain the length of this field. As you probably know, CHARs are padded by spaces to their declared length. There are certainly some comparison rules regarding CHARs, which might be responsible for the behavior you're experiencing, however I don't know them from the top of my head, as I was fortunate enough to avoid CHARs in my career.

This should not be that complicated issue, but you need to PostRealCode (and the length of the CHAR field in question as mentioned).
Ross Howard Miller
Greenhorn

Joined: Jan 23, 2012
Posts: 11
The solution:



Oracle puts some white space around char fields so you have to cast your variable to do an accurate comparison.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19697
    
  20

It's not just Oracle that does that. It's the whole point of a char column. Its contents all have the same fixed length; if the value is too short upon inserting / updating it gets padded. That's why varchar is much more popular. My suggestion - only use char if the field will contain only values of that exact length.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
GeeCON Prague 2014
 
subject: Prepared Statement Issue