Hi All,

This is a cross post from firebird-java.

This thread started because Sun's Java Studio Creator program would
not update tables using the JayBird JDBC driver.

The good news is that it works with the most recent compile of JayBird
that supports scrollable and updateable ResultSets.

After getting those capabilities (Thanks Roman!) the problem was in
the Sun code for Java Studio Creator.

It uses a class called RowSetDataModel that extends the Java Server
Faces class DataModel.

This class defaults to databases that support the non-standard "? IS
NULL" syntax in a Prepared Statement.

MySQL, Postgres, PointBase, and Oracle do support this. DB2 and
Firebird don't.

Fortunately the source code for this class is visible in the Creator
debugger.

The following code in RowSetDataModel has a hard coded test for DB2:

if (driverName != null && driverName.equals("DB2")) { // NOI18N
useConditionalWhereClause = false;
} else {
useConditionalWhereClause = true;
}

To make Firebird with the JayBird JDBC driver work with Creator you
can change this to:

if (driverName != null && (driverName.equals("DB2") ||
(driverName.equals("JayBird JCA/JDBC driver"))))

That is the driver name for JayBird returned in the metadata. This
could be fixed in a more elegant manner too.

When useConditionalWhereClause is false, the program looks up the
column names in the metadata and plugs them in PreparedStatement
rather than the "? IS NULL". I guess they did this for efficiency
because this way may force Creator to use multiple prepared statements
rather than just the one.

You can take this code with the modification above (in several places)
and create a new folder tree under "Java Sources" in the Creator
Project Navigator called com.sun.jsfcl.data. Then put your modified
copy of RowSetDataModel in the "data" folder. Then rename the class
and replace all occurances of "RowSetDataModel" with your new name,
RowSetDataModel2 for example. Then in all of the Java code in the
project replace all the references to RowSetDataModel with
RowSetDataModel2.

I tried extending RowSetDataModel and overriding the necessary
methods, but there were too many private declarations. If you are
willing to spend the time you may be able to sort that out and just
extend the methods that need to change.

The Creator folks really need to just put a boolean parameter in the
program that allows you to specify whether your DataSource supports "?
IS NULL" or not, rather than hard coding driver names. I'm going to
suggest that to them.

In the meantime you can use Creator's graphical layout tools to build
your app, just make the changes before you test each page of the web app.

They may be able to use the "IS [NOT] DISTINCT FROM" syntax but that
might break the other databases.

Thanks for the help!

Rick