Thursday Jan 26, 2006

Creator 2 3rd party DB Driver Support

Creator ships with DB drivers that we support.  Recent MySQL drivers are also supported.


But what about other drivers?


Here's how to determine if your driver works:


Add a datasource using your driver and database.  See the Tutorials page for  Creating Database Server Types and Data Sources.


In the servers pane, open up your data source node and see if you can see any tables.


If that works, pick a double and double click to "View Data".  Now alter the query, prefixing it with "smd " (this is an undocumented command).  E.g., "smd select \* from myTable".  Run.


This creates a PreparedStatement for the given SQL and outputs the ResultSet from the preparedStatement.getMetaData() call.


If data is displayed, does it look "reasonable"?   Most important are the ColumnName and Type columns.  For a valid example, compare it to data from a supported driver (e.g., a table in our bundled database).


Does it throw an exception?  If so, it's not supported and will not work at design time.  Period. 


All the failures I've seen so far are because PreparedStatement.getMetaData() does not work, usually throwing a "not supported" exception.  Some drivers support this call after statement execution, but we require support before executing the statement.


Add a comment to this entry with the driver/database that you'd like to see supported.  Even if someone else has mentioned your database, comment yourself.  Supporting additional drivers is on the unfinalized list of features for next release (no promises!).

Wednesday Jan 25, 2006

View Data Trivia

View data is a simple window that allows you to run sql, any sql, even multiple statements.  It has some undocumented commands you may or may not find useful.  Here they are.


title something meaningful
Ever had more than one View Data document open and forgotten what's in which one?  This allows you to change this document's title.


setautocommit on | off
Default is on.  If you wish to run multiple statements in a single transaction, turn it off


// comment
If you're writing a long script, add comments using this format.




For troubleshooting, check out:


smd your_select_statement
This shows the driver provided pre-execution ResultSet metadata from a preparedStatement.getMetaData() call for the provided select statement.   An exception here means your 3rd party driver is not supported and just won't work at design time.


getmetadata
Show the DataBaseMetaData; see Connection.getMetaData().


table table_name | schema.table_name
Shows the meta data for a particular table.


datasources
For each open project, list the data sources it uses and their config info.




And lastly, a feature I didn't quite finish:


useViewData on | off
When on, selecting any RowSet and right-click "Edit SQL Statement" will open the sql statement in a View Data Window (with a Save button) instead of the Query Editor.   To return to using the Query Editor, set this to off or restart the IDE.  What's missing:  running when there are parameters (?) in the query is not supported.



Using RowSets for CRUD, or Not

I've noticed a few confusing forum posts on just what the heck our CachedRowSet is capable of.


CachedRowSet implements ResultSet.  The ResultSet javadoc describes itself as " A table of data representing a database result set, which is usually generated by executing a statement that queries the database. "  (the emphasis is mine.)


It works by first getting the "rows" of data from the database.  This is done by executing a database query, i.e. an sql SELECT statement.  Note:  stored procedures can also return a ResultSet, but we do not currently support stored procs.


You cannot set the command of a CachedRowSet to an sql UPDATE or DELETE statement.  These statements do not return a ResultSet when executed.


