Handling from UPDATE, SELECT, ... with now rows affected

Hello PostgreSQL-Team,

sorry that i'm not using the standard bug report, but I think i have not a bug.

I'm using the unixODBC with PostgreSQL and I have a little problems discussed
with nick the
maintainer from unixODBC. And for now we are stuck. So what is the problem. I
hope i can get it
on the point.

Looking in the PostgreSQL C-API to the ExecStatusType there is no Retcode for

"I process the command or query but not a single row is returned or touched"

for long: for example using an UPDATE-Statment that does not touch any row, I
does not
get this info. Sure i can get before an UPDATE count(*) .... but this is ugly
and wrong. The same
is for an SELECT-Statment, I can handle this with looking in the RowCount, but
I get a problem using
SELECT MIN(...) (look at the end).

So now the question/suggestion: Is it possible to get the backend get the
information back to the client
if a row is touched/queried in the ExecStatusType for Example:
PGRES_COMMAND_OK_WITH_ROW_TOUCHED,
PGRES_TUPLES_EMPTY ?

Hope to hear from you

Rainer

*An: nick
Verteiler:
Papierkopie:
Von: Rainer Schiele
BTB/Tel: SYTKIV / 0721/4004-1843
Datum: 01.09.2000 12.58.36
*Thema: unixODBC - SQLFetch RC

Hello Nick,

i think i have discovered a mismatch in the Handling of SQLFetch. We have had a
similary Problem (but not the same, I put it on the end: Problem1).

OK, let's start:

Linux 2.2.14, PostgreSQL 7.0.2, unixODBC-1.8.12

For example when I use and SQL-Statement like

SELECT Number FROM TEDAV003 WHERE id = 1000

and the Row with id = 1000 does not exists i get an SQL_NO_DATA_FOUND from
SQLFetch.
This seems OK.

But using the statment:

SELECT MIN(Number) FROM TEDAV003 WHERE id = 1000

SQLFetch return SQL_SUCCESS (Wrong) and the Binded Cols where not been touched
(Right)

I think the problem is using the Row Count from the Select-Results which is 1,
but this does not mean we found data !!!

Rainer

PS: Had you time to look in my Threading-Problem. I have get a deep breath and
going in your code
(..\Drivers\ProstgreSQL\statement.c line 770) where you decide to start a
transaction or not. And I did
not seen a synchronization there. Will that be synchronized in the upper layers
?

Problem 1

I have a problem with SQL-Updates and the Retcode from SQLExecDirect.

I think i make something wrong because the problem is so trivial that this can
not be a bug!

Let's start:

I have a very simple Routine that makes an update in the Database:

int DBProduktForTargInsert(DBHandle *dbHandle,
short prodNr,
int targNr,
int jobNr,
char *userId,
DBErrInfo *errInfo) {

SQLCHAR stmt[MAXSTMTLENGTH];
SQLRETURN sqlrc;

sprintf(stmt, StmtProduktForTargInsert1,
prodNr, targNr, jobNr, userId,
prodNr, targNr);

sqlrc = SQLExecDirect(dbHandle->hstmt, stmt, SQL_NTS);
fprintf(stderr, "sqlrc: %d\n", sqlrc);
if (sqlrc != SQL_SUCCESS) {
DBFillErrInfo(dbHandle, errInfo);
return sqlrc;
}

return sqlrc;
}

The SQL-Statement that is going to the Database is:

UPDATE TESVK005
SET prod_nr = 1,
targ_nr = 2,
job_nr = 3,
upd_uid = 'UserID',
upd_date = CURRENT_DATE,
upd_time = CURRENT_TIME
WHERE prod_nr = 1 AND targ_nr = 2

Seems quite right!

In the table there are no entries !! This is OK!

BUT the sqlrc is SQL_SUCCESS (0) not SQL_NO_DATA_FOUND (100).

My Dokumentation (IBM DB2/2) says for a Searched Update with no rows satisfy
the serach condition SQL_NO_DATA_FOUND must be returned!!!!!!