2

I execute the following queries:

SELECT 1; CREATE TABLE ....; SELECT 2;

after that I try to get all resultSets. I use the following code for this:

while(!stmt.getMoreResults()) {
 stmt.getResultSet();
}

Unfortunately I get jus first result set. Please tell me what I'm doing wrong?

Іван Гладуш
  • 375
  • 1
  • 2
  • 8
  • See the answer in this thread https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement Possible duplicate – Azad Feb 25 '19 at 10:46
  • JDBC isn't even defined when you submit more than one statement at a time. – user207421 Feb 25 '19 at 11:26
  • The problem is that you are misinterpreting the true/false meaning of `getMoreResults`. False doesn't mean that there are no more results, it means the next result is an update count, not a result set. – Mark Rotteveel Feb 25 '19 at 11:49
  • It's a bad idea to run multiple SQL statements inside the same `Statement`. You should just run these separately. – Mick Mnemonic Feb 25 '19 at 12:21

2 Answers2

3

Your second CREATE TABLE statement technically does not even return a result set (though JDBC might return a count of the records affected by DML). So, if you want to capture the conceptual return value of each statement, then you should just run them separately.

If your second statement were actually a SELECT, then perhaps we could find some way to combine the queries together.

Read this canonical answer to see how to handle the case where you really do have mulitple selects. But, note that not all databases support this (e.g. Oracle does not support it). And read here to see why multiple queries in a single JDBC call might even be a bad thing.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you for your answer. But could you please provide link to documentations or article where I can read more about this problem – Іван Гладуш Feb 25 '19 at 10:55
  • Thank you. But you added just a answer from Stack Overflow. Do you have the documentation or something else which can confirm your words. Because now I don't understand why I can't read all result sets in my case – Іван Гладуш Feb 25 '19 at 11:22
  • Um...your second statement does NOT have a result set, because it is a DML statement which creates a table. I recommend against sending three statements to JDBC in a single string, just run them separately. – Tim Biegeleisen Feb 25 '19 at 11:23
  • I don't think that question should be considered a canonical answer, because the solution suggested there is technically wrong (it assume false means that there are no more results, which is not correct if it contains statements that don't produce a result set). My answer [here](https://stackoverflow.com/questions/32561550/jdbc-sql-server-raised-error-handling-in-multi-statement-stored-procedures/32562121#32562121) shows how to deal with multiple results compliant with the definition of JDBC. – Mark Rotteveel Feb 25 '19 at 11:51
  • Specifically with regard to result sets, update counts, and exceptions, [this Q+A](https://stackoverflow.com/q/42169951/2144390) attempts to cover all of the angles. – Gord Thompson Feb 25 '19 at 12:52
  • Well, it wasn't a ploy to get more upvotes, but I see now that I focussed a bit too much on the second example of the question you linked. – Mark Rotteveel Feb 25 '19 at 12:52
2

Are you trying to say that Java is just not capable of doing the thing .Net does without a hitch? Is it as simple as that? No matter what kind of dummy statements are between selects in the script that is run as batch the bool IDataReader.NextResult() in the C# code reliably returns next result jumping over the next dummy statements for Netezza we are trying to debug for now. It did the same thing for many years for all the platforms that support batch calls in case we had to deal with them. enter image description here

DShevelev
  • 71
  • 1
  • 1
  • 5