Create and select statement using jdbc statement

Question

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.

Does Netezza DB support this SQL statement? create temp table as select * from abc; select * from abc or is this a typo? — Jacob
– Jacob, Commented May 28, 2017 at 7: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
– Ananth Gopinath, Commented May 28, 2017 at 7: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
– Jacob, Commented May 28, 2017 at 7:26
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
– PeterMmm, Commented May 28, 2017 at 7:29

joebeeson · Accepted Answer · 2017-06-19 21:01:11Z

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.

answered Jun 19, 2017 at 21:01

joebeeson

4,3861 gold badge25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ananth Gopinath Over a year ago

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.

joebeeson Over a year ago

@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.

Collectives™ on Stack Overflow

Create and select statement using jdbc statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related