Hi All,

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

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 that extends the Java Server Faces class DataModel
called RowSetDataModel.

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

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

Fortunately the source code for this class is visible in the 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 JayBird 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 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.


Rick


--- In Firebird-Java@yahoogroups.com, "Roman Rokytskyy"
<rrokytskyy@a...> wrote:

> Rick,
>
> > Try asking in Firebird-support, maybe they have an idea how to

deal with

> > such SQL.
>
> Can you check where the SQL statement is generated? Maybe we have a
> possibility to override this place to use "PKEY IS NOT DISTINCT FROM ?"
> clause in case of Firebird 2.0?
>
> Roman