Subject Re: [ib-support] FB: Cross db queries, temp tables etc
Author Helen Borrie
At 12:57 AM 28-08-02 +0000, you wrote:
>Couple of Qs:
>1) Does FB support cross-db queries?

Not SELECT queries. But it supports INSERT, UPDATE and DELETE queries
across database boundaries. The mechanism is to wrap connections to
multiple databases inside a single transaction, with two-phase commit.

>2) Does FB have anything similar to Sybase temp tables?

No. FB allows you to output the results of a cursor select directly as a
dataset. See the FOR SELECT....DO....SUSPEND construct and "Selectable
stored procedures".

>3) Is it possible to restrict execute permission on a stor proc to
>certain users/groups?

Yes. On Windows, users (defined at server level) and roles (defined at
database level). On Unix, the same, plus you can apply permissions to Unix
groups at server level. See CREATE ROLE and GRANT. You can (and need to)
also grant permissions to stored procedures as if they were database users.

>4) Does FB create SP's execution plan only once or every time SP is

Every time the SP is prepared. Note that Firebird and InterBase have an
unresolved bug that means it is necessary for your client to re-prepare a
SP that takes input parameters **each time** it is reiterated in a client
loop. (The bug does not affect calls made from within a procedure or
trigger). The cause is in an API macro that fails to reset the input
parameters in the XSQLDA parameter structure when returning to the client.

>5) JDBC: If PreparedStatement is executed in a loop, will it be
>re/compiled when it's prepared or every time it is executed?

You need to ask this question of the folk who are developing the JDBC
driver <> I recollect some
discussion of this issue on that list some time ago.