This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes prepareStatement problem with the driver classes12.zip Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepareStatement problem with the driver classes12.zip" Watch "prepareStatement problem with the driver classes12.zip" New topic
Author

prepareStatement problem with the driver classes12.zip

jeff zhang
Greenhorn

Joined: Sep 25, 2000
Posts: 9
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
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
Greenhorn

Joined: Sep 25, 2000
Posts: 9
It helps.
Thanks,
Jeff
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: prepareStatement problem with the driver classes12.zip
 
Similar Threads
database connectivity
looking for help with datatype match
Null error
DSN name not found error with TOMCAT5.5.7
Invalid operation for read only resultset: updateFloat