Can I use multiple statements in a JDBC prepared query?

Question

Hi,

I'd like to execute something like this on my MySQL server:

SET @id=(SELECT id FROM lookupTable WHERE field=?);
(SELECT * FROM table2 WHERE id=@id)
UNION
(SELECT * FROM table3 WHERE id=@id)
UNION
(SELECT * FROM table4 WHERE id=@id);

This works fine from the console, but not from my Java PreparedStatement. It throws an exception with a syntax error at the ';' separating the statements. I like the variable because I don't have to repeat the lookup clause, but I could rewrite it if necessary. The equivalent JOIN is a little awkward with the UNION clauses too.

Thanks,

Joshua

Answer 1

Just running this as two separate queries (within one connection) should give you same results.

Answer 2

Yes, you can execute multiple statements. You just need to remove the ;s that are causing the syntax error. I set up a similar example on SQL Server and had to declare the scalar like so:

DECLARE @id int 
SET @id=(SELECT id FROM ...

I'm not sure if that's required in MySQL or not, but Java give me a very specific error message that led me right to the problem.

Answer 3

JDBC has never supported parsing delimited queries. Each invocation is one trip to the database. Perhaps you can achieve what you meant to doing PreparedStatement.addBatch() for each separate query, then executing and retrieving the two resultsets ?