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)?
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
JDBC is getting confused by row counts.
You need to use SET NOCOUNT ON
.
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.
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 ";"