File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC Prepared Statement - multiple datatypes as input Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Prepared Statement - multiple datatypes as input" Watch "JDBC Prepared Statement - multiple datatypes as input" New topic
Author

JDBC Prepared Statement - multiple datatypes as input

Nirmal Mekala Kumar
Ranch Hand

Joined: Aug 27, 2005
Posts: 63
Hi
Please see the code and let me know if you find anything wrong. I have explained the details after the code:


The problem is it returns an empty result set. When I run the query with the values hard coded into the query I get the desired result.
DBDetails:
Database - Oracle 10g
JDBC library - ojdbc14.jar (by sun)
After some testing I found that the problem was with String parameter.
When I hard code the String input and parameterize the int, the program works like a charm and gives me the desired result.
When I hard code the int input and parameterize the String poof ... it returns an empty result set.
Can someone please tell me if its a bug with the jdbc library or am i just plain stupid??
Some more weird observation -
When i debugged with eclipse, I saw that Statement object keeps all the parameters by data type - paramString, paramint, paramDouble .... When the statement is created and the parameters are set using statement.setString/setInt the values can be seen in the Statement object.
Once the statement.executeQuery() method is invoked, the String parameters paramString all become NULL.
Just thought you shud know.
So please help me out ... someone


Nirmal Kumar<br />If you have to aim, aim for the moon. Even if you miss you will land among the stars
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


JDBC library - ojdbc14.jar (by sun)

Are you running a 1.4 JDK?


When I hard code the String input and parameterize the int, the program works like a charm and gives me the desired result.
When I hard code the int input and parameterize the String poof ... it returns an empty result set.

I don't follow this. Can you show us your code?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Nirmal Mekala Kumar
Ranch Hand

Joined: Aug 27, 2005
Posts: 63
Paul Sturrock wrote:

JDBC library - ojdbc14.jar (by sun)

Are you running a 1.4 JDK?


Using JDK1.5.0 update 22


When I hard code the String input and parameterize the int, the program works like a charm and gives me the desired result.
When I hard code the int input and parameterize the String poof ... it returns an empty result set.

I don't follow this. Can you show us your code?



Nirmal Mekala Kumar
Ranch Hand

Joined: Aug 27, 2005
Posts: 63

Also when I have tried parameterizing only int and hardcoded the String. This works fine.


Only when I parameterize the String input parameter I face the problem
Abhishek Ralhan
Ranch Hand

Joined: Aug 01, 2010
Posts: 40

Its all fine. I tried the code myself with all the possible ways.. it is just fine..

May be it would be a very trivial bug when you at last find it..


-Abhishek
I came to this world on a Learner's License
Nirmal Mekala Kumar
Ranch Hand

Joined: Aug 27, 2005
Posts: 63
Abhishek Ralhan wrote:Its all fine. I tried the code myself with all the possible ways.. it is just fine..

May be it would be a very trivial bug when you at last find it..


I hope thats true. But similar code works well when the database is MS SQL.
Anyways for now I have taken the ugly route ... string replacements
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The database will not make a difference. The driver may, but this is such basic stuff I would be amazed if a driver implementation had such glaring bugs in it - it would after all make that driver next to useless. That aside, I've used the Oracle drivers for years and never seen the same behaviour you are reporting. My guess is some error in your code logic - hard to say what that is because your code examples change each time you post them, though the versions you post will work just fine (I too, just tried your code and it works).


Anyways for now I have taken the ugly route ... string replacements


I would not use Statements in preference to PreparedStatements. This introduces a security hole, misses out on possible performance benefits and adds considerable effort to manage parameter formatting.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18651
    
    8

It looks very easy to get confused between "t$dtyp" and "t$dpty" (yes, I know the column names are beyond your control). And I noticed that you used them in one order in one example and then later used them in the opposite order. Even more confusing.
Nirmal Mekala Kumar
Ranch Hand

Joined: Aug 27, 2005
Posts: 63
Paul Clapham wrote:It looks very easy to get confused between "t$dtyp" and "t$dpty" (yes, I know the column names are beyond your control). And I noticed that you used them in one order in one example and then later used them in the opposite order. Even more confusing.


sorry about the confusing names. But I did check the order properly.
Nirmal Mekala Kumar
Ranch Hand

Joined: Aug 27, 2005
Posts: 63
Paul Sturrock wrote:The database will not make a difference. The driver may, but this is such basic stuff I would be amazed if a driver implementation had such glaring bugs in it - it would after all make that driver next to useless. That aside, I've used the Oracle drivers for years and never seen the same behaviour you are reporting. My guess is some error in your code logic - hard to say what that is because your code examples change each time you post them, though the versions you post will work just fine (I too, just tried your code and it works).


Yes Paul, it is one of the most basic features and thats why I was puzzled. I will definitely try to check the code again. Also the code is so simple its hard for me to think of things that I might have done wrong. It would be great if you can point out any such areas that I should be checking.


Anyways for now I have taken the ugly route ... string replacements


I would not use Statements in preference to PreparedStatements. This introduces a security hole, misses out on possible performance benefits and adds considerable effort to manage parameter formatting.

I totally agree with you but I just had to get this running in time. Also, I'm not using the major advantage of PreparedStatement, that is repetitive execution with different inputs. I only do it once. So I thought it was ok.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Prepared Statement - multiple datatypes as input