为什么我收到“将数据类型 varchar 转换为数字时出错” Perl 中的浮点数？
我们正在将 Perl 中的值插入到 SQL Server 2005 数据库的 NUMERIC(19,5) 类型列中。只要绝对值是 0.0001 或更大，它就有效。然而，当这些值到达小数点后第五位时，Perl 开始以指数格式存储它们（-9e-05 而不是 -0.00009），然后我们得到错误SQL Server 中的“将数据类型 varchar 转换为数字时出错”。我们如何防止这种情况并使其正确插入小数值？
我们使用的是 perl v5.8.5、DBI 1.56、DBD::Sybase 1.07 和 SQL Server 2005。
代码大致如下，但我删除了无关的字段：
$invoice->{IL_UNITPRICE} = 0.09; # Actually comes from another database
$invoice->{IL_PRICEUNITCONV} = 0.001; # Actually comes from another database
$unitprice = $invoice->{IL_UNITPRICE} * -1 * $invoice->{IL_PRICEUNITCONV};
#$unitprice equals -9e-05 at this point.
$sth = $dbh->prepare('INSERT INTO foo ( bar ) VALUES ( ? )');
$sth->execute($unitprice);
上面的行失败并出现错误： DBD:: Sybase::st 执行失败：服务器消息号=8114 严重性=16 状态=5 行=2 服务器=baz 文本=将数据类型varchar 转换为数字时出错。
We are inserting values into a SQL Server 2005 database column of type NUMERIC(19,5) from Perl. As long as the absolute values are .0001 or greater, it is working. However, when the values go to the 5th decimal place, Perl starts storing them in exponential format (-9e-05 instead of -0.00009), and then we get the error "Error converting data type varchar to numeric" from SQL Server. How do we prevent that and cause it to properly insert small numeric values?
We are using perl v5.8.5, DBI 1.56, DBD::Sybase 1.07, and SQL Server 2005.
The code is roughly the following, but I have removed the extraneous fields:
$invoice->{IL_UNITPRICE} = 0.09; # Actually comes from another database
$invoice->{IL_PRICEUNITCONV} = 0.001; # Actually comes from another database
$unitprice = $invoice->{IL_UNITPRICE} * -1 * $invoice->{IL_PRICEUNITCONV};
#$unitprice equals -9e-05 at this point.
$sth = $dbh->prepare('INSERT INTO foo ( bar ) VALUES ( ? )');
$sth->execute($unitprice);
The above line fails with error: DBD::Sybase::st execute failed: Server message number=8114 severity=16 state=5 line=2 server=baz text=Error converting data type varchar to numeric.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
尝试
扩展一下——当您通过 DBi 接口传递本机 float/double 时，它会转换为字符串，如果您没有另外指定，则使用 Perl 的默认格式约定（由 C 编译器强加）。默认值为 6 位精度，如果数字超过此精度，则会以科学记数法呈现。
要获得定点格式，您必须使用 sprintf 明确请求。
Try
To expand a bit -- When you pass a native float/double through the DBi interface it gets converted to a string, which, if you don't specify otherwise, uses Perl's default formatting conventions (imposed by the C compiler). The default is 6 digits precision, and if the number has more than that it's rendered in scientific notation.
To get fixed-point format you have to ask for it explicitly, using sprintf.
实际上我认为使用 Math::Bigint 及其关联的 Math::BigFloat 也会解决这个问题。每当我使用浮点数或足够大的数字时，我都会使用这些模块，这样我就不必将 sprintf 到处乱扔，除非我真的想控制格式。
Actually I think using Math::Bigint and its associated Math::BigFloat would also solve this problem. Whenever I use floats or numbers of a sufficiently large size I use these modules so I don't have to throw sprintf's all over the place except when I really want to control the formatting.