Programmatically inserting into a row using ResultSet

From: Hugh von Kanada (hughfno_at_spamsympatico.ca)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 13:57:52 -0500

Hi,
   This is a method of insertion right out of Sun's JDBC API Tutorial
and Reference book.

   I have a table in an MS SQL Server 2000 database that I'm trying to
add rows to. I'm first doing a query on the table, SELECT * FROM
T_ACTIVITYDEFINITION WHERE CONTEXT_DEF_ID = ?
then moving the result set to the insert row, inserting my data, and
trying to retrieve the row ID that I just inserted. The primary key on
this table is auto-incremental, i.e. you don't specifiy a value when
inserting into it, and it updates with the next value in a sequence.

My code looks something like this:
String strSQL = "SELECT * FROM " + TBL_ACT_DEF;
stmt = getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rSet = stmt.executeQuery(strSQL);

rSet.moveToInsertRow();
rSet.updateInt(COL_CONTEXT_DEF_ID, iCtxId);
rSet.updateInt(COL_ACT_NUM, act.getActivityNumber());
rSet.updateString(COL_ACT_NAME, act.getName());
if (act.getDescription() != null)
rSet.updateString(COL_DESC, act.getDescription());
                        
char implCode = 'D';
rSet.updateString(COL_IMPL_TYPE, String.valueOf(implCode));
                        
        
// Now do the insert.
rSet.insertRow();
                        
// Get the ID of the row just inserted.
if (rSet.last()) {
        iRetVal = rSet.getInt(COL_ACT_DEF_ID);
}

This always either fails to get the row if this is the first entry into
the result set, or gets the one previous to the insert.

I suppose I could do an explicit INSERT then do a query to find out what
I just inserted, but that seems kind of kludgy and has the potential for
race conditions, although I could mitigate if I had to.

So, the question is, if using the ResultSet isn't the right way of doing
this, how does one properly go about inserting a row, then getting the
ID field of the row just inserted, when the row insert happens from a
sequence?
-Hugh F.

Relevant Pages

  • Re: Auto-generate recently visited record-list
    ... mock up a query using your specific table and some ... Form Company with CompanyID as primary key and autonummered / ... Table History with LogID and FkID ... On the company form I added a combo list CompanyName ORDER BY Inserting ...
    (comp.databases.ms-access)
  • Re: Auto-generate recently visited record-list
    ... On the company form I added a combo list CompanyName ORDER BY Inserting ... I made a history table as you wrote to me with LogID and FKID. ... query statement, or AddNew. ... to navigate throught the records ...
    (comp.databases.ms-access)
  • Re: Programmatically inserting into a row using ResultSet
    ... for that matter) while you were inserting. ... begin transaction ... I'm first doing a query on the table, ... and it updates with the next value in a sequence. ...
    (comp.lang.java.databases)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... was that the Sequence Generator in Oracle remained at the last number PRIOR ... to the insert query, so, even though the new larger numbers went in, Oracle ... If this is a multi-user app, you don't know if someone else is inserting a ...
    (microsoft.public.access.queries)
  • Re: Running counter
    ... >Hey guys any other ideas? ... Hi Michael, ... Here's another way to write the same query, ... inserting the rows. ...
    (microsoft.public.sqlserver.mseq)