Re: executing batch select queries

As it's a development version that hasn't had huge amounts of testing
and this would be used for a production enterprise environment, I should
probably just break up the selects in their own queries.

As for retrieving the results, I planned on using a combination of
stmt.getResultSet() and stmt.getMoreResults(), but maybe my thinking was
wrong here.

Thanks for the input.

-----Original Message-----
From: Oliver Jowett [mailto:oliver(at)opencloud(dot)com]
Sent: Sunday, August 08, 2004 9:01 AM
To: Justin Kennedy
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] executing batch select queries

Justin Kennedy wrote:

> We use batch select queries quite a bit, and to accomplish this in
> Oracle we just separated the queries with "\n" and ran:
> stmt.executeQuery(sql).
>
> Doing this in PostgreSQL (with a semicolon as a delimeter) generates
> this error:
> Cannot handle multiple result groups.

Try the latest development driver from jdbc.postgresql.org. That version

should handle queries that return multiple resultsets correctly. This
was added in build 303. It hasn't had huge amounts of testing, though,
since it's an uncommonly used feature.

> But this doesn't:
> stmt.clearBatch();
> stmt.addBatch("select * from pay_methods");
> stmt.executeBatch();
>
> The following error is output:
> Batch entry 0 .... Call getNextException() to see the cause. calling
> getNextException() produces this:
> A result was returned when none was expected.

Yeah, this isn't ever going to work, the JDBC spec requires the driver
to complain if you give a SELECT-style query to addBatch() (and how
would you get the results, anyway?)

-O