안드로이드 초보입니다 데이터베이스 관련 문제입니다 ㅠㅠ

Question

06-14 15:35:44.938 19615-19615/com.example.doitmission17 E/BookDatabase: Exception in executing insert SQL.
    android.database.sqlite.SQLiteException: table BOOK_INFO has no column named AUTHOR (code 1): , while compiling: insert into BOOK_INFO(NAME, AUTHOR, CONTENTS) values ('아', '야', '어');
    #################################################################
    Error Code : 1 (SQLITE_ERROR)
    Caused By : SQL(query) error or missing database.
     (table BOOK_INFO has no column named AUTHOR (code 1): , while compiling: insert into BOOK_INFO(NAME, AUTHOR, CONTENTS) values ('아', '야', '어');)
    #################################################################
        at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
        at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1067)
        at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:644)
        at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
        at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
        at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
        at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1812)
        at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1743)
        at com.example.doitmission17.BookDatabase.insertRecord(BookDatabase.java:337)
        at com.example.doitmission17.MainActivity$1.onClick(MainActivity.java:106)
        at android.view.View.performClick(View.java:5246)
        at android.widget.TextView.performClick(TextView.java:10626)
        at android.view.View$PerformClick.run(View.java:21256)
        at android.os.Handler.handleCallback(Handler.java:739)
        at android.os.Handler.dispatchMessage(Handler.java:95)
        at android.os.Looper.loop(Looper.java:145)
        at android.app.ActivityThread.main(ActivityThread.java:6939)
        at java.lang.reflect.Method.invoke(Native Method)
        at java.lang.reflect.Method.invoke(Method.java:372)
        at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1404)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1199)

Answer 1

 DB안에 BOOK_INFO라는 table에 AUTHOR라는 field(column)가 없다는군요.

Comments

public class BookDatabase {

    /**
     * TAG for debugging
     */
    public static final String TAG = "BookDatabase";

    /**
     * Singleton instance
     */
    private static BookDatabase database;


    /**
     * database name
     */
    public static String DATABASE_NAME = "book.db";

    /**
     * table name for BOOK_INFO
     */
    public static String TABLE_BOOK_INFO = "BOOK_INFO";

    /**
     * version
     */
    public static int DATABASE_VERSION = 1;


    /**
     * Helper class defined
     */
    private DatabaseHelper dbHelper;

    /**
     * Database object
     */
    private SQLiteDatabase db;


    private Context context;

    /**
     * Constructor
     */
    private BookDatabase(Context context) {
        this.context = context;
    }


    public static BookDatabase getInstance(Context context) {
        if (database == null) {
            database = new BookDatabase(context);
        }

        return database;
    }

    /**
     * open database
     *
     * @return
     */
    public boolean open() {
        println("opening database [" + DATABASE_NAME + "].");

        dbHelper = new DatabaseHelper(context);
        db = dbHelper.getWritableDatabase();

        return true;
    }

    /**
     * close database
     */
    public void close() {
        println("closing database [" + DATABASE_NAME + "].");
        db.close();
        database = null;
    }

    /**
     * execute raw query using the input SQL
     * close the cursor after fetching any result
     *
     * @param SQL
     * @return
     */
    public Cursor rawQuery(String SQL) {
        println("\nexecuteQuery called.\n");

        Cursor c1 = null;
        try {
            c1 = db.rawQuery(SQL, null);
            println("cursor count : " + c1.getCount());
        } catch(Exception ex) {
            Log.e(TAG, "Exception in executeQuery", ex);
        }

        return c1;
    }

    public boolean execSQL(String SQL) {
        println("\nexecute called.\n");

        try {
            Log.d(TAG, "SQL : " + SQL);
            db.execSQL(SQL);
        } catch(Exception ex) {
            Log.e(TAG, "Exception in executeQuery", ex);
            return false;
        }

        return true;
    }




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

        public void onCreate(SQLiteDatabase _db) {
            // TABLE_BOOK_INFO
            println("creating table [" + TABLE_BOOK_INFO + "].");

            // drop existing table
            String DROP_SQL = "drop table if exists " + TABLE_BOOK_INFO;
            try {
                _db.execSQL(DROP_SQL);
            } catch(Exception ex) {
                Log.e(TAG, "Exception in DROP_SQL", ex);
            }

            // create table
            String CREATE_SQL = "create table " + TABLE_BOOK_INFO + "("
                    + "  _id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT, "
                    + "  NAME TEXT, "
                    + "  AUTHOR TEXT, "
                    + "  CONTENTS TEXT, "
                    + "  CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP "
                    + ")";
            try {
                _db.execSQL(CREATE_SQL);
            } catch(Exception ex) {
                Log.e(TAG, "Exception in CREATE_SQL", ex);
            }

            // insert 5 book records
            insertRecord(_db, "Do it! 안드로이드 앱 프로그래밍", "ì •ìž¬ê³¤", "안드로이드 기본서로 이지스퍼블리싱 출판사에서 출판했습니다.");
            insertRecord(_db, "Programming Android", "Mednieks, Zigurd", "Oreilly Associates Inc에서 2011ë…„ 04월에 출판했습니다.");
            insertRecord(_db, "센차터치 모바일 프로그래밍", "이병옥,최성민 ê³µì €", "에이콘출판사에서 2011ë…„ 10월에 출판했습니다.");
            insertRecord(_db, "시작하세요! 안드로이드 게임 프로그래밍", "마리오 ì œíë„ˆ ì €", "위키북스에서 2011ë…„ 09월에 출판했습니다.");
            insertRecord(_db, "ì‹¤ì „! 안드로이드 시스템 프로그래밍 ì™„ì „ì •ë³µ", "ë°•ì„ í˜¸,오영환 ê³µì €", "DW Wave에서 2010ë…„ 10월에 출판했습니다.");

        }

        public void onOpen(SQLiteDatabase db) {
            println("opened database [" + DATABASE_NAME + "].");

        }

        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            println("Upgrading database from version " + oldVersion + " to " + newVersion + ".");

            if (oldVersion < 2) {   // version 1

            }

        }

        private void insertRecord(SQLiteDatabase _db, String name, String author, String contents) {
            try {
                _db.execSQL( "insert into " + TABLE_BOOK_INFO + "(NAME, AUTHOR, CONTENTS) values ('" + name + "', '" + author + "', '" + contents + "');" );
            } catch(Exception ex) {
                Log.e(TAG, "Exception in executing insert SQL.", ex);
            }
        }

    }

    public void insertRecord(String name, String author, String contents) {
        try {
            db.execSQL( "insert into " + TABLE_BOOK_INFO + "(NAME, AUTHOR, CONTENTS) values ('" + name + "', '" + author + "', '" + contents + "');" );
        } catch(Exception ex) {
            Log.e(TAG, "Exception in executing insert SQL.", ex);
        }
    }

    private void println(String msg) {
        Log.d(TAG, msg);
    }


}

테이블을 만들때 코드에 추가를 한거같은데 혹시 빠진 곳이 있나요??

---

SQLiteOpenHelper로 생성한 DB는 최초 생성했던 table 형태를 유지합니다.
따라서 테스트중에 이미 필드가 없는 상태로 한번이라도 생성되었다면
필드가 없을 수 있습니다.
따라서 app data를 삭제 후 다시 해당 코드를 실행하기를 추천 드립니다.