Subject Re: [IBO] TIB_DSQL and Active
Author Helen Borrie
At 02:58 AM 29/04/2006, you wrote:
>Thanks for all your advice.
>I will make the appropriate changes.
>I will track down the runtime browser.
>A couple of comments (and I am trying to be helpful not contentious).
>1. not withstanding your comment, I do get data back from the SQL
>select in the TIB_DSQL.

Yes; that's because the bare TIB_Statement provides a container (the
Fields array) for the output. So - for a simple, i.e. single-table -
SELECT that targets the primary key in the WHERE clause, it will
work. For an instrinsically singleton statement like SELECT
GEN_ID(MyGen, 1) from RDB$DATABASE it will work. Anything else and
you will get a "Multiple rows in singleton select" exception.
(Dealing with an exception as a way to check for existence is OK in
theory, but costly in practice....hence one of the purposes of
implementing the TIB_Cursor!!)

Hence the warning to use an IB_Cursor for SELECTing. It provides the
same "slimness" as TIB_DSQL for singleton selects but it also holds a
cursor on the result set. That makes it possible to do the
navigational checks which, if applied to the bare IB_DSQL, will
potentially cause exceptions instead of what you want to test.

>2. I re-visited both TIB_Statement and TIB_DSQL in the help file and
>saw no caveat about using SELECT.

That's possibly an oversight if it's a correct observation, or
possibly it just reflects the fact that TIB_DSQL *can* be used with a
SELECT statement in a very restricted sense. In practice, it is not
the recommended thing to do.

>It handles all of the necessary API calls to allocate, prepare and
>execute an InterBase dynamic SQL (DSQL) statement.
>Does that mean that SELECT is not a statement?

Far from it. TIB_Statement is the ancestor of all of the statement
classes. It has the "absolute basics" for any statement, i.e. it is

>This component is used for all InterBase DSQL statements that do not
>involve the handling of multiple rows of output data.
>Note the use of "all" in both items.

Umm... note the relative clause beginning at the word "that". The
SELECT statement, by design, returns a [potentially] multi-row
set. Study the API to see how the client defines, passes and
receives both input and output structures. TIB_Statement
(implemented as TIB_DSQL) receives one and only one xsqlda structure
per statement execution. This is fine when there is no possibility
that the server will produce anything other than either [nothing at
all] or exactly one "row" of output.

So, the next step up is to add behaviour to handle a set of > 1 row
and generalise a safe SELECT. The strategy then is for the client to
get a "handle" on the attributes of the set and fetch each row until
the server's buffer is empty. That's what TIB_Dataset (implemented
as TIB_Cursor) does with a SELECT statement. When the statement is
activated, TIB_Cursor sits waiting, with no rows fetched, for the
application to ask it to move through the set. Calling First moves
the cursor to the first row and fetches that row into the row
buffer. Calling Next moves it to the next row....and so on. FB/IB
cursors are unidirectional so it's not possible to "go backwards".

In order to move around a dataset requires a buffered dataset, which
takes us to the TIB_BDataset (implemented as TIB_Query). The "B"
stands for "bi-directional". When it is activated, it starts
fetching rows immediately, in a FOR loop. It keeps fetching rows
until the buffer is full. Once there is a buffer-load in the client,
the set becomes available for use by the application. IBO sets three
cursor handles on the set that is waiting on the server, allowing the
"windowing" of parts of the set that it implements in its
buffers. There is a lot of cleverness in the TIB_BDataset's
buffering, that is great when it is needed and costly when it's not.

>A couple of extra lines in the help file might prevent someone from
>following in my footsteps.

I'm sure you're right. :-) The text for the helpfile is actually
located in the class declarations of the sources as comments. We
build the helpfile using a wonderful tool called Time2Help that
magically constructs an internal model of the entire hierarchy and
extracts those comments as help text.

There is a LOT of additional material available which, because of the
vastness of the component help file, is distributed separately (free
of charge) from the IBO website as "Tech Info Sheets". They are just
as important a resource during your workup as the component help. In
fact, a lot of that material *used* to be in the main help file,
until the help files started to blow out at around 14 Mb!!

You can read summaries of each file and download them at