> Adam, > > Adam Lau wrote: >> Hi all, >> >> I'm running multiple CTAS statements that selects and filters a json > > file to produce another json file on my local filesystem. The first > > ctas works but subsequent ones ... > > Do you execute the subsequent SQL statements using a separate JDBC > Statement object (from Connection.createStatement(...)) or do you use > the same Statement object? > I'm using apache commons DBUtil.QueryRunner.executeQuery() which abstracts the Statement management part away. I supply the method a "current" connection, query text and a handler to interpret the ResultSet. I create a new QueryRunner per query and re-use the connection, which I don't close.
> Remember that executing a SQL statement using a given JDBC Statement > object cancels any still-in-progress SQL statement that was executed > using that Statement object and closes its ResultSet object (as least > normally; I don't recall whether there are exceptions, e.g., for > batches). > > > end up not completing fully and somehow end up getting cancelled. The > > table is created (as a json file) but the data in the file gets > > truncated/interrupted. Select statements seem to always complete > > without issue. > > Drill currently returns a logical result set for every statement, even > for non-query SQL statements, and the execute method returns the physical > ResultSet object before the server is necessarily done with executing > the query. > > To make sure you don't end of canceling execution early, when you call > executeQuery(...), hold on to the ResultSet, and call method next() on > it until next() returns false. That should consume the results on the > client side enough for the server to finish executing its side. > Thanks Daniel that put me in the right direction. It turns out the DBUtil library wasn't playing nicely with the ResultSet coming back from the CTAS statement. No matter how I tried to handle the results of the CTAS query out of DBUtil.QueryRunner.executeQuery(), the query/statement would end up closing too early. I replaced DBUtil with java's inbuilt Connection/Statement/ResultSet classes and things are behaving as expected now.
