Android Question Currency and data types

[tdocs2]

Greetings, all.

Thank you in advance for responding to my question.

I have a SQLite DB - 2 tables, Account and Transactions. The Account record contains a balance due field. The Transaction record includes charges and payments. All amounts have 2 decimals ($$$,$$$.cc).

I am writing a simple trial balance. Take the amounts from the transactions, add them and then compare to the Balance Due in the Account record. This must sound familiar to most since I have done this or similar processing in other programming languages.

All amounts in the SQLite DB are type REAL. In retrieving the amounts for both the transactions and the Account, I use a Type Double. I believe I get false errors due to the conversion from real to double.

What are best practices to deal with this type of processing?

Any and all help is welcomed.

    Dim account_amt As Double
    Dim tran_total As Double
    Dim tran_amt As Double

        account_amt=Cursor1.Getdouble("Current Balance")
        ***
        query = "Select * FROM Tran WHERE [Account ID] = " &  acct_id
        Cursor2 = SQL.ExecQuery(query)
        Select Cursor2.RowCount
            Case 0
                tran_total=0
            Case Else
                For j = 0 To Cursor2.RowCount - 1
                    Cursor2.Position=j
                    tran_amt=Cursor2.Getdouble("Amount")
                    tran_total=tran_total+tran_amt
                Next
                If account_amt<>tran_total Then
                    Log (acct_id & "*" & account_amt & "*" & tran_total)
                End If
        End Select

 

[keirS]

You shouldn't use Reals to store currency. Real is a floating point datatype and cannot accurately represent the base 10 multiples we use for money. What I do is store the value as an int and use implied decimals. So $132.40 would be stored as 13240 in SQLite.

When calculating currency values I use Agraham's BigNumbers library which is a wrapper of the Java BigDecimal class. This is designed to handle the precision required for currency values.

Dim bg1 AsBigDecimal
Dim bg2 AsBigDecimal
bg1.Initialize("1.40")
bg2.Initialize("165")
bg2.Multiply(bg1)
Log(bg2.ToString)
Log(1.40 * 165)

The example above shows the problem with using floating point. The result is 231.00 for the BigDecimal calculation and 230.99999999999997 for 1.40 * 165.

 

[tdocs2]

Thank you, keirS. Clever workaround. I am experiencing the same issue you described.

http://www.sqlite.org/datatype3.html
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

Can the Integer be converted to long when read from SQLite?

The maximum value for an Int type Is 2147483647 or 21,474,836.47 if expressed as currency (assume 2 decimals)? For Colombia, at 2000 to $1 and Paraguay at over 4000 to $1, the max 21MM becomes 5K....

Best regards.

Sandy

 

[tdocs2]

Can the Integer be converted to long when read from SQLite?

I tried it and it seems to work, but I will have to test more thoroughly. (keirS, If it works with long, then your solution is doubly clever...)

 

[keirS]

Its a lot bigger than that. SQLite can store 8 byte integers (2^63.) The big numbers library implements the BigInteger class as well as the BigDecimal class. As I understand it the maximum size of a BigInteger is limited to the available memory! Use getString from the cursor object to initialize your BigDecimals.