• 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

Need help from Oracle Gurus

 
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Problem was found when a CMP entity bean deployed in Weblogic 5.1 with Oracle 8i as backend database, using connection pooling and Oracle thin driver. The finder method did not work while create()/remove() worked fine. The same bean was deployed with SYBASE ASE 12 before and it worked perfectly. Further research showed that the problem is rooting from the setString() method in prepareStatement. Here is the snippet in a test program.
The following code works fine where user_id is defined as int in the table.
String QueryStr = "select * from Users where user_id = ?";
stmt = con.prepareStatement(QueryStr);
stmt.setInt(1, 9999);
rs = stmt.executeQuery();
While the following does not return anything in the ResultSet, where user_name is defined as a varchar in the table
String QueryStr = "select * from Users where user_name = ?";

con = db.getConnection();
rs = stmt.executeQuery(QueryStr);
stmt.setString(1, "lin");
rs = stmt.executeQuery();
If I don't use the setString method, instead use

String QueryStr = "select * from Users where user_name = 'lin'";
It works fine too.
I believe that this has something to do with the setting on Oracle side, maybe with the character set setting. Can someone tell me how to fix it?
Thanks.
 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sholud it not be setString(1,"'lin'");
 
Frank Lin
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Problem went away after changing the column default to NOT NULL. Mystery stays why this could happen.
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You have overlooked something
This is the code that you identified as the problem:

change to
It should work fine after that change
Jamie
[ February 04, 2002: Message edited by: Jamie Robertson ]
 
Frank Lin
Ranch Hand
Posts: 76
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry for the confusion. I made a mistake when I cut and paste to make the code snippet. The problem was caused by not setting the column default to NOT NULL. The strange thing was it worked fine with PL/SQL select. With PreparedStatement, the query returns the result when the condition is defined as field_name > 'value' when actualy the query condition should be field_name = 'value'. I think it is something in the JDBC driver.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have never seen this problem with the Oracle drivers before, which leads me to one of the following conclusions:
1. You have a very old version of the Oracle drivers. Download the newest version to eliminate any old bugs.
2. Your driver has become corrupt in some way. Again, download the newest version of the Oracle drivers to eliminate the corrupt code.
Have you downloaded the most recent Oracle JDBC drivers?
I am assuming either 1 or 2 because most bugs in the Oracle drivers have been accounted for, especially such a major one (setString method)???
Jamie
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can i use my own Connection Pooling in CMP Beans.
I am using Oracle.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic