CODEWITHSUNDEEP
sql-serverjdbcbigdecimal
TimK
TimK

Reputation: 7747

How to insert into SQL Server decimal column?

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?

Upvotes: 3

Views: 11397

Answers (3)

TimK
TimK

Reputation: 7747

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.

Upvotes: 6

Dima
Dima

Reputation: 1761

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.

Upvotes: 0

James Johnson
James Johnson

Reputation: 46047

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.

Upvotes: 0

Related Questions