Re: OCI, how to detect a no_data_found with an update

"José Delfosse" <jdelfosse_at_free.fr> wrote in message news:40c77e7f$0$13821$626a14ce_at_news.free.fr...
> Hi,
>
> I have a C++ application developed on IBM AIX 4.3.3 with Oracle 8.1.7.4
that
> use OCI to access the database.
>
> I have a loop on an update-statement that update different rows in a
table.
>
> During the loop, it might happen that a row is not to be updated because
it
> does not exist.
>
> After each OCIStmtExecute(), I call the OCIAttrGet() APÏ to retrieve the
> OCI_ATTR_ROW_COUNT attribute in order to know
> how many rows were updated (I expected one or zero in my case). My goal is
> to detect which rows were not updated (because they don't exist).
>
> While it returns 0 before the first OCIStmtExecute() , I found out that
this
> attribute thereafter always return 1 even if the update found no rows.
>
> Moreover, OCIStmtExecute() returns OCI_SUCCESS even when the update found
no
> rows.
>
> In fact, the OCI_ATTR_ROW_COUNT seems to work fine only with
> fetch-statement.
>
> So, using OCI, does anyone know how to detect that an update statement
> found no rows ?

If an update doesn't succeed then you, or more appropriately, the server should replace it with a Insert.. or at least you should log the error. AFAIK the server doesn't keep any type of log of failed UPDATE requests. To have server switch to INSERT, put something like this in a Trigger:

CREATE TRIGGER ioi_XYZ ON [dbo].[XYZ]
INSTEAD OF INSERT
AS
BEGIN
 SET NOCOUNT ON

• Check for duplicate Computername IF ( NOT EXISTS ( SELECT xy.ID FROM XYZ xy, Inserted i WHERE xy.ID = i.ID ) ) INSERT INTO XYZ ( ID, AA, .....) SELECT ID, AA, .... FROM Inserted ELSE -- If duplicate, change to UPDATE UPDATE XYZ SET AA=i.AA FROM XYZ xy, Inserted i WHERE xy.ID = i.ID END
  • Sten