Why does my code produce the error: The statement did not return a result set [duplicate]

Question

I am executing the following query from Microsoft SQL Server Studio, which works fine and displays results:

SELECT *
INTO   #temp_table
FROM   md_criteria_join
WHERE  user_name = 'tecgaw'

UPDATE #temp_table
SET    user_name = 'tec'
WHERE  user_name != 'tec'

SELECT *
FROM   md_criteria_join
WHERE  user_name = 'tec'
   AND view_name NOT IN (SELECT view_name
                         FROM   md_criteria_join
                         WHERE  user_name = 'tecgaw')
UNION
SELECT *
FROM   #temp_table
ORDER  BY view_name,
      user_name,
      crit_usage_seq,
      crit_join_seq 

However, if I execute the same query in Java, an Exception is thrown stating

The statement did not return a result set.

Here's the Java code:

statement = conn.getConnection().createStatement();
resultSet = stmt.executeQuery(sql.toString());

Is that because I cannot do multiple SQL queries in one statement (I.e., Creating the #temp_table, updating it, and then using for it my select statement)?

Answer 1

JDBC is getting confused by row counts.

You need to use SET NOCOUNT ON.

Answer 2

Use execute statement for data manipulation like insert, update and delete and executeQuery for data retrieval like select

I suggest you to separate your program into two statements one execute and one executeQuery.

If you do not wish to do that, try separating the statements with semi-colon. But I am not sure about this action if this gives you a resultset or not.

Answer 3

I have found similar question in StackOverflow here. You should enable connection to support multiple statements and separate them using ;. For concrete examples see that answer. However it is for MySql only.

Also I think you can rewrite your SQL into single query

SELECT columnA, columnB, 'tec' as user_name from md_criteria_join
WHERE (
       user_name = 'tec' 
   AND view_name NOT IN (
       SELECT view_name 
       FROM md_criteria_join 
       WHERE user_name = 'tecgaw')
   )
   OR user_name = 'tecgaw' 
ORDER BY view_name, user_name, crit_usage_seq, crit_join_seq

Another option is to move your statements to stored procedure and ivoke it from JDBC using CallableStatement

Or maybe you should try executing it with multiple jdbc statements like this

Connection conn = conn.getConnection(); //just to make sure its on single connection
conn.createStatement("SELECT INTO #temp_table").executeUpdate();
conn.createStatement("UPDATE #temp_table").executeUpdate();
conn.createStatement("SELECT ...").executeQuery();

Note you have to close resources and maybe for better performance you could use addBatch and executeBatch methods

Answer 4

in ms sql you also have to do set nocount on right at the beginning of the stored procedure along with terminating select / update/ insert block statement with ";"