OK, I see the spec, but my concern that only forward only results sets are updatable is this:
I haven't seen this restriction in any other database. I haven't gone back and tried them all to verify this, but I have tried MySQL and Oracle to verify there is no such restriction. I don't believe updatability is very useful for forward only result sets. One would have to perform any update or delete on a row before fetching the next row. This doesn't allow one to present a set of rows to a user and then update the rows based on user input.
It seems to me this restriction was meant to make it easy to implement cursor insensitivity by allowing a copy to be made of the data. SQL99 has ASENSITIVE cursors that don't require the result set to be read only (and what changes are visible is implementation dependent). It might be more practical to equate JDBC's ResultSet.SCROLL_INSENSITIVE to SQL's ASENSITIVE cursors. SROLL_INSENSITIVE is defined as "<x-tad-bigger>scrollable but generally not sensitive to changes made by others.</x-tad-bigger>" This isn't a hard and fast won't see other changes. This definition would allow Derby to provide something very useful and not violate the SQL spec.
I'm also interested in how others feel about this. I would think that everyone using updatable resultsets will want them to be scrollable.
John
On Dec 20, 2004, at 3:59 PM, Mamta Satoor wrote:
Hi John,
My SQL standards is dated March 1999. Not sure what you have, but if you
don't have the same one, I think searching for updatable/insensitive might get
following hits in your doc too.
In section 4.29 Cursors -
A cursor is either updatable or not updatable. If the table identified by a curosr is
not updatable or if INSENSITIVE is specified for the cursor, then the cursors
is not updatable; otherwise, the cursor is updatable.
Also, later on, in the 14.1 <declare cursor> section, under syntax rule,
10) says If <updatablility clause> is not specified, then:
a)If either INSENSITIVE,SCROLL, or ORDER BY is specified, or if
QE is not a simply updatable table, then an <updatablility clause> of
READ ONLY is implicit.
Mamta
John Kline wrote:
Hi Mamta,
Could you help point me to where SQL standards state updatable
insensitive resultsets are not supported? In practice, I believe it is
supported. I just tried Oracle with no problems. I'll try the other
usual suspects when I have time.
- John
On Dec 20, 2004, at 2:00 PM, John Kline wrote:
Hi Mamta,
Keep in mind though that Derby at this point does not
support scrollable sensitive cursors. Derby has support for forward
only resultsets and scroll insensitive resultsets, both in read only
mode.
So Derby supports forward only and scroll insensitive resultsets and
you are working on only supporting CONCUR_UPDATABLE on forward only
result sets. Correct? I think we need to support both. In most
cases, one will want CONCUR_UPDATABLE for scrollable resultsets. Do
you know of anyone working on this? Does anyone if any work is taking
place in this area?
- John
On Dec 20, 2004, at 1:41 PM, Mamta Satoor wrote:
Hi John,
Yes, my patch on the list supports CONCUR_UPDATABLE
for forward only result sets.
I am not currently looking at working on scrollable updatable
resultsets. Keep in mind though that Derby at this point does not
support scrollable sensitive cursors. Derby has support for forward
only resultsets and scroll insensitive resultsets, both in read only
mode.
Mamta.
John Kline wrote:
Hi Mamta,
As a first step, you are going to support CONCUR_UPDATABLE for
forward
only result sets. Is that correct? But will you then continue your
work to support scrollable updatable result sets? If not, does your
solution just not apply to scrollable result sets? Please let me
know
as I'm going to need to figure out how to implement updatable
scrollable result sets if this isn't already being addressed.
Cheers,
John
On Nov 29, 2004, at 11:55 AM, Mamta Satoor wrote:
Just couple more comments to Satheesh's reply. I have a pending
patch
on the
list
to support delete using updatable resultset apis. I am working on
support for
update
using updatable resultset apis next.
Please keep in mind though that this support would be for forward
only
updatable
resultsets only. I noticed that you are trying to get scroll
sensitive
updatable
resultset.
Derby does not currently support scroll sensitive read only
resultsets
and hence
my
patch will not have support for scroll sensitive updatable
resultset.
Hope this helps,
Mamta
Satheesh Bandaram wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Derby currently doesn't support Updatable resultSets. I suspect the
JdbcRowSetImpl might be invoking the updatable resultSet API of
JDBC
specification. Hence you might be getting the feature not
implemented
error.
There are some pending patches to Derby that will get us closer to
Updatable resultsets. Derby might have complete updatable resultset
API
support, hopefully, soon after that.
Satheesh
Ias wrote:
The same thing happens when using embedded mode, but with a-----BEGIN PGP SIGNATURE-----
different error
message:
SQL Exception: Feature not implemented: no details.
Ias
-----Original Message-----
From: Ias [mailto:[EMAIL PROTECTED]
Sent: Friday, November 26, 2004 8:41 PM
To: 'Derby Development'
Subject: RE: Running Rowsets tests against Derby database
I met
com.ibm.db2.jcc.c.SqlException: Driver not capable
error from the following code
String driverName = "com.ibm.db2.jcc.DB2Driver";
Class.forName(driverName);
String dbUrl = "jdbc:derby:net://localhost:1527/test";
String username = "APP";
String password = "APP";
con = DriverManager.getConnection(dbUrl, username,
password);
Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.
CONCUR_UPDATAB
LE);
ResultSet res = stmt.executeQuery("select * from
message_log");
logRowset = new JdbcRowSetImpl(res);
logRowset.moveToInsertRow(); <- at this point
Im using Network mode with IBM DB2 Universal Driver (just
downloaded last week).
Is there any solution to this problem?
Thanks,
Ias
P.S. CacheRowset seems to work fine under the same circumstances.
-----Original Message-----
From: Ias [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 20, 2004 6:22 PM
To: 'Derby Development'; [EMAIL PROTECTED]
Subject: RE: Running Rowsets tests against Derby database
Hi all,
I have been following the Derby project on Apache for quite
sometime.
I have experimented using the Derby database with JDBC Rowsets
implementation which is part of JDK 5.0.
I am pleased to infrom that we have run all the RowSets
Implementation
(JSR 114) tests against the Derby database.We have a pass
percentage
of about *88%* which we feel is great number to begin with.
We hope to
make it better as we go ahead using Derby.
I also have used Derby (network-mode) for running examples in
JDBC
RowSet for JWSDP 1.5 and learned that it worked fine.
Now I'm thinking of using Derby with J2SE 5.0 JDBC RowSet
for my web
services gateway implementation.
Probably I'll test Derby with more (realistic) cases :-)
Thanks,
Ias
I am interested in the progress of Derby Project and
would like to
contribute actively going forward.
thanks,
Shreyas Kaushik
Sun Microsystems,Inc.
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBq3pwENVNIY6DZ7ERAtXlAJ99oagVMcaOXq6QjML82JJxG48jfQCcD2UQ
/I5/T/R7WcmV3A0+gMq6TGE=
=CLfA
-----END PGP SIGNATURE-----
