Re: [INTERFACES] ecpg SQL-return codes?

> > > how do I determine the result from an SQL statement in an ecpg
> > > source?
> > > when I did a simple test yesterday (trying to update a row in an
> > > empty table, which of course fails, since there is nothing to
> > > update in the table) reveiled sqlca.sqlcode to have a value of 0.
> > Well, it of course succeeded! :)
> Not quite of course, though...
> > SQL is set-oriented, and updating no matching rows is a valid
> > result.
> In interactive mode, that is correct. Still, after Hannu personally
> mailed me with a similar answer, I dug up the Oracle manual and looked
> at Server SQL reference topic "UPDATE (embedded)", where I found:
> If no rows satisfy the condition, no rows are updated and Oracle7
> returns an error message through the SQLCODE element of the SQLCA.

SQLCODE has only 0 and 100 defined in the SQL92 standard; other values
are implementation-specific with negative numbers indicating errors. My
Ingres installation was consistant with this definition, and I recall
that it set SQLCODE to zero on updates. My Ingres manual says that
SQLCODE is set to 100 on cursor updates when the row has been deleted,
but isn't specific about the result otherwise, so I can't verify my
(possibly defective) recollection.

However, there is a newer status area (for SQL92) called SQLSTATE which
_does_ allow non-zero return strings from a valid update. I don't know
much about this newer status area, but my reference book ("A Guide to
the SQL Standard", 4th ed., Date et al, 1997) discusses both data areas
in detail.

Michael, who developed the embedded SQL interface, is on vacation for a
little while longer; I'll bet he'll fix it up or explain it when he
returns...

- Tom

> Also, checking the online documentation for Adabas D (which in the
> precompiler is able to emulate ANSI SQL, DB/2, Oracle and its own SQL
> flavour to furnish most users needs), I found on the topic of UPDATE:
>
> [MODE= ANSI SQL]
> 12. If no row is found for which the conditions
> defined by the optional clauses are satisfied, the
> SQLSTATE 02000 - ROW NOT FOUND - is set.
>
> [MODE= DB2 SQL]
> 12. If no row is found for which the conditions
> defined by the optional clauses are satisfied, the
> message 100 - ROW NOT FOUND - is set.
>
> [MODE= Oracle SQL]
> 12. If no row is found for which the conditions
> defined by the optional clauses are satisfied, the
> message 100 - ROW NOT FOUND - is set.
>
> [MODE= Adabas SQL]
> 16. If no row is found for which the conditions
> defined by the optional clauses are satisfied, the
> message 100 - ROW NOT FOUND - is set.
>
> So, it seems like in embedded mode, quite a few databases see an
> update of 0 rows to be at least qualifying for warning 100. I think,
> pgsql should basically do the same... Don't you?