[jira] [Created] (GROOVY-8174) Groovy.Sql+Oracle parameter substitution problem

[Thom DeCarlo (JIRA)]

Thom DeCarlo created GROOVY-8174:
------------------------------------

             Summary: Groovy.Sql+Oracle parameter substitution problem
                 Key: GROOVY-8174
                 URL: https://issues.apache.org/jira/browse/GROOVY-8174
             Project: Groovy
          Issue Type: Bug
          Components: SQL processing
    Affects Versions: 2.4.11
         Environment: Java 1.8.0_131, Groovy 2.4.11, Oracle ojdbc7 12.1.0.2.0
            Reporter: Thom DeCarlo


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

                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() [1].





--
This message was sent by Atlassian JIRA
(v6.3.15#6346)