Android Question Background Rounding?

Frank Cazabon

Member
Licensed User
I have a system running on an Android tablet that downloads invoices from a SQL Server and stores the data in a local SQLite database on the tablet. Every now and then (twice in the last 4 months out of thousands of invoices) an invoice is off by one cent. For example an invoice total in SQL Server is 100.41, but it shows on the tablet as 100.40.

I have no code that does any rounding, could SQLite be doing something unexpected in the background?

The code to create the field in the SQLite table is this:

m.Put("inv_amount", DBUtils.DB_REAL)

and the code to pull the data back from SQL Server (using the RemoteDatebaseConnector) is:

SELECT SUM(ivd_amount) FROM InvoiceDetails INNER JOIN Invoices ON ivd_invfk = inv_pk WHERE inv_awbfk = awb_pk) AS InvoiceTotal

Anyone have any ideas?
 

Frank Cazabon

Member
Licensed User
Agreed, and at the moment that is proving difficult. I was hoping that someone may have seen something like this before.
 

Jeffrey Cameron

Well-Known Member
Licensed User
It sounds like, at some point during the process of retrieving the value, it is being stored in a float instead of "real" or double. Because of how float approximates values this type of behavior can happen. You can demonstrate it with a simple test program; create a float variable and do a for loop from 1 to 10 step 0.10:
B4X:
Sub Button1_Click
   ListView1.Clear
   Dim pfLoop As Float
   For pfLoop = 1 To 10 Step 0.10
       ListView1.AddSingleLine("Loop #" & NumberFormat2(pfLoop, 1, 5, 5, False))
   Next
End Sub
Usually, at around 7 you'll see what I'm talking about:


You might create some test invoices with values that the float type has a hard time approximating and see if you can reproduce the error.
 
Last edited:

Frank Cazabon

Member
Licensed User
Thanks Jeffrey,

I've managed to tie it down to invoice totals that are over 9,999.99. For example I have one invoice that is for 19,303.99 which comes down from SQL server with that value an dI store it in my SQLite table, then when I do this:

Invoices = DBUtils.ExecuteMemoryTable(SQLLite, "SELECT awb_number, pay_type, pay_amount, inv_amount, rnd_pk FROM RunSheetDetails LEFT JOIN Payments ON rnd_pk = pay_rndfk INNER JOIN DeliveryHistory ON rnd_pk = DeliveryHistory.del_rndfk AND del_delivered = 'True' WHERE cus_number = ?", Array As String(CustomerNumber), 0)

It shows up as

19304 (0x4B68)

Have I chosen the wrong storage type for this value?
 

Frank Cazabon

Member
Licensed User
How are you getting the value from the remote database? You need to find out where it loses the precision.
the code to pull the data back from SQL Server (using the RemoteDatebaseConnector) is:

SELECT SUM(ivd_amount) FROM InvoiceDetails INNER JOIN Invoices ON ivd_invfk = inv_pk WHERE inv_awbfk = awb_pk) AS InvoiceTotal

and this is how I put it into the SQLite database:

m.Put("inv_amount", records(result.Columns.Get("InvoiceTotal")))

At that point the value going in is still 19303.99

When I then query the SQLite database like this:

Invoices = DBUtils.ExecuteMemoryTable(SQLLite, "SELECT awb_number, pay_type, pay_amount, inv_amount, rnd_pk FROM RunSheetDetails LEFT JOIN Payments ON rnd_pk = pay_rndfk INNER JOIN DeliveryHistory ON rnd_pk = DeliveryHistory.del_rndfk AND del_delivered = 'True' WHERE cus_number = ?", Array As String(CustomerNumber), 0)

The value in the debugger is 19304 (0x4B68)

So it is either losing the precision when I put it into the SQLite database or when I get it back from it.
 

klaus

Expert
Licensed User
I'm afraid that the problem is the same as here SQLite Cursor GetString versus GetDouble.
DBUtils.ExecuteMemoryTable uses GetString for all columns.
GetString seem to convert REAL numbers to Float which limits it to 7 digits.
 

Frank Cazabon

Member
Licensed User
The cut off is definitely the 9999.99 amount. That stores and retrieves fine. If I go to 10000.01, then it gets rounded to 10000.

Could you explain how I use a cursor with Cursor.getDouble, please?
 

Frank Cazabon

Member
Licensed User
Ah, OK. Go back to the old SQLite ExecQuery function. Gotcha.

Thanks.

Is this problem something that can be fixed in DBUtils?
 

Frank Cazabon

Member
Licensed User
Thanks. I have a lot of code that will need adjusting so was hoping it was a quick fix. Please let me know when it is done.
 
Top