near "NOT": syntax error: , while compiling: CREATE VIRTUAL TABLE IF NOT EXISTS

Question

I have a scenario that "create virtual table if not exists searchcontent using fts4(_id text, desc text)" is crashing with: near "NOT": syntax error: , while compiling: CREATE VIRTUAL TABLE IF NOT EXISTS

This is only happening on a galaxy nexus phone which is running 4.0.2. when I try it on a galaxy note 2 that is running 4.1.2 everything is working fine. If I remove "if not exists" again it will work on nexus phone. Does anyone know what is the error? is it android version problem?

in my sqlitehelper class:

public static final String SearchContentTable_DATABASE_CREATE = "CREATE VIRTUAL TABLE IF NOT EXISTS " + TABLE_NAME + " USING fts4 ( " + 
        COLUMN_ID + " " + "TEXT" + " , " + 
        COLUMN_TITLE + " " + "TEXT" + " , " + 
        COLUMN_DESCRIPTION + " " + "TEXT" + " , " + 
        COLUMN_KEYWORDS + " " + "TEXT" + " , " + 
        COLUMN_TYPE + " " + "TEXT" + " );";

@Override
public void onCreate( SQLiteDatabase database )
{
   .
   .
   .
    database.execSQL( SearchContentTable_DATABASE_CREATE );
   .
   .
   .
}

I'm facing the same problem on versions 4.0.x (4.0.1, 4.0.2, 4.0.3, and 4.0.4). I have an app compatible with v4+ versions and only versions 4.0.x present this problem. — JPMagalhaes
– JPMagalhaes, Commented Nov 25, 2013 at 21:46
I've tried the "EXIST" solution, but it is not supported, only "EXISTS". I really think this is an issue on these versions. Something related to "VIRTUAL TABLE" and "IF NOT EXISTS" commands used together, but I don't have a device running any of these versions to test possible workarounds and it has been very hard to find a solution. Any tip will be welcome! — JPMagalhaes
– JPMagalhaes, Commented Nov 25, 2013 at 21:54

JPMagalhaes · Accepted Answer · 2013-11-26 07:31:36Z

2

"IF NOT EXISTS" statement does not work in some versions of Android, specifically 4.0.x versions. At least when creating virtual tables. It has crashed one of my published apps several times in versions 4.0.4 and 4.0.3. My app works properly in versions 4+ but crashes as soon as it uses DB only in these specific versions.

In fact, you don't need "IF NOT EXISTS" statement since you make correct use of onCreate and onUpgrade functions in your SQLiteOpenHelper implementation. I was using it just for precaution and to avoid problems during development.

answered Nov 26, 2013 at 7:31

JPMagalhaes

3,6211 gold badge27 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

near "NOT": syntax error: , while compiling: CREATE VIRTUAL TABLE IF NOT EXISTS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related