We're converting an Informix application to Postgresql. We relied on
the sqlca structure to report the number of rows changed and errors,
but the structure seems to be always set to 0. Here's some actual code
(Translated literally from the Informix code, no attempt to optimize,
so if it looks especially dumb, that's my excuse :-) Am I missing
something obvious?

EXEC SQL execute immediate :stmt;
/* stmt is an update statement, presumed to be OK since the
update actually happens */

if(error = sqlerror()) goto sqlcleanup

EXEC SQL commit;

/* this never shows any error */
if(error = sqlerror()) goto sqlcleanup

/* this always shows zero rows */
if(error = sqlnorows()) goto sqlcleanup


/* The error checking and reporting */
int sqlerror()
{
static char msg[MSG_LEN];

EXEC SQL begin declare section;
int exception_count;
int i;
char sqlstate[6];
char message_text_val[255];
EXEC SQL end declare section;


last_sqlcode = sqlca.sqlcode;

if(sqlca.sqlcode)
{
sprintf(msg, "SQLCODE = %ld", last_sqlcode);
LOGVIDERR(5, msg);

sprintf(msg, "sqlcode %ld, sqlstate %5.5s: %s", sqlca.sqlcode,
sqlca.sqlstate, sqlca.sqlerrm.sqlerrmc);
LOGVIDERR(5, msg);

return 1;
}
return 0;
}

/* the rows affected counter */
int sqlnorows()
{
int row_count = sqlca.sqlerrd[2];

if(row_count == 0)
{
/* following three statements are added for debug.
Log shows all zeros. */
char tmp[200];
sprintf(tmp, "ssqlerrd: '%s' %ld, %ld, %ld, %ld, %ld, %ld",

sqlca.sqlerrm.sqlerrmc,sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],
sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
LOGVIDERR(5, tmp);
/* end of debug code */
LOGVIDERR(5, "No rows effected.");
return 1;
}
return 0;
}


--
Al Balmer
Sun City, AZ

Al Balmer <albalmer (AT) att (DOT) net> wrote:
I tried your code, and I think it behaves as expected.
I'll add comments to the code where appropriate.

You should insert the call to sqlnorows() here if you want to get the
rowcount from the UPDATE statement.

It is hardly surprising that no error is reported, as the COMMIT
statement executes correctly according to your comment above.

This is also as expected, as COMMIT affects zero rows.

Summary: it seems that in Informix a COMMIT statement will not set the
number of rows affected in the sqlca, else your code would not work there.

Yours,
Laurenz Albe

On 07 Nov 2006 09:19:32 GMT, Laurenz Albe <invite (AT) spam (DOT) to.invalid>
wrote:

That's it! That's the point we were missing. If you ever get near
Phoenix, Arizona, USA, I'll buy lunch <g>.

--
Al Balmer
Sun City, AZ

Al Balmer <albalmer (AT) att (DOT) net> wrote:
If the e-mail address is valid, I'll collect it some day :^)

Yours,
Laurenz Albe

On 08 Nov 2006 08:12:06 GMT, Laurenz Albe <invite (AT) spam (DOT) to.invalid>
wrote:

It is.

I'll look forward to it.
--
Al Balmer
Sun City, AZ