groovy-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chad Berchek <ChadBerc...@carfax.com>
Subject Re: Sql parameter count verification causing exceptions
Date Thu, 15 Jun 2017 20:00:05 GMT
Using 2.4.12-20170615.182042-19 works! I see the log message printed and no exception is thrown.

Just FYI, I opened issues on the SQL Server JDBC driver here https://github.com/Microsoft/mssql-jdbc/issues/343
and here https://github.com/Microsoft/mssql-jdbc/issues/344.

Thank you,
Chad

From: Paul King <paulk@asert.com.au>
Reply-To: "users@groovy.apache.org" <users@groovy.apache.org>, "paulk@asert.com.au"
<paulk@asert.com.au>
Date: Thursday, June 15, 2017 at 11:13 AM
To: "users@groovy.apache.org" <users@groovy.apache.org>
Subject: Re: Sql parameter count verification causing exceptions

I have removed the exception altogether, now you'll just get a log warning if the parameter
count doesn't seem to match. Once the CI server has built the next 2.4.12-SNAPSHOT, can you
check again.

Thanks, Paul.


On Thu, Jun 15, 2017 at 10:57 PM, Chad Berchek <ChadBerchek@carfax.com<mailto:ChadBerchek@carfax.com>>
wrote:
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 <paulk@asert.com.au<mailto:paulk@asert.com.au>>
Reply-To: "users@groovy.apache.org<mailto:users@groovy.apache.org>" <users@groovy.apache.org<mailto:users@groovy.apache.org>>,
"paulk@asert.com.au<mailto:paulk@asert.com.au>" <paulk@asert.com.au<mailto:paulk@asert.com.au>>
Date: Thursday, June 15, 2017 at 5:23 AM
To: "users@groovy.apache.org<mailto:users@groovy.apache.org>" <users@groovy.apache.org<mailto:users@groovy.apache.org>>
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 <ChadBerchek@carfax.com<mailto:ChadBerchek@carfax.com>>
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.


Mime
View raw message