storing currency data in sqlite

[jalz]

Hi all,

I want to store currency in my sqlite database and I've been doing some reading on various sql forums to find the 'correct' way of storing these. Most suggest that currency in sqlite for various reasons (sorting etc) should be stored as integers as sqlite does not contain a "currency" format.

I'm using data grids to display the data, is there anyway to format the data so it displays numbers with 2 decimal places in a datagrid column? Secondly, if I wanted to store a number such as 792.00, should I store it as 79200 and always treat the last two digits needing a decimal in front of it when its on display (I would obviously have to account for a my user entering in 792.50, by storing it as 79250)? Is this the way i should approach it, does LiveCode have field masks where I can store a type of data, but display it differently using certain patterns like in this case a currency format or do I need to manual program that in when I populate the datagrid.

Thanks all as always.

Jalz

[Mark]

Hi Jalz,

For numeric data, SQLite provides you with the options INTEGER and REAL. Because REAL takes more memory and is potentially slower, INTEGER is to be preferred. Since all currency values have an inderterminate length with always two decimals, the easiest solution is to do a guess on the maximum length and store values as integers (multiplies by 100). You could write a simple SQLite command to retrieve the orignal values:

Code: Select all

SELECT someint/100.0 FROM sometabl 

I think that adding ".0" is essential here, but I'm not absolutely sure. It should force SQLite to convert the values to integers before returning them.

Kind regards,

Mark

[MaxV]

I use a lot SQLite and there is no reason to store currency in integer. It's much better to use a normal real number.