Subject Re: [IBO] TIB_DSQL and Active
Author Helen Borrie
At 01:12 AM 29/04/2006, you wrote:
> > It doesn't look like a correct usage of an Execute operation to me
> > (though you still didn't provide an example).
>I thought I did (in the snippet).
>I realize now you also would like to see the SQL.
>WHERE PHOTO_ID = :PHOTO_ID -- Key field

TIB_DSQL is NOT for SELECT statements. I don't know where you got
that idea from, certainly not in the help file.

> >
> > Seriously, if you are somehow passing a key so you can pull over a
> > blob associated with that key, you should use a TIB_Cursor and a
> > SELECT statement. Trying to pass blobs in SP arguments is fraught
> > with problems architecturally.
> >
>Then ,if I may, I would suggest some changes to the help file and FAQ
>to enforce that.

Hmm, I think you have a few "missumptions" to unlearn. But since
your mistake here is in using an object designed to EXECUTE DML
statements and specifically not to SELECT datasets, an explanation of
why passing blobs around inside stored procedures in variables isn't
pertinent. (It's not a IBO problem, btw, but has to do with how the
database works with blobs...)

>The way I understood my reading of those, if you only need a single
>row returning a TIB_DSQL is the leanest way to do that.

Nope. TIB_DSQL is for statements that begin INSERT, UPDATE, DELETE
or EXECUTE PROCEDURE. And the only kind of procedure that can return
values via a TIB_DSQL is an executable procedure that has return
arguments defined. You can't get *anything* back by calling an
executable procedure with SELECT.

>I believe Iread that a TIB_Cursor employs several TIB_DSQL
>components internally.

Well, no, it doesn't have to. However, it does if you make the query
RequestLive. But a select using an IB_Cursor that returns only 0 or
1 row is the leanest way to get that.

>I do not recall reading that a TIB_DSQL component should not be used
>for a single row select statement.

Read it now: A TIB_DSQL can't be used for a SELECT statement at all.

>The current code (as per snippet) is working fine; but I take it that
>you recommend changing to a TIB_Cursor anyway.

Yes indeedy.

>At least you know I try to do the research first<s>.

A useful piece of research would be to track through the hierarchy
from TIB_Statement forward, and see how the highways and byways take
you through the levels of complexity, from TIB_DSQL, which implements
the bare TIB_Statement, through TIB_Cursor, the low-calorie
unbuffered dataset which enables you to fetch one row at a time (call
First, Next, etc. on this, not Open) to TIB_Query, which is the
fully-fledged buffered dataset with bi-directional "moving windows"
on subsets of the full output.

If you haven't already, download the runtime browser for the
helpfile. There are some nice surprises in there for tracking
through the hierarchies.