> Do you happen to be presenting this syntax via an app that uses IBO?

Yes, I'm using IBO via Database Workbench Pro 3.3.

> Aliasing isn't meant to be used for a multi-object query

Excuse my ignorance, but what is a multi-object query?

> IBO's parser adds RDB$DB_KEY to the output list automatically

So I assume I should test this using isql (which is what will run the
command in production anyway) instead of continuing with DBW.

> It is not supposed to matter

Should I file something with the tracker? If so, what would I report?

Besides the aliasing issue I ran in to when trying to test the stored
procedure, is the actual procedure, as posted, sound?
Thanks for responding.

________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Tuesday, April 21, 2009 7:58 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Aliasing?

At 12:40 AM 22/04/2009, you wrote:

>This stored procedure select works: SELECT * FROM
>PRICE_FILE_S
>This one returns the following error: SELECT b.* FROM PRICE_FILE_S b

Aliasing isn't meant to be used for a multi-object query. Previous to
V.2 it didn't matter a lot - it was silently ignored. However, just
because it was ignored, didn't mean it was a good idea to do it:
idiomatic SQL is never guaranteed to be future-proof.

The V.2 and beyond API carries more "cargo" with respect to aliases. For
layers that interface with the API, the adjustment was quite radical. If
you are using a tool or an interface that has made the required
adjustments to handle the Fb 2+ API, it is likely to find and choke on
your idiomatic mis-usages, even though you are using an old version of
the database engine.

>Dynamic SQL Error SQL error code = -607 Cannot SELECT RDB$DB_KEY from a

stored procedure.

>
>If I join the procedure to any table then the aliasing works again.

....because it is correct (and, in the Fb 2+ API, required) to be
totally consistent with alias usage.

Do you happen to be presenting this syntax via an app that uses IBO?
Either your own app code or a tool that uses IBO (IB_SQL, Database
Workbench, possibly others...)? When IBO detects an alias it assumes the
query is multi-object and looks for Keylinks. For multi-object queries
in a read-write transaction you have to set Keylinks manually. If you
don't, IBO's parser adds RDB$DB_KEY to the output list automatically, in
order to be able to identify each output row uniquely.

By using the alias on the single-object query, you are forcing your app
to send the "wrong message" for a SP (and also, I suppose, for derived
tables, though I haven't tested that myself). The client-side parser
doesn't stop you from sending the wrong message: it just does what it
thinks it's meant to do with the material it gets. It doesn't know about
your idiomatic mistake until the (pre-processed) statement is prepared.

The DSQL error you report (336003074) doesn't surface a corresponding
message in the Fb 1.5 client, so its appearance at least tells us that
you are using a Fb 2+ client to access your v.1.5 database. It is not
supposed to matter - we are supposed to be able to assume backward
compatibility - but perhaps, where you are working in an environment
with multiply mismatched layers, it does matter.

(Getting off-topic, but IBO does give you the option to pass raw
statements for your idiomatic cases...and, also, if you are using IBO
4.7.anything, you should dump it - it was a beta series that didn't
properly bridge the gap between IBO 4.6 and the Firebird 2 API.)

./heLen





Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.