At 05:16 PM 21/01/2005 +0000, you wrote:

>Hi, All
>
>1) I have following SQL code in one of my stored procedure:
>
> update DIVISION set
> LOCKED = :PostBy
> where ID = :DivisionID and LOCKED is null;

If this request succeeds, your record is now locked. If it fails, you will
get a lock exception. If you don't have a handler for the exception,
execution will jump immediately to the last END in the entire procedure and
return the exception to the client. The record will remain locked until
the client either rolls back the transaction or commits it (but commit will
continue to fail if you don't do something on the client to change the
conditions causing the exception).

> if (ROW_COUNT <= 0) then
> begin
> Result = -3; /* Failed to get invoice number. */
> exit;
> end

The above block is fairly useless. It would be better to create an
exception and raise it in your code, in order to stop execution and return
something useful to the client application, e.g., you have previously
created one with "CREATE EXCEPTION DIVISION_NO_MATCH 'The supplied division
does not exist'".

Then use it:

if (ROW_COUNT <= 0) then
exception DIVISION_NO_MATCH;

Execution will now pass to the line immediately following the END statement
of the BEGIN...END block where the exception occurred. You can write your
handler here (a WHEN ... DO block) or after another END
statement. Execution just continues from the inner blocks toward the outer
blocks until the engine can find a handler for that exception.

If you don't provide a handler, it goes to the last END statement and
passes the exception back to the client. In this case, you want this to
happen - you want execution to stop and control to return to the client if
the division is invalid.

> LastInvoiceNo = null;
> select LASTINVOICENO from DIVISION
> where ID = :DivisionID and LOCKED = :PostBy
> into :LastInvoiceNo;
>
> LastInvoiceNo = LastInvoiceNo + 1;

This is risky!! Use some atomic method to create your invoice numbers,
e.g. a generator, and make sure that LastInvoiceNumber has a UNIQUE constraint.

> /******** UNLOCK Division and UPDATE its LastInvoiceNo ********/
> update DIVISION set
> LASTINVOICENO = :LastInvoiceNo,
> LOCKED = null
> where ID = :DivisionID and LOCKED = :PostBy;



>Q: Could I use SELECT UPDATE LOCK syntax to replace the above code? if
>so how to do it and how to do unlocking?

You don't have to unlock anything, and this applies even if you don't use
SELECT FOR UPDATE ... WITH LOCK. But this syntax applies to a preceding
SELECT statement back at the client, inside the same transaction, not to an
UPDATE operation in your SP. Forget it for this purpose.

You don't need to keep switching your LOCKED value back and forth between
"a value" and NULL, either. Simply performing *any* dummy update on the
record will lock it.

>2)Q: How to use "WHEN SQLCODE xxxx DO BEGIN ... END", Do I have to put
>each WHEN after update/insert/delete statement, or just one WHEN
>statement at the end of the procedure(more simailiar with the Delphi
>try ... except ... end;)?

Note what I said earlier about the way the engine seeks outward from the
current block to find a handler. Eventually, if no handler is found, it
will simply return the exception.

Note that, from 1.5-onwards, you can use a handler to "do something" and
then re-raise it by calling EXCEPTION afterwards.

>3)Q: Where can I get all the SQLCODE value, e.g. what's the value for
>Deadlock sqlcode, any doc I can take a look at?

http://www.firebirdsql.org/index.php?op=doc&id=userdoc

./heLen