• 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

Odd problem with JdbcOdbc

 
Ranch Hand
Posts: 149
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 149
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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());
}
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Christopher Arthur
Ranch Hand
Posts: 149
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 149
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm using Microsoft Access...
A connection can go stale? Is there a way to check to see if a connection is valid?
Chris
 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sure, you can try using method "isClosed()" on your Connection object to check if it is still good (stale) or not. hope this helps.
 
Drove my Chevy to the levee but the levee was dry. A wrung this tiny ad and it was still dry.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic