File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes prepareStatement problem with the driver Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepareStatement problem with the driver" Watch "prepareStatement problem with the driver" New topic

prepareStatement problem with the driver

jeff zhang

Joined: Sep 25, 2000
Posts: 9
I have an oracle installed on my solaris system. Recently, I downloaded a jdbc driver oracle8i
from the oracle site and encounted the following problems
when using prepareStatement:
If the sql Statement has two table join, using setString
will not work(no any exception caught, but did not get any
data from db). If the parameter values were hard-coded
insided the sql statement instead of using setString, the
prepareStatement.executeQuery() will get some data returned
from the database. I also tried the jdbc driver for oracle 8.1.7
and got the same result. When I switched back to my old for and 8.1.7), everything is ok.
But the old will not support jdbc20 features.
Can anybody tell me what are problems and how to solve those?
Your help is greatly appreciated!
p.s. both jdk1.2.2/lib and jdbc/ are set in the CLASSPATH.
//test code

import java.sql.*;
public class driverTest
public static void main(String[] args)
PreparedStatement ps = null;
Connection my_conn = null;
//load the driver class

// making a conn through the Drivermanager
my_conn =DriverManager.getConnection
("jdbc racle:thin:@myDbServer", userId, password);
ps = my_conn.prepareStatement(
"SELECT a.column1 " +
"from TableA a, TableB b " +
"where a.comColume = b.comColumn " +
"and a.col2 = ? " +
"and a.col3 = ? ");
ps.setString(1, "value1");
ps.setString(2, "values");

ResultSet rs =ps.executeQuery();

while ( {
System.out.println(" ***return the following from db");
System.out.println(" column1 = " + rs.getString(1));

catch(ClassNotFoundException cnfe)
catch(SQLException sqle)

finally {
try { if(ps != null) ps.close(); }
catch (SQLException sqle) {
System.out.println("Error closing ps. " + sqle.getMessage());
try { if(my_conn != null) my_conn.close();
System.out.println("Connection closed.");
catch(SQLException sqle) {
System.out.println("Error closing database connection. " + sqle.getMessage());
Mindy Wu
Ranch Hand

Joined: Jan 12, 2001
Posts: 121
I have the same problem as you experienced, but finally, I fix my problem. I think my answer should help you to fix yours too. First, I did not see anything wrong with your code. So, maybe you should check the database structure in oracle ( I assume you use oracle). Find out the field size of the field that you use after the "=" sign, because in oracle if you defined fixed length for a field and the field value does not take up the full length, oracle will append trailling space after the field value. For example you field size is "10", and you field value is only a 5 digits long number "12345", when you compare subsitue this value with the "?", and = to the field value, as
you see, "12345" never equals "12345 ". Now, I think your mind is clear. To fix this problem, use Rtrim to trim the field space or use like operator and % . e.g fldNum like "12345%" or Rtrim(fldNum, " "), both ways should fix the bug. By the way, in sybase, it ignore trailling space of the field.
Good Luck!
jeff zhang

Joined: Sep 25, 2000
Posts: 9
It helps.
subject: prepareStatement problem with the driver