Why am I getting 'Error converting data type varchar to numeric' on a floating point number in Perl?

Question

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.

Answer 1

Try

$sth->execute(sprintf("%.6f",$unitprice));

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.

Answer 2

See sprintf.

Answer 3

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.