Nest SQL queries and result sets?

Thread view:

Mo - 27 Jul 2006 23:02 GMT

I'm trying to use a while loop to loop through a result set, and inside
that results set call another query. Like....

database_conn1 = new DBConnection(url, user, pass);
database_conn2 = new DBConnection(url, user, pass);

ResultSet records_conn1 = database_conn1.executeQuery(sqlstatment01);

while (records_conn1.next()) {

records1_conn.getString(1);

ResultSet records_conn2 =
database_conn2.executeQuery(sqlstatment02);

while (records_conn2.next()) {
records2_conn.getString(1);
}

}

(I have try and catch around the while statements, just trying to keep
the code simple)

What happens is the outside result set gets the first record, the
inside record gets all of it's results and then it gives me a null
pointer exception. I'm not sure if it is because the inside result set
closes down everything or what. Is this even possible, or is there a
better way to do this?

Reply to this Message

Greg R. Broderick - 28 Jul 2006 01:28 GMT

"Mo" <dmittleider@gmail.com> wrote in news:1154037754.863609.184420
@s13g2000cwa.googlegroups.com:

> I'm trying to use a while loop to loop through a result set, and inside
> that results set call another query. Like....
[quoted text clipped - 25 lines]
> closes down everything or what. Is this even possible, or is there a
> better way to do this?

I'd suggest collapsing the two queries into one query using a join on the two
tables and an ORDER BY clause to insure that one parent table's rows stay
together in the output results - it will save you multiple round trips to the
database at the cost of some denormalization.

Cheers
GRB

Signature

---------------------------------------------------------------------
Greg R. Broderick gregb.usenet200606@blackholio.dyndns.org

A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------------------------------------------------------

Reply to this Message

Mo - 28 Jul 2006 15:21 GMT

These are two different databases. One is off an AS/400 mainframe and
the other is off a n AIX box running DB2 and eventually this will be
expanded for an Oracle database too.

> "Mo" <dmittleider@gmail.com> wrote in news:1154037754.863609.184420
> @s13g2000cwa.googlegroups.com:
[quoted text clipped - 44 lines]
> Q. What is the most annoying thing on Usenet?
> ---------------------------------------------------------------------

Reply to this Message

Greg R. Broderick - 30 Jul 2006 01:30 GMT

> These are two different databases. One is off an AS/400 mainframe and

I hope you don't expect to maintain referential integrity over such a
configuration. You'll get lots of orphaned child rows in the child table as
you delete rows in the parent table, for one.

> the other is off a n AIX box running DB2 and eventually this will be
> expanded for an Oracle database too.

What are you doing with the string that you're retrieving from the result
set?

Is the column that this string is retrieved from nullable? If so, then are
you doing appropriate null checks before using the string?

What is the exact line of code that the NPE's stacktrace points at? Is the
NPE in your code or is it in the JDBC driver's code? If the former, then
what is the exact line of code that is giving the NPE? If the latter, then
what is the line of code further up the stack trace that is your code?

Cheers
GRB

>> "Mo" <dmittleider@gmail.com> wrote in news:1154037754.863609.184420
>> @s13g2000cwa.googlegroups.com:
[quoted text clipped - 35 lines]
>> Cheers
>> GRB

Signature

---------------------------------------------------------------------
Greg R. Broderick gregb.usenet200607@blackholio.dyndns.org

A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------------------------------------------------------

Reply to this Message

Mo - 31 Jul 2006 15:24 GMT

The stack trace errors out on the line with the inner loop.

This isn't really an RI thing... here's what I am trying to
accomplish... I select 5 rows from the AS/400. I march through each
one of them (the outer while). Based on the primary key (which I know
ahead of time from another part of the program) from that table I want
to fire off a query to grab any rows in the DB2 database (inner loop).
This is just a check routine on a small set of data.

> > These are two different databases. One is off an AS/400 mainframe and
>
[quoted text clipped - 66 lines]
> Q. What is the most annoying thing on Usenet?
> ---------------------------------------------------------------------

Reply to this Message

Greg R. Broderick - 01 Aug 2006 17:08 GMT

"Mo" <dmittleider@gmail.com> wrote in news:1154355897.474327.265940
@p79g2000cwp.googlegroups.com:

> The stack trace errors out on the line with the inner loop.

