Syntax error in My sqlite when i add a new table

Syntax error in My sqlite when i add a new table

Now,i'm add a new table to sqlitedatabase and change a databae_version
from 1 to version 2 (my new table is category) this is my code in
BooksDBHelper
package com.example.mutitablesql.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class BooksDBHelper extends SQLiteOpenHelper {
//databse_name
public static String DATABSE_NAME = "bookguk";
// database_version
public static int DATABASE_VERSION = 2;
//table name
public static String TABLE_BOOK = "books";
public static String TABLE_IMAGE = "images";
public static String TABLE_INGREDIENT = "ingredients";
public static String TABLE_CATEGORY = "category";
//column_name
public static String KEY_ID = "id";
public static String KEY_TITLE = "title";
public static String KEY_SOLUTION = "solution";
public static String KEY_BOK_ID = "bookID";
public static String KEY_fILEPATH = "filepath";
public static String KEY_VALUE = "value" ;
public static String KEY_UNIT = "unit";
public static String KEY_NAME = "name";
public static String KEY_CAT_ID = "cat_id";
public static String BOOK_CREATE_SQL = "CREATE TABLE " + TABLE_BOOK + "(" +
""+KEY_ID+" INTEGER PRIMARY KEY
AUTOINCREMENT," +
""+KEY_TITLE+" TEXT(50) not null," +
""+KEY_SOLUTION+" TEXT(255) not null," +
""+KEY_CAT_ID+" INTEGER(11)," +
" FOREIGN KEY("+KEY_CAT_ID+") REFERENCE
"+TABLE_CATEGORY+" ("+KEY_ID+"))";
public static String IMAGE_CREATE_SQL = "CREATE TABLE " + TABLE_IMAGE + "(" +
""+KEY_ID+" INTEGER PRIMARY KEY
AUTOINCREMENT," +
""+KEY_fILEPATH+" VACHAR(255)," +
""+KEY_BOK_ID+" INTEGER(11)," +
" FOREIGN KEY("+KEY_BOK_ID+") REFERENCES
"+TABLE_BOOK+" ("+KEY_ID+"))";
public static String INGREDIENT_CREATE_SQL = "CREATE TABLE " +
TABLE_INGREDIENT + "(" +
""+KEY_ID+" INTEGER PRIMARY KEY
AUTOINCREMENT," +
""+KEY_VALUE+" DOUBLE(11)," +
""+KEY_UNIT+" VACHAR(50)," +
""+KEY_fILEPATH+" VACHAR(255)," +
""+KEY_BOK_ID+" INTEGER(11)," +
" FOREIGN KEY("+KEY_BOK_ID+")REFERENCES
"+TABLE_BOOK+" ("+KEY_ID+"))";
public static String CATEGORY_CREATE_SQL = "CREATE TABLE " +
TABLE_CATEGORY + "(" +
""+KEY_ID+" INTEGER PRIMARY KEY
AUTOINCREMENT," +
""+KEY_NAME+" VACHAR(50) " +
")";
//constructor
public BooksDBHelper(Context context) {
super(context, DATABSE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}
//create
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(BOOK_CREATE_SQL);
db.execSQL(IMAGE_CREATE_SQL);
db.execSQL(INGREDIENT_CREATE_SQL);
db.execSQL(CATEGORY_CREATE_SQL);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE " + TABLE_BOOK);
db.execSQL("DROP TABLE " + TABLE_IMAGE);
db.execSQL("DROP TABLE " + TABLE_INGREDIENT);
db.execSQL("DROP TABLE " + TABLE_CATEGORY);
onCreate(db);
}
}
And this is my logcat error edit
08-31 08:04:06.957: E/SQLiteLog(1683): (1) no such table: category
08-31 08:04:06.978: D/AndroidRuntime(1683): Shutting down VM
08-31 08:04:06.978: W/dalvikvm(1683): threadid=1: thread exiting with
uncaught exception (group=0x40a71930)
08-31 08:04:07.007: E/AndroidRuntime(1683): FATAL EXCEPTION: main
08-31 08:04:07.007: E/AndroidRuntime(1683): java.lang.RuntimeException:
Unable to start activity
ComponentInfo{com.example.mutitablesql/com.example.mutitablesql.MainActivity}:
android.database.sqlite.SQLiteException: no such table: category (code 1):
, while compiling: DROP TABLE category
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2180)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2230)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.ActivityThread.access$600(ActivityThread.java:141)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.ActivityThread$H.handleMessage(ActivityThread.java:1234)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.os.Handler.dispatchMessage(Handler.java:99)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.os.Looper.loop(Looper.java:137)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.ActivityThread.main(ActivityThread.java:5041)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
java.lang.reflect.Method.invokeNative(Native Method)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
java.lang.reflect.Method.invoke(Method.java:511)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:793)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
com.android.internal.os.ZygoteInit.main(ZygoteInit.java:560)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
dalvik.system.NativeStart.main(Native Method)
08-31 08:04:07.007: E/AndroidRuntime(1683): Caused by:
android.database.sqlite.SQLiteException: no such table: category (code 1):
, while compiling: DROP TABLE category
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native
Method)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:882)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:493)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1663)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1594)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
com.example.mutitablesql.db.BooksDBHelper.onUpgrade(BooksDBHelper.java:80)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:257)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
com.example.mutitablesql.db.BooksDB.<init>(BooksDB.java:23)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
com.example.mutitablesql.MainActivity.onCreate(MainActivity.java:21)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.Activity.performCreate(Activity.java:5104)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
08-31 08:04:07.007: E/AndroidRuntime(1683): at
android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2144)
08-31 08:04:07.007: E/AndroidRuntime(1683): ... 11 more
before i add a new table from sqlite .It's not have syntax error in my
BooksDBHelper Please check it.and tell me which line is syntax error .
Thank you