Android sqlite insert issue

Question

I'm trying to add data from a csv file to an sqlite database on Android.

My data in csv file looks like this

SID,Attended,Serial,Time,Title,Forenames,Last name,Parking,How many people will be in your     party (including yourself)?,Any access requirements?,Access requirements,Timetable
9290,,0000000092906,2014-04-07 18:44:59,Miss,foo1,foo1,,2,No,,fooo
9291,,0000000092907,2014-04-08 18:44:59,Miss,foo2,foo2,,2,No,,fooo
9292,,0000000092908,2014-04-07 18:44:59,Miss,foo3,foo3,,2,No,,fooo

I created a DatabaseHelper to import it :

public void importFromCSV(String filename) 
{
    //deleteTable();
    SQLiteDatabase db = this.getReadableDatabase();
    String next[] = {};

    try {
        db.beginTransaction();
        CSVReader reader = new CSVReader(new FileReader(filename));
        reader.readNext();
        for(;;) {
            next = reader.readNext();
            if(next != null) {
                this.addPerson(new Person(Long.parseLong(next[0]),
                        next[1],
                        Long.parseLong(next[2]),
                        next[3],
                        next[4],
                        next[5],
                        next[6],
                        next[7],
                        Integer.parseInt(next[8]),
                        next[9],
                        next[10],
                        next[11]));
            } else {
                break;
            }
        }
        reader.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    finally {
        db.endTransaction();
    }

}

But when I try to do a SELECT on my database, I got an error : CursorIndexOutOfBoundsException : Index 0 requested, with a size of 0. I did some research and found that this error was because of an empty cursor.

Here is my getPerson function :

public Person getPerson(long sid){

    // 1. get reference to readable DB
    SQLiteDatabase db = this.getReadableDatabase();

    // 2. build query
    Cursor cursor = 
            db.query(TABLE_PERSONS, // a. table
            COLUMNS, // b. column names
            " sid = ?", // c. selections 
            new String[] { String.valueOf(sid) }, // d. selections args
            null, // e. group by
            null, // f. having
            null, // g. order by
            null); // h. limit

    // 3. if we got results get the first one
    if (cursor != null) {
        cursor.moveToFirst();
    }

    // 4. build pers object
    Person pers = new Person();

    pers.setSid(Long.parseLong(cursor.getString(0)));
    pers.setAttended(cursor.getString(1));
    pers.setSerial(Long.parseLong(cursor.getString(2)));
    pers.setTime(cursor.getString(3));
    pers.setTitle(cursor.getString(4));
    pers.setForename(cursor.getString(5));
    pers.setLastname(cursor.getString(6));
    pers.setParking(cursor.getString(7));
    pers.setNumberpeople(Integer.parseInt(cursor.getString(8)));
    pers.setAccessreqornot(cursor.getString(9));
    pers.setAccessreq(cursor.getString(10));
    pers.setTimetable(cursor.getString(11));

    Log.d("getPerson()", pers.toString());

    // 5. return pers
    return pers;
}

I think the issue is due to my addPerson function called in importFromCsv(). My log at the beginning of the addPerson function returns me the right thing, but I think the db.insert is not going well. But I don't have any error on this.

My addPerson function :

public void addPerson(Person pers){
    Log.d("addPerson", pers.toString());
    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put(SID, pers.getSid()); 
    values.put(ATTENDED, pers.getAttended());
    values.put(SERIAL, pers.getSerial());
    values.put(TIME, pers.getTime());
    values.put(TITLE, pers.getTitle());
    values.put(FORENAME, pers.getForename());
    values.put(LASTNAME, pers.getLastname());
    values.put(PARKING, pers.getParking());
    values.put(NUMBERPEOPLE, pers.getNumberpeople());
    values.put(ACCESSREQORNOT, pers.getAccessreqornot());
    values.put(ACCESSREQ, pers.getAccessreq());
    values.put(TIMETABLE, pers.getTimetable());

    // 3. insert
    db.insert(TABLE_PERSONS, // table
            null, //nullColumnHack
            values); // key/value -> keys = column names/ values = column values
}

Thanks for your reading time, I would be really grateful if someone had any idea.

EDIT : Stacktrace :

07-08 12:01:57.755: D/addPerson(10828): Person [sid=9290, attended=, serial=92906,    time=2014-04-07 18:44:59, title=Miss, forename=Ladina, lastname=Clement, parking=,    numberpeople=2, accessreqornot=No, accessreq=, timetable=Fine]
07-08 12:01:57.755: D/addPerson(10828): Person [sid=9291, attended=, serial=92907, time=2014-04-08 18:44:59, title=Miss, forename=Ladina2, lastname=Clement2, parking=, numberpeople=2, accessreqornot=No, accessreq=, timetable=Fine]
07-08 12:01:57.763: D/addPerson(10828): Person [sid=9292, attended=, serial=92908, time=2014-04-07 18:44:59, title=Miss, forename=Ladina3, lastname=Clement3, parking=, numberpeople=2, accessreqornot=No, accessreq=, timetable=Fine]
07-08 12:01:59.193: D/ViewRootImpl(10828): ViewRoot  TouchDown(Absolute) DOWN (357 , 189)
07-08 12:01:59.247: D/getAllPersons()(10828): []

Do you have checked if there is some data stored? After inserting something. — Víctor Martín
– Víctor Martín, Commented Jul 8, 2014 at 10:49
Although not aware of the complexity of your program, an easier way would be this — Junaid
– Junaid, Commented Jul 8, 2014 at 10:50
You just copy your DB from the internal folder -- you dont need a rooted device. Check for a better explanation here — Junaid
– Junaid, Commented Jul 8, 2014 at 11:01
You should print the output of your db.insert(TABLE_PERSONS... call as the return code tells you "the row ID of the newly inserted row, or -1 if an error occurred" — dave.c
– dave.c, Commented Jul 8, 2014 at 11:14

Simas · Accepted Answer · 2014-07-08 10:51:13Z

2

Change this line:

if (cursor != null) {
    cursor.moveToFirst();
}

to

if (!cursor.moveToFirst()) {
    // do something when there are no results
}

The null check on the cursor is redundant. You can check if the cursor is empty by doing cursor.moveToFirst(). If it returns false you should prevent executing further commands on the cursor like you are doing later e.g. cursor.getString(0).

answered Jul 8, 2014 at 10:51

Simas

44.3k12 gold badges93 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Junaid Over a year ago

Why not use an else condition? (cursor != null && cursor.moveToFirst())?

Simas Over a year ago

@ByzantineFailure no need to check for null cursor. And why use else condition and enclose a massive code block when you can call return if there are no query results.

Junaid Over a year ago

I am not sure about the performance effects, but it would be interesting to research about this. WIll update and keep posted here.

Kiruchi Over a year ago

I tried to change it, and the "something when there are no results" is not triggered, so it has to be something else.

Nava2011 · Accepted Answer · 2014-07-08 11:11:45Z

My data is like this:

25-07-14 12:00,15,52,16,50,42,58,63,62,52
22-06-14 14:00,15,52,16,50,42,58,63,62,52
12-09-14 19:00,45,51,16,50,42,58,13,34,52
02-02-14 16:00,15,52,16,50,42,58,63,62,52
01-05-14 12:00,15,52,16,50,42,58,63,62,52

i have read that data like this you can find once. In the file path i am checking is that csv file are not.

FilePath = data.getData().getPath();
                if(FilePath.substring(FilePath.length()-4).endsWith(".csv"))

And the code is here

 private void readcsvfile() {

    if(FilePath.length()>4)
    {
    dataGridTable = new DgaDataGridTable(context);
    equipmentTable = new EquipmentTable(context);

    try {
        BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(FilePath)));

        String line;

        while ((line=reader.readLine())!=null) 
        {
            info = new DgaDataGridInfo();
            String[] rowData = line.split(",");

            if(rowData[0].length()!=0&&rowData[1].length()!=0&&rowData[2].length()!=0&&rowData[3].length()!=0&&rowData[4].length()!=0&&rowData[5].length()!=0&&rowData[6].length()!=0&&rowData[7].length()!=0&&rowData[8].length()!=0&&rowData[9].length()!=0)
            {
                info.setDateadded(rowData[0]);
                info.setH2(Integer.parseInt(rowData[1]));
                info.setCh4(Integer.parseInt(rowData[2]));
                info.setC2h2(Integer.parseInt(rowData[3]));
                info.setC2h4(Integer.parseInt(rowData[4]));
                info.setC2h6(Integer.parseInt(rowData[5]));
                info.setCo(Integer.parseInt(rowData[6]));
                info.setCo2(Integer.parseInt(rowData[7]));
                info.setO2(Integer.parseInt(rowData[8]));
                info.setN2(Integer.parseInt(rowData[9]));
                info.setTdcg(Integer.parseInt(rowData[1])+Integer.parseInt(rowData[2])+Integer.parseInt(rowData[5])+Integer.parseInt(rowData[4])+Integer.parseInt(rowData[3])+Integer.parseInt(rowData[6]));
                equipmentname = equipment_spinner.getSelectedItem().toString();

                info.setEquipid(equipmentTable.getEquipmentId(equipmentname));

                dataGridTable.insertRecord(info);

                Toast.makeText(this, "Dga Records Succesfully Added", Toast.LENGTH_LONG).show();
                loadAllDgaRecords();

            }
            else
            {
                showAlertDialog();
            }

        }

    } catch (Exception e) {
        e.printStackTrace();
    }
    }
    else
        Toast.makeText(this, "Please choose csv file", Toast.LENGTH_LONG).show();

}

Thanks

Collectives™ on Stack Overflow

Android sqlite insert issue

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related