Joel Brown's Weblog

Inserting into a Database Table

For my first real entry, I'll describe how to programatically insert a row into a database table using either a CachedRowSet or a CachedRowSetDataProvider.  This has been asked a couple of time of the forum already, so I'll provide some detail.

The real work of inserting is done by a CachedRowSet that you must first create.  The key to the database INSERT is the CachedRowSet's command property. The CachedRowSetDataProvider just provides an alternate and (hopefully) easier to code access to the CachedRowSet. 

For the simpliest RowSet, the actual table INSERT command includes all the columns in the RowSet's SELECT statement (it's command property).  For example, for "select COL1, COL2 from MYTABLE" we'll generate "insert into MYTABLE(COL1,COL2) values(?,?)".  More details are below in the Q&A.

Here are the code snippets. Both examples assume you've got a Message Group Component on your page to display any output from error() or info() calls. 

CachedRowSet crs = getSessionBean1().getMyRowSet() ;
try {
    crs.execute() ;  // unless it's already been executed.

    crs.moveToInsertRow() ;
    crs.updateInt("COL1", 1 ) ;
    crs.updateString("COL2","Row 1") ;
    crs.insertRow() ;
    crs.moveToCurrentRow() ;
    crs.acceptChanges() ;
    info("Row Inserted via CachedRowSet") ;
    // crs.release() ; // release rowset memory
} catch (SyncProviderException spe) {
    try {
        // construct a message with all the possible
        // exceptions in the SyncProvider.  Note that since
        // we're doing an INSERT, we should only care about
        // SyncResolver.INSERT_ROW_CONFLICT, but I'll handle
        // them all here.
        SyncResolver resolver = spe.getSyncResolver();
        String message = spe.getMessage() + ":";
        while(resolver != null && resolver.nextConflict())  {
            int row = resolver.getRow();
            if (!message.equals("")) {
                message += ";";
            }
            String zbrow = Integer.toString(row - 1) ;
            switch (resolver.getStatus()) {
                case SyncResolver.DELETE_ROW_CONFLICT:
                    message += "DELETE_ROW_CONFLICT: " + zbrow ;
                    break;
                case SyncResolver.INSERT_ROW_CONFLICT:
                    message += "INSERT_ROW_CONFLICT: " + zbrow ;
                    break;
                case SyncResolver.UPDATE_ROW_CONFLICT:
                    message += "UPDATE_ROW_CONFLICT: " + zbrow ;
                    break;
            }
            if (resolver instanceof SyncResolverX) {
                message += ": " 
                    + ((SyncResolverX)resolver).getSQLException() ;
            }
        }
        error( message ) ;
    } catch (SQLException sqle) {
        error( sqle.getMessage() ) ;
    }
} catch (SQLException sqle) {
    error(sqle.getMessage() ) ;
}

CachedRowSetDataProvider cdp = getMyDataProvider() ;
try {
    RowKey rk = cdp.appendRow() ;
    cdp.setCursorRow(rk) ;
    cdp.setValue("COL1", new Integer(1) ) ;
    cdp.setValue("COL2", "Row 1" ) ;
    cdp.commitChanges() ;
    info("Row Inserted via Data Provider") ;
    // cdp.refresh() // release rowset memory.
} catch (Exception ee) {
    error( ee.getMessage() ) ;
}

Performance tip: If this rowset is used only for inserting the row, the command's WHERE clause should not force any extra processing. For example:

select COL1, COL2 from norelation where 0=1

Q & A:

How does CachedRowSet know the columns to include in the INSERT statement?

The SQL insert statement is determined from the columns selected in the rowset's command property.  We will include all writable columns.  We rely on the database driver to determine writable columns (see preparedStatement.getMetaData()).  This means if your rowset contains calculated columns, such as "select A, B, C/10 as "div10" from ..." then the 3rd column, C/10, will be ignored.  Column C will not be included in the INSERT statement.   Caution:  not all drivers are, ummm, perfect, and may return incorrect or incomplete metadata.   That would, of course, screw up our INSERT statement.  (Note:  you can exclude columns using the advanced insertableColumns property - see below ).

Can I use an existing CachedRowSet that I use to display data?

Of course.  Be aware of the last point.  Also be aware that if your table has a non-nullable column that is not in your rowset, your insert will fail.

What if my existing CachedRowSet has another table ?

The RowSet's tableName property dictates the table that we'll insert into.  Any other tables, and columns in those tables, are ignored.

Should I use my existing CachedRowSet or create a new one?

This depends on the specific situation.  Does a current RowSet include all the columns you need to insert?  Does it contain columns you don't need to insert?  (If you create a new one, don't forget the performance tip above).

What about IDENTITY or AUTOINCREMENT or other database-generated columns?

This is highly database dependent.  Some databases require you to obtain the key through vendor-specific means.  Others say "set the value to null".  Some say to not include the column in the INSERT statement.

So how do I exclude my IDENTITY or other column from the INSERT statement?

Use the RowSet's advanced insertableColumns property.  Create a boolean[] array with one value for each column in the result.  Use true to include the column in the INSERT statement, otherwise use false.  The RowSet's property sheet doesn't allow you to set this property (in Creator2-EA2), so set the property in your java code.

After the INSERT, how do I retrieve any database generated value?

Unfortunately, we do not offer a way to easily obtain this.  You will have to reselect the data to find the generated value.

How can I tell what SQL statements are being generated?

We've implemented some minimal logging.  Set the RowSet's printStatments property to true.  The output will be sent to your app server's log file.

Not easily.  For the CachedRowSet, interpret the SQLException and create your own.  The SyncProviderException will give you the row within the RowSet and it's SQLException.  The CachedRowSetDataProvider's exception message text would have to be parsed, and it may contain more than one exception if you updated/inserted multiple rows.

Where can I find more info on CachedRowSet?

See the JDBC javadocs.  We implement the interface in our own very special way.

Where can I find more info on CachedRowSetDataProvider?

The Data Provider java docs can by found in creator's Dynamic Help window.  I've got it on my list to improve this a bit - and that's always a never ending battle).

Which method should I use - the CachedRowSet or the CachedRowSetDataProvider?

If either can accomplish the task, pick the one you're most comfortable with.  The CachedRowSetDataProvider introduces a very small amount of overhead.

I don't like either.  Is there another way besides CachedRowSet or CachedRowSetDataProvider?

Generate the SQL yourself and use plain old JDBC.  And please tell us why you don't like either so we can improve them.

Posted at 08:49AM Sep 21, 2005 by jfbrown in Creator  |  Comments[6]