Sqlite Syntax Error with Android

Question

Can anyone point me out where is the syntax error here?

For some reason I can't find it. I'm not sure if my approach is the correct one (I'm still testing it out).

Here's my code:

    final Cursor cursor;

    final String[] PROJECTION_SEARCH_FOR_CONTACT = new String[] {
        Contacts._ID,
        Contacts.DISPLAY_NAME,
        Contacts.PHOTO_ID,
        "name_raw_contact_id",
        Contacts.Data.DATA1 // Telephone number
    };

    // Search by Display Name
    if(where == 0)
    {
        cursor = contentResolver.query( Data.CONTENT_URI, 
                                        PROJECTION_SEARCH_FOR_CONTACT, 
                                        Contacts.DISPLAY_NAME + " LIKE '" + searchFor + "%' AND " + Data.MIMETYPE + " = " + Contacts.CONTENT_ITEM_TYPE, 
                                        null, 
                                        null);
    }

    // Search by Phone Number
    else if(where == 1)
    {
        cursor = contentResolver.query( Data.CONTENT_URI, 
                                        PROJECTION_SEARCH_FOR_CONTACT, 
                                        Contacts.Data.DATA1+ " LIKE '" + searchFor + "%' AND " + Contacts.Data.MIMETYPE + " = " + Phone.CONTENT_ITEM_TYPE, 
                                        null, 
                                        null);
    }

    // Search by Email
    else 
    {
        cursor = contentResolver.query( Data.CONTENT_URI, 
                                        PROJECTION_SEARCH_FOR_CONTACT, 
                                        Contacts.Data.DATA1+ " LIKE '" + searchFor + "%' AND " + Contacts.Data.MIMETYPE + " = " + Email.CONTENT_ITEM_TYPE, 
                                        null, 
                                        null);
    }

Here are the exception errors.

For where == 0 :

03-05 00:31:06.473: E/SQLiteLog(2012): (1) near ".": syntax error
03-05 00:31:06.477: E/DatabaseUtils(2012): Writing exception to parcel
03-05 00:31:06.477: E/DatabaseUtils(2012): android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: SELECT _id, display_name, photo_id, name_raw_contact_id, data1 FROM view_data data LEFT OUTER JOIN (SELECT data_usage_stat.data_id, SUM(data_usage_stat.times_used) as times_used, MAX(data_usage_stat.last_time_used) as last_time_used FROM data_usage_stat GROUP BY data_id) as data_usage_stat ON (data_usage_stat.data_id=data._id) WHERE (1) AND ((display_name LIKE 'c%' AND mimetype = vnd.android.cursor.item/contact))
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteQueryBuilder.validateQuerySql(SQLiteQueryBuilder.java:412)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:389)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.query(ContactsProvider2.java:6438)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.queryLocal(ContactsProvider2.java:6386)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.query(ContactsProvider2.java:4999)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.content.ContentProvider$Transport.query(ContentProvider.java:200)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:112)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at android.os.Binder.execTransact(Binder.java:404)
03-05 00:31:06.477: E/DatabaseUtils(2012):  at dalvik.system.NativeStart.run(Native Method)

For where == 1 :

03-05 00:32:21.922: E/SQLiteLog(2012): (1) near ".": syntax error
03-05 00:32:21.938: E/DatabaseUtils(2012): Writing exception to parcel
03-05 00:32:21.938: E/DatabaseUtils(2012): android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: SELECT _id, display_name, photo_id, name_raw_contact_id, data1 FROM view_data data LEFT OUTER JOIN (SELECT data_usage_stat.data_id, SUM(data_usage_stat.times_used) as times_used, MAX(data_usage_stat.last_time_used) as last_time_used FROM data_usage_stat GROUP BY data_id) as data_usage_stat ON (data_usage_stat.data_id=data._id) WHERE (1) AND ((data1 LIKE 'c%' AND mimetype = vnd.android.cursor.item/phone_v2))
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteQueryBuilder.validateQuerySql(SQLiteQueryBuilder.java:412)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:389)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.query(ContactsProvider2.java:6438)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.queryLocal(ContactsProvider2.java:6386)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.query(ContactsProvider2.java:4999)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.content.ContentProvider$Transport.query(ContentProvider.java:200)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:112)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at android.os.Binder.execTransact(Binder.java:404)
03-05 00:32:21.938: E/DatabaseUtils(2012):  at dalvik.system.NativeStart.run(Native Method)

For where == 2 :

03-05 00:33:13.844: E/SQLiteLog(2012): (1) near ".": syntax error
03-05 00:33:13.852: E/DatabaseUtils(2012): Writing exception to parcel
03-05 00:33:13.852: E/DatabaseUtils(2012): android.database.sqlite.SQLiteException: near ".": syntax error (code 1): , while compiling: SELECT _id, display_name, photo_id, name_raw_contact_id, data1 FROM view_data data LEFT OUTER JOIN (SELECT data_usage_stat.data_id, SUM(data_usage_stat.times_used) as times_used, MAX(data_usage_stat.last_time_used) as last_time_used FROM data_usage_stat GROUP BY data_id) as data_usage_stat ON (data_usage_stat.data_id=data._id) WHERE (1) AND ((data1 LIKE 'c%' AND mimetype = vnd.android.cursor.item/email_v2))
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteQueryBuilder.validateQuerySql(SQLiteQueryBuilder.java:412)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.database.sqlite.SQLiteQueryBuilder.query(SQLiteQueryBuilder.java:389)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.query(ContactsProvider2.java:6438)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.queryLocal(ContactsProvider2.java:6386)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at com.android.providers.contacts.ContactsProvider2.query(ContactsProvider2.java:4999)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.content.ContentProvider$Transport.query(ContentProvider.java:200)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.content.ContentProviderNative.onTransact(ContentProviderNative.java:112)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at android.os.Binder.execTransact(Binder.java:404)
03-05 00:33:13.852: E/DatabaseUtils(2012):  at dalvik.system.NativeStart.run(Native Method)

laalto · Accepted Answer · 2014-03-05 11:36:16Z

4

... mimetype = vnd.android.cursor.item/contact

You need to put the string literals in '' single quotes. For example,

... Data.MIMETYPE + " = '" + Contacts.CONTENT_ITEM_TYPE + "'"

and similarly for the other cases.

answered Mar 5, 2014 at 11:36

laalto

153k68 gold badges293 silver badges306 bronze badges

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

2 Comments

dazito Over a year ago

Thanks, that's what happens when you (me, in this case) wants to code it fast for testing purposes. So solution is to use the selection field on the cursor. Like this:

cursor = contentResolver.query( Data.CONTENT_URI,  											PROJECTION_SEARCH_FOR_CONTACT,  											Contacts.DISPLAY_NAME + " LIKE '" + searchFor + "%' AND " + Data.MIMETYPE + " = ?",  											new String[]{Contacts.CONTENT_ITEM_TYPE},  											null);

laalto Over a year ago

Yes, that will also work. Using ? placeholders for literals is a good habit, even if in this case there's no direct benefit (nor harm).

Collectives™ on Stack Overflow

Sqlite Syntax Error with Android

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related