Bug #9682 CallableStatement failes when Stored Procedure created with Decimal(m,d) type.
Submitted: 6 Apr 2005 15:29 Modified: 7 Apr 2005 18:49
Reporter: Chadwick Baatz Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.7 OS:Windows (XP)
Assigned to: Mark Matthews CPU Architecture:Any

[6 Apr 2005 15:29] Chadwick Baatz
Description:
When calling a stored procedure with datatype Decimal(m,d) (i.e. Decimal(18,0)) the Connector/J library throws the exception below.  After looking into the code it appears that the getCallStmtParameterTypes method does not expect that a parameter datatype could include a comma.  Therefore, any stored procedure created with a compound datatype: Float(M,D), Decimal(M,D), etc... will have this problem.

Error Message:
 java.sql.SQLException: Internal error when parsing callable statement metadata (missing parameter type)
    at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:6902)
Error in JDBC sampling: java.sql.SQLException: Internal error when parsing callable statement metadata (missing parameter type)
    at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:2653)
    at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:1042)
    at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:83)
    at com.mysql.jdbc.Connection.prepareCall(Connection.java:1248)
    at com.mysql.jdbc.Connection.prepareCall(Connection.java:1225)
    at TestCallableStatement.<init>(TestCallableStatement.java:34)
    at TestCallableStatement.main(TestCallableStatement.java:112)

How to repeat:
Create Stored Procedure:
 
DELIMITER //

DROP PROCEDURE IF EXISTS TEST_PROC//

CREATE PROCEDURE TEST_PROC(decimalParam DECIMAL(18,0))
BEGIN
  SELECT param_list FROM mysql.proc WHERE name='TEST_PROC';
END
//

Create Java / JDBC Program to call Stored Procedure

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.CallableStatement;

public class TestClass
{
  public TestClass()
  {
    Connection conn = null;
    CallableStatement cStmt = null;

    try
    {
      // Load class
      Class.forName("com.mysql.jdbc.Driver");
      
      // Modify URL with correct user, pass, host, and db name
      conn = DriverManager.getConnection("jdbc:mysql://<HOST>/<DB>?user=<USER>&password=<PASS>");

      cStmt = conn.prepareCall("Call TEST_PROC(?)");
      cStmt.setDouble(1, 18.0);
      
      cStmt.execute();
    }
    catch (Exception ex)
    {
      System.out.println("Error: " + ex);
      ex.printStackTrace();
    }
    finally
    {
      if (cStmt != null)
      {
        try
        {
          cStmt.close();
        }
        catch (SQLException err)
        {
          cStmt = null;
        }
      }

      try
      {
        if (conn != null && conn.isClosed() == false)
          conn.close();
      }
      catch (SQLException err)
      {
        conn = null;
      }
    }
  }

  public static void main(String[] args)
  {
    new TestClass();
  }
}

Suggested fix:
Work around:
  Don't use , in defining JDBC called stored procedure parameters. For example, DECIMAL(18) instead of DECIMAL(18,0).

Fix:
  On line 6836 of com.mysql.jdbc.DatabaseMetaData the statement for parsing the parameter list is "List parseList = StringUtils.split(parameterDef, ",", true);".    Since this is the problem this needs to be modified.  Either add some intelligence to the argument parsing or change how the parameter list is retrieved.
[7 Apr 2005 18:49] Mark Matthews
Fixed for 3.1.8. You can test the fix with the nightly snapshot from
http://downloads.mysql.com/snapshots.php after 00:00 GMT April 8th.

Thanks for your bug report!