Storing currency values in SQLite database

  1. 11 months ago

    Ralph A

    31 Jul 2017 Santa Monica, California

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

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

    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).

    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.

  2. Dave S

    31 Jul 2017 Answer San Diego, California USA

    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.

  3. Bill

    1 Aug 2017 Pre-Release Testers, Xojo Pro Longview Texas

    I always use DOUBLE and have never had a problem....

  4. Dave S

    1 Aug 2017 San Diego, California USA

    @BillPlunkett 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.

  5. Ralph A

    1 Aug 2017 Santa Monica, California

    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?

  6. Philippe C

    1 Aug 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Ottawa, Canada

    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).

  7. Dave S

    1 Aug 2017 San Diego, California USA

    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

  8. Philippe C

    1 Aug 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Ottawa, Canada

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

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

  10. Dave S

    1 Aug 2017 San Diego, California USA

    @PhilippeCasgrain 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

  11. Philippe C

    1 Aug 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Ottawa, Canada

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

    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.

  12. Dave S

    1 Aug 2017 San Diego, California USA

    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).....

  13. Ralph A

    1 Aug 2017 Santa Monica, California

    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.

  14. Andre K

    2 Aug 2017 Pre-Release Testers

    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:

      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

    and in the action event of the second button:

      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
  15. 10 months ago

    Massimiliano C

    is not verified 3 Aug 2017 Europe (Italy, Bergamo)

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

  16. Philippe C

    17 Aug 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Ottawa, Canada

    @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.

  17. Christian S

    17 Aug 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    Integer in SQLite by the way is Int64.

or Sign Up to reply!