0

I am getting the following error when I am using the code below. 

02-24 12:13:16.972: E/AndroidRuntime(11024): Caused by: android.database.sqlite.SQLiteException: near "?": syntax error: , while compiling: DELETE FROM messages WHERE ? NOT IN ( SELECT ? FROM ? WHERE ?=? ORDER BY ? DESC LIMIT ?) AND ?=?
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:41)
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:1149)
02-24 12:13:16.972: E/AndroidRuntime(11024):    at android.database.sqlite.SQLiteDatabase.delete(SQLiteDatabase.java:1623)

Code:

// query idea from
// http://stackoverflow.com/questions/6528117/keep-only-n-last-records-in-sqlite-database-sorted-by-date
// I have added the AND ?=? because I only want to delete records from a particular conversation thread, not the entire table.
String whereClause = "? NOT IN ( SELECT ? FROM ? WHERE ?=? ORDER BY ? DESC LIMIT ?) AND ?=?";

String[] whereArgs = new String[]{
        DbAdapter.COL_ROWID,
        DbAdapter.COL_ROWID,
        DBConstants.DB_TABLE_MESSAGES,
        DbAdapter.COL_CONVERSATION_ID,
        String.valueOf(conversationId),
        DbAdapter.COL_TIMESTAMP,
        String.valueOf(numMessagesToRetain),
        DbAdapter.COL_CONVERSATION_ID,
        String.valueOf(conversationId)
};

deleted = mDb.delete(DBConstants.DB_TABLE_MESSAGES, whereClause,
        whereArgs);

Also, the Android documentation for the SQLite delete method seems to be incomplete regarding the whereArgs parameter.

public int delete (String table, String whereClause, String[] whereArgs)

Since: API Level 1
Convenience method for deleting rows in the database.
Parameters

table   the table to delete from
whereClause the optional WHERE clause to apply when deleting. Passing null will delete all rows.
Returns

the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

It works fine if I use the following where clause:

String whereClause = String
.format("%s NOT IN ( SELECT %s FROM %s WHERE %s=%s ORDER BY %s DESC LIMIT %s) AND %s=%s",
        DbAdapter.COL_ROWID,
        DbAdapter.COL_ROWID,
        DBConstants.DB_TABLE_MESSAGES,
        DbAdapter.COL_CONVERSATION_ID,
        String.valueOf(conversationId),
        DbAdapter.COL_TIMESTAMP,
        String.valueOf(numMessagesToRetain),
        DbAdapter.COL_CONVERSATION_ID,
        String.valueOf(conversationId));

The only thing I can think of is that the delete method does not support parameterised queries, but the following query works, so it rules out that.

mDb.delete(DB_TABLE_MESSAGES, COL_ROWID + "=?",
                new String[]{ String.valueOf(rowId)});

Solution

Thanks to antlersoft, the solution is like so:

String whereClause = String
        .format("(%s NOT IN ( SELECT %s FROM %s WHERE %s=? ORDER BY %s DESC LIMIT ?)) AND %s=?",
                DbAdapter.COL_ROWID,
                DbAdapter.COL_ROWID,
                DBConstants.DB_TABLE_MESSAGES,
                DbAdapter.COL_CONVERSATION_ID,
                DbAdapter.COL_TIMESTAMP,
                DbAdapter.COL_CONVERSATION_ID);

String[] whereArgs = new String[]{
        String.valueOf(conversationId),
        String.valueOf(numMessagesToRetain),
        String.valueOf(conversationId)
};

deleted = mDb.delete(DBConstants.DB_TABLE_MESSAGES, whereClause,
        whereArgs);
aj.esler
  • 921
  • 1
  • 8
  • 18

1 Answers1

1

AFAIK, you are trying to use parameterization where it is not allowed.

You can only use parameterization to replace what would otherwise be constant values in your query.

You are trying to use it to replace identifiers, as well, which won't work.

If you want the identifiers to be dynamic, replace them via string manipulation before you pass the query to SQLite; don't pass them as ? and expect SQLite to make sense of the query..

antlersoft
  • 14,636
  • 4
  • 35
  • 55