SQL lite query reads wrong Id's

Question

It's been long fight between mysql and sql lite date issue. I have a complex query which returns multiple data.

I read server Json objects and store them in sql lite android was fine. So i did a query which is like below one

"SELECT * FROM " + MAIN_TABLE + " LEFT OUTER JOIN " + SERVICE_INFO_TABLE + " ON ServiceInfo.main_id = MAIN_TABLE._ID" + " LEFT OUTER JOIN " + M_USER_TABLE + " ON M._ID = ServiceInfo.m_id" + " LEFT OUTER JOIN " + U_TABLE + " ON MAIN_TABLE.u_id = U_TABLE._ID"

The problem starts when i read the MAIN_TABLE which contains creation_time and upation_time, The SERVICE_INFO_TABLE also contains creation_time, updation_time.

However the table contains different date and time respectively. The cursor returns same date and time for all entity.

It seems the cursor method got confused and return same data and time read from ServiceInfo table.

I dig deeper into debugging mode while SQL lite read the query, i saw strange things happening over there. Below is the reading example

Cursor cursor = sqLiteDatabase.rawQuery(selectQuery, null);

    if (cursor != null && cursor.getCount() > 0) {
        cursor.moveToFirst();
        do {
            Log.d("dummy", "dummy stopper");
            VParcelable vParcelable = new VParcelable();
            vParcelable.setId(cursor.getInt(cursor.getColumnIndex(VEntry._ID)));
            vParcelable.setModel(cursor.getString(cursor.getColumnIndex(VEntry.MODEL)));
            vParcelable.setYearMdl(cursor.getInt(cursor.getColumnIndex(VEntry.YEAR_MODEL)));
                            vParcelable.setCreationTime(cursor.getString(cursor.getColumnIndex(VEntry.CRE_TIME)));
            vParcelable.setUpdationTime(cursor.getString(cursor.getColumnIndex(VEntry.UPA_TIME)));

So the ServiceInfo table also contains the same creation_time and updation_time.

vParcelable.setCreationTime(cursor.getString(cursor.getColumnIndex(VEntry.CRE_TIME)));

mColumns from cursor debugging mode shows "updation_time" and "creation_time" for VTable and same as for ServiceInfo table.

Is that problem of sql lite couldn't differentiate table column names?

That really sucks.

Help please.

Thanks

Please beware that, SQL lite android reads the column table id's as a group. If you have few tables and Entry id's are similar SQL lite will get confused. — EngineSense, May 06 '16 at 15:42

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

Your date string '15-04-2015' is in a DD-MM-YYYY format but it appears it is being treated as MM-DD-YYYY at some point. You aren't showing ANY code in your question, so I can't tell you exactly how you need to solve this. In general you need to specify to the database the date format you are using, or switch to storing it as a string or integer in the database. You might look at some of the answers to this question: Best way to work with dates in Android SQLite

edited May 23 '17 at 10:33

Community

1
1

answered Apr 17 '16 at 22:02

Mark B

183,023
24
297
295

Thanks Mark B, i thought it was the date issue, but the problem is sql lite couldn't distinguish the variable name for the columns names. My problem was solved however by renaming the above columns names. – EngineSense Apr 27 '16 at 13:35

SQL lite query reads wrong Id's

1 Answers1