Subject | Re: KeyLinks and Table Aliases |
---|---|
Author | Matt Nielsen |
Post date | 2003-06-06T15:12:39Z |
I tried both RDB$DB_KEY and just DB_KEY in the keylinks property and
both give me the same error: Invalid keylinks.
I need keylinks to accept table aliases. If you look at the
Performance monitor and the statement that it generates when you do a
InvalidateBookmark then you would see that it is trying to use the
physical table name for the where clause when it should be using the
alias name. the function CursorFields.GetByName in IBA_Dataset.IMP
needs to be updated to allow aliases.
Thanks,
Matt
both give me the same error: Invalid keylinks.
I need keylinks to accept table aliases. If you look at the
Performance monitor and the statement that it generates when you do a
InvalidateBookmark then you would see that it is trying to use the
physical table name for the where clause when it should be using the
alias name. the function CursorFields.GetByName in IBA_Dataset.IMP
needs to be updated to allow aliases.
Thanks,
Matt
--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> At 12:07 AM 6/06/2003 +0000, you wrote:
> >If I have a Query with the following
> >SELECT T1.ID, T2.ID
> >FROM TABLE1 T1
> >LEFT OUTER JOIN TABLE1 T2
> >ON (T1.ID = T2.PARENTID)
> >
> >The proper KeyLinks value is T1.ID not TABLE1.ID. However the
system
> >gives me an error that this is an invalid keylinks.
> >
> >What am I doing wrong? I can' imagine that noone out there is
doing
> >tree type joins and using aliases.
>
> Since this is not a naturally-updatable table anyway, it should be
possible
> to use the RDB$DB_KEY of the set as the Keylinks (the column name
is
> DB_KEY: every output set has it). Don't specify a table
identifier at
> all, since the DB_KEY of a joined set is a composite (constructed
by the
> server) of the primary keys of the underlying logical tables. (The
only
> "gotcha" that I can't predict is how the nulls from the outer join
will
> affect the DB_KEY...you would need to experiment with that...)
>
> You can't use the DB_KEY as the "automatic positioner" for a
searched
> update but it should be able to satisfy the unique-row-identifier
> requirement for KeyLinks, e.g. to locate/relocate the buffered row
in the
> current transaction context. DB_KEY is supposed to be unique -
there's
> just that little niggle in my head that *maybe* t1.id + a null
t2.id
> (derived via the outer join) generates identical db_keys...
>
> With a re-entrant join, you'd normally achieve updatability for
joined sets
> in your code, by reading unique input values from the output set
and
> passing them to stored procedure parameters in your XXXXSQL
properties.
>
> However, this statement, per se, doesn't include enough non-null
columns to
> establish the uniqueness of each node relationship, anyway
(parentID isn't
> in the output set and the outer join means you have nulls in the T2
ID) so
> it can't be made "live" under any conditions. I assume
updatability is not
> an issue for you in the current case.
>
> It's worth understanding that a DB_KEY keyvalue won't survive a
hard
> commit, so bookmarking it for relocation on refresh after a hard
commit
> won't be reliable.
>
> Helen