Thread: pqgetresultset problem
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.
I am expecting the same behaviour as that of psql.
Plz somebody help me in this regard.
TIA.
Regards,
Hari
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.
Thanx for the reply.
I am using asynchronous execution of queries and not pqexec.
So it is possible to get all the result sets one by one by calling pqGetResultset till it returns NULL
So I am assuming that i can leave the query which failed and process the rest of the resultsets. But what is happening is once a
query fails, pqGetResultset return NULL.
Can anybody help me plz.
Stephan Szabo wrote:
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.---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- Regards, Hari
On Thu, 14 Aug 2003, P.Harikrishnan wrote: > Hi Stephan, > Thanx for the reply. > I am using asynchronous execution of queries and not pqexec. > So it is possible to get all the result sets one by one by calling > pqGetResultset till it returns NULL > So I am assuming that i can leave the query which failed and process the > rest of the resultsets. But what is happening is once a > query fails, pqGetResultset return NULL. My guess would be that the queries are probably still sent as a single transaction given that PQexec appears to basically be: setup, use PQsendQuery, get the result from the last result. The following queries fail because you can't do queries while in the failed transaction state.