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
– Jibran Khan, Commented May 1, 2015 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
– ThomasS, Commented May 1, 2015 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
– Jibran Khan, Commented May 1, 2015 at 13:00

Jibran Khan · Accepted Answer · 2015-05-01 13:06:16Z

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.

anthonycr · Accepted Answer · 2015-05-01 12:04:41Z

1

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.

answered May 1, 2015 at 12:04

anthonycr

4,1861 gold badge30 silver badges37 bronze badges

1 Comment

ThomasS Over a year ago

I have upvoted your answer as well since it is indeed better to use insert.

Collectives™ on Stack Overflow

Android - SQlite insert not inserting

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related