Re: Updateable recordsets

[Rodrigo Moya <rodrigo linuxave net>]

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?
> 
well, it's not a MUST, we can live without them, but if there is an easy
way to do it, we should try. Although if this means complicating too
much the current stuff, maybe we shouldn't try to do it.

As you say, one possibility is to leave all this work to the client lib,
for which we would need the provider  to specify the table to which each
field belongs. The caveats of this is that we risk to update rows we
didn't want to. And in most cases, it would be quite difficult to know
which exact row is the one to update.

In some providers, I think it would be quite easy to store the current
row being referenced. And we can extend the current cursor
implementation to allow also server-side cursors, in which case,
providers supporting SELECT FOR UPDATE can do so, but this will mean in
some cases lots of work in some providers. So we can leave it as an
optional thing to be implemented by the provider (as transactions for
example). .

I'm not hurry about having this in GDA, so if nobody shows a big
interest, we can leave it on the TODO list and concentrate now on other
things.