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.



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)

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.

About

jfbrown

Search

Categories
Archives
« July 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
31
  
       
Today
News
Blogroll

No bookmarks in folder