database.sqlite.SQLiteException: near "create"

Question

I just started learning how to develop in Android and my first project is building an app using SQLite to store student names and consequently displaying the list. I am getting the below error message when attempting to run the application: database.sqlite.SQLiteException: near "create": syntax error (code 1): , while compiling: SELECT _id, name FROM create table Group(_id integer primary key autoincrement, name text not null);

MyDatabase class:when call getAllContacts iam see force close;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

    public class DBAdapter {
    static final String KEY_ROWID = "_id";
    static final String KEY_NAME = "name";

    static final String TAG = "DBAdapter";

    static final String DATABASE_NAME = "MyDB";
    static final String DATABASE_MAINTABLE = "Group";

    static final int DATABASE_VERSION = 3;

    static final String CREATE_MAINTABLE =
            " Create table " + DATABASE_MAINTABLE  +"(" + KEY_ROWID + " integer primary key ,"
            + KEY_NAME + " text not null);";
        private static final String SQL_DELETE_TABLES =
                "DROP TABLE IF EXISTS " + DATABASE_MAINTABLE;

    final Context context;

    DatabaseHelper DBHelper;
    SQLiteDatabase db;

    public DBAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            try {
                db.execSQL(CREATE_MAINTABLE);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            if (newVersion > oldVersion) {
                Log.w("DBAdapter", "Updating database from version " + oldVersion + " to "
                        + newVersion + " .Existing data will be lost.");

                db.execSQL( SQL_DELETE_TABLES);
                onCreate(db);
            }
        }
    }
    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---
    public void close() 
    {
        DBHelper.close();
    }

    //---insert a contact into the database---
    public long insertContact(String name,String tabale)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);

        return db.insert(tabale, null, initialValues);
    }

    //---deletes a particular contact---
    public boolean deleteContact(String id,String tabale)
    {
        return db.delete(tabale, KEY_ROWID + "=" + id, null) > 0;
        //return db.delete(tabale, KEY_ROWID == id , null) > 0;
    }

    //---retrieves all the contacts---
    public  List<ListAdapterdb> getAllContacts(String tabale)
    {

        Cursor cursor = db.query(tabale, new String[] {KEY_ROWID, KEY_NAME
        }, null, null, null, null, null);
        List<ListAdapterdb> mokhatabha = cursorToList(cursor);
        return mokhatabha;
 }
    //in method ro joda gune tarif kardim (ke cursor ro migire va mokhatab ha ro bar migardune)
    //chun niaz nabashe dakhele method haye dige in cod ha ro har bar copy konim va faghat sedash bezanim kafi bashe
    private List<ListAdapterdb> cursorToList(Cursor cursor) {
         List<ListAdapterdb> mokhatabha = new ArrayList<ListAdapterdb>();
         if (cursor.getCount() > 0)
         {
             while (cursor.moveToNext()) {
                 ListAdapterdb mokhatab = new ListAdapterdb();
             ///    mokhatab.setId(cursor.getString(0));
                //mokhatab.setname(cursor.getString(1));

                mokhatabha.add(mokhatab);
             } ;
         }
         return mokhatabha;
    }

    //---retrieves a particular contact---
    public Cursor getContact(String namegroup,String tabale) throws SQLException
    {
        Cursor mCursor =
                db.query(true, tabale, new String[] {KEY_ROWID,
                KEY_NAME}, KEY_NAME + " LIKE '" + namegroup + "'", null,
                null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    ///search
    public List<ListAdapterdb> findContacts(String name,String tabale) throws SQLException
    {
        Cursor cursor =
                // % ha yani inke mohem nis ghabl ya badesh harchi mikhad bashe
                db.query(true, tabale, new String[] {KEY_ROWID,
                KEY_NAME}, KEY_NAME + " LIKE '%" + name + "%'", null,
                null, null, null, null);
        List<ListAdapterdb> mokhatabha = cursorToList(cursor);
        return mokhatabha;
    }





    //---updates a contact---
    public boolean updateContact(long rowId, String name, String email) 
    {
        ContentValues args = new ContentValues();
        args.put(KEY_NAME, name);

        return db.update(DATABASE_MAINTABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }

}

This is my logcat:

-08 21:31:58.652 19393-19393/www.project_category.ir.project_category W/DBAdapter: Updating database from version 2 to 3 .Existing data will be lost.
12-08 21:31:58.652 19393-19393/www.project_category.ir.project_category E/SQLiteLog: (1) near "Group": syntax error
12-08 21:31:58.652 19393-19393/www.project_category.ir.project_category D/AndroidRuntime: Shutting down VM
12-08 21:31:58.652 19393-19393/www.project_category.ir.project_category W/dalvikvm: threadid=1: thread exiting with uncaught exception (group=0x417d5898)
12-08 21:31:58.652 19393-19393/www.project_category.ir.project_category E/AndroidRuntime: FATAL EXCEPTION: main
java.lang.RuntimeException: Unable to start activity ComponentInfo{www.project_category.ir.project_category/www.project_category.ir.project_category.MGroupActivity}: android.database.sqlite.SQLiteException: near "Group": syntax error (code 1): , while compiling: DROP TABLE IF EXISTS Group
  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2308)
  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2362)
  at android.app.ActivityThread.access$700(ActivityThread.java:168)
  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1329)
  at android.os.Handler.dispatchMessage(Handler.java:99)
  at android.os.Looper.loop(Looper.java:177)
  at android.app.ActivityThread.main(ActivityThread.java:5493)
  at java.lang.reflect.Method.invokeNative(Native Method)
  at java.lang.reflect.Method.invoke(Method.java:525)
  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1225)
  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1041)
  at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: near "Group": syntax error (code 1): , while compiling: DROP TABLE IF EXISTS Group
  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1120)
  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:693)
  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
  at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
  at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1794)
  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1725)
  at www.project_category.ir.project_category.DBAdapter$DatabaseHelper.onUpgrade(DBAdapter.java:66)
  at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
  at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
  at www.project_category.ir.project_category.DBAdapter.open(DBAdapter.java:74)
  at www.project_category.ir.project_category.MGroupActivity.onCreate(MGroupActivity.java:34)
  at android.app.Activity.performCreate(Activity.java:5372)
  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1104)
  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2270)
  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2362) 
  at android.app.ActivityThread.access$700(ActivityThread.java:168) 
  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1329) 
  at android.os.Handler.dispatchMessage(Handler.java:99) 
  at android.os.Looper.loop(Looper.java:177) 
  at android.app.ActivityThread.main(ActivityThread.java:5493) 
  at java.lang.reflect.Method.invokeNative(Native Method) 
  at java.lang.reflect.Method.invoke(Method.java:525) 
  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1225) 
  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1041)

