Android Question Background Rounding?

Discussion in 'Android Questions' started by Frank Cazabon, Apr 28, 2015.

  1. Frank Cazabon

    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?
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    The first step is to find a reproducable case. Then we can debug it to find the problematic step.
     
  3. Frank Cazabon

    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.
     
  4. Jeffrey Cameron

    Jeffrey Cameron Active 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:
    Code:
    Sub Button1_Click
       ListView1.Clear
       
    Dim pfLoop As Float
       
    For pfLoop = 1 To 10 Step 0.10
           ListView1.AddSingleLine(
    "Loop #" & NumberFormat2(pfLoop, 155False))
       
    Next
    End Sub
    Usually, at around 7 you'll see what I'm talking about:
    [​IMG]

    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: Apr 29, 2015
  5. Frank Cazabon

    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?
     
  6. Erel

    Erel Administrator Staff Member Licensed User

    How are you getting the value from the remote database? You need to find out where it loses the precision.
     
  7. Frank Cazabon

    Frank Cazabon Member Licensed User

    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.
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    I see it too. The problem is in DBUtils.ExecuteMemoryTable which uses Cursor.GetString to get the value and apparently causes this rounding error.

    You should instead use a cursor with Cursor.GetDouble.
     
  9. klaus

    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.
     
  10. Frank Cazabon

    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?
     
  11. Erel

    Erel Administrator Staff Member Licensed User

  12. Frank Cazabon

    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?
     
  13. Erel

    Erel Administrator Staff Member Licensed User

    It can be fixed using an API that is only available in Android 4+ (which allows finding the column type). I will do it in the future.
     
  14. Frank Cazabon

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice