• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC Prepared Statement - multiple datatypes as input

 
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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?
 
Nirmal Mekala Kumar
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 40
Eclipse IDE Chrome Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Nirmal Mekala Kumar
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 63
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 promise I will be the best, most loyal friend ever! All for this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic