Derby and updatable scrollable ResultSets

[John Kline <Jo...@Sun.COM>]

Hi Mamta and others,

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 
"scrollable but generally not sensitive to changes made by others."  
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
>>>>>>>> different error
>>>>>>>> message:
>>>>>>>>
>>>>>>>> SQL Exception: Feature not implemented: no details.
>>>>>>>>
>>>>>>>> Ias
>>>>>>>>
>>>>>>>>> -----Original Message-----
>>>>>>>>> From: Ias [mailto:iasandcb@hotmail.com]
>>>>>>>>> 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:iasandcb@hotmail.com]
>>>>>>>>>> Sent: Saturday, November 20, 2004 6:22 PM
>>>>>>>>>> To: 'Derby Development'; Shreyas.Kaushik@Sun.COM
>>>>>>>>>> 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.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>> -----BEGIN PGP SIGNATURE-----
>>>>>>> Version: GnuPG v1.2.5 (MingW32)
>>>>>>> Comment: Using GnuPG with Thunderbird - 
>>>>>>> http://enigmail.mozdev.org
>>>>>>>
>>>>>>> iD8DBQFBq3pwENVNIY6DZ7ERAtXlAJ99oagVMcaOXq6QjML82JJxG48jfQCcD2UQ
>>>>>>> /I5/T/R7WcmV3A0+gMq6TGE=
>>>>>>> =CLfA
>>>>>>> -----END PGP SIGNATURE-----
>>>>>>
>>>>
>>>
>