MySQL Bugs: #26524: CallableStatement throws NumberFormatException

Bug #26524	CallableStatement throws NumberFormatException
Submitted:	21 Feb 2007 13:16	Modified:	21 Mar 2007 19:12
Reporter:	Radek www.zolv.pl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Windows (WinXPsp2)
Assigned to:		CPU Architecture:	Any
Tags:	jdbc callableStatement numberFormatException

Description:
Configuration:

Apache Tomcat/5.5.17
MySQL 5.0.27-community-nt
Connector/J: official stable and snapshot from 2007.02.21

I trie to invoke stored procedure from servlet deployed on tomcat server. Getting connection as datasource works ok, executing statements also, but there is error during prepare a callable statement that throws an exception:

java.lang.NumberFormatException: For input string: " 9 "
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.<init>(Unknown Source)
	at com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.<init>(DatabaseMetaData.java:282)
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1772)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3971)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:702)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
	at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4519)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4593)
	at com.mysql.jdbc.Connection.prepareCall(Connection.java:4567)
	at org.apache.commons.dbcp.DelegatingConnection.prepareCall(DelegatingConnection.java:212)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareCall(PoolingDataSource.java:268)
	at com.bitcomp.datawarehouse.webservice.DWService.getReport(DWService.java:88)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.axis2.receivers.RawXMLINOutMessageReceiver.invokeBusinessLogic(RawXMLINOutMessageReceiver.java:100)
	at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.receive(AbstractInOutSyncMessageReceiver.java:37)
	at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:454)
	at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:284)
	at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:136)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
	at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
	at java.lang.Thread.run(Unknown Source)

How to repeat:
Apache Tomcat/5.5.17
MySQL 5.0.27-community-nt
Connector/J: official stable and snapshot from 2007.02.21

tomcat's server.xml resource:
...
      <Resource
      name="jdbc/xyz"
      type="javax.sql.DataSource"
      driverClassName="com.mysql.jdbc.Driver"
      factory="org.apache.commons.dbcp.BasicDataSourceFactory"
      username="username"
      password="password"
      validationQuery="select 1"
      testOnBorrow="true"
      loginTimeout="10"
      maxWait="5000"
      maxActive="-1"
      removeAbandoned="false"
      logAbandoned="true"
      url="jdbc:mysql://server:3306/databaseName" />
...

procedure in database:

CREATE PROCEDURE someProcedure( 
    in _par1 varchar( 9 ) ,
    in _par2 varchar( 4 )
)
BEGIN
  select 1;
END$

in servlet after getting connection from pool (no tricks and magic):

...
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup( "java:comp/env" );
DataSource ds = (DataSource) envCtx.lookup( "jdbc/xyz" );
conn = ds.getConnection();
CallableStatement cs = conn.prepareCall( "{call someProcedure(?,?)}" );
...

this line throws an exception described above

I have tried the same "as simple as possible" code (changed ONLY datasource, I didn't change the sourcecode) on MSSQL server, and it works. but when I change to MySQL - it fails.

I have found what is the problem, and I think it is a bug:
problem is because some parser treat parameters of varchar(eg) eg:
  create procedure procName (
    paramName varchar( 5 ) )
  ...
so parser treat it like " 5 " not like "5"
problem probably is with other types (e.g. int(6)).
But this is stupid as I know. white spaces shouldn;t be treated special in this case - "why?"

The "why" is due to the JDK. For some reason in JDK-5, Sun's class libraries started not liking whitespace when parsing numbers. Prior to JDK-5, they ignored it.

We'll have to patch things up to trim it out ourselves.

This is fixed for 5.0.6, see latest nightly snapshot at http://downloads.mysql.com/snapshots.php#connector-j to test it.