At 01:47 AM 04-03-02 +0100, you wrote:

>this is something I read in one of the sample programs.
>
>with TIB_Cursor.Create(nil) do
>try
> ...
> SQL.Strings = (
> 'Select'
> ' EMPLOYEE.EMPNO,'
> ' EMPLOYEE.LASTNAME,'
> ' EMPLOYEE.FIRSTNAME,'
> ' EMPLOYEE.PHONEEXT,'
> ' EMPLOYEE.HIREDATE,'
> ' EMPLOYEE.SALARY'
> 'From EMPLOYEE'
> 'FOR UPDATE')
>...
>finally
> Free;
>end;
>
>What is the meaning of the _for update_ clause ?
>
>Is this standard SQL ? (I cannot find it in the IB LangRef)...

In standard SQL, it's a clause that is used inside a DECLARE CURSOR
operation. It is for optionally naming the columns in the cursor selection
statement which will be updatable. In some other RDBMSs (e.g. MSSQL
Server, Oracle), declaring and opening a cursor and then FETCHING each
individual row is the only way to operate on a set inside a stored procedure.

IB stored proc language has the more powerful FOR SELECT...INTO <variables>
DO.... for operating on database cursors in SPs, also optionally giving us
SUSPEND to output the variables as a dataset.

You can't use a cursor in direct DSQL statements (the subset of SQL which
we use with Delphi), which is not a limitation. The DECLARE CURSOR syntax
isn't necessary with DSQL . It's used in embedded SQL programs (an
original feature of IB that provides a language subset to let you compile
direct database operations directly into C or Cobol programs without using
the API).

But, even there, the ESQL subset has an alternative syntax involving
SELECT...INTO <variables> which avoids the need to use FOR UPDATE [OF
<columnlist>]. I believe the FOR UPDATE concept offers database vendors a
standard surface beneath which to implement pessimistic row-level locking,
reflecting the limitations of the big name RDBMSs (Sybase, SQLServer,
Oracle) rather than any supposed "best mechanism". (The standards do not
make rules about implementation, they only say HOW to surface the
underlying mechanism in the SQL language...)

The reason for this redundancy? I think it is there just for standards
compliance. It was never necessary in InterBase to use a FOR UPDATE
mechanism to tell the engine that we want to lock a cursor, since the
multi-generational architecture gives us a much more sophisticated
mechanism which operates at row level and, for most requirements, can be
totally optimistic. In IB (except in CONSISTENCY isolation or table
RESERVING) a row does not become "locked" until you actually post an update.

I've never understood why some people in the IBO community (including
Jason, in some of his code) use FOR UPDATE. In DSQL it seems quite
redundant. As you have observed, it is virtually undocumented in the IB
manuals, simply because IB doesn't need it. You can find some trivial and
undeveloped examples in the Embedded SQL guide (alias Programmers' Guide,
if you have manuals for older IB versions).

I'm sure Jason can (and will) clarify his reasons for using it. I've never
considered using it in DSQL - I regard it as a "wisdom tooth" - I never had
those either, and I never missed them.
^^^^^^^^^^^^^^^^^^
About re-posting: please understand that Jason devotes Sundays to church
and family and does not read email. So please be considerate about your
demands on weekends...
^^^^^^^^^^^^^^^^^^

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com