[GROOVY-8174] Groovy.Sql+Oracle parameter substitution problem

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.4.11
• Fix Version/s: 2.4.12
• Component/s: SQL processing
• Environment: Java 1.8.0_131, Groovy 2.4.11, Oracle ojdbc7 12.1.0.2.0
• Assignee: Paul King
• Reporter: Thom DeCarlo
• Created: 03/May/17 19:29
• Updated: 06/Mar/18 23:14
• Resolved: 05/May/17 03:55

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

• links to: GitHub Pull Request #534