How to insert into SQL Server decimal column?

Question

I'm using the MS JDBC driver for SQL Server, and trying to insert values into a decimal(18,5) column. The docs say that 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();

On the call to execute(), I get this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.

The driver does seem to be happy with doubles, so I can do this:

ps.setObject(1, 3.14);

How can I do an insert if I need the extra precision BigDecimal gives me?

Update: Of course I don't have to worry about this if I'm going to insert 3.14. What if I want to insert a value that actually needs decimal precision? Something with 30 decimal places, for example?

Answer 1

It turns out this wasn't a DB problem at all. The error about converting from varchar is basically saying that the value doesn't fit into the DB field. It must be converting from the string sent in the command into the decimal value.

The issue is actually that new BigDecimal(3.14) creates a BigDecimal with a value like 3.140000000003457234987. This is because a double can't store 3.14 exactly. Then when this value is sent to the DB, it won't take it because the column only has five decimal places. The fix is to use the other constructor for BigDecimal: new BigDecimal("3.14"). This will hold 3.14 exactly.