本文发表在 rolia.net 枫下论坛Hi Jabber,

I am working on a project involving servlet or JSP and MS SQL Server. It is a IIS Server + Resin structure.

Now, I need to insert a record into a table and id number is auto-generated when inserting data. I need the id number after insertion. I lock the table during the inserting and quering the id by defining it as a transaction to prevent other database user operating the table.

But the problem is that sometimes a null is retured from the id query. Here is the code:

// insert status info
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
stmt.execute();
stmt = con.prepareStatement("BEGIN TRANSACTION");
stmt.execute();
stmt = con.prepareStatement("insert into import_status WITH (TABLOCKX) (site_user_id, type, progress) values (?,?,?) ");
stmt.setString(1, dbc.getUserID());
stmt.setString(2, group_name);
stmt.setString(3, "Now is processing uploaded file");
stmt.executeUpdate();
// get the import_status_id
// Thread.sleep(3000); // no use
stmt = con.prepareStatement("select SCOPE_IDENTITY()");
//stmt = con.prepareStatement("SELECT @@IDENTITY AS \'Identity\'"); // no use. same thing
ResultSet rs = stmt.executeQuery();
if ( rs.next() )
import_status_id = rs.getInt(1);
stmt = con.prepareStatement("COMMIT TRANSACTION");
stmt.execute();
System.err.println("import_status_id="+import_status_id);
if ( import_status_id == 0 )
throw new Exception("SQLException: import_status_id == 0");

Can you give any ideas about this. Thanks.更多精彩文章及讨论，请光临枫下论坛 rolia.net