Thread: 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!!!!!!