This error can come if you are inserting or updating a NUMERIC column in the Microsoft SQL Server database from a Java Program using the 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 the 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 a 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 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 the 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, you can choose from this list of best Microsoft SQL Server courses online.
Well, that's where your logging comes to the 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 tip 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 that loads a batch file then you can simply run the job with the previous day's file, and if it succeeds then just compare today's file with the previous day's file to find out new rows and try to find out any anomaly or out-of-range values there.
Another thing that 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 the error database is throwing. That's why I suggest Java developers working with JDBC to learn the database they are connecting like MySQL, Oracle, or Microsoft SQL Server.
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 files generated from other systems then ask them to fix the issue on their end and regenerate the file.
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 a 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 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 the 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, you can choose from this list of best Microsoft SQL Server courses online.
Cause of java.sql.BatchUpdateException: Error converting data type float to numeric
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 the 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 tip 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 that loads a batch file then you can simply run the job with the previous day's file, and if it succeeds then just compare today's file with the previous day's file to find out new rows and try to find out any anomaly or out-of-range values there.
Another thing that 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 the error database is throwing. That's why I suggest Java developers working with JDBC to learn the database they are connecting like MySQL, Oracle, or Microsoft SQL Server.
Solution of java.sql.BatchUpdateException: Error converting data type float to numeric
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 files generated from other systems then ask them to fix the issue on their end and regenerate the file.
By the way, If you are new to JDBC and looking for a free course to start learning then you can also check these free JDBC courses to start with
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 a total of 6 digits and out of the 2 is after the decimal, but if you add more digits 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.
Other common error which comes while connecting to a 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 it with your friends and colleagues. If you have any questions or feedback please drop a note.
No comments:
Post a Comment
Feel free to comment, ask questions if you have any doubt.