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?

What happens if you use setBigDecimal instead? BTW: This is clearly a bug, the JDBC standard (JDBC 4.1, appendix B) explicitly specifies that a JDBC driver must accept BigDecimal for numeric fields, bit, boolean, char, varchar and longvarchar. — Mark Rotteveel
– Mark Rotteveel, Commented Apr 10, 2012 at 17:20

TimK · Accepted Answer · 2012-04-11 11:13:02Z

6

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.

answered Apr 11, 2012 at 11:13

TimK

7,90712 gold badges43 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Icegras Over a year ago

Actually you can also fix this by setting a scale(number of decimals) on your BigDecimal: BigDecimal amount = new BigDecimal(117.56d); amount = amount.setScale(8, BigDecimal.ROUND_HALF_UP);

James Johnson · Accepted Answer · 2012-04-10 13:44:06Z

0

As long as the datatype of the column column is decimal, you shouldn't have to worry about converting it in code. Try casting it back to an object and send it in that way.

answered Apr 10, 2012 at 13:44

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

2 Comments

TimK Over a year ago

I'm not sure what you mean by casting it back to an Object.

James Johnson Over a year ago

I'm not all that familiar with Java, but in C# you would cast it back to an object like this: (object)value.

Dima · Accepted Answer · 2012-04-10 13:48:21Z

0

Command from your code to server goes as a string. So when MSSQL parses command text, it successfully converts your 3.14 (from second example) to decimal value. Probably "new BigDecimal(3.14)" is converted to string as "3,14" or some other non-valid value. Try to check your command statement after setting the value.

answered Apr 10, 2012 at 13:48

Dima

1,76115 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to insert into SQL Server decimal column?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related