如何插入SQL Server的十进制列? [英] How to insert into SQL Server decimal column?

问题描述

我正在使用SQL Server的MS JDBC驱动程序，并尝试将值插入十进制(18,5)列.文档说十进制列映射到BigDecimal，所以我正在尝试这样做:

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();

在调用execute()时，出现此错误:

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

com.microsoft.sqlserver.jdbc.SQLServerException:将数据类型nvarchar转换为十进制时出错.

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);

如果我需要BigDecimal给我的额外精度怎么办?

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

更新:如果我要插入3.14，我当然不必担心这一点.如果我想插入一个实际上需要十进制精度的值怎么办?例如，小数点后30位?

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?

推荐答案

事实证明，这根本不是数据库问题.从varchar转换的错误基本上是说该值不适合DB字段.它必须从命令中发送的字符串转换为十进制值.

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.

问题实际上是新的BigDecimal(3.14)创建了一个BigDecimal，其值类似于3.140000000003457234987.这是因为双精度不能完全存储3.14.然后，当该值发送到数据库时，它将不会使用它，因为该列只有五个小数位.解决方法是对BigDecimal使用其他构造函数:new BigDecimal("3.14").这将精确地保持3.14.

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.

这篇关于如何插入SQL Server的十进制列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！