MySQL Bugs: #4024: prepareCall cannot call Store Procedure

Bug #4024	prepareCall cannot call Store Procedure
Submitted:	6 Jun 2004 17:54	Modified:	6 Jun 2004 22:35
Reporter:	Benny Tang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	4.1.1-alpha	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I have created a stored procedure SP_LOAD_CUSTOMER, SP_INSERT_CUSTOMER.
I found that when I execute conn.prepareCall to call these stored procedure,e.g.
cs = conn.prepareCall("{call SP_LOAD_CUSTOMER()}"
cs = conn.prepareCall("{call SP_INSERT_CUSTOMER(?,?)}"

An exception is thrown.
java.lang.StringIndexOutOfBoundsException: String index out of range: -9
	at java.lang.String.substring(String.java:1444)
	at com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.<init>(DatabaseMetaData.java:7031)
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:6615)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:2637)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:904)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:72)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:999)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:978)
	at com.bugsbiter.dao.AbstractDAO.getCallableStatement(AbstractDAO.java:67)
	at Test.main(Test.java:27)

Then I created other stored procedures and the same error occurs.

How to repeat:
using conn.prepareCall to call any valid stored procedure

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

This has been fixed in the source for a few months. Check out a nightly snapshot from http://downloads.mysql.com/snapshots.php

I just happen to hit the same bug with 5.0.21-pro-gpl-log using MySQL J/Connector 3.1.13, on java version "1.4.2_09", on Mac OS 
X 10.3.9!

I have a stored procedure:
PROCEDURE `mgl_search_alias_interactions_test`(IN objectIds VARCHAR(4096), IN searchAll INT, IN l INT, IN n INT)

and this in Java:

            cs = connection.prepareCall("{call " + SP_NAME_MGL_SEARCH_ALIAS_INTERACTIONS + "(?,?,?,?)}");
            cs.setString(1, commaSeparatedObjectIdsString);
            cs.setInt(2, searchAll ? 1 : 0);
            cs.setInt(3, 0);
            cs.setInt(4, 3000);

I then get:

Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: -103
	at java.lang.String.substring(String.java:1444)
	at com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.<init>(DatabaseMetaData.java:239)
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1450)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3640)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:560)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:450)
	at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4118)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4192)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4166)
	at com.vpharm.db.mastergenelist.MasterGeneList.getAliasInteractionsUsingSP(MasterGeneList.java:2684)

By looking at the source code and providing some clues on my procedure ...

My SP looks like:

PROCEDURE `mgl_search_alias_interactions_test`(IN objectIds VARCHAR(4096), IN searchAll INT, IN l INT, IN n INT)
sp_main: BEGIN

DROP TEMPORARY TABLE IF EXISTS interaction_found;

END sp_main

Note the label 'sp_main' is used before the tag BEGIN in the SP.

In the com.mysql.jdbc.DatabaseMetaData
private void getCallStmtParameterType method, the parsing of the parameter of the SP seems to be confused and instead of getting the type VARCHAR, INT, which it does well for the first 3 parameters, for the last one the typeinfo object gets this instead of INT:

'INT)
sp_main:BEGIN

DROPTEMPORARYTABLEIFEXISTSinteraction_found;

CREATETEMPORARYTABLE`interaction_found`(
`component_id`int(10)NOTNULL'

The label is not well supported I tried without it and the parsing went fine and so did the query.

19.1.4 BEGIN ... END Compound Statement

[begin_label:] BEGIN
    [statement_list]
END [end_label]

Stored routines may contain multiple statements, using a BEGIN ... END compound statement.

begin_label and end_label must be the same, if both are specified.