Android - SQlite insert not inserting

Question

So i am trying to insert some data in the internal sqlite database but after i run the insert no data has been added. There are, as far as i can see no errors in the logs and every debug log i put into it is shown. If i try to run the query that is returned in the log in sqlitestudio it works without a problem so i haven't got a clue as to what is going wrong.

@Override
public void onCreate(SQLiteDatabase db) {
    String SQL = pictureTable();
    db.execSQL(SQL);
}

private String pictureTable() {
    return "CREATE TABLE geophoto_db_pictures ( picid integer,"
            + "name varying character(50),"
            + "city varying character(20) NOT NULL,"
            + "zipcode varying character(20) NOT NULL,"
            + "country varying character(20) NOT NULL,"
            + "picdate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,"
            + "tags varying character(200),"
            + "image varying character(200) NOT NULL,"
            + "uploaded integer NOT NULL DEFAULT 0, PRIMARY KEY (picid))";
}

@Override
public void savePicture(Picture pic) {
    Log.d(LOG_TAG, "saving picture started. Data: " + pic.getName());

    // clean the inputs
    String name = pic.getName();
    String city = pic.getCity();
    if (city != null) {
        city = "'" + city + "'";
    }
    String country = pic.getCountry();
    if (country != null) {
        country = "'" + country + "'";
    }
    String zip = pic.getZipcode();
    if (zip != null) {
        zip = "'" + zip + "'";
    }
    String tags = tagsToString(pic.getTags());
    String image = pic.getImage();

    // Insert Query, all possible null values on "not null" rows will be
    // replaced by a default value.
    String SQL = "INSERT INTO geophoto_db_pictures(name, city, zipcode, country, tags, image)"
            + "VALUES('"
            + name
            + "',"
            + "IFNULL("
            + city
            + ", 'Unknown')"
            + ","
            + "IFNULL("
            + zip
            + ", 'Unknown')"
            + ","
            + "IFNULL("
            + country + ",'Unknown')" + ",'" + tags + "','" + image + "')";
    Log.d(LOG_TAG, SQL);
    executeWriteQuery(SQL);
    ArrayList<Picture> list = getAllPictures();
    Log.d(LOG_TAG, "Size :"+list.size());
}

private Cursor executeWriteQuery(String query){
    Log.d(LOG_TAG, "execute write query");
    SQLiteDatabase db = getWritableDatabase();
    Cursor response = db.rawQuery(query, null);
    Log.d(LOG_TAG, "write query executed");
    return response;
}

All tips/help greatly appreciated!

Thomas

Try to put a semicolon here `"uploaded integer NOT NULL DEFAULT 0, PRIMARY KEY (picid));"` — Jibran Khan, May 01 '15 at 11:57
@JibranKhan you are a genius :D everything works now! so thanks a lot. Would you have any idea as to why this happended? Since my db did have the right tables/columns and there were no sql errors.. — ThomasS, May 01 '15 at 12:41
Good it worked for you. Table was not created so insertion will also not work. When executing the query through external string, you have to provide inner end of statement (i.e semicolon). Using primitive functions of SQLite will not cause or require semicolon — Jibran Khan, May 01 '15 at 13:00

score 1 · Answer 1 · answered May 01 '15 at 12:04

The problem you are facing is that you are trying to use rawQuery() to insert a record, when you should be using execSQL() instead (see this answer).

So, the correct code for executeWriteQuery would be as follows:

private void executeWrite(String command){
    Log.d(LOG_TAG, "execute write");
    SQLiteDatabase db = getWritableDatabase();
    db.execSQL(command); 
    Log.d(LOG_TAG, "write executed");
}

Also, consider using insert() instead as that will allow you to get a return value to determine whether or not the data was inserted successfully.

I have upvoted your answer as well since it is indeed better to use insert. — ThomasS, May 01 '15 at 14:18

score 1 · Accepted Answer · answered May 01 '15 at 13:06

Try to put a semicolon at the end of table creation query. In your case as show below

private String pictureTable() {
    return "CREATE TABLE geophoto_db_pictures ( picid integer,"
            + "name varying character(50),"
            + "city varying character(20) NOT NULL,"
            + "zipcode varying character(20) NOT NULL,"
            + "country varying character(20) NOT NULL,"
            + "picdate datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,"
            + "tags varying character(200),"
            + "image varying character(200) NOT NULL,"
            + "uploaded integer NOT NULL DEFAULT 0, PRIMARY KEY (picid));";
}

While providing a query through an external String, you will need to provide SQL query with an end of statement ;. Using the primitive SQLite does not require ; as it just takes arguments and create function query itself. I have experienced both cases and I ended up understanding the way I have put it here.

Android - SQlite insert not inserting

2 Answers2

Linked