Page 1 of 1

advice needed re mysql decimal field type

Posted: Monday 4th October 2021 5:09pm
by bill-lancaster
I have always used an integer value for a money type, then simply divide by 100 for display. This avoids fractional penny values occurring.
Although mysql supports a decimal type, I've never found a way of using it in Gambas.
Any ideas?

Re: advice needed re mysql decimal field type

Posted: Tuesday 5th October 2021 8:30am
by bill-lancaster
Create a field of type DECIMAL(6,2) using an sql statement .
This then shows as type 'float' in Gambas but is always has only 2 decimal places.

Re: advice needed re mysql decimal field type

Posted: Tuesday 5th October 2021 8:20pm
by Quincunxian
You can convert a float to a currency by using Round(

Value = Round ( Number [ , Digits ] )

Rounds a number to its nearest integer, if Digits is not specified.
If Digits is specified, rounds to 10 ^ Digits.
Examples
PRINT Round(Pi, -2)
RunPlay
3.14
PRINT Round(1972, 2)
RunPlay
2000

Re: advice needed re mysql decimal field type

Posted: Friday 8th October 2021 8:41am
by bill-lancaster
Thanks Quincunxian that is useful.

With accounting and financial projects its important to avoid unwanted fractions of a penny. For this reason I have always used integers to represent money values, dividing by 100 to display the true value. This way there is never a problem with penny fractions.

Recently I've tried the sql DECIMAL(x,y) field. Although Gambas sees the database field (column) as type 'float' an attempt to change the sign of a value (e.g. hResult!DebitValue) results in an error (got STRING, wanted FLOAT). The work around is to move the value into a float variable then change the sign.

Also a simple arithmetic functions on float values throws up fractional penny vales. This is where ROUND helps.

I see there is a functions _DataTypes.SignedDecimal (gb.mysql) but I've no idea how to use it.