MySQLTalk.org

RAPPAZ Francois · Guest

I am looking for CachedRowSets and JoinRowSets which would support updates. Update of Sun implementation of JoinRowSet does not work with MySQL. I have tried OracleCachedRowSet and OracleJoinRowSet but it does not work either.

Can somebody on this list point me to a free/open source implementation of joinrowset (for mysql) ? are implementations of cached and join rowset planed in a some future ?

François Rappaz

Mark Matthews · Guest

RAPPAZ Francois · Guest

Mark,

Say you have 2 tables orders ans detailOrders, aving a 1->n relation, orders for the 1 side, detailOrders for n side. Field orderID makes the join.

I made two CachedRowSet detOrders and orders, one for each table, with the following sql command
<pre>
import com.sun.rowset.*;
...
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/OrderDB?user=...&password=...");
§ ...
CachedRowSet orders = new CachedRowSetImpl();
CachedRowSet detOrders = new CachedRowSetImpl();
....
detOrders.setCommand("SELECT * FROM detailOrders where orderID=13");
orders.setCommand("SELECT orderID, orderRef, orderDate, ClientID FROM orders");
</pre>
Next I have made a JoinRowSet jrs using the orderID field,
<pre>
jrs = new JoinRowSetImpl();
jrs.addRowSet(orders, "orderID");
jrs.addRowSet(detOrders, "orderID");
</pre>
and tried an update in the field orderRef in table orders.
On the line
<pre>
jrs.acceptChanges(conn);
</pre>
I got the SQLException from MySQL "MergedCol" missing.

I download code for CachedRowSet, JoinRowSet & co (dated 1.5 04/03/12)
from http://sun.calstatela.edu/~cysun/documentation/java/1.5.0-source/j2se/src/share/classes/com/sun/rowset
and found that the field MergedCol belongs to a table that did'nt exist. orders#detailOrders ...
Using JoinRowSetImpl from jdk1.6.0 gives the same error.

François