Android Question DbUtils Insertmaps decimal values lost

[Anser]

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)

    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

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.

 

[Anser]

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()

 

[Erel]

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

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

I tested it with this code and it returns 1.5:

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.

 

[Anser]

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

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

I tested it with this code and it returns 1.5:

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.

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

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

    Sql1.ExecNonQuery(cSql)

 

[OliverA]

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:

When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

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]

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

 

[OliverA]

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.