On 18-11-2014 11:01, hugo.larson@... [Firebird-Java] wrote:

> I have tried an even more trivial example.
>
> Connectioncon = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
> con.setAutoCommit(false);
> CachedRowSet set = RowSetProvider.newFactory().createCachedRowSet();
> set.setCommand("select * from product order by 1");
> set.execute();
> set.absolute(5);
> set.updateString("NAME", "HEJ");
> set.updateRow();
> set.acceptChanges(con);
>
> The "order by 1" cause at acceptChanges:
> org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic
> SQL Error
> SQL error code = -104
> Token unknown - line 1, column 331
> WHERE

This specific problem is in the (default) implementation of rowset in
Java, see
http://grepcode.com/file/repository.grepcode.com/java/root/jdk/openjdk/8-b132/com/sun/rowset/internal/CachedRowSetWriter.java#CachedRowSetWriter.updateOriginalRow%28javax.sql.rowset.CachedRowSet%29

It appends a WHERE clause to the original query, generating:
SELECT ID, NAME FROM items order by 1 WHERE ID = ? AND NAME = ?

which of course is invalid.

The only way I know to work around this, is to implement your own
javax.sql.rowset.spi.SyncProvider and javax.sql.RowSetWriter

My first guess at a workaround was to change the query to something like:
select * from (select * from product order by 1) a

but this makes the cachedrowset generate:
UPDATE (select * from items order by 1) a SET NAME = ? WHERE ID = ? AND
NAME = ?

which of course doesn't work...

I haven't used the rowset specification and Reference Implementation
much. From the basic errors a lot of people run into with it (also on
other databases), I don't think it is used much.

Depending on your exact needs, you might want to consider looking at
jOOQ (for a simple example look at
http://www.jooq.org/doc/3.4/manual-single-page/#jooq-for-crud) or at JPA
(Hibernate, EclipseLink etc).

As I mentioned earlier, I think your original problem might be a
different one (although, looking at the naive approach used to generate
update queries, maybe not).

Mark
--
Mark Rotteveel