java.sql.BatchUpdateException: Error converting data type float to numeric - Java + SQL Server

This error can come if you are inserting or updating a NUMERIC column in Microsoft SQL Server database from a Java Program using executeUpdate()method, I mean executing a  batch update query. It could also happen if you are calling a stored procedure and passing a float value corresponding to a NUMERIC column, and value happened to be out-of-range like generating "Arithmetic overflow error converting numeric to data type numeric" on the SQL Server end. For example, if your column is defined as NUMERIC (6,2) the maximum value it can represent is 9999.99, not 999999.99

If you are new to Microsoft SQL Server then you might not know that NUMERIC(6,2) means the total number of digits is 6 and out of them, only 2 are after the decimal point.

If you insert a float value like 10000.00 then it will throw "Arithmetic overflow error converting numeric to data type numeric" at the SQL Server and java.sql.BatchUpdateException: Error converting data type float to numeric at Java end.

Btw, if you are working on JDBC as Java developer then you not only need to know Java well but also the database you are connecting, for example, Microsoft SQL Server in this case. If you don't know about the database then it would take a long time to understand and resolve these kinds of errors, hence I suggest you to spend some time learning both Java and MSSQL.

If you need some guidance then The Complete Java Masterclass is the best course to learn Java. It's also the most up-to-date and recently updated for Java 10 and strongly recommend all Java developers and people learning Java to go through this course not just to learn but also to fill the gaps in your learning.

On Microsoft SQL Server side, there are many courses but you need to choose the right course depending upon your knowledge and experience with the database, for beginners, I really liked Microsoft SQL Server for Beginners by Brewster Knowlton on Udemy.




Troubleshooting

If you are using a stored procedure to insert and update data, which you should isolate database functionality in one place, then you can run the same proc for the input which is failing. How do you get the input which is failing?

Well, that's where your logging comes to rescue. Many developers make the mistake of just logging the error but not the input which is causing the error. You must always log the input or data which has caused the error otherwise you will spend countless hours debugging and troubleshooting. This is also the single most important logging tips for programmers.

If you are lucky that your application is also logging the values which are causing the problem then you can simply run the stored procedure with the same set of input, but if that's not the case then you need to adopt a strategy to reduce the problem.

For example, if this error is coming on a job which loads batch file then you can simply run the job with previous days file and if it succeeds then just compare the today's file with previous days file to find out new rows and try to find out any anomaly or out-of-range values there.

Another thing which helps is good knowledge of the database you are connecting. In that case, you can figure out from the original error message, printed as part of stack trace that what error database is throwing. That's why I suggest Java developer working with JDBC to learn the database they are connecting like MySQL, Oracle, or Microsoft SQL Server.

java.sql.BatchUpdateException: Error converting data type float to numeric


Solution

There are two solutions to this problem:

1. First, just filter out out-of-range values or increase the range by changing the data type of column like if your existing column is NUMERIC(5,2) then change it to NUMERIC(6,2) to accommodate additional value.

This is usually not the case because the range has been decided after consultation and driven by business logic. It's more of the case to find out which system is generating out-of-range values and fixing it.

2. Fix the root cause. I mean if you are loading file generated from other systems then ask them to fix the issue in their end and regenerate the file.


That's all about how to fix java.sql.BatchUpdateException: Error converting data type float to numeric in Java. Remember, NUMERIC(6,2) in SQL Server means total 6 digits and out of the 2 is after the decimal, but if you add more digit after decimal point then SQL Server will use rounding to keep the value inside specified precision for example 9999.999 will again cause the same error "java.sql.BatchUpdateException: Error converting data type float to numeric" because after rounding 9999.999 will become 10000.00 which is out-of-range for NUMERIC(6,2) data type.

Further Learning
The Complete Java Masterclass
JSP, Servlets, and JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 and 2
Java Platform: Working with Databases Using JDBC

Other common error which comes while connecting to database like Oracle, MySQL, SQL SERVER from Java program :

  • How to connect to MySQL database from Java Program [steps]
  • General Guide to solve java.lang.ClassNotFoundException in Java [guide]
  • java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory? [solution]
  • How to solve java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver in Java? [solution]
  • How to fix java.lang.ClassNotFoundException: org.postgresql.Driver error in Java? [solution]
  • java.sql.SQLException: No suitable driver found for jdbc:jtds:sqlserver [solution]
  • How to connect to SQL Server database using Eclipse [solution]
  • The java.sql.BatchUpdateException: String or binary data would be truncated [solution]
  • Java guide to connect Oracle 10g database using JDBC thin driver (guide)
  • Solving java.lang.classnotfoundexception sun.jdbc.odbc.jdbcodbcdriver [solution]
  • How to solve java.lang.ClassNotFoundException:org.Springframework.Web.Context.ContextLoaderListener [solution]
  • Fixing java.lang.ClassNotFoundException: org.postgresql.Driver [solution]
  • Solving java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver [solution]
  • Dealing with java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [fix]
  • How to solve java.lang.ClassNotFoundException: com.mysql.jdbc.Driver in Java MySQL? [solution]

Thanks for reading this article so far. If you like this article or if this has helped you to solve your issue then please share with your friends and colleagues. If you have any questions or feedback please drop a note.

P. S. - If you are new to JDBC and looking for a free course to start learning then you can also check JDBC In Simple Way: Overview, a free course on Udemy to start with.

No comments:

Post a Comment