Bug #41269 DatabaseMetadata.getProcedureColumns() returns wrong value for column length

• Submitted: 5 Dec 2008 19:28
• Modified: 23 Jun 2009 10:07
• Reporter: David Van Couvering
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.6
• OS: Any
• Assigned to: Jess Balint
• CPU Architecture: Any

[5 Dec 2008 19:28] David Van Couvering

Description:
When you call DatabaesMetaData.getProcedureColumns(), the value for "LENGTH" is *always* 65535, regardless of the column type (fixed or variable) or the actual length of the column.

If, however, you get the "PRECISION" value, this is correct for both fixed and variable length columns.

According to the documentation (http://tinyurl.com/426cuu): "LENGTH int => length in bytes of data "

How to repeat:
        stmt.executeUpdate("CREATE TABLE bar (" +
                "`i+d` INT NOT NULL PRIMARY KEY, " +
                "foo_id INT NOT NULL, " +
                "bar_name  VARCHAR(16), " +
                "bar_digit DECIMAL(12,2) NOT NULL, " +
                "FOREIGN KEY (foo_id) REFERENCES foo(id)) Engine=InnoDB");
        stmt.executeUpdate("CREATE PROCEDURE barproc(IN param1 INT, OUT result VARCHAR(255), INOUT param2 DECIMAL(5,2)) " +
                "BEGIN SELECT * from bar; END");

then

conn.getMetaData().getProcedureColumns() for the barproc stored procedure.  The value of "LENGTH" for 'result' is 65535, as it is for 'param1' and 'param2'

[8 Dec 2008 22:06] Jess Balint

=== modified file 'src/com/mysql/jdbc/DatabaseMetaData.java'
--- src/com/mysql/jdbc/DatabaseMetaData.java    2008-09-09 18:49:12 +0000
+++ src/com/mysql/jdbc/DatabaseMetaData.java    2008-12-08 22:02:27 +0000
@@ -858,7 +858,7 @@
                row[5] = s2b(Short.toString(typeDesc.dataType)); // DATA_TYPE
                row[6] = s2b(typeDesc.typeName); // TYPE_NAME
                row[7] = typeDesc.columnSize == null ? null : s2b(typeDesc.columnSize.toString()); // PRECISION
-               row[8] = s2b(Integer.toString(typeDesc.bufferLength)); // LENGTH
+               row[8] = row[7]; // LENGTH
                row[9] = typeDesc.decimalDigits == null ? null : s2b(typeDesc.decimalDigits.toString()); // SCALE
                row[10] = s2b(Integer.toString(typeDesc.numPrecRadix)); // RADIX
                // Map 'column****' to 'procedure****'

=== modified file 'src/testsuite/regression/MetaDataRegressionTest.java'
--- src/testsuite/regression/MetaDataRegressionTest.java        2008-09-09 18:49:12 +0000
+++ src/testsuite/regression/MetaDataRegressionTest.java        2008-12-08 22:04:37 +0000
@@ -2250,6 +2250,22 @@
                }
        }

+       public void testBug41269() throws Exception {
+               createProcedure("bug41269",
+                               "(in param1 int, out result varchar(197)) BEGIN select 1, ''; END");
+               try {
+                       ResultSet procMD = this.conn.getMetaData()
+                                                       .getProcedureColumns(null, null, "bug41269", "%");
+                       assertTrue(procMD.next());
+                       assertEquals("Int param length", 10, procMD.getInt(9));
+                       assertTrue(procMD.next());
+                       assertEquals("String param length", 197, procMD.getInt(9));
+                       assertFalse(procMD.next());
+               } finally {
+                       closeMemberJDBCResources();
+               }
+       }
+
        public void testNoSystemTablesReturned() throws Exception {
                if (!versionMeetsMinimum(5, 0)) {
                        return; // no information schema

[8 Dec 2008 22:45] David Van Couvering

Wow, fast work, thanks!

[2 Jun 2009 5:59] Jess Balint

Pushed for release in 5.1.8

[23 Jun 2009 10:07] Tony Bedford

An entry was added to the 5.1.8 changelog:

When DatabaseMetaData.getProcedureColumns() was called, the value for LENGTH was always returned as 65535, regardless of the column type (fixed or variable) or the actual length of the column.

However, if you obtained the PRECISION value, this was correct for both fixed and variable length columns.

[2 Jul 2010 11:13] Tonci Grgin

This is not enough. Check Bogdan's patch in Bug#51712 too.