Android: long => SQLiteDatabase => long , precision lost

Question

I'm creating an app which includes data stored in an SQLitedatabase. I use a long as primary key (generated buy the significant bits of an java.util.UUID). I handle the database connection with an extended SQLiteOpenHelper. The query:

INSERT INTO Categorys VALUES('-7311183930966261019', 'Mat', 'Matkonto', '#FFFFFF', '#F23341', '0', '231451', '0', '0', '');

goes through without problem, but when i retrieve the value a lot of the precision in the first value -7311183930966261019, have been lost. The query

SELECT * FROM Categorys WHERE _id='-7311183930966261019';

returns null;

I have tried the same querys in sqlite3 and the command line and not gotten the same problem so i've concluded that the problem is either related to android or my handling along the way.

Update: After correcting the querys i now have:

INSERT INTO Categorys VALUES(-6645129292311215613, 'Sparande', 'Pengar som sparas inför framtiden', '#FFFFFF', '#F23341', '0', '231451', '0', 0, '');

And from the database i get (toString() from my class):

Category: name: Sparande; description: Pengar som sparas inför framtiden; colorLight: #FFFFFF; colorDark: #F23341 _id: -6645129292311215104

The code retrieving data:

public static List<Category> readCategorys(Context context){
    TransactionSQLOH helper = new TransactionSQLOH(context, DATABASE_NAME);
    SQLiteDatabase db = helper.getReadableDatabase();
    Cursor cursor = db.query(CategorySQLOH.TABLE_NAME, CategorySQLOH.COLUMNS, null, null, null, null, null);
    List<Category> result = CategorySQLOH.cursorToCategory(cursor);
    helper.close();
    return result;
}
public static List<Category> cursorToCategory(Cursor cursor){
    List<Category> result = new Vector<Category>();
    if (cursor == null || cursor.getCount() == 0){
        return result;
    }
    cursor.moveToFirst();
    Category c;
    do{
        long _id = cursor.getLong(cursor.getColumnIndex(CategorySQLOH.COLUMNS[0]));
        String name = cursor.getString(cursor.getColumnIndex(CategorySQLOH.COLUMNS[1]));
        String description= cursor.getString(cursor.getColumnIndex(CategorySQLOH.COLUMNS[2]));
        String colorLight = cursor.getString(cursor.getColumnIndex(CategorySQLOH.COLUMNS[3]));
        String colorDark = cursor.getString(cursor.getColumnIndex(CategorySQLOH.COLUMNS[4]));
        int destribution = cursor.getInt(cursor.getColumnIndex(CategorySQLOH.COLUMNS[5]));
        long categoryGroup =  cursor.getLong(cursor.getColumnIndex(CategorySQLOH.COLUMNS[6]));
        int intData =  cursor.getInt(cursor.getColumnIndex(CategorySQLOH.COLUMNS[7]));
        long longData  = cursor.getLong(cursor.getColumnIndex(CategorySQLOH.COLUMNS[8]));
        String strData = cursor.getString(cursor.getColumnIndex(CategorySQLOH.COLUMNS[9]));
        c = new Category(_id, name, description, colorLight, colorDark, destribution, categoryGroup, intData, longData, strData);
        result.add(c);
    }while(cursor.moveToNext());
    return result;
}

Update:

Result of SELECT * FROM Categorys; on the pulled database:

sqlite> SELECT * FROM Categorys;
-6.64512929231122e+18|Sparande|Pengar som sparas inf├Âr framtiden|#FFFFFF|#F2334
1|0|231451.0|0|0.0|

Update:

result of .schema on pulled database.

CREATE TABLE Categorys ( _id REAL PRIMARY KEY, name TEXT NOT NULL, description T
EXT , colorLight TEXT , colorDark TEXT , destribution INTEGER , categoryGroup RE
AL , intData INTEGER , longData REAL , strData TEXT );
CREATE TABLE android_metadata (locale TEXT);

have you dumped that database after the insert to see what values are in which columns? you say precision is list, but there's no direct evidence here that is what's happening. — Jeffrey Blattman, Mar 25 '13 at 19:54
No, i've only read what i get back when i query the database. — SverkerSbrg, Mar 25 '13 at 20:04
at this point you are making an assumption of the problem without any evidence. dump the DB rows and check the actual value of the row. — Jeffrey Blattman, Mar 25 '13 at 20:28
I hate to sound like an amature but i'm compiling on a S2 and im not sure of how :S How do i extract it? Or is it simpler to run it on an emulator? — SverkerSbrg, Mar 25 '13 at 20:29
`adb shell`. the .db file is under `/data/data//databases`. you can use the `sqlite3` command line from the shell. if your device is not rooted, you won't be able to do that you will need to use the emulator. you can always add the "select * from mytable" call into your code and log the results. — Jeffrey Blattman, Mar 25 '13 at 20:44
It's not rooted. And what i've written earlier is a result from a select *; but i will try with an emulator. — SverkerSbrg, Mar 25 '13 at 20:48
if you want to map to a `long` in java, the sqlite data type should be `INTEGER`, not `REAL`. — Jeffrey Blattman, Mar 25 '13 at 21:51
Using BLOB solved the problem, i'll try using INTEGER too, Thank you — SverkerSbrg, Mar 25 '13 at 22:04

Simon Dorociak · Answer 1 · 2013-03-25T20:10:07.453

1

Android: long => SQLiteDatabase => long , precision lost

I guess that since you defined it as LONG i think you need to remove single quotes because with quotes, SQLite interprets it as couple of characters and not as number.

INSERT INTO Categorys VALUES(-7311183930966261019, 'Mat', ...);
SELECT * FROM Categorys WHERE _id = -7311183930966261019;

I tested it myself and everything works.

edited Mar 25 '13 at 20:10

answered Mar 25 '13 at 19:53

Simon Dorociak

33,374
10
68
106

I was not aware of the difference but I still get the precision loss :S – SverkerSbrg Mar 25 '13 at 20:00
@SverkerSbrg it's very interesting, add here some snippet of code how you retrieving data from db. – Simon Dorociak Mar 25 '13 at 20:09
Thank you for taking the time. I can add that the loss appears on every call to the database and with the same exact difference between input and result (regardless of type of query). If i try to fetch the inserted data with the same id as i put i it will find it, but with the 'wrong' id – SverkerSbrg Mar 25 '13 at 20:20
@SverkerSbrg so when you insert rows to db, check db from some sqlite manager how it looks, especially ids. – Simon Dorociak Mar 25 '13 at 20:33
I insert -6645129292311215613 and when i retrieve i get -6645129292311215104 when i ask for -6645129292311215613 (WHERE _id=-6645129292311215613) i find the row. Asking for the row with the retrieved value -6645129292311215104, gives null. From this i conclude that i get an error at every interaction with the db and that it applies equaly every time. – SverkerSbrg Mar 25 '13 at 20:37
@SverkerSbrg i need to see java code how you are retrieving it. – Simon Dorociak Mar 25 '13 at 20:53
@SverkerSbrg how you are getting data from cursor. – Simon Dorociak Mar 25 '13 at 21:18
I've posted that code, it's the public static List cursorToCategory(Cursor cursor) method. – SverkerSbrg Mar 25 '13 at 21:21

Android: long => SQLiteDatabase => long , precision lost

1 Answers1