Int64 and Decimal

Sep 9, 2010 at 8:49 AM

I just discovered that I didn't read the documentation properly and I thought that JET_coltyp.Decimal corresponded to the CLR Decimal type. I also though there was no Int64 equivalent, or perhaps it wasn't complete yet. Now I realise that JET_coltyp.Decimal is an 8-byte signed integer (Int64). But I'm still not sure how to store CLR Decimal types in ESE. The API documentation says:

JET_coltypCurrency ... This column type is identical to the variant currency type. This column type can also be used as a native 8-byte signed integer

This implies that I can put a CLR Decimal and Int64 value into JET_coltyp.Decimal column. Is that correct? I've never tried casting between Int64 and Decimal, but I suppose it's valid as they're the same size. Does that mean this is valid? ...

Api.SetColumn(sessid, tabid, colid, (long)mydecimal);
decimal mydecimal = (decimal)Api.RetrieveColumnAsInt64(sessid, tableid, colid);

This is a CLR question more than an Esent one, but it would nice to have the issue clarified. It's a bit tricky that JET_coltyp.Decimal is an Int64 and JET_coltyp.Long is an INT32.

Cheers, Greg

Sep 14, 2010 at 8:50 AM

The names of the column types are all horrible because they come from the C version of the API, which was originally written in the early 90s.

Casting to Int64 will definitely lose resolution because a decimal has a fractional comp,onent.

The easy, but slow, way to store a decimal type is to serialize it (Api.SerializeObjectToColumn). That won't allow correctly ordered sorting though. Other possibilities include manually serializing or using a string representation. I'm want to add an overload that would store a Decimal in a binary column in a way that sorted correctly. I wasn't aware of any demand so I hadn't done it so far. I'll try to work out how to do that.


Sep 16, 2010 at 3:09 AM

Having a Decimal column is low priority for me also, so I would keep it low on your list.

D'oh! Of course my cast sample code won't work, as I'd be losing precision. What I would need to do (and what i was actually thinking) is to bit convert 8 bytes out of the Decimal, then bit convert to long and save it in a column, then I reverse the process on the way back. I would have to exchange the Decimal and Int64 via the raw bytes. They are the same size, so that should work in the decimal -> long -> decimal roundtrip direction. Perhaps not the other way.

Mar 16, 2013 at 2:07 AM
What about decimal as guid?
Mar 19, 2013 at 9:43 PM
While a Guid would have enough bits to store a Decimal, the bits would end up in the wrong order. When you create an index over that column it would end up all out of order. Having jumbled records is a bit of a deal breaker...