Storing BigDecimal in SQL Server

[cliodhna.hurst]

Hi,

I need to store very small numbers in my database, generally something
like 1.2516E-128. I'm using BigDecimal is Java to manipulate these
numbers however when storing them in MS SQL server as a double they are
converted to 0. SQL Server will only allow a a double with a scale of
maximum size 18.

I've also tried to use the Float object to store and manipulate the
numbers in Java instead of BigDecimal. Once they are smaller than E-47
however, Java stores them as 0 also.

Does anyone know of a way to manipulate these numbers in Java and then
store them in MS SQL Server?

Thanks
Cli

 

[Dag Sunde]

Hi,

I need to store very small numbers in my database, generally something
like 1.2516E-128. I'm using BigDecimal is Java to manipulate these
numbers however when storing them in MS SQL server as a double they are
converted to 0. SQL Server will only allow a a double with a scale of
maximum size 18.

I've also tried to use the Float object to store and manipulate the
numbers in Java instead of BigDecimal. Once they are smaller than E-47
however, Java stores them as 0 also.

Does anyone know of a way to manipulate these numbers in Java and then
store them in MS SQL Server?

Have you tried the SQL type 'decimal(precision, scale)'?

precicion: total number of digits
scale : number of digits after the decimal-point

ConlumnName DECIMAL(130, 128) NOT NULL ...

Note! I never tried DECIMAL with this large scale, but it might be
worth looking into...

 

[cliodhna.hurst]

Hi Dag,

The maximum precision allowed by MS SQL on a decimal is 38 and the max
scale is 18. Have you used a decimal with a larger precision and scale
than this?

Cli

 

[Dag Sunde]

Hi Dag,

The maximum precision allowed by MS SQL on a decimal is 38 and the max
scale is 18. Have you used a decimal with a larger precision and scale
than this?

No, sorry... I said I hadn't tested it...

Looks like you have to make a hybrid datatype in MS SQL, then...
You could for example store the exponent in one column, and the mantissa
in another... Ugly, I Know,but...

 

[TechBookReport]

Hi Dag,

The maximum precision allowed by MS SQL on a decimal is 38 and the max
scale is 18. Have you used a decimal with a larger precision and scale
than this?

Cli

An obvious hack is to store as a string, and parse back into a
BigDecimal after reading back into your code. But this depends what it
is you're doing in SQL Server. If it's just storage then it should be
OK, if you're using SQL for manipulating and arithmetic than it it's not
much help.