-1

My sql query consists of 5 part which are highly connected to each other. First part creates a temporary table, second part uses that temporary table and creates another temporary table, third part uses the temporary table that created in second part and again creates another temporary table. And the 4th part select some data from 3rd temporary table and 5th part selects the count of 3th temporary table.

Since temporary tables are only usable within a preparedStatement (what I mean is that a temporary table which created by a preparedStatement are not usable from another preparedStatement, I tried that before it is okey) I need to do that within a prepare statement.

So the first 3 part creates temporary tables because of that after setting the parameters to preparedStatement I run preparedStatement.execute() 3 times(I also tried 1....x times) and then I run the preparedStatement.execute() but it returns false which means that there is no resultset. Why is that? 

PreparedStatement preparedStatement = conn.prepareStatement("select * into #tmp from tablex where ...\n" +
                "    select * into #tmp2 from #tmp where ...\n" +
                "    select * into #tmp3 from #tmp2 where ...\n" +
                "    select * from #tmp3\n" +
                "    select count(*) from #tmp3");

Above, I added a simple illustration. Here I need to get the result of 4th and 5th query with prepared statement. How can I do that?

JollyRoger
  • 505
  • 8
  • 24
  • 2
    Please add the relevant code and queries . – Arnaud Jan 02 '19 at 12:50
  • @Arnaud I can not do that because of confidentiality, but I am adding a simple illustration. – JollyRoger Jan 02 '19 at 12:51
  • 1
    Please read the API documentation of `execute()` carefully for the meaning of `false`. You haven't accounted for the update count that your first query produces. – Mark Rotteveel Jan 02 '19 at 12:54
  • 1
    please show an [mcve] – Jens Jan 02 '19 at 12:55
  • @MarkRotteveel I added a simple version of my code, can you explain what to do? – JollyRoger Jan 02 '19 at 12:55
  • @Jens I think this simple illustration explains my question briefly, – JollyRoger Jan 02 '19 at 12:57
  • Add `SET NOCOUNT ON;` to the beginning of your SQL command string and see if that helps. – Gord Thompson Jan 02 '19 at 13:00
  • @GordThompson Okey, but how can I get the resultset? Only with a preparedStatement.execute() returns false, and if I run preparedStatement.executeUpdate() 1 times or 3 times, it again returns false. I added the statement that you gave to the beginning of first select statement. – JollyRoger Jan 02 '19 at 13:05
  • Also, "a temporary table which created by a preparedStatement are not usable from another preparedStatement" is not necessarily true. The temporary table will be available to other statements that share the same SQL Server session, i.e., those created on the same active (open) `Connection` as the statement that created the temporary table. – Gord Thompson Jan 02 '19 at 13:08
  • @GordThompson, Yes I read it, but for example if I run the queries seperately in the above example, it does not recongizes temporary tables which are created before within the same connection unfortunately. – JollyRoger Jan 02 '19 at 13:11
  • 1
    @JollyRoger Did you disable auto commit before trying that? – Mark Rotteveel Jan 02 '19 at 13:23
  • @MarkRotteveel, No I did not, I will give it a try. Thank you, – JollyRoger Jan 02 '19 at 13:32

1 Answers1

4

The statements you're executing produce the following results:

  1. An update count
  2. An update count
  3. An update count
  4. A result set
  5. A result set

The meaning of the boolean false returned by execute(String) is:

true if the first result is a ResultSet object; false if it is an update count or there are no results

This means that you need to use getUpdateCount() to obtain the (first) update count, and getMoreResults() to get the next result (again, this returns a boolean with the same meaning). Only if execute() or getMoreResults() returns false and getUpdateCount() returns -1 are there no more results.

You need to do something like:

boolean nextResultSet = statement.execute(...);
int resultSetCount = 0;
while (true) {
  if (nextResultSet) {
    resultSetCount++;
    try (ResultSet rs = statement.getResultSet()) {
      // Do something with result set
    }
  } else {
    int updateCount = statement.getUpdateCount();
    if (updateCount == -1) {
      // no more results
      break;
    }
    // do something with update count
  }
  nextResultSet = statement.getMoreResults();
}

You can probably skip part of this complexity by adding SET NOCOUNT ON as the first statement you execute; then you'll not get the update counts and only need to handle the two result sets.

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158