Обсуждение: ExecCommandOk() return code always zero?

I've got a little test program that I'm playing with, and am finding an
unusual behaviour.  When I query the database with an ExecCommandOk(),
it always returns zero.  If I query the database with an ExecTuplesOk(),
it always returns 1 (regardless of whether or not there are any records
in the database).

Here is my little test program:
#include <stdio.h>#include <pgdatabase.h>
int main(void);int main(){    PgDatabase myDB("host=localhost port=5432 dbname=test");
    printf("ExecCommandOk returns: %d\n", myDB.ExecCommandOk("select *
from mytable");    printf("ExecTuplesOk return: %d\n", myDB.ExecTuplesOk("select * from
mytable");
    printf("Database has %d record(s)\n", myDB.Tuples());
    return 0;}

If I have, say, five records in the database, the result of running this
program is:
ExecCommandOk returns: 0ExecTuplesOk returns: 1Database has 5 record(s)

If I purge all the records in the database, the result of running this
program is:
ExecCommmandOk returns: 0ExecTuplesOk returns: 1Database has 0 record(s)

Why would ExecCommandOk() and ExecTuplesOk() be returning these
consistent values?  The docs indicate the ExecCommandOk() should be
returning TRUE instead of FALSE, and ExecTuplesOk() should only be
returning TRUE if there are tuples to be retreived.

Thanks for your insight.

Cheers,
Richard

Richard <blackw@sfu.ca> writes:
> Why would ExecCommandOk() and ExecTuplesOk() be returning these
> consistent values?  The docs indicate the ExecCommandOk() should be
> returning TRUE instead of FALSE, and ExecTuplesOk() should only be
> returning TRUE if there are tuples to be retreived.

Those docs are certainly wrong about ExecTuplesOk.  ExecTuplesOk
should return TRUE if your query was valid, regardless of whether
any tuples were found.  Otherwise, you cannot tell the difference
between a bogus query (a syntax error, for example) and a correct
query that returned zero tuples.

ExecCommandOk is supposed to be used for query types that are incapable
of ever returning tuples (INSERT or UPDATE for example).  Perhaps it
shouldn't return FALSE if you give it a valid SELECT --- that's an
interesting philosophical question.  The query is "valid" ... but on
the other hand, if the application used ExecCommandOk rather than
ExecTuplesOk, that implies that it's not expecting to get any tuples
back.  So *in that context* you could argue that the query is the wrong
type of query.  Similarly, it's not entirely clear what ExecTuplesOk
should do if you hand it an INSERT-type query ... but since that
strongly suggests that the application is badly confused, I think
raising an error isn't unreasonable.

In short, I think we've got a documentation bug here.  Can you
suggest a revision that would have make it more clear to you?
        regards, tom lane

So, the apparent problem is not with the code so much as my
interpretation of what the functions are for, and what the meaning of
the return value is.

I guess I find these two functions a little esoteric; I don't believe
they should be meddling in what the command/query is as that is for the
backend server to deal with.  I would have thought that ExecCommandOk()
would return PGRES_COMMAND_OK if execution was successful, or
PGRES_NONFATAL_ERROR or PGRES_FATAL_ERROR if execution failed. 
ExecTuplesOk() would return PGRES_TUPLES_OK if successful and tuples
were available, PGRES_QUERY_EMPTY if successful but no tuples were
returned, and PGRES_NONFATAL_ERROR or PGRES_FATAL_ERROR if execution
failed.  However, I know that if for no other reason than backward
compatibility, this ain't gonna happen.  (I see where I'm going to have
to whip together some new Exec() wrappers! ;)

Anyways, I'd be happy to suggest a doc revision.  First of all, I like
to suggest a bit of a nomenclature change.  That is this; SQL is a
language of statements.  Every statement is either a command (does not
return data), or a query (returns data).  That is, a "command" tells the
backend server to do something (INSERT, UPDATE, ALTER) and expects
nothing in return other than information about how the command execution
went.

All that ranting and whining aside, how about something like the
following:

ExecCommandOk(): Sends a command to the backend server.  The command
should not be a query that can return tuples (e.g. SELECT).
PgConnection::ExecCommandOk(const char *command)
Returns TRUE if execution was successful.Returns FALSE if execution failed or a query was sent.

ExecTuplesOk(): Sends a query to the backend server.  The query should
not be a command that can not return tuples (e.g UPDATE, INSERT, etc.).
PgConnectin::ExecTuplesOk(const char *query)
Returns TRUE if command executed successfully.Returns FALSE if execution failed or a command was sent.


(I know that in light of my previous rantings about
statement/command/query that saying "The command should not be a query
[...]" and "The query should not be a command [...]" are somewhat odd
statements.  If the documentation DID distinguish between a statement,
command and query, the descriptions could be reduced to "Sends a command
to the backend server" and "Sends a query to the backend server"
respectively).

Also, it might not hurt to put a note in the Caveats section indicating
that ExecCommandOk() will properly execute a query and ExecTuplesOk()
will properly execute a command.  However in both cases a return code of
FALSE will result.

Thank-you very much for explaining these two functions, Tom.  I'd have
dicked around with this for quite a while before I discovered what was
going on here.

Cheers,
Richard



Tom Lane wrote:
> 
> Richard <blackw@sfu.ca> writes:
> Those docs are certainly wrong about ExecTuplesOk.  ExecTuplesOk
> should return TRUE if your query was valid, regardless of whether
> any tuples were found.  Otherwise, you cannot tell the difference
> between a bogus query (a syntax error, for example) and a correct
> query that returned zero tuples.
> 
> ExecCommandOk is supposed to be used for query types that are incapable
> of ever returning tuples (INSERT or UPDATE for example).