Re: pqgetresultset problem

On Wed, 13 Aug 2003, P.Harikrishnan wrote:

> Hi all,
>     I am using postgresql 7.1.3 and a client using libpq.
>     I am executing a statement with muliple SQL commands semicolon
> seperated.
>     If any one of the query in between fails, pqGetResultset  returns
> NULL on the failed query. So i am unable to process the rest of the
> queries.
>    Also the queries which were before the failed query, which were
> successful, were also not commited to the database.
>
> (eg) query1;query2;query3;query4
>
>    If query1 is successful and query2 fails, the pqGetResultset returns
> NULL on query2. So my client comes out of while (pqGetResultSet). The
> query1 which was successful was also not commited to the database.
>
>   If i run the same from psql client, the query2 alone fails, the rest
> other are inserted to the database.

From the current libpq docs:

It is allowed to include multiple SQL commands (separated by semicolons)
in the command string.  Multiple queries sent in a single
<function>PQexec</> call are processed in a single transaction, unless
there are explicit BEGIN/COMMIT commands included in the query string to
divide it into multiple transactions.  Note however that the returned
<structname>PGresult</structname> structure describes only the result of
the last command executed from the string.  Should one of the commands
fail, processing of the string stops with it and the returned
<structname>PGresult</structname> describes the error condition.

I think psql separates the query at the semicolons before passing it to
the database.  If you want psql's behavior you should probably do the
same.