Error on sqlite database with 2 table creation in android, unfortunately,"dbtest2 has stopped"

159 views Asked by At
package com.example.dbtest2;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper {
    public static String DATABASE_NAME="riyas.db";
    public static int DATABASE_VERSION=1;



    //table login
    public static String TABLE_NAME1="login";

    public static String COL_L1="ID";
    public static String COL_L2="USERNAME";
    public static String COL_L3="PASSWORD";

    //table record
    public static String TABLE_NAME2="record";

    public static String COL_R1="ID";
    public static String COL_R2="NAME";
    public static String COL_R3="ADDRESS";
    public static String COL_R4="MOBILE";
    public static String COL_R5="MALE";
    public static String COL_R6="FEMALE";
    public static String COL_R7="DOC_NAME";
    public static String COL_R8="CHECK1";
    public static String COL_R9="CHECK2";

    public static String mytab1="create table"+TABLE_NAME1+" ("
            +COL_L1 +" INTEGER PRIMARY KEY AUTOINCREMENT,NOT NULL,"
            +COL_L2 +"TEXT,NOT NULL,"
            +COL_L3+ "TEXT,NOT NULL,"
            +");";


     public static String mytab2="create table"+TABLE_NAME2+" ("
            + COL_R1+ "INTEGER PRIMARY KEY AUTOINCREMENT,NOT NULL,"
            + COL_R2 +"TEXT,"
             +COL_R3+ "TEXT,"
             +COL_R4 +"INTEGER,"
             +COL_R5 +"TEXT,"
             +COL_R6 +"TEXT,"
            + COL_R7 +"TEXT,"
            + COL_R8+"TEXT,"
            + COL_R9 +"TEXT,"
              +");";

    public DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        // TODO Auto-generated constructor stub
        SQLiteDatabase Cdb=this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase Cdb) {
        // TODO Auto-generated method stub
        Cdb.execSQL(mytab1);
       Cdb.execSQL(mytab2);
               }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
    db.execSQL("DROP TABLE IF EXIST"+TABLE_NAME1);
    db.execSQL("DROP TABLE IF EXIST"+TABLE_NAME2);

    onCreate(db);
    }
}

my main activity is...

package com.example.dbtest2;

import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;

public class MainActivity extends ActionBarActivity {
    DataBaseHelper mydb;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mydb=new DataBaseHelper(this);

    }
}

logcat result is....

06-27 02:09:36.392: E/AndroidRuntime(13937): FATAL EXCEPTION: main
06-27 02:09:36.392: E/AndroidRuntime(13937): Process:
com.example.dbtest2, PID: 13937 06-27 02:09:36.392:
E/AndroidRuntime(13937): java.lang.RuntimeException: Unable to start
activity
ComponentInfo{com.example.dbtest2/com.example.dbtest2.MainActivity}:
android.database.sqlite.SQLiteException: near "tablelogin": syntax
error (code 1): , while compiling: create tablelogin (ID INTEGER
PRIMARY KEY AUTOINCREMENT,NOT NULL,USERNAMETEXT,NOT
NULL,PASSWORDTEXT,NOT NULL,); 06-27 02:09:36.392:
E/AndroidRuntime(13937):    at
android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2195)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2245)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.app.ActivityThread.access$800(ActivityThread.java:135) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.os.Handler.dispatchMessage(Handler.java:102) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
android.os.Looper.loop(Looper.java:136) 06-27 02:09:36.392:
E/AndroidRuntime(13937):    at
android.app.ActivityThread.main(ActivityThread.java:5017) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
java.lang.reflect.Method.invokeNative(Native Method) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
java.lang.reflect.Method.invoke(Method.java:515) 06-27 02:09:36.392:
E/AndroidRuntime(13937):    at
com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
dalvik.system.NativeStart.main(Native Method) 06-27 02:09:36.392:
E/AndroidRuntime(13937): Caused by:
android.database.sqlite.SQLiteException: near "tablelogin": syntax
error (code 1): , while compiling: create tablelogin (ID INTEGER
PRIMARY KEY AUTOINCREMENT,NOT NULL,USERNAMETEXT,NOT
NULL,PASSWORDTEXT,NOT NULL,); 06-27 02:09:36.392:
E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native
Method) 06-27 02:09:36.392: E/AndroidRuntime(13937):    at
 android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1672)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
com.example.dbtest2.DataBaseHelper.onCreate(DataBaseHelper.java:61)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
 android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:252)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
 android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
com.example.dbtest2.DataBaseHelper.<init>(DataBaseHelper.java:55)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
com.example.dbtest2.MainActivity.onCreate(MainActivity.java:21) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
android.app.Activity.performCreate(Activity.java:5231) 06-27
02:09:36.392: E/AndroidRuntime(13937):  at
android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
06-27 02:09:36.392: E/AndroidRuntime(13937):    at
android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2159)
06-27 02:09:36.392: E/AndroidRuntime(13937):    ... 11 more 06-27
02:09:43.582: I/Process(13937): Sending signal. PID: 13937 SIG: 9
2

There are 2 answers

1
SteveFerg On

At first look, it seems to be your table definitions (too many commas in the wrong place), try changing to:

public static String mytab1="create table"+TABLE_NAME1+" ("
        +COL_L1 +" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        +COL_L2 +" TEXT NOT NULL,"
        +COL_L3+ " TEXT NOT NULL"
        +");";


 public static String mytab2="create table"+TABLE_NAME2+" ("
        + COL_R1+ "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        + COL_R2 +" TEXT,"
         +COL_R3+ " TEXT,"
         +COL_R4 +" INTEGER,"
         +COL_R5 +" TEXT,"
         +COL_R6 +" TEXT,"
        + COL_R7 +" TEXT,"
        + COL_R8+" TEXT,"
        + COL_R9 +" TEXT"
          +");";

also there is no space between your variable names and the data type. LogCat is letting you know there are syntax errors.

3
Phantômaxx On

Your table creation are both wrong:
You put some unneeded commas and didn't provide some required spaces, as well.

These are the correct ones

public static String mytab1 = "CREATE TABLE " + TABLE_NAME1 + " ("
    + COL_L1 + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
    + COL_L2 + " TEXT NOT NULL,"
    + COL_L3 + " TEXT NOT NULL)";

public static String mytab2 = "CREATE TABLE " + TABLE_NAME2 + " ("
    + COL_R1 + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
    + COL_R2 + " TEXT,"
    + COL_R3 + " TEXT,"
    + COL_R4 + " INTEGER,"
    + COL_R5 + " TEXT,"
    + COL_R6 + " TEXT,"
    + COL_R7 + " TEXT,"
    + COL_R8 + " TEXT,"
    + COL_R9 + " TEXT)";