No results returned with JDBC query using UNION and DUAL

From: Andy (andy.cole_at_fdgroup.com)
Date: 01/26/04 

Date: 26 Jan 2004 03:17:01 -0800

I am trying to run the following sql using

Statement s = connection.createStatement();
ResultSet result = s.executeQuery(command);
while (result.next()) { ... }

The sql returns the privileges that the user does *not* have, given a
list of privs that are required. It returns the correct results using
Sql*plus but no results using jdbc (i.e. result.next()=false). No
exceptions are thrown, unless I ignore the fact that resultset
returned false and try to do result.getString(1) anyway. Then I get
java.sql.SQLException: Exhausted Resultset

I wondered if it was due to the unusual nature of the query, i.e.
using selects from dual to create a 'fake' table.

select tempPrivs.priv from (
select 'ALTER USER' priv from dual
union select 'CREATE PROCEDURE' priv from dual
union select 'CREATE SYNONYM' priv from dual
union select 'CREATE TABLE' priv from dual ) tempPrivs
where not exists (select null from user_sys_privs
                  where privilege = tempPrivs.priv);

I am using Windows XP, Oracle 8.1.7.4 database, Oracle JDBC drivers
9.2.0.4 and J2SE 1.4.1.
Anyone have any ideas?

Andy Cole.

Relevant Pages

  • RE: datatable.select("Distinct") or such to string?
    ... SQL SELECT statements including UNION, JOINS, GROUP BY, HAVING, ORDER BY, ... resultset and issue queries using the QueryADataSet assembly. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Performance problem in sql ce
    ... Resultset seems to be work. ... SqlCeResultSet rs = CeCommand.ExecuteResultSet; ... even with the amount of data you have. ... I am very disappointed with the sql queries performance in sql ce. ...
    (microsoft.public.sqlserver.ce)
  • Re: Performance problem in sql ce
    ... Resultset seems to be work. ... SqlCeResultSet rs = CeCommand.ExecuteResultSet; ... even with the amount of data you have. ... I am very disappointed with the sql queries performance in sql ce. ...
    (microsoft.public.sqlserver.ce)
  • Re: sql :table does not exist or alias is not currently open
    ... Opening albe and specifying:PRIV: inside the SQL comment should make ... alias as the opened database-variable. ... Specifying a different database in the SQL-comment has never come to my ...
    (comp.databases.paradox)
  • Re: ADO
    ... whether the resultSet is actually ... ADOCommand has overloaded version of Execute: ... DDL statement or an SQL query returning a result set. ... but only single SQL statements? ...
    (borland.public.delphi.thirdpartytools.general)