running multiple sql queries in one executeQuery

Thread view:

tony - 26 Apr 2004 10:56 GMT

Hi

Is it possible to run multiple select statements in one (Java)
executeQuery command and get back a result set for each one, maybe as
an array? I have a txt file that contains a number of select queries
and I want to be able to load and run the select queries contained in
it in one go, getting back a number of distinct result sets. Currently
when I try, I only get back a result set for one of them.

Thanks

Tony

Reply to this Message

Michael Rauscher - 26 Apr 2004 12:30 GMT

Hi Tony,

tony schrieb:

> Hi
>
[quoted text clipped - 4 lines]
> it in one go, getting back a number of distinct result sets. Currently
> when I try, I only get back a result set for one of them.

I guess you use the same Statement all the time. You need one Statement
for one open ResultSet. If there are n SQL strings, you need n open
ResultSets and therefore n Statements.

But it's not a good idea to return resultsets/statements since AFAIK
each statement produces an SQL cursor - and open SQL cursors are limited
(in Oracle 8i you can have no more than 255 open cursors at a time)

If you have the possibility to use disconnected RowSets - use them.

> Thanks
>
> Tony

Michael

Reply to this Message

Robert Klemme - 26 Apr 2004 13:16 GMT

> Hi Tony,
>
[quoted text clipped - 17 lines]
>
> If you have the possibility to use disconnected RowSets - use them.

Alternatively batch processing might be applicable, although I doubt this
will work for SELECT's:

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#addBatch(java.la
ng.String)
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeBatch()

robert

Reply to this Message

Lee Fesperman - 26 Apr 2004 23:44 GMT

> > tony schrieb:
> > > Is it possible to run multiple select statements in one (Java)
[quoted text clipped - 19 lines]
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#addBatch(java.la
ng.String)
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeBatch()

The Batch methods are intended for update statements, not for queries. If your DBMS
supports sending multiple queries at once (usually separated by semicolons), you should
be able to use getMoreResults() in java.sql.Statement to retrieve resultsets from
subsequent queries. Please the docs on getMoreResults() very closely.

Signature

Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

Abdullah Kauchali - 28 Apr 2004 00:11 GMT

"Lee Fesperman" <firstsql@ix.netcom.com> wrote in message

> The Batch methods are intended for update statements, not for queries. If your DBMS
> supports sending multiple queries at once (usually separated by semicolons), you should
> be able to use getMoreResults() in java.sql.Statement to retrieve resultsets from
> subsequent queries. Please the docs on getMoreResults() very closely.

I was also after something like this recently. :)

The real trick of these JDBC drivers that support "multiple queries" at the
same time would be to execute all at once and return all at once (ie. one
roundtrip to the database). Instead of going to the server each time for
"getMoreResults" - which would defeat the purpose, IMO.

There is a similar functionality with Microsoft's ADO Recordset called
getNextRecordset ... which certainly goes to the server for the next
recordset even though you submitted the queries all at once. :)

Kind regards

Abdullah

Reply to this Message

running multiple sql queries in one executeQuery

Free Magazines