Bug #38367 Procedure Paramater Meta Data Error
Submitted: 25 Jul 2008 9:28 Modified: 10 Mar 2011 10:35
Reporter: Paul Mouton Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.6 OS:Microsoft Windows (MySql AB 5.0.56. )
Assigned to: Tony Bedford CPU Architecture:Any
Tags: nullable, Parameter, PROCEDURE

[25 Jul 2008 9:28] Paul Mouton
Description:
MySql Procedure parameters do accept a null arguments.
The JDBC Meta Data does not indicate that.

How to repeat:
The attached test case demonstrate.

Suggested fix:
none
[25 Jul 2008 9:30] Paul Mouton
MySql test case

Attachment: NulabablePatameterBugReport.java (application/text, text), 2.55 KiB.

[10 Apr 2009 11:52] Tonci Grgin
Verified as described using latest c/J 5.1 sources:
	try {
		this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS nullableParameterTest");
        	this.stmt.executeUpdate("CREATE PROCEDURE nullableParameterTest (OUT nfact VARCHAR(100), IN ccuenta VARCHAR(100),"
		+ "\nOUT ffact VARCHAR(100),"
		+ "\nOUT fdoc VARCHAR(100))"
		+ "\nBEGIN"
		+ "\nEND");
			
		DatabaseMetaData dbMeta = this.conn.getMetaData();
		this.rs = dbMeta.getProcedureColumns("test", null, "nullableParameterTest", null);
		while (this.rs.next()) {
		String columnName = this.rs.getString(4);
		Short columnType = new Short(this.rs.getShort(5));
		int columnTypeValue = columnType.intValue();
		Integer dataType = new Integer(this.rs.getInt(6));
		String columnTypeName = this.rs.getString(7);
		Integer dataLength = new Integer(this.rs.getInt(9));
		Short scale = new Short(this.rs.getShort(10));
		Short columnNullable = new Short(this.rs.getShort(12));
		assertTrue("Parameter " + columnName + " do not allow null arguments", columnNullable.intValue() == java.sql.DatabaseMetaData.procedureNullable);
		      }
    	} finally {
			closeMemberJDBCResources();
	}

produces:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

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
F
Time: 0,294
There was 1 failure:
1) testBug38367(testsuite.simple.TestBug38367)junit.framework.AssertionFailedError: Parameter nfact do not allow null arguments

Just for sake of completeness, any value used for useInformationSchema makes no difference.
[7 Feb 2011 15:25] Tonci Grgin
Proposed patch:
=== modified file 'src/com/mysql/jdbc/DatabaseMetaData.java'
--- src/com/mysql/jdbc/DatabaseMetaData.java	2011-02-07 10:14:26 +0000
+++ src/com/mysql/jdbc/DatabaseMetaData.java	2011-02-07 15:17:13 +0000
@@ -1694,7 +1694,7 @@
 
 	String returnsDefn = procedureDef.substring(declarationStart, endReturnsDef).trim();
 	TypeDescriptor returnDescriptor = new TypeDescriptor(
-			returnsDefn, null);
+			returnsDefn, "YES"); //null);
 
 	resultRows.add(convertTypeDescriptorToProcedureRow(
 		procNameAsBytes, procCatAsBytes, "", false, false, true,
@@ -1821,7 +1821,7 @@
 
 	String typeInfo = typeInfoBuf.toString();
 
-	typeDesc = new TypeDescriptor(typeInfo, null);
+	typeDesc = new TypeDescriptor(typeInfo, "YES"); //null);
 	} else {
 		throw SQLError.createSQLException(
 				"Internal error when parsing callable statement metadata (missing parameter type)",
[7 Feb 2011 17:17] Tonci Grgin
Pushed up to revision 1038. DatabaseMetaData.getProcedureColumns will set isNullable member to java.sql.DatabaseMetaData.procedureNullable now as opposed to just null.

Note to Tony:
In relation to binary_logging, on some older servers (5.1.10 <=) it *might* be necessary for users to set log_bin_trust_function_creators or add DETERMINISTIC, NO SQL ... as per http://dev.mysql.md/doc/refman/5.1-maria/en/stored-programs-logging.html, http://dev.mysql.md/doc/refman/5.1-maria/en/create-procedure.html to their SP's.
[10 Mar 2011 10:35] Tony Bedford
An entry has been added to the 5.1.15 changelog: 

        MySqlProcedure accepted null arguments as parameters, however 
        the JDBC meta data did not indicate that.