Re: libpq doing strange things

On Mon, 7 Jan 2002 19:54, you wrote:
> Daniel Andersen wrote:
> > Hi,
> >
> > Is there some undocumented feature in libpq to do with executing update
> > queries? I just programmed my mail server to put a mark next to a users
> > name whenever they check their email, but for some reason the update
> > query does not execute properly. It returns PGRES_COMMAND_OK, and if i
> > check the text output of the query it says its updated one record. I know
> > that the connection is working ok, because it extracts the password and
> > username from the database ok in the query before. Is there any other way
> > to check what is going wrong, or is there something else i should be
> > doing i don't know about? all i do is load the query into a string using
> > sprintf, and then use that string to execute the query like PGexec (conn,
> > string)
> > Heres the exact query and commands in case it helps:
> >
> > snprintf (ptr, 256, "update accounting set mail = 1 where
> > time_stamp in (select max (time_stamp) from accounting where
> > username='%s') and username='%s';", pinfo->userid, pinfo->userid);
> > syslog(LOG_ERR,ptr);
> > res2 = PQexec (conn, ptr);
> > if (PQresultStatus(res2) != PGRES_TUPLES_OK) syslog(LOG_ERR,
> > PQresStatus(PQresultStatus(res2)));
> >
> > and the output in the syslog is like:
> >
> > Jan 4 09:21:49 spa teapop[17449]: update accounting set mail = 1 where
> > time_stamp in (select max (time_stamp) from accounting where
> > username='bsn') and username='bsn';
> > Jan 4 09:21:49 spa teapop[17449]: PGRES_COMMAND_OK
> >
> > which seems like it should be doing ok. Can anyone shed some light on
> > what might be causing this problem?
>
> So what are you worried about? EVERYTHING IS FINE :-) Just use the
> PGRES_COMMAND_OK return code for your check:
>
> if (PQresultStatus(res2) != PGRES_COMMAND_OK)
> syslog(LOG_ERR, PQresStatus(PQresultStatus(res2)));
>
> The PGRES_TUPLES_OK is the return code that informs you about a valid
> _selection_ of records - but with an UPDATE statement, you just don't
> select anything. Therefore it just can't give you the result
> PGRES_TUPLES_OK.
>
> N.B.: If you do a select and get a PGRES_TUPLES_OK it is not a necessity
> to have records selected. You might have executed a valid query which just
> returned an empty result (for example any valid query in a totaly empty
> database).
>
> There are other queries which only return PGRES_COMMAND_OK (for
> example UPDATE and DELETE).
>
> Hope this helps
> Andreas

Hrm i probably should have fixed up that earlier mistake in my code before
copying and pasting. The pgres_tuples_ok thing is irrelevant,the printed
results show pgres_command_ok is being returned, so as i said things should
theoretically be ok. But they aren't. The query is not being executed
correctly as the database is remaining unmodified, despite the query saying
it has updated one row (the expected result). If i copy the query word for
word into the psql database interface, it is executed perfectly and update
the one record as it should. I probably just didn't explain this well enough
in my original email :)
Daniel Andersen