Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: pass (hermes.apache.org: local policy)
Date: Mon, 20 Dec 2004 18:32:43 -0800
From: John Kline <John.Kline@Sun.COM>
Subject: Derby and updatable scrollable ResultSets
In-reply-to: <41C7675E.DB15C2F9@Remulak.Net>
To: Derby Development <derby-dev@db.apache.org>
Message-id: <978CB698-52F8-11D9-ACCE-000A95928944@sun.com>
MIME-version: 1.0
Content-type: multipart/alternative;
 boundary="Boundary_(ID_r22ZRNXNuOeR0iXisoYj5w)"
References: <BAY12-DAV17D2530A47A7D0E5933A0CDEBA0@phx.gbl>
 <41AB7A71.5090806@Sourcery.Org> <41AB7E9E.2E65D698@Remulak.Net>
 <19A263B7-52CF-11D9-ACCE-000A95928944@sun.com>
 <41C74726.A6AD9B0F@Remulak.Net>
 <84A83E9C-52D2-11D9-ACCE-000A95928944@sun.com>
 <259F8E5A-52D7-11D9-ACCE-000A95928944@sun.com>
 <41C7675E.DB15C2F9@Remulak.Net>


--Boundary_(ID_r22ZRNXNuOeR0iXisoYj5w)
Content-type: text/plain; format=flowed; charset=US-ASCII
Content-transfer-encoding: 7BIT

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-----
>>>>>>
>>>>
>>>
>

--Boundary_(ID_r22ZRNXNuOeR0iXisoYj5w)
Content-type: text/enriched; charset=US-ASCII
Content-transfer-encoding: 7BIT

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
"<fontfamily><param>Times</param><x-tad-bigger>scrollable but
generally not sensitive to changes made by
others.</x-tad-bigger></fontfamily>"  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:


<excerpt>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:


<excerpt>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:


<excerpt>Hi Mamta,


<excerpt>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.

</excerpt>

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:


<excerpt>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:


<excerpt>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:


<excerpt>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:


<excerpt>-----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:


<excerpt>The same thing happens when using embedded mode, but with a

different error

message:


SQL Exception: Feature not implemented: no details.


Ias


<excerpt>-----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.


<excerpt>-----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


<excerpt>Hi all,


I have been following the Derby project on Apache for quite

</excerpt>

sometime.


<excerpt>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

</excerpt>

Implementation


<excerpt>(JSR 114) tests against the Derby database.We have a pass

</excerpt>

percentage


<excerpt>of about *88%* which we feel is great number to begin with.

</excerpt>

We hope to


<excerpt>make it better as we go ahead using Derby.

</excerpt>

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

</excerpt>

for my web


<excerpt>services gateway implementation.

Probably I'll test Derby with more (realistic) cases :-)


Thanks,


Ias


<excerpt>I am interested in the progress of Derby Project and

</excerpt></excerpt>

would like to


<excerpt><excerpt>contribute actively going forward.


thanks,


Shreyas Kaushik

Sun Microsystems,Inc.


</excerpt></excerpt></excerpt>


</excerpt>-----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-----

</excerpt>

</excerpt></excerpt>

</excerpt>

</excerpt></excerpt>

</excerpt>

--Boundary_(ID_r22ZRNXNuOeR0iXisoYj5w)--