Storing currency values in SQLite database

[Ralph_Alvy]

What is the currently recommended way to store currency values in s SQLite database? I see the following in the Xojo docs:

[quote]
Use the REAL data type to store currency values in conjunction with DatabaseRecord.CurrencyColumn and DatabaseField.CurrencyValue.[/quote]

But I also see others in the forum saying to use INTEGER.

I will definitely need to compare currency values, along with adding and multiplying them. I imagine I can store them as cents in an INTEGER field, and then rely on the UI to convert to 2-digit currency values for display to the user. If I do that, I can rely on SQLite itself to compare values (larger, or smaller than, or equal).

[DaveS]

remember, SQLite doesn’t have true “datatypes”, but it has data affinity…
So I would suggest you use “REAL”, or as suggested, “INTEGER” and handle the “cents” youself.
if you end up assigning any other affinity there is a chance an “ORDER BY” will be “text” instead of “numeric” compare.

My Tadpole app has to in some cases apply a CAST(x as double) to insure the desired result.

[LiamP]

I always use DOUBLE and have never had a problem…

[DaveS]

@343260:@Bill Plunkett

I always use DOUBLE and have never had a problem…

DOUBLE has the affinity of REAL

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:

• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
• TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
• BLOB. The value is a blob of data, stored exactly as it was input.

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

If the declared type contains the string “INT” then it is assigned INTEGER affinity.

If the declared type of the column contains any of the strings “CHAR”, “CLOB”, or “TEXT” then that column has TEXT affinity. Notice that the type VARCHAR contains the string “CHAR” and is thus assigned TEXT affinity.

If the declared type for a column contains the string “BLOB” or if no type is specified then the column has affinity BLOB.

If the declared type for a column contains any of the strings “REAL”, “FLOA”, or “DOUB” then the column has REAL affinity.

Otherwise, the affinity is NUMERIC.

[Ralph_Alvy]

So far, it sounds like it doesn’t matter. I can use either INTEGER or REAL, on the SQLite side of things. Certainly it seems that using REAL would be easier.

If I use REAL on the SQLite side, wouldn’t it make more sense to use the Xojo Currency data type instead of Double?

[Philippe_Casgrain]

If you use REAL you will get some weird rounding issues UNLESS you store a fixed number of decimals. For dollar-based amounts (with cents, so two significant decimals) I’d recommend storing three decimal places, and the last (unseen) decimal place is stored using Banker’s Rounding .

The third decimal will come in handy when you have to handle weird taxes like 9.975% or fractional quantities (e.g. 0.15 ft) for large amounts (e.g. 471).

[DaveS]

All currency is is a INT64 that Xojo handles differently…
SQLite doesn’t know what it is… so I’d stay with REAL if you want to be sure other non-Xojo apps can read the data properly

Look up/read more about SQLite Affinity

[Philippe_Casgrain]

And yes, do use the Xojo Currency data type for all currency calculations. Never use Double, Float or Real for currency.

[Norman_P]

Or an infinite precision type (like from Bob Delaney’s plugins)
OR a BCD type which has no such issue

[DaveS]

@343276:@Philippe Casgrain

And yes, do use the Xojo Currency data type for all currency calculations. Never use Double, Float or Real for currency.

SQLite HAS NO IDEA WHAT XOJO DATATYPES ARE
in this case it will default to NUMERIC anyways

[Philippe_Casgrain]

[quote=343280:@Dave S]SQLite HAS NO IDEA WHAT XOJO DATATYPES ARE
in this case it will default to NUMERIC anyways[/quote]

Of course it doesn’t, no need to scream. Once you get your currency data out of SQLite and into your app, make sure to use Currency for all operations. Then convert it back to REAL with appropriate decimals and rounding when writing back to your database.

[DaveS]

I wasn’t “screaming”… I was attempting to make a point… everyone keeps insisting (or inferring) that Sqlite knows and understands all the same datatypes that Xojo does, and that is not only an untrue statement, but a very dangerous assumption to make…

ANY Sqlite data affinity can be manipulated via STR(), VAL() and other related functions… but it is not, will not and cannot be “currency” datatype while in the realm of SQLite. Meaning it is still left to the developer to convert things.

That all being said… Those conversions from DOUBLE to Currency and back are susceptible to the same precision and rounding issues that the Xojo Double datatype have… Less so, because you are “probably” not going to be dealing with numbers that exceed the precision limits (4 decimal points at most)…

[Ralph_Alvy]

This entire thread has been very helpful. I really appreciate it. For currency values that have cents stored (i.e, not rounded to the nearest dollar), I’ll use the Xojo Currency data type and store it in a SQLite REAL column. For currency values that are rounded to the nearest dollar, I’ll use the Xojo Integer data type and store it as INTEGER.

The FileMaker app I’m converting has both kinds of currency fields in various layouts.

[Andre_Kuiper]

My advice would be to not use the currency datatype at all if you are not in full control of the calculations that are made with the data from the database. The currency datatype handles division by zero in a very strange manner and although there is said that the currency datatype is in fact a Int64 datatype, they are both handled different in case of division by zero.

In the case of the Int64 the division by zero results in a ‘inf’ -value (infinity), but in the case of the currency the result is a ‘normal’ number that can be used in further calculations without ever been noticed. There was said that this would be a random number but with every new start of the program it generates every time the same sequence of numbers.

To show what i mean make a new program with 2 listboxes and 2 buttons, in the action event of the first button put this code:

[code] Me.caption=“Currency / 0”
Listbox1.DeleteAllRows

Dim c As Currency =123456.789
Dim c0 As Currency = 0

For i As Integer = 0 To 100
Listbox1.AddRow Str(c/c0,"#######0.0000")
Next i[/code]

and in the action event of the second button:

[code] Me.caption=“Int64 / 0”
Listbox2.DeleteAllRows

Dim c As Int64 =123456.789
Dim c0 As Int64 = 0

For i As Integer = 0 To 100
Listbox2.AddRow Str(c/c0,"#######0.0000")
Next i[/code]

[Massimiliano_Chiodi]

Ciao from Italy,
i simply use double column in database, and read/write in xojo as currency.

[Philippe_Casgrain]

@343310:@Dave S

I was attempting to make a point… everyone keeps insisting (or inferring) that Sqlite knows and understands all the same datatypes that Xojo does, and that is not only an untrue statement, but a very dangerous assumption to make…

Your point is well-taken, but the way you quoted me was not related to your point. If you read my quote, it only pertains to Xojo. Use only Currency in Xojo if you are doing currency calculations. If you use Double you will get bitten at some point and your users will be angry, and may even sue for damages when they made a sale and lost money because of a weird value that doesn’t get properly represented by a Double.

How you store your Currency in SQLite is another matter entirely. You could store it as a numeric type or even a string., since you usually care about two digits of precision. In our software we store it with three digits of precision: we convert the Currency to a Double and round it to three digits using Bankers’ Rounding.

[Christian_Schmitz]

Integer in SQLite by the way is Int64.