Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement binding not working properly (Oracle Problem?)

 
chetak faldesai
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • 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 ]
 
Herman Schelti
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
  • 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
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • Quote
  • Report post to moderator
Thanks Again Guys!!
It worked.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic