Storing currencies with Coldfusion Orm

Question

I need to create a property in a CF Orm entity that stores currencies (dollars and cents). For example: 100.99 or 1.30 etc. I have traditionally used decimal to store currencies as you can foce the number of decimal points, but there is no ORM property type for decimal, only float. I tried setting scale=2, but (for example) 1.30 was being stored as 1.3, or 5.00 is stored as 5.

Can anyone suggest the optimal way to work with currencies (having 2 decimal places) using CF ORM?

Many Thanks

Answer 1

Bob Silverberg has a good blog article on ColdFusion ORM data types "CF9 ORM - Experimenting with type vs ormtype".

Personally I prefer to store currencies as integers of their base unit e.g. cents as in 130 for $1.30 and then handle the decimal place at presentation. It also helps with possible rounding errors when dealing with computation of float values.

Answer 2

Well there's a big_decimal ormtype according to the docs. And also don't forget that there's the sqltype property as well as the ormtype one. See docs: "Map the properties". sqltype (and scale and precision) will let you set the DB column appropriately.

That said, this will mean that there's a disconnect between CF (any amount of decimal places) and the DB (which you'll be able to force to 2DP), which is a bit crap. I dunno if here's a way to automate the truncation... you might have to create a setter() method to deal with it.