Bug #50502 J/Connector doesn't allow to insert NULL as a field value with setNull(int, int)

• Submitted: 21 Jan 2010 10:44
• Modified: 26 Jan 2010 12:19
• Reporter: Alexey Malev
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: 5.1.10
• OS: Any
• CPU Architecture: Any
• Tags: connector, null, string

[21 Jan 2010 10:44] Alexey Malev

Description:
MySQL JDBC Driver doesn't allow to set NULL as a field value using preparedStatement.setNull(int, int) method - field value after executing code in "how to repeat" section is "" instead of NULL. Field type is varchar, null as value is allowed ofc.

JDBC connection URL is: jdbc:mysql://some.mysql.server:3306/schema_name?autoReconnect=true&useUnicode=true&characterEncoding=utf8&noAccessToProcedureBodies=true

MySQL server version is 5.1.42, running on Fedora Core 12 x86_64.

How to repeat:
//obtain a connection

		CallableStatement userUpdateStatement = connection.prepareCall("{call update_user(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

//set other fields

			if (values[4] == null)
			{
				userUpdateStatement.setNull(6, Types.NULL);
			}
			else
			{
				userUpdateStatement.setString(6, (String) values[4]);
			}
//set other fields
userUpdateStatement.execute();

[22 Jan 2010 13:26] Alexey Malev

More specifically, the situation described above seems to happen only when target procedure parameter is varchar. When it is int (for example), method works fine.

[26 Jan 2010 12:19] Alexey Malev

Not a bug, I just used stored procedure improperly.