advice needed re mysql decimal field type

Post your Gambas programming questions here.
Post Reply
bill-lancaster
Posts: 138
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

advice needed re mysql decimal field type

Post by bill-lancaster » Monday 04th October 2021 5:09pm

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?

bill-lancaster
Posts: 138
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: advice needed re mysql decimal field type

Post by bill-lancaster » Tuesday 05th October 2021 8:30am

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.

User avatar
Quincunxian
Posts: 134
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: advice needed re mysql decimal field type

Post by Quincunxian » Tuesday 05th October 2021 8:20pm

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
Cheers - Quin.
I code therefore I am

bill-lancaster
Posts: 138
Joined: Tuesday 26th September 2017 3:17pm
Location: NW England

Re: advice needed re mysql decimal field type

Post by bill-lancaster » Friday 08th October 2021 8:41am

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.

Post Reply