Sean3z Sean3z - 4 months ago 29
SQL Question

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

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)

Answer

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.