Re: "No results were returned by the query" exception

Tim,

In postgres an error (any error) causes the transaction to be aborted.
The transaction then must be rolledback in order to continue.

It is clear from your example here that you are rollingback on a
duplicate key error, but I don't know what you are doing for other errors.

thanks,
--Barry


Tim Lewis wrote:
> Dave,
>
> I checked my code against all instances in the driver logs where a
> duplicate key message arose.  In all cases, my code has the following
> structure:
>
> try {
>   table.insert(tablerec);
>   db.commit();
> }
> catch (SQLException e) {
>   if (e.getMessage().indexOf("duplicate key") != -1) {
>     db.rollback();
>   } else {
>     throw(e);
>   }
> }
>
> So I'm not sure if this is the issue.  It may also explain why I have
> not had this problem with the previous driver.
>
> Tim.
>
> Dave Cramer wrote:
>
>     Tim,
>
>     Here is the good bits from your logs that you sent me.
>
>     What they are saying is that you have started a transaction and done an
>     insert which failed. After that the transaction needs to be committed,
>     or rolled back before anything can be done, even selects.
>
>     ERROR:  Cannot insert a duplicate key into unique index
>     crawlqueue_url_idx
>     ERROR:  Cannot insert a duplicate key into unique index
>     crawlqueue_url_idx
>     ERROR:  current transaction is aborted, queries ignored until end of
>     transaction block
>
>     NOTICE:  current transaction is aborted, queries ignored until end of
>     transaction block
>
>     NOTICE:  current transaction is aborted, queries ignored until end of
>     transaction block
>
>     SQLWarning: reason(NOTICE:  current transaction is aborted, queries
>     ignored until end of transaction block
>
>     Dave
>
>      > -----Original Message-----
>      > From: tlewis@kyle.sinewave.com.au
>      > [mailto:tlewis@kyle.sinewave.com.au] On Behalf Of Tim Lewis
>      > Sent: Thursday, March 21, 2002 11:02 PM
>      > To: Dave@micro-automation.net
>      > Cc: 'David Hancock'
>      > Subject: Re: [JDBC] "No results were returned by the query"
>     exception
>      >
>      >
>      > I'm not clear on what you mean by a transaction is in error?
>      >
>      > I'm not sure how a select statement returning a resultset
>      > with no rows should trigger an exception.
>      >
>      > Could you explain this in some more detail.
>      >
>      > Tim.
>      >
>      > Dave Cramer wrote:
>      >
>      > > Actually, this only occurs when there is an error in the query. If
>      > > there are no results from the query
>      > >
>      > > Ie select * from foo; returns nothing then you will get an empty
>      > > result set
>      > >
>      > > If on the other hand there is an error in the query like a
>      > transaction
>      > > is in error, then the exception will be thrown
>      > >
>      > > Dave
>      > >
>      > > > -----Original Message-----
>      > > > From: pgsql-jdbc-owner@postgresql.org
>      > > > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of
>      > David Hancock
>      > > > Sent: Thursday, March 21, 2002 10:00 PM
>      > > > To: pgsql-jdbc@postgresql.org
>      > > > Subject: [JDBC] "No results were returned by the query"
>     exception
>      > > >
>      > > >
>      > > > Hi,
>      > > >
>      > > > Someone recently pointed out that a SQLException is thrown
>     when a
>      > > > query returns no results. I have noticed this too. It used to be
>      > > > the case (driver version 6.x) that an empty ResultSet was
>      > > > returned, and no
>      > > > exception thrown.
>      > > >
>      > > > This new behaviour means that the Postgres driver behaves
>      > > > significantly differently to other JDBC drivers, and that code
>      > > > written for a generic JDBC connection doesn't work as
>      > expected with
>      > > > Postgres.
>      > > >
>      > > > Is this new behaviour a bug or a feature ?
>      > > >
>      > > > regards,
>      > > >
>      > > > Dave Hancock.
>      > > >
>      > > >
>      > > >
>      > > >
>      > > >
>      > > >
>      > > >
>      > > > ---------------------------(end of
>      > > > broadcast)---------------------------
>      > > > TIP 4: Don't 'kill -9' the postmaster
>      > > >
>      > > >
>      > >
>      > > ---------------------------(end of
>      > > broadcast)---------------------------
>      > > TIP 1: subscribe and unsubscribe commands go to
>      > majordomo@postgresql.org
>      >
>      >
>
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> --
> Tim Lewis
> Senior Software Engineer
> Hitwise
> Level 7/580 St Kilda Road
> Melbourne, Victoria 3004
> Phone : +61-3-8530-2400
> Direct: +61-3-8530-2402
> Fax   : +61-3-9529-8907
> Mobile: 0414 726 899
> Email : tim.lewis@hitwise.com
>
>