Currency in sqlite

Hi,

perhaps I miss something, but at the moment I can’t see why this behavior is like it is.

Sqlite db with REAL-Columns to store currency values.

I have a field called kk6000 with 5.2 stored:

If is do something like:

dim tmpd as Double = row.Column(“kk6000”).DoubleValue
Dim tmpc As Currency = row.Column(“kk6000”).CurrencyValue

I get:

tmpd: 5,2000000000000002
tmpc: 922337203685477.5807

Any ideas?

Marius

And by the way:

dim tmpe as Currency = tmpd

gives:

tmpe: 5.2

What Xojo version?
What OS?
What’s your locale?
What’s the row.Column(“kk6000”).StringValue for such row?

1 Like

And something more. With integer-type in sqlite, the same data gives:

row.Column(“kk6000”).DoubleValue: 5,2000000000000002
row.Column(“kk6000”).CurrencyValue: 52

2021 3.1

macOs 21.1

de_DE.UTF-8

5.2000000000000001776

I never use .currencyvalue
it fails too often, specially on non-english systems (you know the dot, comma or space in currencies)
better use the stringvalue, and then convert yourself to a currency the way you like
if your original field is a real, then use the doublevalue it is the best guess.

1 Like

Soundy like a bug for me. Or is it a feature? :slight_smile:
Especially the “non-english systems” is the reason why I decided to use currency. It would be nice, if Xojo could handle that for me…

You should use integer columns and put in your number *10000.
That is what currency type should do for you.

2 Likes

Here is what I got from windows. Seems ok here, Windows PT-BR Xojo 2021r3.1:

image

Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child("test-delete-it.sqlite")
db.CreateDatabase

Const createIt As String = "DROP TABLE IF EXISTS test; CREATE TABLE test (""kk6000"" real); "

db.ExecuteSQL(CreateIt + "INSERT INTO test (""kk6000"") VALUES (5.2);")

Var rs As RowSet = db.SelectSQL("SELECT * FROM test")

Var d1 As Double = rs.Column("kk6000").DoubleValue
Var c1 As Currency = rs.Column("kk6000").CurrencyValue
Var s1 As String = rs.Column("kk6000").StringValue
Var sc1 As String = c1.ToString
Var sd1 As String = d1.ToString

Break
1 Like

That’s not the reason to use currency. The reason should be fidelity to the cents when adding and subtracting values, as Double carries floating decimals that could potentially affect such kind of math, and a currency 111.01 is 111.01 and not 111,0100000000000051 (for sum and subtraction, because for complex math it gets converted to temporary doubles)

2 Likes

it is a bug, but I have waited too long for it to be fixed, so I use something that works.
<https://xojo.com/issue/63556>
<https://xojo.com/issue/61761>
<https://xojo.com/issue/58065>

2 Likes

I just see a presentation inconsistency in the debugger. d1 takes the locale in consideration, and c1 and s1 acts as using a “standard” value (always a dot as decimal separator ignoring the locale).

1 Like

That works for me, too.
But its fails, when I do that:

Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child(“test-delete-it.sqlite”)
db.CreateDatabase

Const createIt As String = “DROP TABLE IF EXISTS test; CREATE TABLE test (”“kk6000"” real); "

db.ExecuteSQL(CreateIt + “INSERT INTO test (”“kk6000"”) VALUES (5.2);")

Var rs As RowSet = db.SelectSQL(“SELECT * FROM test”)

for each row as databaserow in rs

Var d1 As Double = row.Column(“kk6000”).DoubleValue
Var c1 As Double = row.Column(“kk6000”).CurrencyValue
Var s1 As String = row.Column(“kk6000”).StringValue

Break

next

1 Like

The magic comes with the “for each row as databaserow in rs”

3 Likes

image NEEDS FIX ASAP !!!

image

row contents:
image


Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child("test-delete-it.sqlite")
db.CreateDatabase

Const createIt As String = "DROP TABLE IF EXISTS test; CREATE TABLE test (""kk6000"" real); "

db.ExecuteSQL(CreateIt + "INSERT INTO test (""kk6000"") VALUES (5.2);")

Var rs As RowSet = db.SelectSQL("SELECT * FROM test")

For each row As DatabaseRow in rs
  
  Var d1 As Double = row.Column("kk6000").DoubleValue
  Var c1 As Currency = row.Column("kk6000").CurrencyValue
  Var s1 As String = row.Column("kk6000").StringValue
  Var sc1 As String = c1.ToString
  Var sd1 As String = d1.ToString
  
  break // c1 is DESTROYED !
  
Next

Break
1 Like

This kind of bug can put dozens companies out of business!!! And subsequently Xojo.
cc. @Geoff_Perlman

1 Like

Rick are you testing on a mac? Intel or M1?

I have correct results with intel macOS 10.14.6

Edit: maybe is the combination with the locale

1 Like

Welcome to the magic and concistency of the API 2!! :partying_face:

Currency is another grat idea… half backed. limited to the USA needs and doing conversions to float loosing the concistency and having the rounding problems it is suposed to prevent.

As I said, USA centric, you are going to have “correct” results when your locale uses a dot as a decimal separator.

As a workwround,
For concistency, you can use plugins to have decimal data types instead of the currency. Also you can make your own class. Make some extensions on the recordset to save and retrieve those data types without loosing presicion saving it as text for example.

1 Like

Thank you Ivan, changed Region to “Spain” and I get the wrong information.
image

image

1 Like

the main problem is the comma replacing the dot in non-us systems
also sometimes you have commas to separate thousands
in currencies you can have the money symbol $ £ € …
you can have spaces between number groups that make the conversion fail
and also you can have the minus sign at the end of the currency instead of the beginning…
which makes the negative currencies become positive …

everything xojo makes as international methods is bug prone…
I don’t blame them as I don’t know if MY conversion methods works in ANY country…