Re: updateable recordsets

["David T. Bath" <bathd edipost auspost com au>]

Vivien Malerba wrote:
> If the provider, for an update/remove operation cannot alter/remove only one
> row, then it will issue an error.
> 
> Another thing to mention is that for select queries with several tables, one
> must tell which table he wants updated.

I am prompted to recall the syntax (in Oracle) of a cursor declaration
if you want to perform updates through it.

   cursor my_cursor_name [(
       {{argument argtype} [,...]}
   )] as select column [,...] from .....
   for update of tablename;

Updates via the cursor are then issued as
   update tablename
   set col=value [,...]
   where current of my_cursor_name;

I believe it entirely reasonable that the gda "cursor" is declared
as being "for update" of a particular table when it is opened.
Similarly, we the frontend will need to tell the provider (which
tells the engine) if and when a lock on a particular row is to
be requested.

There are some traps for the unwary that the gda provider will need
to give to the gda metadata that tools like Access cannot deal with,
but that PowerBuilder nicely allows: i.e. only issuing updates for
columns that have actually changed.  (TYpically PowerBuilder generates
UPDATE statements on the fly rather than updating through a cursor,
and this is the way gda will have to work, especially when displaying
a grid, not just updating one row at a time).

This is important, because the high-end databases may allow the user
to update ONE column, but not another.

(Actually, the metadata tables PowerBuilder can create are a good
"source of inspiration", as is the Oracle CASE Dictionary).

Using MS-Access as a backend to Oracle, I find I am unable to use
column-level security.  I am thus forced to do checking in post-update
triggers as follows: (imperfect partial solution to the problem)
	create trigger BU__MYTBL
	before update on MYTBL
	for each row
	begin
		:new.ID = :old.ID ;
		:new.DT_INS = :old.DT_INS ;
		:new.USR_INS = :old.USR_INS ;
		:new.DT_UPD = :old.DT_UPD ;
		:new.USR_UPD = :old.USR_UPD ;
		-- tidy up other stuff
		-- trim strings, etc
		if (
		   -- Check values to see
		   -- if they have REALLY
		   -- changed after tidying
		   :new.NM <> :old.NM
		or :new.DT_BIRTH <> :old.DT_BIRTH
		) then
		   -- A real change - do the audit
		   :new.DT_UPD := sysdate ;
		   :new.USR_UPD := user ;
		end if ;
	end;

Now, (and please excuse the non-gda terms) if the gda "cursor" has
supplementary data on current user column permissions, supplied by
the provider, the cursor could have a flag set by the programmer to
act differently if the user is doing something against the backend
column security rules.  (And I know that role/user stuff will be
tricky to scan, but neverthelesss, assume it is doable).
   a) RowUpdErrMsgAction
	a1) Display warning/error message to user/log facility
	a2) Be silent
   b) RowUpdErrUpdAction
	b1) Attempt update of record anyway
		(DBA might have altered permissions AFTER cursor
		opened and permissions parsed - this is another
		can of worms - when is metadata collected?
		can it be refreshed? etc.  What about the record
		itself?  How will it behave during update if it
		becomes "stale"?: A retrieves, B retrieves,
		B updates, A updates.
		)
	b2) Skip update of this row only - others in transaction 
	    still attempted
	b3) Skip all transactions, similar in concept to a rollback.
   c) Add your own flags here ?????

The more I think about it, the more I think that having tables in a
database to hold metadata is a good idea.  This can be either in the
database you are connecting to anyway, or somewhere else.  It could
be global or overriden by the user.  It could even be in flat or dbm
files held by GConf.

A repository of info for columns that described help, default format
(in printf-like format), long heading (say 32 characters), short heading
(say 16), help/tips text that is accessed in parallel by the gda toolset
are items that would be extremely helpful to rapid development of apps.

"Simply" point to a different GConf repository (or order a different
locale), refresh the metadata repository associated with the cursor,
flush these to the widgets, and you should be reconfigured without
even having to close the cursor you were playing with.

I know this is all non-trivial.  Far from it.  But the underlying
gda datastructures are extremely flexible, and I'd hate to see the
update side take shortcuts like some vendors.  I'm sorry I have no
time to actually work on the code, and hope my comments are useful.

Regards to you all

-- 
David T. Bath bathd@edipost.auspost.com.au
+613 9204 8713 (W) 0418 316 634 (Mbl)