Re: 3.1.7 Not Using Server-Side Prep Stmt?
We do read our queries from a file, and there are parens, but just to make sure I stuck a debugger breakpoint on the function you showed me, and checked the imcoming input. I traced the execution to see that the method returned false for such statements like:
(SELECT * FROM Alert WHERE alertId = ? AND time >= ? AND time <= ?) UNION (SELECT * FROM EvAlert WHERE alertId = ? AND time >= ? AND time <= ?)
Since no exceptions are thrown when emulate is turned off and my application runs correct, I'm assuming that MySQL supports statements of the above form but Connector/J does not recognize that fact.
From what I understand, and based on a brief test, the parens are not needed here, so I'm not sure why we have them (maybe for readability?). We originally had an ORDER BY, but to do an ORDER BY you need a set of parens around the whole union.
I mention the order by possiblity explicitly because you might be tempted to not code the driver to allow the pedantic case I showed because the parens are not needed for that statement, but they are needed for the ORDER BY, and AFAIK there is no other way to do it.
I can't suggest a code fix for this that would not involve a full SQL parser because I don't know well enough what the server does and does not support. You could just ignore any leading whitespace and parens and do the check, but I'm not sure if that will get you into trouble or not.
Still I wonder why 3.1.6 didn't work, since I thought 3.1.6 and earlier always tried to prepare whether the server supported it or not, unless you disabled server-side prepare completely?
Jason Winnebeck
Center for Integrated Manufacturing Studies
Rochester Institute of Technology, Rochester NY