Java SQL 异常：解析可调用语句时出错 <缺少参数类型>

Java SQL Exception: Error when parsing callable statement <missing parameter type>

提问by Sean3z

I'm trying to call a MySQL stored procedure from within a Java application. However I receive the following error: java.sql.SQLException: Internal error when parsing callable statement metadata (missing parameter type).

我正在尝试从 Java 应用程序中调用 MySQL 存储过程。但是我收到以下错误：java.sql.SQLException: Internal error when parsing callable statement metadata (missing parameter type).

The stored procedure functions correctly when called from outside the application. Also, the application does successfully connect to the database.

从应用程序外部调用时，存储过程可以正常运行。此外，该应用程序确实成功连接到数据库。

• MySQL Server: 5.5.27
• java version "1.7.0_15"
• Java(TM) SE Runtime Environment (build 1.7.0_15-b03)
• Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

• MySQL 服务器：5.5.27
• java版本“1.7.0_15”
• Java(TM) SE 运行时环境（构建 1.7.0_15-b03）
• Java HotSpot(TM) 64 位服务器 VM（构建 23.7-b01，混合模式）

Any assistance would be greatly appreciated. Thanks!

任何帮助将不胜感激。谢谢！

Java Code:

Java代码：

protected Connection mysql;
...
try {
    CallableStatement query = this.mysql.prepareCall("{call spInsertUser(?,?,?,?,?,?,?)}");
    query.setString(1, "user");
    query.setString(2, "password");
    query.setString(3, "test");
    query.setString(4, "test");
    query.setString(5, "test");
    query.setString(6, "test");
    query.setString(7, "test");
    query.execute();
    query.close();
    this.mysql.close();
}
catch (SQLException e) {
    e.printStackTrace();
}

MySQL store procedure

MySQL存储过程

CREATE DEFINER = `root`@`localhost` PROCEDURE `spInsertUser`(IN `nick` varchar(30),IN `pass` varchar(255),IN `param1` varchar(255),IN `param2`varchar(255),IN `param3` varchar(22), IN `param4` varchar(255), IN `param5` varchar(255))
    SQL SECURITY INVOKER
BEGIN
  DECLARE uid INT DEFAULT 0;

  # check if nick exists
  DECLARE userExists INT DEFAULT 0;
  SELECT count(*) INTO userExists FROM users WHERE nick = nick;
  IF (userExists > 0) THEN
    # do nothing if nick already exists
    # SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User already exists!';
  ELSE
    # if nick doesn't exist, insert it
    INSERT INTO users (nick, pass, param1, created, active, param2) VALUES (nick, pass, param1, UNIX_TIMESTAMP(), 0, param2);
    SELECT LAST_INSERT_ID() INTO uid;

    IF (uid IS NOT NULL AND uid > 0) THEN
      # create email
      INSERT INTO emails (uid, email) VALUES (uid, email);
    END IF;
  END IF;
END

Error:

错误：

java.sql.SQLException: Internal error when parsing callable statement metadata (missing parameter type)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1836)
at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4305)
at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4146)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:856)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:629)
at com.mysql.jdbc.JDBC4CallableStatement.<init>(JDBC4CallableStatement.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:523)
at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:4313)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4397)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:4371)
at pack.File.method(File.java:65)

采纳答案by Sean3z

While it's certainly not the most ideal solution, I DROP'd then redefined the (same) stored procedure to circumvent this error. It seems the meta data was some how corrupt.

虽然这肯定不是最理想的解决方案，但我还是DROP重新定义了（相同的）存储过程来规避这个错误。元数据似乎有些损坏。

回答by jsaludas

In my case, I found that when the parameter is defined with no blank space between name and type, it raises the error

就我而言，我发现当参数定义为名称和类型之间没有空格时，它会引发错误

CREATE DEFINER = `root`@`localhost` PROCEDURE `spInsertUser`(IN `nick` varchar(30),IN `pass` varchar(255),IN `param1` varchar(255),IN `param2`varchar(255),IN `param3` varchar(22), IN `param4` varchar(255), IN `param5` varchar(255))

param2varchar(255) must be param2varchar(255)

param2varchar(255) 必须是param2varchar(255)

回答by lymenglei

you need to check your sql codes,check about the blank space.

你需要检查你的sql代码，检查空格。

CREATE DEFINER=`root`@`localhost` PROCEDURE `verifyregister`(IN `cdkey` varchar(16), IN `ingsid` int, IN `inusername` varchar(128) character set utf8, OUT `retcode` int)