Re: "No Results were returned by the query" exception - Mailing list pgsql-jdbc

From Jon Swinth
Subject Re: "No Results were returned by the query" exception
Date
Msg-id 200206070056.g570uYp24779@localhost.localdomain
Whole thread Raw
In response to Re: "No Results were returned by the query" exception  (Dave Cramer <Dave@micro-automation.net>)
Responses Re: "No Results were returned by the query" exception
List pgsql-jdbc
Dave,

Thanks for the guess, but no the insert is not failing.  If I comment out the call to the sequence then everything
worksfine. 

I only think the insert has something to do with it because I don't get the exception every time a poll the sequence.
I get the exception only in this one place.  And it is the only place I can think of where an insert happens before
a sequence select.  The insert may be irrelevant to the issue, but it is the only thing I can see different from where
things work and don't work.

What I am actually doing is trying to advance the sequence beyond a certain value.  The code actually calls a select
from currval() first and then on exception calls nextval() to see what the sequence is currently at.  The currval()
callis 
failing too.  I had to implement things this way because PostGre will throw an error if the sequence has been newly
created and you try to call currval() on it.  I need to currval() (or nextval()) to make sure that the user is not
tryingto 
set the sequence to a previous value.

I used to have this issue with the non-dev driver all the time.  Especially when I would be making a lot of successive
calls.  Upgrading to the dev driver did away with all the errors until now.

Jon

On Thursday 06 June 2002 05:19 pm, Dave Cramer wrote:
> Jon,
>
> then chances are the insert is failing and then the transaction needs to
> be rolledback. Once a transaction has an error it cannot be used until
> ended, by either rollback, or commit
>
> Dave
>
> On Thu, 2002-06-06 at 20:01, Jon Swinth wrote:
> > Thanks for the reply Joe.
> >
> > I am using PostGreSQL 7.2.1 with JDBC driver of
> > http://jdbc.postgresql.org/download/devpgjdbc2.jar.
> >
> > Code is:
> >
> >   public int getNextValue(
> >       String                           sequenceName )
> >       throws SQLException {
> >     checkAll(sequenceName);
> >     Statement s = null ;
> >     ResultSet rs = null ;
> >     try {
> >       s = connection.createStatement() ;
> >       rs = s.executeQuery("SELECT nextval('"+sequenceName+"')");
> >       if (!rs.next()) {
> >         throw new SQLException("No Rows Returned from Sequence " +
> > sequenceName); } //end if
> >       int returnValue = rs.getInt(1) ;
> >       rs.close();
> >       s.close();
> >       return returnValue ;
> >     } catch (SQLException e) {
> >       if (rs != null) {
> >         rs.close();
> >       } //end if
> >       if (s != null) {
> >         s.close();
> >       } //end if
> >       throw e ;
> >     } //end try
> >   } //end getNextValue()
> >
> > And yes I am using it from within a transaction.
> >
> > What is odd is it is not consistant.  This same code runs fine for a lot
> > of things.
> >
> > My guess is that there is a variable that is not being reset properly in
> > the driver.  That would explain why it does not happen all the time.  In
> > this case, I am receiving an error when running the above code after an
> > INSERT statement on the same connection.
> >
> > Jon
> >
> > On Thursday 06 June 2002 04:02 pm, Joe Shevland wrote:
> > > Sorry I'm not across the actual issue, but have you got a small snippet
> > > of Java code to demonstrate the problem (enough to see if its inside a
> > > transaction etc and how you're executing the statement and looping
> > > through the RS)? Also the version number of the PostgreSQL backend.
> > >
> > > I do the below sort of thing regularly and haven't had a problem. The
> > > drivers on http://jdbc.postgresql.org/download are the best ones to go
> > > for if you're not already using those.
> > >
> > > Cheers,
> > > Joe
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: "No Results were returned by the query" exception
Next
From: Dave Cramer
Date:
Subject: Re: "No Results were returned by the query" exception