Thread: executing batch select queries

Hi,

Short version:
How can I do batch select queries with jdbc-postgresql?

Long version:
I'm migrating a transaction management system from Oracle to
PostgreSQL.

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.

So then I figured it wasn't supported and looked up the 'executeBatch'
function, but now that leads me to this problem:

This works:
stmt.executeQuery("select * from pay_methods");

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.

Please advise me on how to accomplish batch select queries.

Thanks for the help,
-Justin

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

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@opencloud.com]
Sent: Sunday, August 08, 2004 9:01 AM
To: Justin Kennedy
Cc: pgsql-jdbc@postgresql.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