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

Question

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).

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)

Any assistance would be greatly appreciated. Thanks!

Java Code:

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

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)

code all working fine... did u try any other Stored procedure other than this from Java.? I wana check whether it s problem with Mysql Java communication.. — Akshay Joy
– Akshay Joy, Commented Mar 24, 2013 at 18:00
Please share the Complete Stored Procedure. I wana check in my enev. — Akshay Joy
– Akshay Joy, Commented Mar 24, 2013 at 18:12
I receive the same error using other (complete) stored procedures. And I'm using the same credentials for procedure creation as within my tests and the application. I've added a snippet of the stored procedure used in this example. — Sean3z
– Sean3z, Commented Mar 24, 2013 at 18:28
then it should be the Problem with Mysql env. No stored procedure gives the complete Result. — Akshay Joy
– Akshay Joy, Commented Mar 24, 2013 at 18:31
Thank Akshay but, I'm not sure that I follow. This particular stored procedure doesn't return a Result nor does the application expect one. Do all CallableStatement's expect data to be returned? — Sean3z
– Sean3z, Commented Mar 24, 2013 at 18:38

Sean3z · Accepted Answer · 2013-03-27 12:20:43Z

0

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.

answered Mar 27, 2013 at 12:20

Sean3z

3,7456 gold badges28 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2971415 · Accepted Answer · 2015-03-09 15:57:25Z

0

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 param2 varchar(255)

answered Mar 9, 2015 at 15:57

user2971415

Comments

lymenglei · Accepted Answer · 2016-07-16 08:50:30Z

0

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

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

answered Jul 16, 2016 at 8:50

lymenglei

11 bronze badge

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related