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

[Dmitry Y. <fir...@ya...>]

Jason,

> 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.

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.

> 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.

> 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.


Dmitry