B_Mahoney · Accepted Answer · 2015-12-08 18:53:27Z

1

The SQL standard specifies a huge number of keywords which may not be used as the names of tables, indices, columns, databases, user-defined functions, collations, virtual table modules, or any other named object.

You are trying to create a table named Group, which is an SQLite keyword, as seen here - https://www.sqlite.org/lang_keywords.html

If you'd like, the most common way around this is to put Group in double quotes - "Group" - as so. Double quotes are used as identifiers in SQLite. However, you might decide to rename the table to avoid potential future confusion either on your part or with future collaborators.

answered Dec 8, 2015 at 18:53

B_Mahoney

211 silver badge2 bronze badges

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

Comments

Karan Khurana · Accepted Answer · 2015-12-09 13:46:05Z

0

I think you have problem with table creating syntax try updating with this code

static final String CREATE_MAINTABLE =
        " Create table " + DATABASE_MAINTABLE  +"(" + KEY_ROWID + " integer primary key ,"
        + KEY_NAME + " text not null)";

for further guidance use this example

answered Dec 9, 2015 at 13:46

Karan Khurana

5858 silver badges22 bronze badges

Comments

Buddy · Accepted Answer · 2015-12-08 18:19:20Z

0

"GROUP" is a SQLite keyword, try using another table name, like "GroupTbl".

Or put quotes around it: CREATE TABLE "GROUP" ....

https://www.sqlite.org/lang_keywords.html

answered Dec 8, 2015 at 18:19

Buddy

11k5 gold badges47 silver badges63 bronze badges

2 Comments

user4816237 Over a year ago

iam chang but this error :ry E/SQLiteLog: (1) near "Create": syntax error

user4816237 Over a year ago

new error is E/SQLiteLog: (1) near "Create": syntax error

Collectives™ on Stack Overflow

database.sqlite.SQLiteException: near "create"

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related