Joel Brown's Weblog

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!).

Posted at 08:24AM Jan 26, 2006 by jfbrown in Creator  |  Comments[3]

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.

Posted at 07:32AM Jan 26, 2006 by jfbrown in Creator  |  Comments[1]

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.

Posted at 03:17PM Jan 25, 2006 by jfbrown in Database Stuff  |