--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 06:53 AM 11/03/2005 +0000, you wrote:
>
>
> >Helen,
> >
> >I don't know if it's a bug or not, but TIB_Script.Executing was
> >getting stuck on TRUE, and when I tried to use TIB_Script again I got
> >the error message that, after taking a look at your source code, now I
> >understand: "Executing already or not in Passive mode" - means exactly
> >what it means: "TIB_Script is already executing or it is not in
> >Passive mode"!
> >
> >Do You think it could be getting stuck because of my 'ugly' Commit?
> >
> >Here's a snippet of what solved my problem:
> >
> >procedure TDM.LimpaTabelas;
> >begin
> > if (SplashForm <> NIL) and SplashForm.Showing then begin
> > SplashForm.ShowMessage('Limpando tabelas...');
> > end;
> >
> > try
> > dscScript.SQL.Text := 'update Visitante ....'; /// a valid script
> >
> > SaveSQLToFile('LimpaTabelas', LoginForm.pUSERCODE, dscScript.SQL);
> > dscScript.Execute;
> >
> > /// -------------- I ADDED THESE LINES
> > while dscScript.Executing do begin
> > /// Will wait forever?
> > /// I have to do something here to avoid an endless loop
> > end;
> > /// -----------------------------------
> >
> > dscScript.IB_Transaction.Commit;
> > except
> > on E: Exception do LogError('DM.LimpaTabelas EXCEPTION:

'+E.Message);

> > end;
> >end;
>
> Actually, your problem could be actually one very simple thing.
> Instead of this:
>
> try
> dscScript.SQL.Text := 'update Visitante ....'; /// a valid script
>
> SaveSQLToFile('LimpaTabelas', LoginForm.pUSERCODE, dscScript.SQL);
> dscScript.Execute;
>
> Make it this:
>
> try
> with dscScript do
> begin
> SQL.Clear;
> SQL.Add (a command );
> SQL.Add(another command );
> SQL.Add(.................................);
> SQL.Add(#13#10); /// <----------------------------------- !!!!!
> SaveSQLToFile('LimpaTabelas', LoginForm.pUSERCODE, dscScript.SQL);
> dscScript.Execute;
> .....
>
> One thing - although I think IBO strips out statement terminators

when it

> passes each statement to ExecuteImmediate, I consider it a good

practice to

> include them (except on that last CRLF, of course!) The CRLF is an
> absolute requirement in ISQL scripts, the problem being that, if you

don't

> include it, the filesystem appends the EOF char to the last

statement and

> you will get an exception. Using the SQL.Add() method *should* take

care

> of string termination, whereas using the Stringlist.Text property

could be

> expected to give you improper termination. Hence your Execute may

simply be

> waiting interminably for a next statement that never comes.
>
> <preach on> I always treat the Text property of a stringlist as
> read-only: I never assign to it. TStrings are used everywhere in

IBO as a

> very convenient, predictable way to store sets of program data. A
> TIB_Script, by nature, is a component designed to process multiple
> statements in an indexed order. It defeats the purpose to assign a

"jumble

> of characters" to the Text property.
>
> You really should bomb-proof scripts. I really don't like the idea of
> using scripts for everyday DML, either, and especially not across a

slow wire.

> <preach off>
>

Helen,

You're telling me to change my whole life. :)

I've been using this .Text='string' approach for the last 5 years, but
I respect your respect for TStrings, and, after what you said, I'm
really thinking of changing.

If CRLF is absolute requirement, I'll never forget it again, I promise.

I'm no expert in database systems, I'm just a user, but one thing is
still confusing me: what's faster for a slow line, a 1000 commands
executed via a script or 1000 DML commands executed one by one?

I use scripts only when I know that a dynamic SQL, anytime, could send
a huge number of inserts. The example I sent was exactly the
exception: a kind of garbage collection that I dumbly execute every
day that's done with 'update'.

I have a question: is the script treated at client or server?

I think that you answered it when you said that "IBO strips out
statement terminators when it passes each statement to ExecuteImmediate".

If the script is treated at the client-side and passed one by one to
the server, what's the difference from a loop that executes each
command with a DSQL?

Thank you!