Bug #49831 connector/j CallableStatement w/varchar does not work w/executeBatch

• Submitted: 20 Dec 2009 2:29
• Modified: 30 Aug 2010 7:44
• Reporter: Charles Woerner
• Status: Can't repeat
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 5.1.10
• OS: MacOS
• CPU Architecture: Any
• Tags: CallableStatement, Connector/J, executeBatch, java, mac

[20 Dec 2009 2:29] Charles Woerner

Description:
When creating a CallableStatement via prepareCall() from a connection with "noAccessToProcedureBodies=true" any arguments added to a batch with type Types.VARCHAR using setObject(int parameterIndex, Object value, int type) are sent as null.  My understanding was that using parameterIndex along with the explicit setObject supplying a specific Types value would not require the driver to access the procedure body.

Everything works if I use setObject(String, Object, int) w/noAccessToProcedureBodies=false.

How to repeat:
First create a stored procedure and test table.

/*
create database test;

use test;

create table test (id bigint not not null primary key auto_increment, val varchar(32) not null) engine=innodb default charset utf8;

delimiter $
create procedure testProc(testval varchar(32)) 
BEGIN
insert into test (val) values (testval);
END; $

*/

Then execute the following java code using 5.1.10 Connector/J on Mac OS X.

DriverManager.registerDriver((Driver)Class.forName("com.mysql.jdbc.Driver").newInstance());

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?noAccessToProcedureBodies=false&jdbcCompliantTruncation=false&characterEncoding=utf8&useUnicode=yes", "xxx", "xxx");
    	
String val1 = "test-" + System.currentTimeMillis();
String val2 = "test2-" + System.currentTimeMillis();
    	    	    	
CallableStatement stmt = conn.prepareCall("{call testProc(?)}");

stmt.setObject(1, val1, Types.VARCHAR, 32);
stmt.addBatch();
		
stmt.setObject(1, val2, Types.CHAR, 32);
stmt.addBatch();

int[] updateCounts = stmt.executeBatch();

[28 Jan 2010 14:16] Tonci Grgin

Hi Charles and thanks for your report.

I don't quite get it so I tested both with noAccessToProcedureBodies=true and false. I also gathered you say "it works with SetObject" so I used setString...

Anyways, I see no problems here...

	if (versionMeetsMinimum(5, 0)) {
		Connection batchedConn = null;
		System.out.println("java.vm.version         : " + System.getProperty("java.vm.version"));
		System.out.println("java.vm.vendor          : " + System.getProperty("java.vm.vendor"));
		System.out.println("java.runtime.version    : " + System.getProperty("java.runtime.version"));
		System.out.println("os.name                 : " + System.getProperty("os.name"));
		System.out.println("os.version              : " + System.getProperty("os.version "));
		System.out.println("sun.management.compiler : " + System.getProperty("sun.management.compiler"));
			
		try {
			createTable("testBatchTable", "(field1 VARCHAR(32))");
			createProcedure("testBatch", "(IN foo VARCHAR(32))\n"
			+ "begin\n"
				+ "INSERT INTO testBatchTable VALUES (foo);\n"
			+ "end\n");
	
			batchedConn = getConnectionWithProps("rewriteBatchedStatements=true,profileSQL=true,noAccessToProcedureBodies=true,jdbcCompliantTruncation=false,characterEncoding=utf8,useUnicode=yes");
				
			StringBuffer outBuf = new StringBuffer();
			StandardLogger.bufferedLog = outBuf;
	
			CallableStatement storedProc = batchedConn.prepareCall("{call testBatch(?)}");
				
			try {
				int numBatches = 300;
				
				for (int i = 0; i < numBatches; i++) {
					storedProc.setString(1,  Integer.toString(i+1));
					storedProc.addBatch();
				}

				int[] counts = storedProc.executeBatch();
					
				assertEquals(numBatches, counts.length);
...
				}
			} finally {
				closeMemberJDBCResources();
					
				if (storedProc != null) {
					storedProc.close();
				}
			}
produces:
Connected to 5.1.31-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler

Thu Jan 28 15:04:59 CET 2010 INFO: Profiler Event: [QUERY] 	at testsuite.simple.TestBug49831.testBug49831(TestBug49831.java:66) duration: 2 ms, connection-id: 51, statement-id: 7, resultset-id: 0, message: CALL testBatch('1');CALL testBatch('2');CALL testBatch('3');CALL testBatch('4');CALL testBatch('5');CALL testBatch('6');CALL testBatch('7');CALL testBatch('8');CALL testBatch('9');CALL testBatch('10');CALL testBatch('11');CALL testBatch('12');CALL testBatch('13');CALL testBatch('14');... till 300

[1 Mar 2010 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".