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. 

Using a CachedRowSet Using a CachedRowSetDataProvider
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.

My users tell me that the exception message text sucks.  Can I override the message?

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.

Comments:

THANK YOU for posting this information and Example! I've been trying to find the best way to use Creator with DataProviders and RowSets and there seems to be little documentation out there to answer some of the issues you addressed. I still have questions on how much Creator will do behind the scenes via bindings vs. what parts need to be coded. If you'd like to add an "Update" example, it would be appreciated! Also a list of other links you know of regarding this subject...

Posted by D. Blais on October 04, 2005 at 03:23 AM PDT #

Thanks. I found that really helpful. Very useful to see the CachedRowSet and DataProvider examples side-by-side plus the very clear answers to the questions. I'm really keen on Creator EA2, but, like the previous poster, I find myself tapping around in the dark much of the time. Does anyone intend to produce a tutorial + reference book on any of this? One question (if you have a moment to spare): the documentation for TableDataProvider says we should call canAppendRow before calling appendRow to check that the DataProvider can indeed append a row. Why might it not be able to? Again, many thanks.

Posted by Mick on October 13, 2005 at 07:43 AM PDT #

Why call canAppendRow()?

In theory, you may not know if the instance of the DataProvider you are using allows appending rows.

In reality, your testing will determine if it's needed.

For a CachedRowSet, we prepare it's statement with CONCUR_UPDATABLE. On execution, the driver/database may say the resultset CONCUR_READONLY. We pass this fact onto you via canAppendRow() - because we assume the driver/database knows something about the statement that would make inserting/updating likely to fail. One driver I've used requires selecting the primary key in order to be updatable.

But like I said, testing your app will determine if it's really needed. If your sql is constant I wouldn't bother. But if you're using some abstract case where it "can do anything and I don't know what it may be until runtime", then use the canAppendRow() check. It'll allow you to catch and handle the issue sooner, rather than later at the acceptChanges()/commitChanges() step.

Posted by Joel on October 14, 2005 at 12:15 AM PDT #

Thanks for that very clear explanation. My DataProvider passes the canAppendRow test, but nothing gets added and I don't get a new row in the datatable, but no Exception is thrown. Ah, well it's one way of passing the time, I suppose, and, no doubt, by trying every possible permutation I get to learn something :) Thanks for the help.

Posted by Mick Underwood on October 14, 2005 at 07:23 AM PDT #

not really happy with it.
No autoincrement support, cryptic error messages, poor performance, bad BLOB support, weird workflow usage and the list goes on.
A simple advice, DONT waste your time and energy with this old stuff, if you need easier database integration, go ahead with JPA straight away.

Posted by sunstopmakingstupidframeworks on September 17, 2008 at 11:32 AM PDT #

PostgreSQL has some encryption functions to encrypt data in columns. Is it possible to use one such function to encrypt data while using CachedRowSet Data Provider? Thanks.

Posted by Ashok Kumar Harnal on November 05, 2008 at 05:01 PM PST #

<br>helloo

Posted by xxx on September 14, 2010 at 07:42 PM PDT #

Hello

I have a web application which uses CachedRowSet and CachedRowSetDataProvider.
Unfortunately when the application is called simultaneously by multiple users, inserting a new row into a table using CachedRowSetDataProvider crashes connection: java.sql.SQLException: Connection is closed. It works without problems if the number of users is very small, but when more than 10 users simultaneously use the connection, application crashes. Can you give me some advice about what is happening and how can I fix it. Unfortunately the problem is very quite unpleasant and needs to be solved as soon as possible. Thank you.

Posted by manu on October 26, 2010 at 12:23 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

jfbrown

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News
Blogroll

No bookmarks in folder