Subject Re: [IB-Architect] Some IB questions.
Author Ann Harrison
At 03:25 AM 6/1/00 -0400, Claudio Valderrama C. wrote:
> Excuse me, but I need to divert from the great themes of this moment:
>1.- When a query is prepared and executed, what happens then? Does the
>server push the records to the client or does the client pull the records
>from the server? The issue has appeared many times and I'm left again with
>the doubt. This question almost always is connected with closing/unpreparing
>datasets in Delphi. If you add the BDE in the equation, things are more
>difficult to trace.

The client pulls.

>2.- Why does Interbase always put the NULLs at the tail regardless of the
>ORDER BY clause being ASC or DESC? Is this mandated in the SQL standard?

Yes. Though the standard may allow them to appear first, regardless of

>3.- Is the FOR/INTO statement atomic in a stored procedure? I mean if I do a
>and inside the loop there's a clause that updates X on the same table and
>another clause that adds records to the same table such that the new rows
>have X<100, too; then
>- Does the FOR pick the updated records that now have X<100 or miss the
>updated ones that now have X>100 (and before they had X<100) or added
>records that satisfy the WHERE clause?

The FOR is (to the best of my knowledge) not atomic. The standard, which
doesn't cover FOR but covers a lot of similar things would require that
this be a finite loop:

for select x.a, x.b, x.c
from t1 x
do begin
insert into t1 (a, b, c) values (x.a + 10, x.b + 10, x.c + 10);

In InterBase, that's an infinite loop - at least until it eats all your
disk, or runs across the file size limit and starts writing on the first
few pages of the database.

>- Is the result set of such construction calculated on advance, before the
>FOR begins to run or the next record is searched and retrieved only on each
>iteration of the FOR clause?

The latter.

>- If I invoke a selectable procedure from the client instead of having an
>executable procedure that uses the FOR results internally (without returning
>the rows to the client), is there any difference?


>4.- What's the trick behind the idea that R/O result sets are sent with as
>many records as possible inside a network packet whereas FOR UPDATE result
>sets are sent with one record per network packet? What have I gained using
>FOR UPDATE if live-recordsets in the server side are almost an illusion?
>Usually, these live recordset are produced in the client side thanks to

Charlie? Help!