Thread: ecpg SQL-return codes?
Hi there,
how do I determine the result from an SQL statement in an ecpg source?
From using adabas/oracle I am somewhat used to using sqlca.sqlcode, but
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.
[Used a selfcompiled postgresql-6.3.2 on Debian/GNU Linux 2.0beta,
Linux kernel 2.1.108 SMP on a Dual PPro 200/256k 256M].
Any ideas?
Benedikt
ULTIMATUM, n. In diplomacy, a last demand before resorting to concessions.
[Ambrose Bierce, "The Devil's Dictionary"]
> how do I determine the result from an SQL statement in an ecpg source?
> >From using adabas/oracle I am somewhat used to using sqlca.sqlcode, but
> 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! :)
SQL is set-oriented, and updating no matching rows is a valid result.
Now, if you mis-type the update command so there is a syntax or naming
error, you will probably find an error return. Also, if you do a select
on a cursor, then loop through, you might find a return code of "100"
when there are no more rows to select. But updates on well-formed
queries are likely to succeed and return zero.
- Tom
> > how do I determine the result from an SQL statement in an ecpg source?
> > >From using adabas/oracle I am somewhat used to using sqlca.sqlcode, but
> > 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.
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?
Benedikt
ULTIMATUM, n. In diplomacy, a last demand before resorting to concessions.
[Ambrose Bierce, "The Devil's Dictionary"]
> > > 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?