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, agencyOri, 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.