Why Does My Code Produce The Error: The Statement Did Not Return A Result Set

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 = '

Solution 1:

JDBC is getting confused by row counts.

You need to use SET NOCOUNT ON.

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

Solution 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 NOTIN (
       SELECT view_name 
       FROM md_criteria_join 
       WHERE user_name ='tecgaw')
   )
   OR user_name ='tecgaw'ORDERBY 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

Connectionconn= 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

Solution 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 ";"