Bug #17335 Unable to execute stored procedure from Java

• Submitted: 13 Feb 2006 1:50
• Modified: 21 Feb 2006 9:53
• Reporter: Prabhu Krishnan
• Status: Duplicate
• Category: Connector / J
• Severity: S1 (Critical)
• Version: MySQL Server 5.0
• OS: Windows (Windows XP)
• CPU Architecture: Any

[13 Feb 2006 1:50] Prabhu Krishnan

Description:
Database version : MySQL Server 5.0
JDBC driver         : mysql-connector-java-5.0.0-beta-bin.jar

The same problem exists for mysql-connector-java-3.1.11.jar also.
The stored procedure executes fine from the command line.

The stack trace is as follows

java.sql.SQLException: Driver requires declaration of procedure to either contain a '\nbegin' or '\n' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1343)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3663)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:508)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:403)
	at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4161)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4235)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4209)

How to repeat:
Create a Callable Statement and execute a stored procedure from Java.

[13 Feb 2006 14:53] Mark Matthews

What does the DDL for your stored procedure look like?

Since the server doesn't yet have INFORMATION_SCHEMA.parameters, the driver has to parse the declaration of the stored procedure to determine parameter metadata. This isn't a full-fledged SQL parser, so there are some limitations to what format of stored procedures it can successfully parse.

[14 Feb 2006 12:36] Johannes Jonsson

I get the problem when I run a stored procedure from another host than the one from which I created the stored procedure. And only with java and Connector/J (I use 3.1.12).
From the MySQL command line it works just fine.
Here is a simple procedure that causes the problem:

DELIMITER $$

DROP PROCEDURE IF EXISTS test $$
CREATE PROCEDURE test()
BEGIN
  SELECT true AS result;
END $$

DELIMITER ;

[15 Feb 2006 3:33] Prabhu Krishnan

The Stored procedure I am trying to call is as follows

DROP PROCEDURE IF EXISTS update_stockfetchstatus;
DELIMITER ?
CREATE PROCEDURE `update_stockfetchstatus`(
IN _symbol varchar(10)
)
BEGIN
     DECLARE maxDate date;
     SELECT MAX(quotedate) INTO maxDate FROM stocktable WHERE symbol = _symbol;

     if (select count(*) from stockfetchstatus where symbol = _symbol) then
             UPDATE stockfetchstatus SET lastquotedate=maxDate WHERE symbol=_symbol;
     ELSE
             INSERT INTO stockfetchstatus (symbol,lastquotedate) VALUES (_symbol, maxDate);
     end if;
END ?
DELIMITER ;

[21 Feb 2006 9:53] Valeriy Kravchuk

Looks like a duplicate of bug #10640.

[17 Feb 2007 4:48] hajunma hajunma

I also meet the bug.