Uploaded image for project: 'Groovy'
  1. Groovy
  2. GROOVY-8174

Groovy.Sql+Oracle parameter substitution problem

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.4.11
    • Fix Version/s: 2.4.12
    • Component/s: SQL processing
    • Labels:
      None
    • Environment:
      Java 1.8.0_131, Groovy 2.4.11, Oracle ojdbc7 12.1.0.2.0

      Description

      I'm using the groovy.sql libraries inside of a Java program and having trouble with parameter substitution. My code looks like this:

                      def mdds = Sql.newInstance(...)
      
      		Map<String, Object>params = new HashMap<String, Object>();
      		params.clear();
      		params.put("source_system_id", Integer.valueOf(6));
      		params.put("rec_loc_txt", "Test" + randomNum.toString());
      		params.put("created_dt_tm", new Timestamp(System.currentTimeMillis()));
      		
      		String mdSqlString = "INSERT INTO MD_CATALOG " +
      		                  "      (SOURCE_SYSTEM_ID, REC_LOC_TXT, CREATED_DT_TM)" + 
      		                  "      VALUES" + 
      		                  "      (:source_system_id, :rec_loc_txt, :created_dt_tm)";
      
      		try {
      			List<List<Object>> keys = mdds.executeInsert(params, mdSqlString);
      		} catch (SQLException e) {
      			// TODO Auto-generated catch block
      			e.printStackTrace();
      		}
      

      But, when it runs, I get an exception thrown that says:

      Exception in thread "main" java.lang.IllegalArgumentException: Found 4 parameter placeholders but supplied with 3 parameters
      	at groovy.sql.Sql.setParameters(Sql.java:4116)
      	at groovy.sql.Sql.getPreparedStatement(Sql.java:4394)
      	at groovy.sql.Sql.executeInsert(Sql.java:2610)
      	at groovy.sql.Sql.executeInsert(Sql.java:2674)
      	at org.mitre.mac.Main.main(Main.java:70)
      

      But, I only see 3 placeholders for my 3 parameters.

      Via email, John Wagenleitner replied:

      I did a quick test with Groovy 2.4.11 and Oracle 11.2.0.4 (ojdbc6.jar 11.2.0.4 and ojdbc7.jar 12.1.0.2.0) and get the same error. From what I am seeing Oracle changes the sql to:

      INSERT INTO MD_CATALOG (SOURCE_SYSTEM_ID, REC_LOC_TXT, CREATED_DT_TM) VALUES (?, ?, ?) RETURNING ROWID INTO ?

      adding the extra bind parameter for ROWID and this is reflected in the call to metaData.getParameterCount()

        Issue Links

          Activity

          Hide
          pixelpshr Thom DeCarlo added a comment -

          The same error is produced when using the

          public List<GroovyRowResult> executeInsert(Map params,
          String sql,
          List<String> keyColumnNames)
          throws SQLException

          version and explicitly providing the autoincrementing key column name.

          Show
          pixelpshr Thom DeCarlo added a comment - The same error is produced when using the public List<GroovyRowResult> executeInsert(Map params, String sql, List<String> keyColumnNames) throws SQLException version and explicitly providing the autoincrementing key column name.
          Hide
          paulk Paul King added a comment -

          Looks like we can't win using parameter count metadata. I think we'll just have to give a warning in the log if the counts don't match.

          Show
          paulk Paul King added a comment - Looks like we can't win using parameter count metadata. I think we'll just have to give a warning in the log if the counts don't match.
          Hide
          githubbot ASF GitHub Bot added a comment -

          GitHub user paulk-asert opened a pull request:

          https://github.com/apache/groovy/pull/534

          GROOVY-8174: Groovy.Sql+Oracle parameter substitution problem

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/paulk-asert/groovy groovy8174

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/groovy/pull/534.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #534


          commit 0efbd83cf46678cc7dacc365634af24db0affb96
          Author: paulk <paulk@asert.com.au>
          Date: 2017-05-04T00:53:41Z

          GROOVY-8174: Groovy.Sql+Oracle parameter substitution problem


          Show
          githubbot ASF GitHub Bot added a comment - GitHub user paulk-asert opened a pull request: https://github.com/apache/groovy/pull/534 GROOVY-8174 : Groovy.Sql+Oracle parameter substitution problem You can merge this pull request into a Git repository by running: $ git pull https://github.com/paulk-asert/groovy groovy8174 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/groovy/pull/534.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #534 commit 0efbd83cf46678cc7dacc365634af24db0affb96 Author: paulk <paulk@asert.com.au> Date: 2017-05-04T00:53:41Z GROOVY-8174 : Groovy.Sql+Oracle parameter substitution problem
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user asfgit closed the pull request at:

          https://github.com/apache/groovy/pull/534

          Show
          githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/groovy/pull/534
          Hide
          paulk Paul King added a comment -

          Proposed PR merged. Thanks for spotting the issue and thanks for checking John.

          Show
          paulk Paul King added a comment - Proposed PR merged. Thanks for spotting the issue and thanks for checking John.
          Hide
          paulk Paul King added a comment -

          The fix was changed to remove throwing the exception in the one remaining case that we thought worked with all drivers - since some commonly used drivers are buggy for the functionality we were using. We now just give a log warning message if the number of declared and supplied parameters don't match. Since we haven't yet released 2.4.12, the fix version remains the same.

          Show
          paulk Paul King added a comment - The fix was changed to remove throwing the exception in the one remaining case that we thought worked with all drivers - since some commonly used drivers are buggy for the functionality we were using. We now just give a log warning message if the number of declared and supplied parameters don't match. Since we haven't yet released 2.4.12, the fix version remains the same.

            People

            • Assignee:
              paulk Paul King
              Reporter:
              pixelpshr Thom DeCarlo
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development