*
The moose likes JDBC and the fly likes java.sql.SQLException: Invalid handle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: Invalid handle" Watch "java.sql.SQLException: Invalid handle" New topic
Author

java.sql.SQLException: Invalid handle

Laura Mike
Greenhorn

Joined: Nov 13, 2003
Posts: 17
hi,
i am trying to insert record in a table where id is autogenerated. for generating id i have made another method "generateId()" which generates a unique id for me.
Then in my insertion method first i call method "generateId()" for id field then call insert query.
The code for insertion works well if i give any hard coded value for id but gives error when i tries to generate an idfrom my method and input that newly generated value.
the exception i read is as follow:
java.sql.SQLException: Invalid handle
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6229)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2509)
at sun.jdbc.odbc.JdbcOdbcStatement.execute2(JdbcOdbcStatement.java:421)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:302)

plz help me.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

post the offending code, as well as stating the database your using. This will eliminate the guesswork.
thanks
Jamie
Laura Mike
Greenhorn

Joined: Nov 13, 2003
Posts: 17
The code is here:
int myId = generateId();
int value = -1;
try
{
value = this.stmt.executeUpdate("INSERT INTO "+ this.tableName + " (id,firstName) VALUES (" + myId + ",XYZ ) ");

// this is dummy select just to deal with missing update bug in access or jdbc bridge.
int val = this.stmt.executeUpdate("DELETE FROM "+this.tableName +" where id= -1" );
}
i have tried to delete the 2nd executeUpdate statement but it makes no difference means that the error is not coming due to this.
the code of generateId is as follows:
public int generateId()
{
int id = 0;
try{
this.rs = this.stmt.executeQuery("SELECT MAX(id) FROM MyTable");
rs.next();
id = rs.getInt(1) + 1 ;
pool.returnConnection(connection);
stmt.close();
return id;

}
plz help me....
Andres Gonzalez
Ranch Hand

Joined: Nov 27, 2001
Posts: 1561
this is not a direct answer to your problem but a suggestion. Do not generate the unique ID yourself (I mean, by code). Let the DB do that for you. Most of the current DB allow you to have a column like "autoincrement" (MySQL has, if I remember correctly). In this way you just insert the values you're interested in and let the database do the work for the unique id.
with the code you've got at the moment,
id = rs.getInt(1) + 1 ;
what will happen if the above line is executed and another user is inserting a row in the DB? you'll be in trouble
Again, this is not an immediate solution to your problem, but I felt like pointing this out. I've seen many times developers trying to rely on the number of rows in the database to generate unique identifiers, which is not appropriate way to do it..
regards


I'm not going to be a Rock Star. I'm going to be a LEGEND! --Freddie Mercury
Laura Mike
Greenhorn

Joined: Nov 13, 2003
Posts: 17
hi Andres..thanx for ur sugestion..but thats not applicable in the situation where client want everything to be reside and handled from code.
Secondly in my code i have kept generateId() method synchronied so that just a single access is possible at any time.
Plz friends if u have anyother solution then plz help me im still trying to come out ofit.
Awaiting desperately for ur help.
 
Consider Paul's rocket mass heater.
 
subject: java.sql.SQLException: Invalid handle