| Subject | Re: [firebird-support] Aliasing? |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-04-21T23:58:26Z |
At 12:40 AM 22/04/2009, you wrote:
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.
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
>This stored procedure select works: SELECT * FROMAliasing 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.
>PRICE_FILE_S
>This one returns the following error: SELECT b.* FROM PRICE_FILE_S b
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.....because it is correct (and, in the Fb 2+ API, required) to be totally consistent with alias usage.
>
>If I join the procedure to any table then the aliasing works again.
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