• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

prepareStatement problem with the driver classes12.zip

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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());
}
}
}
}
 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It helps.
Thanks,
Jeff
 
He was giving me directions and I was powerless to resist. I cannot resist this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic