prepareStatement problem with the driver classes12.zip

[jeff zhang]

Hello,
I have an oracle 8.1.6.2 installed on my solaris system. Recently, I downloaded a jdbc driver oracle8i 8.1.6.2.0
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
classes111.zip(not for 8.1.6.2 and 8.1.7), everything is ok.
But the old classes111.zip will not support jdbc20 features.
Can anybody tell me what are problems and how to solve those?
Your help is greatly appreciated!
Thanks,
Jeff
p.s. both jdk1.2.2/lib and jdbc/classes12.zip 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;
try
{
//load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");

// 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 (rs.next()) {
System.out.println(" ***return the following from db");
System.out.println(" column1 = " + rs.getString(1));

}
}
catch(ClassNotFoundException cnfe)
{
System.err.println(cnfe);
}
catch(SQLException sqle)
{
System.err.println(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]

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]

It helps.
Thanks,
Jeff