• 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

PreparedStatement binding not working properly (Oracle Problem?)

 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to run the following query.

select emp_id, emp_fName, emp_lName from
employee where emp_lName = 'ABC' and emp_status='ACT';

I should get at least one row from the database(as there is data) but I am not getting any data when using PreparedStatement. Following is the Jdbc code to access the same. ( Not the actual code but similar snippet. )

Connection conn = null;
PreparedStatement stmt = null;
List rslts = new ArrayList();
String sql="select emp_id, emp_fName, emp_lName, emp_status from employee e where e.emp_lName = ? and emp_status = ?";
try {
conn = -- get the connection from connection Pool (oracle thin driver and server is weblogic 7) --

stmt = conn.prepareStatement(sql);
stmt.setString( 1, "ABC" );
stmt.setString( 2, "ACT" );
ResultSet rs = stmt.executeQuery();
while( rs.next() ) {
rslts.add(rs.getString(1));
}
rs.close();


With this I do not get any rows. There is no syntax errors. no other errors, if I hardcode the last name in sql query, it works and returns the resulting row.

The oracle database table is structured as follows
emp_lName - char(15)
emp_status - char(3)

If I pad the lastname to 15 chars to mimic the data structure of last_name column, then it works. i.e if I do something like stmt.setString( 1, "ABC############" ); (substitute # with space. Couldnt show space. whitespace is trimmed) then it works
Sorry for the bold but Its a long question and wanted to catch your attention
Is this something wrong with Jdbc PreparedStatement binding? if so what is the workaround for that?
[ August 22, 2007: Message edited by: chetak faldesai ]
 
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi chetak,

what type of column is emp_lName?

Did you try the rtrim function:
where rtrim(e.emp_lName) = ? and emp_status = ?";

Herman
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Chetak,
Note that char means fixed length character field while varchar means variable length character field. The database will only match if the values are the same. A common solution is to use the trim function that Herman mentioned.

PreparedStatement is working properly. When you hard code the value (without the spaces), it shouldn't match. If it does, it is a bug and you should be wary of relying on that behavior. If Oracle fixes the bug, it would change. They fixed a number of bugs like this in Oracle 10 and I imagine they would fix more in the future. Better to use trim so you know it will work.

And the bold is fine. It highlights an important part of your post.
 
chetak faldesai
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your prompt replies. I spent a lot of time on trying to figure this out by myself. I should have posted here earlier

I will try the rtrim function and see if that works.

Herman, its a fixed length char (15) column.

Will keep you guys posted about the results. Thanks!!
 
chetak faldesai
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Again Guys!!
It worked.
 
Just the other day, I was thinking ... about 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