Android Question DbUtils Insertmaps decimal values lost

Discussion in 'Android Questions' started by Anser, Aug 24, 2017.

  1. Anser

    Anser Well-Known Member Licensed User

    Hi,

    I am downloading data from a remote MySQL server and then inserting it the SqlLite DB on the Android phone.

    On the SqlLite Db's table, I have a column to keep the price. It is defined as Decimal (13,2)

    Code:
    cSql = $"CREATE TABLE items
                    (  Item_Code INTEGER,
                       Item_Price DECIMAL(13,2) "$


        Sql1.ExecNonQuery(cSql)
    I checked the data and found that the data received from the MySQL server contains the decimal for eg. 250.50 but when I insert the data to the Sqlite table via DbUtils.InsertMaps the decimal part ie .50 is lost and the value stored is 250 instead of 250.50.

    The following is the code that I use to insert the data to Sqlite table

    Code:
    Sub Write_Items(Data As List, Meta As Map)
        
    Dim ListOfMaps As List
        ListOfMaps.Initialize
        
    For i=0 To Data.Size-1
            
    Dim cur As Map = Data.Get(i)
            ListOfMaps.Add(cur)
        
    Next
        
    ' Here it displays the correct value ie 250.50, but lost .50 in the table after writing. It writes as 250
        MsgBox(cur.Get("Item_Price"),"Price")

        Starter.Sql1.ExecNonQuery(
    "DELETE FROM items")
        DBUtils.InsertMaps(Starter.Sql1, 
    "items", ListOfMaps)

    End Sub
    I am using DbUtils version 1.20

    I modified the column type of my sqlite table from Decimal(13,2) to Text, but the decimal part is still lost. So I assume that the DbUtils.InsertMaps is the culprit.

    Any help will be appreciated.
     
  2. Anser

    Anser Well-Known Member Licensed User

    I tested inserting data to the sqlite table using Sql1.ExecNonQuery() method and found that it is working perfectly ie the correct data is getting inserted.

    Now the table stored 250.50 instead of 250.

    The Sql1.ExecNonQuery() is slower than the DbUtils.InsertMaps

    So I hereby confirm that the problem is with the DbUtils.InsertMaps()
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    It is not slower. You need to create a transaction.

    I tested it with this code and it returns 1.5:
    Code:
    DBUtils.InsertMaps(sql"grades"Array(CreateMap("Id"0"grade"1.5)))
    Log(sql.ExecQuerySingleResult("SELECT grade FROM grades WHERE id = 0"))
    The type of grade is DBUtils.DB_REAL.
     
  4. Anser

    Anser Well-Known Member Licensed User

    Yes you are right. With transaction enabled, it is equally good as InserMaps

    Is DB_REAL and Decimal( 13,2 ) are same ?
    I created the table using

    Code:
    cSql = $"CREATE TABLE items
                    (  Item_Code INTEGER,
                       Item_Price DECIMAL(13,2) "$


        Sql1.ExecNonQuery(cSql)
     
  5. OliverA

    OliverA Well-Known Member Licensed User

    Please note that SQLite does not have a native DECIMAL type. As per Datatypes in SQLITE Version 3, creating a column with the type DECIMAL will result in a SQLite NUMERIC type column. The conversion rules from DECIMAL to NUMERIC are stated as:
    So you are relying on SQLite to do the correct conversion, which in this case does not seem to happen. Since NUMERIC is just an representation of either an INT or a REAL (edit: or a TEXT), I would just declare the column to be a REAL (instead of DECIMAL) (as in @Erel's example above) and thus taking any SQLite's guessing out of the equation. If precision is important, then you may need to store your numbers as TEXT in SQLite and use BIGDECIMAL in you application for accuracy.
     
    Anser and Mahares like this.
  6. Anser

    Anser Well-Known Member Licensed User

    OliverA,

    I perfectly understand your point. Thank you for providing the information.

    But I am puzzled at the following
    1. I tried changing the Decimal column type to Text and then inserted the records using DBUtils.InsertMaps(), unfortunately I still got the same result.
    2. On a Decimal Column, instead of using DBUtils.InsertMaps(), I used Sql1.ExecNonQuery("INSERT INTO TableName......."), this resolved my issue.
    May be a problem with the DbUtils version 1.20 that I am using.

    Regards

    Anser
     
  7. OliverA

    OliverA Well-Known Member Licensed User

    Odd. DBUtils uses "INSTERT INTO" in the background. It does use ExecNonQuery2 instead of ExecNonQuery, but that is to prevent SQL injection attacks. The only way to trace the issue would be to have the sample code and data that is causing this, otherwise we're just spinning our tires on this issue. So if you're interested to find out what is really going on, please post sample code and data. If it is determined that DBUtils is the issue, DBUtils could then be fixed and improved.
     
    rboeck likes this.
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