At 06:01 AM 26/01/2006, you wrote:

>--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> >
> > At 12:02 PM 25/01/2006, you wrote:
> > >w/IBODatabase, IBQuery and FB 1.0.0.794
> > >
> > >When I execute the complete script below in IB_SQL
> > >(as a new db with the required GENERATOR), it returns
> > >without error.
> > >
> > >When I run it through IBOQuery flattened out as a single
> > >string, it returns "Token Unknown" on the first COMMIT.
>
> snip
>
> > >
> > >I presume that there are some clues to running DSQL
> > >through an IBQuery (or other similar components)
> > >that I need to become aware of.
> >
> > Erm, yes. A statement object such as TIBOQuery is for a single
> > statement. If you want to run a script, use a TIB_Script component!
>
>I'll match your "Erm" with my "Ahh."
>
>The IB_Script worked like magic.
>
>Is it the concluding <;> semicolon which defines the
>"single statement?

The answer is: in scripts, Yes, in DSQL, No. A statement terminator
is not valid in a DSQL statement at all. If you put one into an IBO
statement object, it will swallow it silently for you in most cases,
but it's better to understand what statement terminators are used for.

The one place in SQL where the statement terminator *is* part of the
statement syntax is at the end of statements embedded inside a
CREATE/ALTER/RECREATE/CREATE OR ALTER PROCEDURE/TRIGGER
statement. These complex statements belong to the data definition
language (DDL) and, in the forthcoming Firebird 2, are available to
DSQL as the EXECUTE BLOCK syntax.

In isql (which is an application), you must also use terminators on
every DSQL statement. This is a requirement of the application, not
the database engine. The default terminator in isql is *also* the
semicolon. You must change the terminator used by isql to something
else IF you are using isql to define or alter SPs or triggers. The
isql command to do that is SET TERM.

A script is a batch of statements that you can execute in isql
sequentially. That's the reason for needing the terminator in isql -
for isql's parser to distinguish where one statement ends and the
next begins. If there is PSQL in the script (or in an interactive
isql session) then you have to change the end-of-statement terminator
so that isql can recognise that any succeeding semicolon-terminated
statements are part of a PSQL declaration.

IBO's TIB_Script doesn't run isql but it *can* understand isql's SET
TERM statements and behave accordingly. And, like isql, it uses the
terminators to parse out the individual DSQL statements.

Most of us writing scripts for execution in a TIB_Script simply use
the caret ^ as the default terminator. That way, we don't have to
care about alternating between terminators in scripts where we are
declaring PSQL.

>And as such, then, <COMMIT;> also equals a "single statement?"

Indeed. If you want to commit work inside a script, you use a COMMIT
statement.

You never use COMMIT in a statement object. IBO has methods for
Commit, Rollback (as well as CommitRetaining and RollbackRetaining)
that are called when you execute DSQL from an IBO application and you
are ready to commit work.

>Is a "statement object" then, anything which descends from
>IB_Statement?

Yup.

The TIB_ data access classes all descend from TIB_Statement. The
TIBO classes descend from Delphi's TDataset and have wrappers which
are TIB_Statement descendants. If you find members (properties,
methods, events) in the TIBO classes that are not in the Delphi
ancestors then you know to look in the relevant TIB_Statement
descendants for the help about them.

It's highly recommended that you understand Delphi's object model to
make the most of IBO. Use the help file - you can click the green
"Hierarchy" item in the help's menu bar to access the whole of the
hierarchy of a component. The helpfile also has a run-time graphical
viewer that's very useful for getting your head around the hierarchy.

Helen