Обсуждение: 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,
Deepa

Deepa K <kdeepa@midascomm.com> writes:
>     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.

That's what it's supposed to do.

>    Also the queries which were before the failed query, which were
> successful, were also not commited to the database.

Likewise.

If this is not the behavior you want, then submit the commands
separately.

            regards, tom lane

Thanks Tom.

But we run a set of commands in psql command line, if any one of query
fails, other commands gets executed. Why this is possible in psql and
not using application.

TIA
Deepa. K


Tom Lane wrote:

> Deepa K <kdeepa@midascomm.com> writes:
> >     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.
>
> That's what it's supposed to do.
>
> >    Also the queries which were before the failed query, which were
> > successful, were also not commited to the database.
>
> Likewise.
>
> If this is not the behavior you want, then submit the commands
> separately.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Deepa K <kdeepa@midascomm.com> writes:
> But we run a set of commands in psql command line, if any one of query
> fails, other commands gets executed. Why this is possible in psql and
> not using application.

psql splits your input at semicolons, and ships each command to the
backend in a separate PQexec.  You're wasting your time suggesting
that we should change this behavior in either PQexec or psql --- we'd
break far too many applications for far too little gain.

            regards, tom lane