Thread: "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.
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 > >
On Fri, 2002-03-22 at 04:19, 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 For JSP you must have a "show if result set is not empty" clause or you will get a nullpointer exception error. Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
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 > >
Tony, What does JSP have to do with it? Please explain? Thanks, Dave > -----Original Message----- > From: tony [mailto:tony@animaproductions.com] > Sent: Friday, March 22, 2002 2:05 AM > To: Dave@micro-automation.net > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] "No results were returned by the query" exception > > > On Fri, 2002-03-22 at 04:19, 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 > > For JSP you must have a "show if result set is not empty" > clause or you will get a nullpointer exception error. > > Cheers > > Tony Grant > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia > UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html > >
On Fri, 2002-03-22 at 11:35, Dave Cramer wrote: > What does JSP have to do with it? Please explain? > I don't know. Maybe it is the Ultradev code I am using (?) but if a result set is empty then a nullpointer exception is returned every time. Even for select * from foo where bar = '1' If bar has no value of 1 then error not a nice blank page. I am almost sure that back with the 7.0 version that was not the case. Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
Tony, The new jar should have fixed that. If the resultset was null, the driver didn't handle it very well. This is different from the "No results from query exception" Dave > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of tony > Sent: Friday, March 22, 2002 6:58 AM > To: Dave@micro-automation.net > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] "No results were returned by the query" exception > > > On Fri, 2002-03-22 at 11:35, Dave Cramer wrote: > > > What does JSP have to do with it? Please explain? > > > > I don't know. Maybe it is the Ultradev code I am using (?) > but if a result set is empty then a nullpointer exception is > returned every time. > > Even for select * from foo where bar = '1' > > If bar has no value of 1 then error not a nice blank page. I > am almost sure that back with the 7.0 version that was not the case. > > Cheers > > Tony > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia > UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > >
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 blockNOTICE: current transaction is aborted, queries ignored until end of
transaction blockNOTICE: current transaction is aborted, queries ignored until end of
transaction blockSQLWarning: reason(NOTICE: current transaction is aborted, queries
ignored until end of transaction blockDave
> -----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
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
>
>