Re: [gnome-db] gda_data_model_update_row syntax

[Paisa Seeluangsawat <paisa unt edu>]

> > We may want to add a 'GDA_CONNECTION_FEATURE_UPDATABLE_CURSOR' to the 
> > GdaConnectionFeature enum to allow the user to check for this before 
> > attempting to perform the update.
> > 
> I've just added this to HEAD. So, now we need to have providers use
> cursors, where possible, to read recordsets, instead of using a simple
> "SELECT ..." command.

I take back my words on 'GDA_CONNECTION_FEATURE_UPDATABLE_CURSOR'.  It
seems useful now :-).  I also agree that updatable cursor is a good
thing to use--if available.

Anyway, I can't find updatable cursor in mysql C api, and postgre
doesn't seem to support it either [1].  I'm new at this so please
correct me if I'm wrong.

Despite that, I think we can still implement update_row() by
generating a SQL command like,

  'update ... where column1=old_value1, column2=old_value2... limit 1'

which works in most cases where updatable_cursor is normally allowed
(i.e. select from only one table, no aggregates, etc.,.).  It works
even in the case that our 'where ...' matches more than one record
[2].

The design I sent to the list on 14 Oct [3] can accommodate both
updatable cursor and update command implementations, and I'd love to
see it implemented.  Though if someone could improve it or propose a
better design, that would also be great :-).

Paisa



[1] http://www.postgresql.org/docs/7.3/interactive/sql-declare.html

[2] If we have identical records, it doesn't matter which one we
    update.  If the underlying records are different.  For example,

      col_A   col_B    col_C
     ------------------------
        1	2	foo
	1	2	bar

    "SELECT col_A col_B FROM table;"

    The user knows only that col_A==1, and col_B==2 and doesn't know
    about col_C at all.  If he decide to update a record anyway.  So
    he apparently doesn't care whether the first or second record get
    updated.

[3] http://mail.gnome.org/archives/gnome-db-list/2003-October/msg00026.html