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

"No results were returned by the query" exception

From
"David Hancock"
Date:
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.







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

From
"Dave Cramer"
Date:
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
>
>


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

From
tony
Date:
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


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

From
"Dave Cramer"
Date:
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
>
>


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

From
"Dave Cramer"
Date:
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
>
>


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

From
tony
Date:
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


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

From
"Dave Cramer"
Date:
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
>
>


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

From
Tim Lewis
Date:
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
 

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

From
Barry Lind
Date:
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
>
>