Re: [Firebird-devel] Updatable cursors are in need of some touchup

[Jason L. W. <jwh...@jw...>]

Dmitry,

> > SELECT sh.ORDERID
> >      , sh.DDATE
> >      , sh.QTY
> >      , sh.CU_ID
> > FROM SALES_HREF sh
> > FOR UPDATE OF sh.QTY
> >
> > Now, for the first problem.  I cannot use the sh.QTY in the FOR UPDATE
> list.
> > I can't even use the fully qualified name of the table as a prefix.
> > I have to actually use the field name without any relation name or alias
> > at all.
> 
> Right, this is accordingly to the SQL specification. What you need could
> be done in ten minutes, but we already have a few places in the parser
> marked with "IBO hack" (table aliases in INSERT and UPDATE, for example)
> :-) And the intention was to restore the standard syntax as soon as IBO
> is fixed. So, I really doubt that we need more such "hacks" now.

I believe table alias prefixes should be made use of uniformly throughout.

> BTW, I'm wondering why do you need this stuff provided that the
> for-update column list is ignored by the engine? FOR UPDATE OF <smth> is
> an absolute equivalent of just FOR UPDATE, in both cases all columns are
> updatable.

I tried to explain.  It has to do with legacy VERY THIN and VERY DUMB client
applications that just want the server to figure out how to do the updates.

The way this was programmed was working excellently as needed.  It is now
broken.

When you are performing updates WHERE CURRENT OF <cursorname> you are
actually using the cursor on the server and there are no other conditions in
the WHERE clause.  The server uses the context of the records on the cursor
to figure out how to perform the updates.  The FOR UPDATE OF <list of
columns> makes it such that the cursor on the server will only allow certain
columns to be updated.  I suppose this is less important and that it could
be deprecated because it can be worked around.

> > The next problem has to do with the update failing due to a conflict of
> > relation alias names between the live cursor and the update statement
> > against the live cursor.
> >
> > SELECT sh.ORDERID
> >      , sh.DDATE
> >      , sh.QTY
> >      , sh.CU_ID
> > FROM SALES_HREF sh
> > FOR UPDATE OF QTY
> >
> > UPDATE SALES_HREF SH
> >   SET QTY = ?
> > WHERE CURRENT OF C12357620765374753
> >
> > alias SH conflicts with an alias in the same statement
> 
> Yes, this looks like a bug. It will be investigated.

While you are looking at the code, please investigate it for the opportunity
to make use of the table alias in order to clarify rather than conflict.

> > case of a join, which should be made to work like it used to, this would
> > be ideal to make sure the exact column is updated that should be.
> > Especially in the case of a self-referencing table that is joined with
> > itself.  My testing shows that updatable joins are no longer supported
> > and I'll send a sample of that if anyone so desires.
> 
> This is an intentional change, but it will be re-enabled as soon as all
> proper checks are in place. The old code allowed too many things that
> shouldn't work at all.

I'm looking forward to these things being re-enabled.  Positional updates
(as opposed to searched updates) through the cursor on the server should be
relatively easy to re-enable because they worked before.  I'm not aware of
how much revision has taken place in the server-side cursor management but
I'd be glad to take a peek at the sources to see if I can spot some things.


> Dmitry


Thanks,
Jason L Wharton