After you have a populated CachedRowSet (again, because you've executed some SELECT statement), you can then update or delete rows that exists within the CachedRowSet, or you can insert entirely new rows.  To propagate your changes to the database, call acceptChanges().   You can also do the update or delete of rows through the DataProvider interface.  For more info on doing this, see the tutorials for Accessing Databases on the Java Studio Creator Tutorials page.


But what if you want to batch update many rows, let's say reduce every manager's salary by 10%:

update EMP_TBL set salary=salary\*.90 where title='manager'

This single UPDATE statement is efficient - it's all done within the database server process. 


However, to do this with a CachedRowSet is not efficient.   First, you'd have to compose a select statement that included the primary key and the column(s) to be updated.  You execute it.  All rows to be updated get transferred to the web server  where your code resides.  Now your code has to loop through each row, setting the new salary value.  Lastly, you acceptChanges().  If you look at the logging for what happens during the acceptChanges(), you'll see we do one SELECT and one UPDATE for each row in the CachedRowSet that has changed.


For a twenty row update, the single UPDATE statement sends one single command to the db server.  The CachedRowSet solution does the initial select, creates a bunch of java objects, then does 20 SELECT and 20 UPDATE commands to the db server. 


So how do you that single UPDATE within Creator?  Use plain old JDBC.


For the next release, we hope to provide a framework or at least helper classes to assist you.  But for now, you're on your own.


One final note: our CachedRowSet is implemented with class CachedRowSetXImpl (which implements our own CachedRowSetX).  The eXtension of jdbc's CachedRowSet adds the advanced properties you see in the CachedRowSet's property sheet; most of these properties are used to work around problems in database driver metadata, or allow fine tuning of the generated INSERT and UPDATE statements.

Thursday Dec 15, 2005

CachedRowSetDataProvider and CachedRowSet Info



The CachedRowSetDataProvider is a wrapper to data in a CachedRowSet.  It's primary purpose is to facilitate binding to components. 


The only piece of information the CachedRowSetDataProvider holds is a cursor position into the CachedRowSet. If multiple CachedRowSetDataProvider instances reference the same CachedRowSet, they can each have their own cursor position (caution: see the section below on threading).   Again, all data from the database is stored in the CachedRowSet.


CachedRowSet Cursor Position


Many the CachedRowSetDataProvider methods may move the cursor position in the underlying CachedRowSet.  The CachedRowSetDataProvider methods findFirst(), setCursorRow(), getValue(), setValue() and others may/will move the CachedRowSet's cursor.


Big Rule:  if you rely on cursor postion in the CachedRowSet,  access it only through CachedRowSet methods or only through CachedRowSetDataProvider methods.  Any cursor movement through CachedRowSet methods will not be seen by a CachedRowSetDataProvider.


Closing your CachedRowSetDataProvider


If your CachedRowSet is in a "higher" scope, remember to call close() on your CachedRowSetDataProvider.  For example, if your CachedRowSetDataProvider  is in your page (request scope) and the CachedRowSet is in your session bean (session scope), call close().  This is the default for Creator, so you'll notice we add the call to close() the CachedRowSetDataProvider into the page's destroy() method.


Internally, the dataProvider adds a listener to the RowSet instance to detect changes so that listeners to the dataProvider can be notified of those changes.  The method close() removes that listener.


Without the close()  (i.e., removing the listener) the DataProvider instance cannot be garbage collected even though it's in request scope and should be gc'd   after the response is rendered.  Failure to close the CachedRowSetDataProvider in this situation will manifest itself as a slow memory leak.


CachedRowSetDataProvider.close() also move's the CachedRowSet's cursor to the first row.


The CachedRowSetDataProvider.close() does nothing else to the CachedRowSet: the CachedRowSet's data and properties are not changed.


What happens in a CachedRowSet.close()?
The row data is released and many properties are reset to default, such as showDeleted, queryTimeout, maxRows,
maxFieldSize, type, concurrency, readOnly, and transactionIsolation.


What happens in a CachedRowSet.release()?
The row data is released (see the javadoc for more details).  Use release() if you intend to re-execute the query at a later time


What happens in a CachedRowSetDataProvider.refresh()?
This calls CachedRowSet.release() and resets the CachedRowSetDataProvider's cursor.  It does not execute() the CachedRowSet at this time.


When is a CachedRowSet executed?
CachedRowSetDataProvider methods that would require an executed CachedRowSet will automagically execute it.
You can force execution by called CachedRowSet.execute().



CachedRowSet and Multiple Request Threads


Although multiple CachedRowSetDataProvider instances may use the same CachedRowSet (there's nothing to stop you)  care should be taken that those CachedRowSetDataProvider instances do not access the CachedRowSet at the same time.   Creator (and JSF) does nothing to help prevent simultaneous access.


The chances for problems are small, but they do exist.


Sample problem: Let's say each dataProvider calls setValue() on a different row.  The dp.setValue() essentially does two steps:  it moves the CachedRowSet cursor to the row and then calls setObject(). If the first thread yields after moving the cursor, the second dataProvider may sneak in and move the CachedRowSet cursor.  This means the first thread would then call setObject() on the wrong row.


Multiple button clicks before a response is returned can generate multiple requests and thus multiple threads.

Saturday Dec 10, 2005

CachedRowSet Logging in Creator 2

This applies to the production release of Creator 2.


To log our CachedRowSetX select/insert/update/insert/delete statements, set the "printStatements" property of the CachedRowSet.


(In EA2, you can see when the SELECT is executed and when acceptChanges() is called, but other logging isn't there or is incomplete.)



SELECT statement execution


When the RowSet is executed, you'll see the select statement and any parameters.


[#|2005-12-10T14:56:50.906-0800|INFO
Reader executing query ps=0 SELECT ALL TRAVEL.TRIPTYPE.TRIPTYPEID,
                    TRAVEL.TRIPTYPE.NAME,
                    TRAVEL.TRIPTYPE.DESCRIPTION
FROM TRAVEL.TRIPTYPE
WHERE TRAVEL.TRIPTYPE.TRIPTYPEID > ?
  Param[1]=(java.lang.Integer,5)|#]<BR>
[#|2005-12-10T14:56:50.968-0800|INFO
Reader executing finished|#]


Notes:


the "ps=0" is short for the pagesize=0


The "executing finished" logs when all rows have been retrieved from the database and cached into the CachedRowSet instance. Based on the timestamps, this query took .062 seconds to run.


The parameter logging is "(datatypeName,value.toString())" where the string representation of the value is everything between the "." and ")".


For null values, you'll see one of two formats: "(null, typeNumber)". Refer to java.sql.Types javadoc for typeNumber values. For example, 12 means VARCHAR.


For null values sometime you'll see something cryptic :like this:
Param[1]=([Ljava.lang.Object;,[Ljava.lang.Object;@ae324b)|#]



Method acceptChanges() Logging


First, we decided to start with something confusing.  The first thing you'll see logged is the text of an  INSERT statement


[#|2005-12-10T15:07:13.421-0800|INFO
INSERT INTO TRAVEL.TRIPTYPE (TRIPTYPEID, NAME, DESCRIPTION) VALUES (?, ?, ?)|#]


This means nothing more than the acceptChanges() was called.   It's not doing an INSERT, but if it does, this is the statement we'll use.


From here, we process each row in order. If no other output is found, it means none of the CachedRowSet rows were updated, deleted, or inserted.


If you don't see this, it means there aren't any columns in the rowset that are updatable.  If so, check your CachedRowSet's tableName parameter or other advanced properties.


Example if your tableName is completely bogus:

[#|2005-12-10T16:37:48.046-0800|WARNING|java.sql.SQLException: No columns in table: xxxTRIPTYPE
        at com.sun.sql.rowset.internal.CachedRowSetXWriter.initSQLStatements(CachedRowSetXWriter.java:1251)
        at com.sun.sql.rowset.internal.CachedRowSetXWriter.writeData(CachedRowSetXWriter.java:327)


Inserted Row Logging


We just log the INSERT execution with its parameters.  The actual INSERT statement is logged when the acceptChanges() begins.


[#|2005-12-10T15:28:52.656-0800|INFO
Writer: executing insert , params: Col[1]=(java.lang.Integer,69) Col[2](java.lang.String,Stuff) Col[3]=(null:12)|#]



Updated Row Logging


We log:



  • the SELECT to get the original row
  • the parameters for that select
  • the execution of that select
  • the UPDATE statement
  • the values for the columns to be updated. The parameters within the WHERE clause are logged three lines up
  • the update execution

Example of a successful update:


[#|2005-12-10T15:50:46.921-0800|INFO
SELECT TRIPTYPEID, NAME, DESCRIPTION FROM TRAVEL.TRIPTYPE WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION = ? |#]

[#|2005-12-10T15:50:46.921-0800|INFO Paramm[1]=(java.lang.Integer,6) Paramm[2]=(java.lang.String,CONF) Paramm[3]=(java.lang.String,Conference/Tradeshow)|#]

[#|2005-12-10T15:50:46.937-0800|INFO Writer: executing pre-update SELECT|#]

[#|2005-12-10T15:50:46.968-0800|INFO Writer: UPDATE TRAVEL.TRIPTYPE SET NAME = ? WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION = ? |#]

[#|2005-12-10T15:50:46.968-0800|INFO UpdateCol[1]=(java.lang.String,YoYo CONF)|#]

[#|2005-12-10T15:50:46.968-0800|INFO Writer: executing update() |#]



Deleted Row Logging


\\What's logged:



  • the SELECT for retrieving the original row, called the "pre-delete select"
  • the parameter(s) for that SELECT.
  • actual execution the pre-delete select.
  • the execution of the actual DELETE. The parameters for the DELETE were logged previously with the pre-delete SELECT

Example:


[#|2005-12-10T15:37:30.656-0800|INFO Writer: pre-delete select SELECT TRIPTYPEID, NAME, DESCRIPTION FROM TRAVEL.TRIPTYPE WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION IS NULL |#]


[#|2005-12-10T15:37:30.656-0800|INFO DeleteParam[1]=(java.lang.Integer,69) DeleteParam[2]=(java.lang.String,Stuff)|#]


[#|2005-12-10T15:37:30.671-0800|INFO Writer: executing pre-delete select|#]


[#|2005-12-10T15:37:30.781-0800|INFO Writer: executing delete DELETE FROM TRAVEL.TRIPTYPE WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION IS NULL |#]


If the original row no longer exists, you will not the the "executing delete" but rather the resulting exception:


Writer: executing pre-delete select|#]
[#|2005-12-10T15:46:08.875-0800|SEVERE| Error Description javax.sql.rowset.spi.SyncProviderException: Number of conflicts while synchronizing: 1
    at com.sun.sql.rowset.internal.CachedRowSetXWriter.writeData(CachedRowSetXWriter.java:512)

Wednesday Nov 16, 2005

Using JDBC in Creator

There's nothing special about using JDBC from within a Creator Web Application. - just get your connection and away you go.  You can google the net for all sorts of advice on using jdbc.  And there's always those book thingies.

Some notes:

Getting the connection from the app server's connection pool. 

For the bundled Application Server 8.1 PE, or whatever app server you plan to use, it's likely that you will be using database connection pools.  Getting a connection from the pool is usually done through a JNDI lookup.  For example, if you wish to use the creator data source "Travel", just do this:

            
import javax.sql.DataSource;
import java.sql.Connection;
    .....
        Connection conn = null ;
        // the following should be in a try-catch...


javax.naming.Context ctx = new javax.naming.InitialContext() ;
     DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Travel") ;
           
     conn = ds.getConnection() ;

Setup your connection

Because you are likely using your app/web server's connection pool, set your connection properties at the start. For example, to assure the connection behaves the way you desire, call setAutoCommit().

Cleanup your connection and JDBC objects when finished.

Commit or rollback your transaction.

Close all your JDBC objects. Close each Statement and ResultSet, then close the connection.  Put this code in a finally block to make sure it's executed.

Don't assume a close() on the connection will do all of the above.  If the connection is from a pool, the jdbc driver never sees the close().

Can I use the same Data Source for my CachedRowSet(s) and for my own JDBC stuff?

Yes.

Having Creator setup your Data Source in the bundled App Server

For test deploys to Creator's bundled App Server, Creator will configure any data sources used by your CachedRowSet properties.  If you need a Data Source that's not used by a CachedRowSet, you can tell crreator to handle it by going to the Projects Pane, select the "Data Source References" node, then right-click "Add a Data Source Reference".

Synchronizing a CachedRowSet with my manual JDBC changes

Suppose you have a Table component displaying data from a CachedRowSet.  You need to do some complex operation that updates/deletes/inserts data.  How do you need to synchronize the data in the CachedRowSet?  Answer:  that's up to you.  The easiest way is to just re-execute the CachedRowSet.  Otherwise you'll need to iterate through the CachedRowSet, or use a wrapping CachedRowSetDataProvider, to manually update the CachedRowSet.

When should I use CachedRowSets and when should I use JDBC?

That's up to you.  A CachedRowSet is good for retrieving data, and it's easy to use for simple updates/inserts/deletes of that data.  However, it doesn't do everything, so don't hesitate to use plain old JDBC if that's what is needed.

Sample

        Connection conn = null ;
        Statement sqlStatement = null ;
        ResultSet rs = null ;
        try {
            javax.naming.Context ctx = new javax.naming.InitialContext() ;
            DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/Travel") ;
           
            conn = ds.getConnection() ;
           
            // setup the connection
            conn.setAutoCommit(false) ;
           
            // execute the query
            sqlStatement = conn.createStatement() ;
            rs = sqlStatement.executeQuery("select count(\*) from TRIP" ) ;
            rs.next() ;
            int rows = rs.getInt(1) ;
            conn.commit() ;
           
            info("Rows in table TRIP: " + Integer.toString(rows)) ;
           
        } catch (Exception ex) {
            error("Error counting rows: " + ex.getMessage() );
            try {
                if ( conn != null ) {
                    conn.rollback() ;
                }
            } catch (SQLException sqle) {
                log("Error on rollback " + sqle.getMessage() );
            }
        }
        finally {
           
            // close the ResultSet
            if ( rs != null ) {
                try {
                    rs.close() ;
                } catch (Exception ex) {
                    log("Error Description", ex);
                }
            }
            // close the statement
            if ( sqlStatement != null ) {
                try {
                    sqlStatement.close() ;
                } catch (Exception ex) {
                    log("Error Description", ex);
                }
            }
           
            if ( conn != null ) {
                // cleanup and close the conneciton.
                try {
                    conn.close() ;    
                } catch (Exception ex) {
                    log("Error Closing connection ", ex);
                }
            }
        }

Tuesday Oct 11, 2005

Logging Database Access

Last Friday I received a bug that was very vague in it's description - "Creator pages seem slow".  My first guess was that it was an application issue, but there wasn't a way to prove it.  We need to see what sql is being executed and when.


So I went through our CachedRowSet implementation to beef up the logging.  Now I know my esteemed co-workers opinion is to don't log, debug.  I'll just say I disagree.  There are reasons to log at run time and "cross-boundary" tracking (such as shipping sql off to a database) is a common one.  With the FCS version, you'll be able to see



  • when a query is executed, including it's actual SELECT statement and any input parameters
  • what actually happens within acceptChanges().  You might  be surprised - a select will precede every update or delete of a row.

What it doesn't show is the actual time it takes to execute the statement.  To see this information, check the rowset property "printStatements".  Oh, and the output is rather ugly as I'm more substance over style.


Back to the bug.   Yuck.  I've just exposed a performance problem.  It turns out we were executing rowsets when we didn't need to, sometimes multiple times.  So if you think your web pages that access your database is slow in EA or EA2, it'll be better with the production release.


 


 


 

Monday Oct 03, 2005

Using Parameters in SQL Statements

 


When you wish to execute a select statement containing WHERE criteria that changes from request to request, there are two typical approaches:



  • use input parameters within the statement. For example, set your rowset's command is "select a,b,c from mytable where xyzzy = ?".  Each "?" represents a single value.
    Then set the input parameter in your code:

    rowset.set Object(1, qvalue ) ; // parameter numbers start at 1.

  • build the sql command at runtime


    String command = "select a, b, c from mytable" ; // stash this constant value somewher
    rowset.setCommand(
    command + " where xyzzy = " + qValue.toString() ) ;

In general, use parameters whenever possible. Creator always executes the rowset's command by using a java.sql.PreparedStatement.  PreparedStatements are compiled (prepared) by the JDBC driver or database and accept input parameters so they can be reused with different data. This reuse improves performance of your application.


However, for any specific situation, you may need to build your command at runtime. If you do, keep in mind that the rowset's command should be valid sql and have the same columns that you plan to have at runtime. This allows the creator design time to determine column names for binding. At runtime, you can set the rowset's command property to just about anything - just make sure any bound columns are included in the query.


Input parameters do have shortcomings. Common issues are::



  • runtime query performance. For example, the database's query optimizer may come up with a poor query plan when one uses "wumpus like ?" compared to "wumpus like 'big%'".
  • IN comparisons. Until runtime, you may not know how many values will exists - "xyzzy IN ( ?,?)" or "xyzzy IN (?, ?, ?, ?)"

Most of this isn't specific to Creator, as we use plain old JDBC to prepare and execute the statements.


Q & A


Where do I call setObject()?



Before the rowset is executed or re-executed.  Exactly where is up to you and your application.


What object type do I pass to setObject()?



To be safe, use the java object type corresponding to the database's type - here's where you need to know a little bit about JDBC. 


How can I share the SQL between the rowset's command property if I'm building the actual statement at runtime?



Often you want the SQL's column list -select a,b,c form mytable - to be in the rowset's command.  At runtime, you'll append the WHERE clause.     The rowset's command property is initially set in the bean's constructor (open the code folded section called "Creator-managed Component Initialization" to see it).  So later in the constructor, save a copy of the command into a property.  You'll also need to create that property.


 

Wednesday Sep 21, 2005

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.

Friday Sep 09, 2005

Why?

Why do this weblog?  There's no one particular reason.  I'd like to gather feedback on stuff we'd like to do or plan to do and explain why things are the way they are.

I've developed business applications in my past using various technologies.  I understand that no matter how cool the IDE may be, the only meaningful metric is for you, a Creator user, to deploy an application that the end user likes. And uses.  And uses with a minimum of complaining. :)

I work in the database area of Sun Java Studio Creator.  There seems to be lots of questions regarding how CachedRowSets work and how best to use them.  I hope to shed some light in this area.

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