How to insert into decimal column in SQL Server?
I am using MS JDBC Driver for SQL Server and am trying to insert values into a decimal (18.5) column. The docs say decimal columns map to BigDecimal, so I'm trying to do this:
PreparedStatement ps = conn.prepareStatement("INSERT INTO [dbo].[AllTypesTable] ([decimal18_0Col]) VALUES (?)");
ps.setObject(1, new BigDecimal(3.14));
ps.execute();
In the execute () call, I get this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Error converting nvarchar data type to decimal.
The driver seems to be happy with the doubles, so I can do this:
ps.setObject(1, 3.14);
How can I do an insert if I need extra BigDecimal precision?
Update: Of course, I don't need to worry about this if I'm going to insert 3.14. What if I want to insert a value that really requires decimal precision? Something with 30 decimal places, for example?
source to share
It turns out that this is not a problem with the database at all. An error when converting from varchar basically means that the value does not fit into the DB field. It must convert from the string sent in the command to a decimal value.
The problem is that the new BigDecimal (3.14) creates a BigDecimal with a value of 3.140000000003457234987. This is because double cannot store 3.14 exactly. Then, when that value is sent to the DB, it doesn't accept it because the column only has five decimal places. The fix is to use a different constructor for BigDecimal: the new BigDecimal ("3.14"). It will be exactly 3.14.
source to share
The command from your code goes to the server as a string. Therefore, when MSSQL parses the command text, it successfully converts your 3.14 (from the second example) to a decimal value. Probably "new BigDecimal (3.14)" is converted to string as "3.14" or some other invalid value. Try to check your command statement after setting the value.
source to share