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.
Soundy like a bug for me. Or is it a feature?
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…
Here is what I got from windows. Seems ok here, Windows PT-BR Xojo 2021r3.1:
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
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)
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).
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
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
Welcome to the magic and concistency of the API 2!!
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.
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…