SQL UNION query in Java

[goli]

Hi,
I have this query:
(code summary)
********
....
bsrQuery =
"SELECT * FROM devices INNER JOIN bsr ON
devices.DeviceOrigIndex = bsr.BsrIndex WHERE DeviceType="
+ WipmngConstants.BSR;
sprQuery =
"SELECT * FROM devices INNER JOIN spr ON
devices.DeviceOrigIndex = spr.SprIndex WHERE DeviceType="
+ WipmngConstants.SPR;
bsduQuery =
"SELECT MngIP, GetCommunity, SetCommunity, DeviceType FROM
devices INNER JOIN bsdu ON devices.DeviceOrigIndex = bsdu.BsduIndex
WHERE DeviceType="
+ WipmngConstants.BSDU;
q1 = bsrQuery + " UNION ALL " + sprQuery/* + " UNION " +
bsduQuery*/;
//q1 = sprQuery + " UNION ALL " + bsrQuery/* + " UNION " +
bsduQuery*/;

statementDevTbl =
wipllDb.connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

//System.out.println(q1);

resultSet = statementDevTbl.executeQuery(q1);
.....
*********
When I run q1 in MS-Access it gives me the correct answer. Eg. the
actual union of the two queries.
BUT !! when I do the code above, it doesn't return the union, but only
results of one table.
My driver supports UNION (getMetaData().supportsUnion() is true).

Does anyone knows something about it ?

Tahnks

 

[tzvika.barenholz]

not sure , but the * may be a problem. why not specify column names ?
are they even identical in the bsdr, spr, bsdu tables?
also try union all in stead of union.

 

[Kevin McMurtrie]

Hi,
I have this query:
(code summary)
********
...
bsrQuery =
"SELECT * FROM devices INNER JOIN bsr ON
devices.DeviceOrigIndex = bsr.BsrIndex WHERE DeviceType="
+ WipmngConstants.BSR;
sprQuery =
"SELECT * FROM devices INNER JOIN spr ON
devices.DeviceOrigIndex = spr.SprIndex WHERE DeviceType="
+ WipmngConstants.SPR;
bsduQuery =
"SELECT MngIP, GetCommunity, SetCommunity, DeviceType FROM
devices INNER JOIN bsdu ON devices.DeviceOrigIndex = bsdu.BsduIndex
WHERE DeviceType="
+ WipmngConstants.BSDU;
q1 = bsrQuery + " UNION ALL " + sprQuery/* + " UNION " +
bsduQuery*/;
//q1 = sprQuery + " UNION ALL " + bsrQuery/* + " UNION " +
bsduQuery*/;

statementDevTbl =
wipllDb.connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);

//System.out.println(q1);

resultSet = statementDevTbl.executeQuery(q1);
....
*********
When I run q1 in MS-Access it gives me the correct answer. Eg. the
actual union of the two queries.
BUT !! when I do the code above, it doesn't return the union, but only
results of one table.
My driver supports UNION (getMetaData().supportsUnion() is true).

Does anyone knows something about it ?

Tahnks

It probably doesn't like kind of cursor that you've requested. A UNION
is a special operation that has restricted features in the database.
What happens if you call statementDevTbl.getMoreResults() or use the
default Statement?

 

[goli]

not sure , but the * may be a problem. why not specify column names ?
are they even identical in the bsdr, spr, bsdu tables?
also try union all in stead of union.

I think there is something with the column names.
I have 2 type of devices: type 1 and type 2.

This is q1:
"SELECT DeviceType, MngIP FROM devices INNER JOIN bsr ON
devices.DeviceOrigIndex = bsr.BsrIndex WHERE devices.DeviceType=1 UNION
ALL SELECT DeviceType, MngIP FROM devices INNER JOIN spr ON
devices.DeviceOrigIndex = spr.SprIndex WHERE devices.DeviceType=2"

This is the MS-Access result:
DeviceType MngIP
1 174285834
1 174285836
1 174285835
1 174285840
1 174285839
1 174285838
1 174285837
1 174285841
1 174285843
1 174285844
1 174285845
1 174285826
2 174262817
2 174262833
2 174263935
2 174286035
2 174262785
2 174265346
2 174264578
2 174285847
2 174262801

This is what I get in my Java query:
(If I print as mentioned in my first post)
1 174285834 10.99.100.10
1 174285836 10.99.100.12
1 174285835 10.99.100.11
1 174285840 10.99.100.16
1 174285839 10.99.100.15
1 174285838 10.99.100.14
1 174285837 10.99.100.13
1 174285841 10.99.100.17
1 174285843 10.99.100.19
1 174285844 10.99.100.20
1 174285845 10.99.100.21
1 174285826 10.99.100.2

I noticed that the second type (2) is missing.
Similar issue:
If I don't ask for DeviceType, in MS-Assccess the result is seprated to
3 groups:
1. MngIP of devicetype = 2
2. MngIP of devicetype = 1
3. MngIP of deviceType = 2
(it's ordered by MngIP)

In my Java app. I see only the first two groups :-/

(I can show it here, but the post will be much longer...)

BTW:
UNION ALL does not work
statementDevTbl.getMoreResults() returns false at the end of the while.

Thanks for any help

 

[goli]

Hi,
I think the problem is something with the column names.

Here is the new query (q1):
"SELECT DeviceType, MngIP FROM devices INNER JOIN bsr ON
devices.DeviceOrigIndex = bsr.BsrIndex WHERE devices.DeviceType=1 UNION
ALL SELECT DeviceType, MngIP FROM devices INNER JOIN spr ON
devices.DeviceOrigIndex = spr.SprIndex WHERE devices.DeviceType=2"

This is the MS-Access result:
DeviceType MngIP
1 174285834
1 174285836
1 174285835
1 174285840
1 174285839
1 174285838
1 174285837
1 174285841
1 174285843
1 174285844
1 174285845
1 174285826
2 174262817
2 174262833
2 174263935
2 174286035
2 174262785
2 174265346
2 174264578
2 174285847
2 174262801

This is my Java result (printing using while (resultSet.next()) {...})
1 174285834 10.99.100.10
1 174285836 10.99.100.12
1 174285835 10.99.100.11
1 174285840 10.99.100.16
1 174285839 10.99.100.15
1 174285838 10.99.100.14
1 174285837 10.99.100.13
1 174285841 10.99.100.17
1 174285843 10.99.100.19
1 174285844 10.99.100.20
1 174285845 10.99.100.21
1 174285826 10.99.100.2

Notice that only deviceType =1 is here.

If I don't ask for DeviceType, the result in MS-Access is devided to 3
groups:
(The result itself is ordered by MngIP)
Gruop 1 - Devices of type 2
Group 2 - Devices of type 1
Group 3 - Devices of type 2

In my Java app. I get only groups 1 and 2.
(I won't show it here becaus it will make my message much longer).

BTW
statementDevTbl.getMoreResults­() returns FALSE at the end of the
while loop.

Thanks for any help ...