GeeCON Prague 2014*
The moose likes JDBC and the fly likes JDBC with Oracle prob URGENT Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC with Oracle prob URGENT" Watch "JDBC with Oracle prob URGENT" New topic
Author

JDBC with Oracle prob URGENT

Amit Punjwani
Ranch Hand

Joined: Jul 10, 2000
Posts: 50
Hi everyone,
I am working on a project on JSP/Servlets with Oracle as the database. I have a field Custoner ID (char)(10)(PK) in the Customer table of the database. so now for e.g if the value is C001 there are 6 white spaces along with C001.
So when i try to search the value C001 in SQL query,
for e.g "select * from Customers where CustID = 'C0001'"
But I do not receive any records matching with C001, coz in the database there is C001 and 6 white spaces
Lemme know what is the solution to this..
Thanx in adv....

Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
I think the best solution would be to get rid of the white space, but if you need it, change your SQL statement to
"select * from Customers where CustID LIKE 'C0001%'"
This will find all records that START with C0001. There may be better ways to do this in SQL, but not sure.
Brian Nice
Ranch Hand

Joined: Nov 02, 2000
Posts: 195
I think you could also use rtrim:
select * from Customers where rtrim(CustID,' ') = 'C0001'
Brian
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I am pretty sure if you rtrim the field, no index will be used which will impact performace.
Tom Hennigan
Ranch Hand

Joined: Feb 25, 2001
Posts: 71
Are you using jdbc 'select' or Oracle SQL/plus 'select'
In either case, the spaces should not matter, in my experience. What version of Oracle? I used only Oracle8i, jdbd sql statements and widely varying field lengths. Good luck.


Tom Hennigan<P>Sun Certified Java 2 Platform Programmer
Rahul Rathore
Ranch Hand

Joined: Sep 30, 2000
Posts: 324
Why not append the empty spaces BEFORE passing to the where clause? There can be many ways of doing it. I think one way can be as follows:-

public ResultSet findByCustomer(String custID) {
if(custID.length()>10) throw new IllegalArgumentException();
char[] dest= {' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '};
char[] src=custID.getChars();
System.arraycopy(src, 0, dest, 0, src.length);
String paddedCustID=new String(dest);
ResultSet rs=stmt.executeQuery("SELECT * FROM customers WHERE custid='" + "paddedCustID" + "'");
return rs;
}
 
GeeCON Prague 2014
 
subject: JDBC with Oracle prob URGENT