Description:
Using a Sproc to insert data into table. Calling from Java using mysql-connector-java-5.1.6-bin.jar
Java class uses CallableStatement, and sets parameters to sproc. On prepareCall line NumberFormatException thrown.
This exception did not occur with same Java code and sproc code using previous versions of mysql-connector.
In the sproc simple insert statement with field and value list. One of the fields of the table in the insert statement was named "notes", it was varchar(255), allow nulls, default to empty string.
Determined that NumberFormatException thrown on prepareCall line when insert statement in sproc contained a field named "notes"
Found work-around to resolve issue, by surrounding "notes" in field list and value list with field delimiter: `
Here is a copy of the stack trace
2008-04-14 14:11:06,979 WARN [org.jboss.resource.connectionmanager.TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@607398[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@a76cce handles=1 lastUse=1208200266921 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@1f5fcb6 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@a23bca xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@452e82 txSync=null]
java.lang.NumberFormatException: For input string: "20)
DETERMINISTIC
begin
declare retval bigint;
insert into packlist (vmi_fulfillment_entry_id"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.Integer.parseInt(Integer.java:456)
at java.lang.Integer.valueOf(Integer.java:553)
at com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.<init>(DatabaseMetaData.java:251)
at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1641)
at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4109)
at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4046)
at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:808)
at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:612)
at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:510)
at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:3856)
at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3927)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareCall(BaseWrapperManagedConnection.java:379)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareCall(BaseWrapperManagedConnection.java:374)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareCall(WrappedConnection.java:269)
How to repeat:
create table with at least one field called "notes"
create sproc to insert data into that table. insert statement refer to notes field. pass parm to sproc to insert into notes field. call parm "notes".
create Java class, use mysql-connector-5.1.5 or 5.1.6 to connecto to mysql database with table
Use CallableStatement. call connection.prepareCall({? = call sproc(?,?);
Receive NumberFormatException.
Place ticks (`) around field name "notes" in field list and value list.
Run test again. This time no exception.