Then it seems to me that the only object reference that can possibly
be null is records_conn2. You might get a clearer idea of what is
going on by reformatting the code thusly:

ResultSet records_conn2 = database_conn2.executeQuery(sqlstatment02);

while (records_conn2.next())
{
records2_conn.getString(1);
}

so that you can tell at a glance (when stepping through the code in
the debugger) whether the code inside of the while() is being
executed.

Given that java.sql.Statement.executeQuery() explicitly states in the
JavaDoc that it will never return null, it sounds like you may have a
defective JDBC driver. You can, of course, test this out by
surrounding the while() loop with an if (records_conn2 != null) {}
block.

It is also barely possible that the query that you're using to
populate this result set is returning zero columns, which might cause
the getString() method to fault. Separating the two lines of code, as
I've suggested above, will determine which statement is actually at
fault.

Cheers
GRB

Reply to this Message

Mo - 02 Aug 2006 14:58 GMT

I am using the JDBC-ODBC bridge driver from Java 1.4.2_06.

I've run the inside query on it's own and it returns results just fine.
At this point I've also set it to be a select * query just so it
should return something. And it does return rows the first time
through, it's when the outer loop should fire the second time that i
get the exception.

To get the outer query to work I have to take the entire inner while
loop out.... but I can leave the execute query statement in for it and
it will work.

> "Mo" <dmittleider@gmail.com> wrote in news:1154355897.474327.265940
> @p79g2000cwp.googlegroups.com:
[quoted text clipped - 30 lines]
> Cheers
> GRB

Reply to this Message

Mo - 02 Aug 2006 18:18 GMT

I went back to using true JDBC drivers for DB2 and AS/400. Same thing.
I found a news group posting that thought ODBC might be the problem.
Seems to not be the case. However, it runs faster so I get the errors
faster =)

> I am using the JDBC-ODBC bridge driver from Java 1.4.2_06.
>
[quoted text clipped - 42 lines]
> > Cheers
> > GRB

Reply to this Message

Wojtek Bok - 31 Jul 2006 19:44 GMT

I have had problems where I am not retrieving (getxx(1) the columns in
the exact order they were specified in the select statement. This is
especially important where you are retrieving a TEXT type.

I always use columns names, rather than index numbers as this assures
that I an getting what I think I am getting. So getxxx("columnName").

Reply to this Message

Mo - 31 Jul 2006 20:11 GMT

I've tried both ways and doesn't seem to matter.

> > I'm trying to use a while loop to loop through a result set, and inside
> > that results set call another query. Like....
[quoted text clipped - 32 lines]
> I always use columns names, rather than index numbers as this assures
> that I an getting what I think I am getting. So getxxx("columnName").

Reply to this Message

IchBin - 31 Jul 2006 20:41 GMT

> I've tried both ways and doesn't seem to matter.
>
[quoted text clipped - 34 lines]
>> I always use columns names, rather than index numbers as this assures
>> that I an getting what I think I am getting. So getxxx("columnName").

Could you just un-nest your two connects\querys and run them in
sequentially in separate sequences. That is, run your first query and
load the results into an array. Once all data is retrieved on the first
query perform a loop against the second database looping on the array
from the first db connection.

Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
__________________________________________________________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor, Regular Guy (1952-)

Reply to this Message

Mo - 04 Aug 2006 23:17 GMT

The way I got this to work is similar to what you suggested. I
actually used a HashTable so I could call things by name. It works,
just not as simple as I'd hoped. Thanks for all the help on this
thread!

> > I've tried both ways and doesn't seem to matter.
> >
[quoted text clipped - 47 lines]
> 'If there is one, Knowledge is the "Fountain of Youth"'
> -William E. Taylor, Regular Guy (1952-)

Reply to this Message

steve - 05 Aug 2006 07:46 GMT

the above code must be wrong, if you are using 2 separate databases

how can you pass the same url to the sql class & expect it to make a
connection to 2 different databases and obtain a connection to them
separately.

steve

Reply to this Message

steve - 05 Aug 2006 07:41 GMT

you cannot, but what you can do is return a subquery of the query within the
loop.

you could for example do this:

select a,b, function(xy) innerset from atable,

where function is a subquery.

when you recover the data in java the query returns a resultset in a
resultset.

steve

Reply to this Message

Nest SQL queries and result sets?

Free Magazines