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.
