Currency values gt 999.99 corrupted when reading from Rowset

I’m reading in a rowset from PostgreSQL which includes currency values.

When I copy the currency field into a DatabaseColumn variable the CurrencyValue is set to 1st digit only if value > 999.99

Not sure if it’s my code in error or a bug - anybody able to replicate?

Is it possible the notation (, and .) lead to an error?

Taken from the Documentation:

Currency is a fixed-point number format that holds approximately 15 digits to the left of the decimal point and 4 digits to the right.

BTW: I’d recommend to use the Currency Type only for on screen presentation, not for calculation or other things. Better use Double or even higher precision types, to avoid rounding issues.


I use Currency to AVOID rounding issues: Taken from the same document:


Currency can contain values from -922337203685477.5808 to 922337203685477.5807. It uses 8 bytes.

Currency is a 64-bit fixed-point decimal. This means that Currency does not have some of the rounding issues that are inherent in floating-point numbers stored in Double.

Oh! I was not aware of that. I apologize. :slight_smile:

Can you create a sample project? It looks like a bad conversion when the Value has a comma.

@AlbertoD : As I already mentioned, it is suspicious that only the number to the left of the “,” is returned as soon as a “,” is present.

@Sascha_S correct, we need OP to provide a sample. It is strange the screenshot of Value and NativeValue

so my guess there is something wrong with localization.

1 Like

I’m not sure how easy it will be to supply a sample as the DB is PostgreSQL and sqlite
doesn’t support type of ‘money’.

I now understand your concern re: the comma (didn’t register this on first reading) - will see if I can modify Postgres to store without locale formatting.

As a workaround pull the StringValue and remove £ and ,
or try Currency.FromText using your locale, whatever work best for you.

If you still find something wrong, open an Issue.

SOLVED (or getting there) - just discovered that the ‘money’ type is deprecated in Postgres.

Initial tests suggest a type of ‘integer’ works as required.

I would prefer ‘numeric’ or ‘decimal’.

1 Like

Arrgh - I meant ‘numeric’

1 Like

That is the problem, even if the value is stored correctly, postgres returns a formated STRING, not a numeric value.

Then xojo SUCKS at parsing this localized string.

As a work around, you can get the StringValue, then use Locale.CurrencySymbol and Locale.GroupingSeparator to remove (String.Replace) those symbols from the string and finaly String.Value to get the correct Currency Value.

Yep, that was very similar to my first solution, but have now modified all the fields to be ‘numeric’ instead of ‘money’ - will go thru my code now to check all behaving.

Thanks to all for your input.

You could try letting a column as is but forcing reading it as ::numeric as

SELECT my_money_column::numeric FROM my_table;
1 Like

in past xojo hat issues with german localization.
we using comma for decimal char instead of dot and currency value variable got broken to/from database for some reason. (PostgreSQL)
it was a really upset bug if you need to deal with money values.