Subject RE: [IB-Architect] SQL Scripts
Author Marco Lauria
At 04.30 08/12/2001 -0400, you wrote:
> > -----Original Message-----
> > From: Marco Lauria [mailto:mslauria@...]
> > Sent: Viernes 7 de Diciembre de 2001 18:45
> >
> > Also I am thinking now about nested transactions, but
> > FB doesn't support them...
> > and it is another story.
>I only have seen nightmares as a result of nested transactions. What are
>they good for?

I can call from one SP multiple SPs that does different works.
The works are one independent from the other,
Every SP has its transaction context.
If one of the SPs called by the main one encounters an error it cal roll back
only his transaction, while the other can continue to work.
This sort of thing allows to really have SQL JOBS on the server....

> > I answer also to a message of Jim regarding this topic:
> > For the error handling I think it's not a problem,
> > use the absolute line from the beginning of the script can be the
> > right choice.
>Ok, this assumes the engine should hold the whole script as a big string to
>be able to calculate the position. Otherwise, the engine is psychic and
>knows where a script starts and finishes as opposed to individual
>interactive commands. A Russian developer gave me a SQL script that weights
>more than 1.2 MB... and it's only made up of DDL statements. I can't imagine
>why the engine has to load 1.2 MB in memory and start executing. Any tool
>you know sends one command at a time: isql, IBConsole, ib_sql, Marathon,
>etc. Will we create BEGIN SCRIPT and END SCRIPT markers in isql?

Claudio I repeat, what I am saying is that this sort of thing is more
powerful than
single execution.
I think I can strict it in this declaration:
Why can we use the entire stored procedure language in ISQL?
You are already able to compile multiple statements as you do this in
SP compilation,
so why can't we extend it to ISQL?

We can make a limitation to this such as 255 Lines....

But this can be a useful thing.

> > For the prepare....
> > can't we prepare the entire script?
>Naive. Every statement should be compiled to be prepared. The compilation of
>one statement has virtually nothing to do with the compilation of the next
>statement. It would be funny to know what a tool does when it receives an
>array of 400 statement handles in response to an script. Also, why would
>anyone put such burden in the engine?

As I told before, what I am telling is extending ISQL with the benefit of SP
such as variables declaration, cycles, conditionals....
and to do this we should be able to prepare and parse multiple statements.


P.S. you have not to return an array of handles.........
we can use BEGIN SCRIPT
and END SCRIPT....
or we can use a structure of this type
that returns after the first statement and returns the first handle,
then next_execution()
that executes subsequent...
but this can be a problem in case of variables...
I think that my first explanation of two days ago was wrong...
now it should be more clear.