Re: Why?? executeQuery() & exception: "No results were returned by the query."

At 15:32 8.5.2001 -0400, Bruce Momjian wrote:

>Are you saying that this "no result" error is required by the JDBC
>standard, or is it a PostgreSQL bug?

My apologizes !

It seems that I've been barking the wrong tree here and there's nothing
wrong with executeQuery() function. It was in our code.

We set the auto-commit mode globally off. Executing a query that queried
e.g. an non-existant table got the JDBC driver into the state where the
following warning was given:

"Warnings on connection 0 java.sql.SQLWarning: NOTICE: current transaction
is aborted, queries ignored until end of transaction block"

And when the next query was taking place in the executeQuery() it got the
"No results were returned by the query." answer.

Since we were running a kind of application server with multiple users this
phenomenal that empty results were given seemed to be the cause of the
error! What also hindered the real cause was that we also used a connection
broker with quite short "refresh" time that re-connects to the database and
thus cleans up the connection and pending transactions and thus got the
whole system running nicely without giving any other signs but the warning
we eventually noticed !!

The cure was to set every SELECT clause into a auto-commit mode. And use
the auto-commit off with INSERT/DELETE/UPDATE ! I didn't know that every
SELECT statement must be rolled back if any exceptions occur, I thought
that since it doesn't "update" anything it's different from
INSERT/DELETE/UPDATE.

- Panu

> > Hi there !
> >
> > Can anyone explain why does executeQuery() throws an exception
> > "No results were returned by the query."
> > when result set is empty ???????
> >
> > In my opinion (and also e.g. Oracle's JDBC driver's opinion) that this is
> > just normal and user can check the number of rows (=0) that none was
> > returned. It's totally different to make a query where something is wrong
> > in the query itself e.g. non-existant column is used or perhaps in the
> > database connection!
> >
> > This is "pain in the *ss" to test against in one's code for every
> different
> > JDBC driver's DIFFERENT return string there available and it's impossible
> > to do code that works for even unknown drivers. And Postgresql has even
> > language dependant versions of this !!!
> >
> > Since Postgresql source code is so nicely publicly available (thank you
> all
> > there!) and thus improvements are easily given by anyone I just wonder if
> > this could be improved, please !!!
> >
> > - Panu
> >
> > PS. I know for sure that at least SQL Server throws the following when
> used
> > through JDBC-ODBC bridge: "No ResultSet was produced".
> >
> >
> > ----------------------------------------------------------------------
> >
> > org\postgresql\jdbc2\Statement.java:
> >
> > ...
> > /**
> > * Execute a SQL statement that retruns a single ResultSet
> > *
> > * @param sql typically a static SQL SELECT statement
> > * @return a ResulSet that contains the data produced by the query
> > * @exception SQLException if a database access error occurs
> > */
> > public java.sql.ResultSet executeQuery(String sql) throws
> SQLException
> > {
> > this.execute(sql);
> > while (result != null &&
> > !((org.postgresql.ResultSet)result).reallyResultSet())
> > result = ((org.postgresql.ResultSet)result).getNext();
> > if (result == null)
> > throw new PSQLException("postgresql.stat.noresult");
> > return result;
> > }
> > ...
> >
> > ----------------------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

------
Panu Outinen Tel. +358 3 313 411
Vertex Systems Oy Fax +358 3 313 41 450
Vaajakatu 9 http://www.vertex.fi
33720 Tampere, FINLAND mailto:Panu(dot)Outinen(at)vertex(dot)fi