Re: Updateable recordsets

[Brian Bruns <camber ais org>]

Having worked with a number of 4GL environments (Powerbuilder most
notably), I'd thought I'd throw my 2c in about how they handle updates
through a DataWindow/DataStore (think recordset).  PB requires that the 
field to be updated be marked updatable, the name of the table to update,
and the key to use for the update.  This is necessary because not all
databases can provide that info.  Of course, Powerbuilder by default only
allows one table to be updated from a result set, and then you have to do
some black magic to update the others.  We can do better than that ;)

Also, the allow you to specify whether to use UPDATE or a DELETE/INSERT to
do update, very important in certain situations.

So I would say allow the fields to be grouped under table names, and allow
the table to be assigned key fields to use in the update.  If a provider
can do this then let them prefill the fields, otherwise it is up to the
client to do the work.

The other aspect as someone mentioned was concurrency control.  Should the
update just be done without concern for a change that occurred from
another application, or should the row be reread with a holdlock and
compared the make sure everything is clean? And what happens if they don't
match? The client needs to be notified of the conflict and allowed to
indicate it to the user.  Most of the 4GL tools don't handle this all that
well but many of the add ons for them have some sort of concurrency
control, which basically consists of spitting back 'update failed, someone
changed the data, heres the new data, put your changes back in'.  Again I
think we could do alot better here.

Cheers,

Brian

On Sun, 30 Jul 2000, Reinhard Müller wrote:

> I have taken a look at the possibility to make
> recordsets updateable. Unfortunately, I have
> found some issues that really could get us
> in the way:
> 
> With client-side cursor (only possibility that
> is currently implemented) the gda-server does
> not know which is the current record and thus
> can't update/delete it. Probably, the update
> of recordsets would have to be implemented
> client-sided (,too).
> 
> Apart from that, we will run into problems
> when we have joins. Consider the statement
> 
> select name, country_id, text from customer,
> country where customer.country_id = country.id;
> 
> We can't find out if text is a field of the
> customer table or of the country table,
> except we analyze the database schema.
> 
> When field values are allowed to be changeable
> (which must be to make recordsets updateable),
> a change of the value of the field "country_id"
> would have to result in re-reading the country
> table to obtain the new value for country.text.
> 
> All that means very much that GDA will have
> to deal with how the tables of the database
> are related to each other.
> Of course that would be possible to do by
> analyzing the SQL select statements and
> the table schemes, but at a high effort.
> On the other hand, there will in every case
> sit an application on top of GDA that already
> knows about all these relationships.
> Exactly that application that generates the
> SQL select statement could with not very
> much additional effort create like SQL insert,
> delete and update statements.
> 
> Not that I want to get out of it :) but I really
> have to ask this question again:
> 
> Do we really want updateable recordsets in gda?
> 
> Thanks,
> --
> Reinhard Müller
> GNU Enterprise
> reinhard@gnue.org
> http://www.gnue.org
> 
> 
> _______________________________________________
> gnome-db-list mailing list
> gnome-db-list@gnome.org
> http://mail.gnome.org/mailman/listinfo/gnome-db-list
>