Android Sqlite INSERT error when table contains only _id

3
votes

I have a SQLite table that contains only the _id:

"create table rule (_id integer primary key);";

When running this set of commands:

ContentValues initialValues = new ContentValues();
mDb.insert(TABLE, null, initialValues)

I obtain the following exception:

android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: INSERT INTO rule(null) VALUES (NULL)

The initial error occurs because ContentValues cannot be empty. Android provides a convenience parameter called nullColumnHack that allows you to pass a single column with the value null, to bypass this problem.

However this doesn't apply in my case because the row id (_id) cannot be null! Based on the syntax found in the SQLite language docs, I would like to be able to run the SQLite code:

INSERT INTO rule DEFAULT VALUES;

How can i achieve something like this using the android insert method? Or is there something I need to add to my create statement?

UPDATE: In the situation where a table contains ONLY a rowid, the proper syntax is to use INSERT INTO __ DEFAULT VALUES.

The sqlite insert method listed in android does not support DEFAULT VALUES as an option.

A bug has been filed with google and to get support for default values the following commands would need to be executed:

mDb.execSQL("INSERT INTO rule DEFAULT VALUES;");
Cursor c = mDb.rawQuery("SELECT last_insert_rowid()",null);
c.moveToFirst();
int rowid = c.getInt(0);

As stated in the accepted answer, we can get around this (and DEFAULT VALUES) by using nullHackColumn and assigning the row id (_id) to null and letting SQLite make the conversion from null to the auto-incremented value.

5
androidsqlite

5 Answers

3
votes

As jeet mentioned you can provide nullColumnHack as a second parameter. And as you yourself mentioned autoincrement isn't necessary to increment a value of primary key. So the syntax:

insert into rule (_id) values(null)

where _id is primary key and autoincremented value is correct for sql. I think most SQL databases will replace null with new incremented value, at least MySQL, SQLite and Oracle can do this

Thus:

ContentValues cv = new ContentValues();
db.insert("rule", "_id", cv);

should give you desired results.

1
votes

You need to add autoincrement to your create table query like:

"create table rule (_id integer primary key autoincrement);";

In your case you need to manually set the ID of the row with each insert. this way it will increment it automatically when you insert an empty row as you did in your case.

0
votes

Try this way :

ContentValues initialValues= new ContentValues();
if(check here --id is null----)
{
  initialValues.put("_id", "0");  
}
else
{
  initialValues.put("_id", id);  
}

mDb.insert(TABLE, null, initialValues)
0
votes

Check following:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insert(java.lang.String, java.lang.String, android.content.ContentValues)

SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.

0
votes

the insert needs a null value you just have to put

db.insert ("people", null, c);