Inserting a row into a ResultSet in a JDBC application

If a ResultSet has a resultSetConcurrency attribute of CONCUR_UPDATABLE, you can insert rows into the ResultSet.

Before you begin

Ensure that the enableExtendedDescribe property is not set, or is set to DB2BaseDataSource.YES (2). Insertion of a row into a ResultSet does not work unless extended describe capability is enabled.

Procedure

  1. Perform the following steps for each row that you want to insert.
    1. Call the ResultSet.moveToInsertRow method to create the row that you want to insert. The row is created in a buffer outside the ResultSet.

      If an insert buffer already exists, all old values are cleared from the buffer.

    2. Call ResultSet.updateXXX methods to assign values to the row that you want to insert.

      You need to assign a value to at least one column in the ResultSet. If you do not do so, an SQLException is thrown when the row is inserted into the ResultSet.

      If you do not assign a value to a column of the ResultSet, when the underlying table is updated, the data source inserts the default value for the associated table column.

      If you assign a null value to a column that is defined as NOT NULL, the JDBC driver throws and SQLException.

    3. Call ResultSet.insertRow to insert the row into the ResultSet.

      After you call ResultSet.insertRow, all values are always cleared from the insert buffer, even if ResultSet.insertRow fails.

  2. Reposition the cursor within the ResultSet.

    To move the cursor from the insert row to the ResultSet, you can invoke any of the methods that position the cursor at a specific row, such as ResultSet.first, ResultSet.absolute, or ResultSet.relative. Alternatively, you can call ResultSet.moveToCurrentRow to move the cursor to the row in the ResultSet that was the current row before the insert operation occurred.

    After you call ResultSet.moveToCurrentRow, all values are cleared from the insert buffer.

Example

The following code illustrates inserting a row into a ResultSet that consists of all rows in the sample DEPARTMENT table. After the row is inserted, the code places the cursor where it was located in the ResultSet before the insert operation. The numbers to the right of selected statements correspond to the previously-described steps.
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM DEPARTMENT");
rs.moveToInsertRow();                                 1a 
rs.updateString("DEPT_NO", "M13");                    1b 
rs.updateString("DEPTNAME", "TECHNICAL SUPPORT");
rs.updateString("MGRNO", "000010");
rs.updateString("ADMRDEPT", "A00");
rs.insertRow();                                       1c   
rs.moveToCurrentRow();                                2