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

Odd problem with JdbcOdbc

Christopher Arthur
Ranch Hand

Joined: Mar 09, 2004
Posts: 149
I'm getting a java.sql.SQLException: General Error in trying to execute a prepared statement. The weird thing is that the same prepared statement sometimes throws the exception and sometimes it doesn't. Well, it isn't exactly the same prepared statement...it's a different run of the program...
Do prepared statements get stored in the database across multiple connections or something? My guess at what the problem may be, although unlikely, is that if I make a prepared statement and use it, then run the program again and make the exact same prepared statement, I get the exception when I try to execute it the second time. So maybe the problem is that the database gets confused and doesn't know which one to use, because they're both in there. But this seems unlikely because if it were so that the prepared statements were permanently part of the database, there would be some kind of handle method to retrieve them. Note that I am not using stored procedures
This is all happening using the JdbcOdbc driver and a Microsoft Access datasource.
Any ideas?
Chris
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Chris,
I'm only guessing, but I think the problem is probably with your code. Excuse me, but I couldn't see any of your code in your post. If you care to post your code -- along with the complete error message and stack trace you are getting -- I may be able to help you.
Good Luck,
Avi.
Christopher Arthur
Ranch Hand

Joined: Mar 09, 2004
Posts: 149
Here's some of the relevant code:
This bit is in the constructor, and the prepared statement insertNew is an instance variable. This bit never throws an exception.
****************************************************************************
con = DriverManager.getConnection(url);

// create a prepared statement to do inserts
//
insertNew = con.prepareStatement("INSERT INTO " + this.table + "(iteration, email, client_time, status, client_count, minA, maxA,minB, maxB, minX,maxX, minY, maxY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");

****************************************************************************

The next bit throws an exception at the insertNew.execute call. But not always
****************************************************************************
public void insert(BealNetObject bno, BigInteger masterIteration,String status) throws ServletException{

try {

insertNew.setString(1,masterIteration.toString());
insertNew.setString(2,bno.getUsername());
insertNew.setString(3,Long.toString(bno.getStartTimeAsLong()));
insertNew.setString(4,status);
insertNew.setString(5,Long.toString(bno.getCount()));

IterativePoint min = bno.getMin();
IterativePoint max = bno.getMax();

insertNew.setString(6,Long.toString(min.a));
insertNew.setString(7, Long.toString(max.a));
insertNew.setString(8, Long.toString(min.b));
insertNew.setString(9, Long.toString(max.b));

insertNew.setString(10,Integer.toString(min.x));
insertNew.setString(11, Integer.toString(max.x));
insertNew.setString(12, Integer.toString(min.y));
insertNew.setString(13, Integer.toString(max.y));
insertNew.execute();
insertNew.clearParameters();

} catch (SQLException sql) {
throw new ServletException(sql);

}
****************************************************************************
here's the relevant part of the stack trace:
****************************************************************************
----- Root Cause -----
java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
at arthur.beal.net.BealDAO.insert(BealDAO.java:155)

****************************************************************************
Now the exception is only thrown when a very important bit of code is executed before the prepared statement is even created. Here is the code that leads up to that, but it is a bit lengthy, so I'll try to explain it. In the constructor, I try to make a table, but not if it's already made, so I issue a CREATE statement regardless, knowing that I'll get an exception if the table is already there. So if it is, I put some code in the catch block, to get some info about the table. Then I open a new connection and create the prepared statement to use for the life of this object...and it throws the exception if the table already existed, although the rest of the code runs fine.
Here's the code to create the database and/or get some info about the table
****************************************************************************
try {

// open a connection
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url);
Statement s = con.createStatement();

// create table
try {
s.execute("CREATE TABLE " + this.table + " (iteration NUMERIC PRIMARY KEY,email CHAR(40), client_time NUMERIC,status CHAR(20),client_count NUMERIC,minA NUMERIC,maxA NUMERIC,minB NUMERIC,maxB NUMERIC,minX INTEGER,maxX INTEGER,minY INTEGER,maxY INTEGER)");
con.close();

// we used the first connection to create the table, and for some reason we need to close it
// to make the table persistent.

// since we're just starting the computation, we set the current point at the lowest setting
currentPoint = info.getAbsoluteMin();
currentIteration = BigInteger.ONE;

// we're going to use a row in the database to store the state of the master iterator.
// that row will be row 0, so the actual iteration value will be stored in client_count
// and the values for a,b,x and y will be in the 'max' fields. Here we create that entry

con = DriverManager.getConnection(url);
s = con.createStatement();

IterativePoint p = info.getAbsoluteMin();
s.execute("INSERT INTO " + this.table + " (iteration, client_count, maxA,maxB,maxX,maxY) VALUES (0,1," + p.a + "," + p.b + "," + p.x + ","+ p.y +")");

con.close();
System.out.println("The table was successfully created.");



} catch (SQLException sql) {
// If we get it here, it probably means that the table already exists.
// So, we pull the state of the iterator out of the database
con = DriverManager.getConnection(url);
s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT iteration,client_count,maxA,maxB,maxX,maxY FROM " +this.table+" WHERE iteration = 0");
rs.next();
currentIteration = rs.getBigDecimal("client_count").toBigInteger();
currentPoint = new IterativePoint();
currentPoint.a = rs.getLong("maxA");
currentPoint.b = rs.getLong("maxB");
currentPoint.x = rs.getInt("maxX");
currentPoint.y = rs.getInt("maxY");
con.close();

}

con = DriverManager.getConnection(url);

// create a prepared statement to do inserts
//
insertNew = con.prepareStatement("INSERT INTO " + this.table + "(iteration, email, client_time, status, client_count, minA, maxA,minB, maxB, minX,maxX, minY, maxY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
} catch (Exception sql) {
System.out.println(sql.toString());
}
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30919
    
158

Christopher,
That code is dangerous because you get a SQL Exception for many other reasons. For example, the connection could be stale. In that case, the catch block would have further problems.
What database are you using? Some have a statement for creating a table if it is not already present.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Christopher Arthur
Ranch Hand

Joined: Mar 09, 2004
Posts: 149
Ah! I figured it out. THe problem wasn't the prepared statement, it was that I was accidentally trying to INSERT a duplicate primary key!
Christopher Arthur
Ranch Hand

Joined: Mar 09, 2004
Posts: 149
I'm using Microsoft Access...
A connection can go stale? Is there a way to check to see if a connection is valid?
Chris
senthil doraiswamy
Greenhorn

Joined: Feb 14, 2002
Posts: 9
sure, you can try using method "isClosed()" on your Connection object to check if it is still good (stale) or not. hope this helps.




--- sen.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Odd problem with JdbcOdbc