com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field .

Spring JIRA | Yu Liu | 9 years ago
tip
Click on the to mark the solution that helps you, Samebug will learn from it.
As a community member, you’ll be rewarded for you help.
  1. 0

    We are seeing the following issue when using Spring's StoredProcedure class. Given the sample procedure and code below we get and Arithmetic overflow error if the total_rows parameter is defaulted and the proc returns >= 10 for total_rows. It appears that the default value can influence the scale of what can be returned. For example you can increase the default in factors of ten or set it to null and the below scenario works. This behaviour does not seem correct to us. CODE SNIPPET //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// import java.sql.Types; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import org.springframework.jdbc.core.SqlInOutParameter; import org.springframework.jdbc.object.StoredProcedure; /** * @author knoxl on Oct 14, 2008 @ 6:15:58 PM * @version $Revision:$, submitted by $Author:$ */ public class TestOutParamSet extends TestCase { private DataSource dataSource; protected void setUp() throws Exception { super.setUp(); dataSource = getDataSource(""); // NOTE Need to provide your own somehow } public void test() { SetId s = new SetId(dataSource); Map m = new HashMap(); m.put("total_rows", 0l); // NOTE null would work Map r = s.execute(m); System.out.println(r); } private class SetId extends StoredProcedure { private static final String SQL = "test_set_id"; public SetId(DataSource ds) { super(ds, SQL); declareParams(); compile(); } private void declareParams() { declareParameter(new SqlInOutParameter("total_rows", Types.NUMERIC)); } } } PROC //////////////////////////////////////////////////////////////////////////////////////////////////// CREATE PROC dbo.test_set_id ( @total_rows numeric(10) = 0 OUTPUT ) AS BEGIN SELECT @total_rows = 10 END ERROR ////////////////////////////////////////////////////////////////////////////////////////////////////// org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call test_set_id(?)}]; SQL state [ZZZZZ]; error code [247]; Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field . ; nested exception is com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field . at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982) at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113) at magnet.persistence.proc.TestOutParamSet.test(TestOutParamSet.java:39) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at junit.framework.TestCase.runTest(TestCase.java:168) at junit.framework.TestCase.runBare(TestCase.java:134) at junit.framework.TestResult$1.protect(TestResult.java:110) at junit.framework.TestResult.runProtected(TestResult.java:128) at junit.framework.TestResult.run(TestResult.java:113) at junit.framework.TestCase.run(TestCase.java:124) at junit.framework.TestSuite.runTest(TestSuite.java:232) at junit.framework.TestSuite.run(TestSuite.java:227) at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field .

    Spring JIRA | 9 years ago | Yu Liu
    com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field .
  2. 0

    We are seeing the following issue when using Spring's StoredProcedure class. Given the sample procedure and code below we get and Arithmetic overflow error if the total_rows parameter is defaulted and the proc returns >= 10 for total_rows. It appears that the default value can influence the scale of what can be returned. For example you can increase the default in factors of ten or set it to null and the below scenario works. This behaviour does not seem correct to us. CODE SNIPPET //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// import java.sql.Types; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import org.springframework.jdbc.core.SqlInOutParameter; import org.springframework.jdbc.object.StoredProcedure; /** * @author knoxl on Oct 14, 2008 @ 6:15:58 PM * @version $Revision:$, submitted by $Author:$ */ public class TestOutParamSet extends TestCase { private DataSource dataSource; protected void setUp() throws Exception { super.setUp(); dataSource = getDataSource(""); // NOTE Need to provide your own somehow } public void test() { SetId s = new SetId(dataSource); Map m = new HashMap(); m.put("total_rows", 0l); // NOTE null would work Map r = s.execute(m); System.out.println(r); } private class SetId extends StoredProcedure { private static final String SQL = "test_set_id"; public SetId(DataSource ds) { super(ds, SQL); declareParams(); compile(); } private void declareParams() { declareParameter(new SqlInOutParameter("total_rows", Types.NUMERIC)); } } } PROC //////////////////////////////////////////////////////////////////////////////////////////////////// CREATE PROC dbo.test_set_id ( @total_rows numeric(10) = 0 OUTPUT ) AS BEGIN SELECT @total_rows = 10 END ERROR ////////////////////////////////////////////////////////////////////////////////////////////////////// org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call test_set_id(?)}]; SQL state [ZZZZZ]; error code [247]; Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field . ; nested exception is com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field . at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982) at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113) at magnet.persistence.proc.TestOutParamSet.test(TestOutParamSet.java:39) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at junit.framework.TestCase.runTest(TestCase.java:168) at junit.framework.TestCase.runBare(TestCase.java:134) at junit.framework.TestResult$1.protect(TestResult.java:110) at junit.framework.TestResult.runProtected(TestResult.java:128) at junit.framework.TestResult.run(TestResult.java:113) at junit.framework.TestCase.run(TestCase.java:124) at junit.framework.TestSuite.runTest(TestSuite.java:232) at junit.framework.TestSuite.run(TestSuite.java:227) at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196) Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field .

    Spring JIRA | 9 years ago | Yu Liu
    com.sybase.jdbc3.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field .

    Root Cause Analysis

    1. com.sybase.jdbc3.jdbc.SybSQLException

      Arithmetic overflow during implicit conversion of NUMERIC value '10' to a NUMERIC field .

      at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate()
    2. Spring Framework
      StoredProcedure.execute
      1. org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
      2. org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
      3. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:949)
      4. org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:982)
      5. org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113)
      5 frames
    3. magnet.persistence.proc
      TestOutParamSet.test
      1. magnet.persistence.proc.TestOutParamSet.test(TestOutParamSet.java:39)
      1 frame
    4. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      4. java.lang.reflect.Method.invoke(Method.java:597)
      4 frames
    5. JUnit
      JUnit38ClassRunner.run
      1. junit.framework.TestCase.runTest(TestCase.java:168)
      2. junit.framework.TestCase.runBare(TestCase.java:134)
      3. junit.framework.TestResult$1.protect(TestResult.java:110)
      4. junit.framework.TestResult.runProtected(TestResult.java:128)
      5. junit.framework.TestResult.run(TestResult.java:113)
      6. junit.framework.TestCase.run(TestCase.java:124)
      7. junit.framework.TestSuite.runTest(TestSuite.java:232)
      8. junit.framework.TestSuite.run(TestSuite.java:227)
      9. org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81)
      9 frames
    6. JUnit4 Runner
      JUnit4TestReference.run
      1. org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
      1 frame
    7. JUnit3 Runner
      RemoteTestRunner.main
      1. org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
      2. org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
      3. org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
      4. org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
      5. org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
      5 frames