Hi, Helen

Thanks for the great help. I'm really appreciated
it.

Best Regards,

William

--- Helen Borrie <helebor@...> wrote:

> 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
>
>
>

=====
William, Yu




__________________________________
Do you Yahoo!?
Yahoo! Mail - 250MB free storage. Do more. Manage less.
http://info.mail.yahoo.com/mail_250