Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: Invalid handle

 
Laura Mike
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
post the offending code, as well as stating the database your using. This will eliminate the guesswork.
thanks
Jamie
 
Laura Mike
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1561
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Laura Mike
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic