Hi Paul,
Thanks for the background info.
I brought in 2.4.12-SNAPSHOT via Gradle
('org.codehaus.groovy:groovy-all:2.4.12-SNAPSHOT') and verified that the PR#534
code is present (less-than sign on line 4151 of Sql.java). The same exception
still occurs because the driver is returning fewer parameters in the count
metadata than are actually present.
Regards,
Chad
From: Paul King <[email protected]>
Reply-To: "[email protected]" <[email protected]>,
"[email protected]" <[email protected]>
Date: Thursday, June 15, 2017 at 5:23 AM
To: "[email protected]" <[email protected]>
Subject: Re: Sql parameter count verification causing exceptions
Hi Chad,
Relying on consistent SQL driver behavior has certainly been problematic. Can
you confirm when using Groovy 2.4.12-SNAPSHOT (it has PR#534 already merged)
that your problem remains? We might have to water down the remaining check left
after GROOVY-8174 to be just a warning in the log. We'll likely start the
release process for 2.4.12 in about a week's time.
Just on the history, you are right that fixing GROOVY-8082 didn't require the
extra checks but we have had feedback previously indicating that the exception
messages users receive when getting the number of parameters wrong has been
inconsistent and confusing. Perhaps it was wishful thinking that these
inconsistencies could be fixed through using another part of the same api. We
certainly don't want Groovy's SQL processing to get in the way of getting work
done.
Cheers, Paul.
On Thu, Jun 15, 2017 at 5:37 AM, Chad Berchek
<[email protected]<mailto:[email protected]>> wrote:
In 2.4.9, groovy.sql.Sql started checking that the number of parameters passed
to query execution methods equals the number of parameters reported by the JDBC
driver through the PreparedStatement.getParameterMetaData().getParameterCount()
method in response to issue GROOVY-8082.
We are using the MS SQL Server JDBC driver, which returns incorrect parameter
counts for some queries. We have tried multiple driver versions (4.0.2206.100,
and the latest, 6.1.0.jre8). Of course, this is a driver bug, not a Groovy bug,
and I plan to file an issue there, but the end result is the same: we are now
stuck on Groovy 2.4.8.
I would like to raise the question of whether this check is even necessary and
whether Groovy Sql is really the appropriate place for such checking. The
original request which motivated the change, GROOVY-8082, has to do with
sending an empty parameter Map with an SQL string containing no parameters. It
doesn’t seem like resolving the original issue creates any need to also throw
an exception if the parameter count returned by the driver does not match the
parameter list size. I believe this is unnecessary because the driver itself
should throw an exception when the statement is executed if all the parameters
have not been set.
I noted that in GROOVY-8174 someone encountered a problem because the Oracle
driver appears to be adding a parameter. In pull request 534, evidently slated
for Groovy 2.4.12, the parameter count check was weakened as follows:
- if (metaData.getParameterCount() != params.size()) {
+ if (metaData.getParameterCount() < params.size()) {
throw new IllegalArgumentException("Found " +
metaData.getParameterCount() + " parameter placeholders but supplied with " +
params.size() + " parameters");
However, this will not solve our problem because in our case the SQL Server
driver is returning a parameter count that is too low. Here is an example of
one such query:
sql.execute('insert into municipality_fields (agency_ori, field_id)
select ?,? where not exists ' +
'(select * from municipality_fields where agency_ori=? and
field_id=?)',
agencyOri, field.id<http://field.id>, agencyOri,
field.id<http://field.id>)
This query causes the SQL Server driver to return a parameter count of 2 when
there are actually 4 parameters. Please note that without the parameter
metadata count check the statement actually executes fine. But with Groovy
2.4.11 we get:
java.lang.IllegalArgumentException: Found 2 parameter placeholders but supplied
with 4 parameters
at groovy.sql.Sql.setParameters(Sql.java:4116)
at groovy.sql.Sql.getPreparedStatement(Sql.java:4394)
at groovy.sql.Sql.getPreparedStatement(Sql.java:4482)
at groovy.sql.Sql.execute(Sql.java:2379)
at groovy.sql.Sql.execute(Sql.java:2486)
See also GROOVY-8128.
