0

I have a requirement to create a temp table and select on the temp table against Netezza DB in one session.

Tables: abc, def

String query = create temp table MyTemp as select * from abc join def; select col1, col2 from MyTemp;";
boolean result = statement.execute(query);
while(true) {
     if(result) {
       ResultSet rs = statements.getResultSet();
       while(rs.next()) {
        //Access result set.
       }
     } else {
        int count = statement.getUpdateCount(); -- CREATE statement output
         if(count == -1) {
          break;
         }
        result = statement.getMoreResults();
     }
}

I expected to get the updatecount and then a resultset from the statement as I am executing CREATE temp statement followed by SELECT statement.

I get result as true for the first statement(CREATE statement output). But later when statement.getMoreResults gets executed I get false. But according to documentation the statement.getMoreResults returns true for ResultSet output.

I have an alternative solution of splitting the string using semicolon and executing the individual queries using the same statement. But I want to know why the above solution doesn't work.

Ananth Gopinath
  • 31
  • 2
  • 6
  • 1
    Does `Netezza DB` support this SQL statement? `create temp table as select * from abc; select * from abc` or is this a typo? – Jacob May 28 '17 at 07:07
  • It does I could execute the same query using Aginity(Netezza SQL editor). I add a missing semicolon at the end of the select statement. Also I could verify that the queries are indeed executed by Netezza in Netezza Adminstrator tool. – Ananth Gopinath May 28 '17 at 07:20
  • I have not worked with Netezza. However, how come your query has two select statements from the same table `select * from abc; select * from abc;` – Jacob May 28 '17 at 07:26
  • 1
    Your query isn't a SQL statement, but a script. I don't know the Netezza JDBC driver, but most JDBC driver allows only one statment and not a *script*. Can you do 2 seperate statements in the same transaction ? What do you mean `in one session`? – PeterMmm May 28 '17 at 07:29
  • 2
    Because JDBC accepts one query per statement – N00b Pr0grammer May 28 '17 at 07:34
  • The temp table is created by performing joins on different tables in my use case. But for simplicity I just made it to join two tables abc, def. In the second part of the query I am getting selective columns from the temp table. Also the temp table stays only for the session. So if the select query has to use the temp table both the queries have to be executed in the same sql session. – Ananth Gopinath May 28 '17 at 07:38
  • @AnanthGopinath Yes, joining the multiple tables using columns does make sense. With all due respect and by all means, having multiple select statements separated by semicolon does not make sense when it is invoked using JDBC. – Jacob May 28 '17 at 07:44
  • @user75ponic I have written a java program which can execute a sql select statement entered by user and create a data file from the results which can be used by a downstream application. The select statement can be edited by end user through a UI tool. Earlier the query had with syntax but the performance was poor. So I modified the query to use create temp statements(with distributed columns) in place of with. – Ananth Gopinath May 28 '17 at 07:58
  • @AnanthGopinath I am not all aware of Netezza. Having mentioned this, why not create a stored procedure (if it supports) and invoke the stored procedure using JDBC. Thus you can execute multiple SQL statements in the same session. – Jacob May 28 '17 at 08:01
  • Two issues with stored procedure: 1. Schema of my select query is dynamic. User can change the output columns through UI tool. So stored procedure can't return fixed number of columns. 2. In netezza, stored procedure can't execute create and select statement in one exec immediate statement. Both the statement s have to be of same type i.e. either create or insert. – Ananth Gopinath May 29 '17 at 01:29

1 Answers1

1

I'm unsure as to whether the Netezza JDBC driver supports it, or if it will even work with your example queries and existing code, but it looks like you may need to pass allowMultiQueries=true as an option for your JDBC URL.

See this answer for more information.

joebeeson
  • 4,159
  • 1
  • 22
  • 29
  • I tried the option, but didn't work. As pointed out by you, it could be because of the Netezza JDBC driver. For now I am splitting the string using semicolon and executing the SQL statements separately. – Ananth Gopinath Jun 27 '17 at 03:59
  • @AnanthGopinath -- Good call. I've recently had to do something similar so, a couple unsolicited tips: make sure the semicolons aren't in quotes when splitting. It also helps to remove comments before splitting to ensure you don't wind up running a query that was commented out. – joebeeson Jun 30 '17 at 20:01