Do not forget that UPDATE can also cause exception that you should catch
(e.g. if the record is currently updated by other user but not committed yet),
or that the UPDATE may not update anything
(e.g. if the record is already inserted (thus Insert fails) but not committed,
so your transaction can't see it).

Your logic should contain both traping exceptions and checking whether
the record you updated really exists (i.e. whether it is visible to your transaction).
If you can, wrap everything into SP.


Ivan Prenosil
[ I am looking for a job: InterBase - Firebird - Delphi - C - fulltext db - and more ... ]
Ivan.Prenosil@...
http://www.volny.cz/iprenosil/interbase

----- Original Message -----
From: "gorepj" <peter@...>
Subject: [firebird-support] Better way to update table?


> I have an Import routine that updates a table. If a record already
> exists then it is updated otherwise it is appended. I impelement this
> by having a primary key constraint and program SQL statements in
> Delphi accordingly.
> TRY
> //Try to append record
> INSERT INTO MyTable (Myfield ...) VALUES (MyValue ...)
> EXCEPT
> //Primary key violated therefore update that record instead
> UPDATE MyTable SET MyField = MyValue WHERE KeyField = SearchKeyValue
> END;
>
> This works beautifully but if I don't commit every statement I lose
> cached updates / inserts when the exception occurs.