At 12:43 PM 12/06/2003 +0200, Svein wrote:

> >what is "FOR UDPATE" sql statement that I usually see at the end of
> >some TIB_query.sql properties?
>
>"FOR UPDATE" is something Firebird requires when you want to update
>records. In IBO, you simply set RequestLive and then Jason takes care of
>the rest.

Not so. It's a keyword that tells the server to bring the rows of the
output across to the client one-by-one, rather than as a batch of some
specified size. It is meaningful in embedded sql, which takes the approach
of defining the output set on the client for delivery into a series of host
variables. So, in a SELECT .. FOR UPDATE query, when the statement is
EXECUTED, one row will come across and populate the variable, the app will
do something with the variables and then execute some other SQL statement
to perform DML.

It has no meaning in IBO, which controls the database cursors for you in
hidden methods. This isn't the case in ESQL apps, which are by nature not
object-oriented.

If you choose to use an IB_Cursor, you can simulate (sort of) this
one-by-one behaviour. If you use an IB_Query, you get the ability to
scroll back and forth through the whole set or a subset of output rows, et
al. This "scrolling" character of the output sets is due to client-side
magic in the data access object, since the database itself does not have
the ability to scroll output. Nothing comes close to IBO for the level of
wizardry in its scrolling cursors.

But the idea of fetching rows over to the client one-by-one, doing
something non-interactive with it and then pushing back the results before
moving on to the next row is characteristic of an embedded sql type of
application written in structured code, not of a client/server application
dependent on an event model and encapsulated data access. A good
client/server app doesn't do any non-interactive processing on the client
side at all. Over in the server, we have very powerful capabilities in
PSQL to operate on cursors, including embedding cursors within cursors. So
- we write stored procedures to do all this grunt stuff, and we pass a DML
statement and a set of parameters across the wire, not great lumps of
client-processed data.

As Jason commented a few hours ago, IBO doesn't complain if you throw a
SELECT...FOR UPDATE statement at it, but it doesn't do anything useful for
your application and it might indeed interfere with the ebb and flow
between the client buffers and the rows waiting on the server.

If you notice it FOR UPDATE appearing in the SQL Monitor output, it means
that Jason's code in the dataset classes is working on a unidirectional set
in some way that is encapsulated in a dataset's internal methods. By the
time your client gets hold of the dataset, the dataset object already knows
how to behave.

I can understand IBO newbies getting confused when seeing FOR UPDATE in
examples offered by well-intentioned list members. Some folk seem to go
from year to year being convinced that FOR UPDATE does something useful and
has some invisible positive effect on what happens to DML statements. It
doesn't. Just ignore it.

Next round: FOR UPDATE WITH LOCK in Firebird 1.5. {{{{shudder}}}}

Helen