SQLite error creating table

Question

Hello i am having a problem creating a table in onCreate in my class that extends SQLiteOpenHelper, here's the code:

public class EntriesDatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "accounts.db";
    private static final String TABLE_NAME = "diary";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_TITLE = "title";
    private static final String COLUMN_DETAILS = "details";
    private static final String COLUMN_SAVE_DATE = "save_date";
    private static final String COLUMN_USER_ID = "user_id";
    SQLiteDatabase db;

    private static final String TABLE_CREATE = "create table " + TABLE_NAME
        + " (" + COLUMN_ID + " integer primary key not null, "
        + COLUMN_TITLE + " text not null, "
        + COLUMN_DETAILS + " text not null, "
        + COLUMN_SAVE_DATE + " text not null, "
        + COLUMN_USER_ID + " integer not null);";


    public EntriesDatabaseHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(TABLE_CREATE);
        this.db = db;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        String query = "DROP TABLE IF EXISTS " + TABLE_NAME;
        db.execSQL(query);
        this.onCreate(db);
    }

    public void insertEntry(DiaryEntries entries){
        db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        String query = "SELECT * FROM " + TABLE_NAME;
        Cursor cursor = db.rawQuery(query, null);
        int count = cursor.getCount();
        values.put(COLUMN_ID, count);
        values.put(COLUMN_TITLE, entries.getTitle());
        values.put(COLUMN_DETAILS, entries.getDetails());
        values.put(COLUMN_SAVE_DATE, entries.getDate());
        values.put(COLUMN_USER_ID, entries.getUser_id());

        db.insert(TABLE_NAME, null, values);
        db.close();

    }

}

Error: android.database.sqlite.SQLiteException: no such table: diary (code 1): , while compiling: SELECT * FROM diary

full logcat:

FATAL EXCEPTION: main
               android.database.sqlite.SQLiteException: no such table: diary (code 1): , while compiling: SELECT * FROM diary
               at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
               at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
               at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
               at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
               at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
               at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
               at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
               at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
               at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1253)
               at elec4.app.android.diaryapp.EntriesDatabaseHelper.insertEntry(EntriesDatabaseHelper.java:55)
               at elec4.app.android.diaryapp.WriteDiary$1.onClick(WriteDiary.java:81)
               at android.view.View.performClick(View.java:4240)
               at android.view.View$PerformClick.run(View.java:17721)
               at android.os.Handler.handleCallback(Handler.java:730)
               at android.os.Handler.dispatchMessage(Handler.java:92)
               at android.os.Looper.loop(Looper.java:137)
               at android.app.ActivityThread.main(ActivityThread.java:5103)
               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:737)
               at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
               at dalvik.system.NativeStart.main(Native Method)

here's the code where use the class:

EntriesDatabaseHelper helper = new EntriesDatabaseHelper(this);
DiaryEntries entries = new DiaryEntries();
             entries.setTitle(data_title);
             entries.setDetails(data_details);
             entries.setDate(date);
             entries.setUser_id(Integer.parseInt(user_id));

             helper.insertEntry(entries);
             //inserted into the database
             finish();

it should already created the table but how come it isnt. help thanks

Try to run it after changing DATABASE_VERSION version from 1 to 2 — ρяσѕρєя K, Dec 15 '16 at 04:54
ok ill try, u guy should answer not comment so you can have points — Jarvis Millan, Dec 15 '16 at 04:56
U can find lots of answers for similar qsn :) @ρяσѕρєяK have enough reps :) — Raghavendra, Dec 15 '16 at 04:58
Upgrade version number? But if your app is still only under construction, you just have to delete the database if you made some changes. I would only increase the version number if it is a published app and some changes in database structure are necessary. Everytime you change the structure of database on a published app, you should increase the version number. But if it is in construction, just deinstall and reinstall your app, so your database is build new. — Opiatefuchs, Dec 15 '16 at 04:59
Hello can I give you my code for this solution ? Or else you can change db version code . — Gevaria Purva, Dec 15 '16 at 04:59
it was the version, it solved my problem. but can i ask why you have to change the version? — Jarvis Millan, Dec 15 '16 at 05:02
Perhaps you create your database without table create, then run your app, then you added the code to create table, this time `onCreate` will not get call, until you perform an upgrade, which mean changing the db version to a higher version, then `onUpgrade` will triggered. — alijandro, Dec 15 '16 at 05:05
but this is not a good way. The database should be created if a new instance of `EntriesDatabaseHelper`is created. Calling onUpgrade() to create the database on the very first create will cause to allways start on version number 2. Just call `this.getWritableDatabase();` in your `EntriesDatabaseHelper` constructor and you create the database on initialize `EntriesDatabaseHelper`. Then you don´t have to upgrade the version number. — Opiatefuchs, Dec 15 '16 at 05:31

score 0 · Accepted Answer · answered Dec 15 '16 at 05:01

0

ok someone commented to change the database version, i have a first class that uses database_version = 1, i changed the value to 2. it worked. i'd like to ask why u need to change the version? sorry, quite new here on sqlite

answered Dec 15 '16 at 05:01

Jarvis Millan

451
1
7
19

Because if the DB was created before with some schema it's not updated unless you increase the version number. You had a DB with no `diary` table. – Diego Torres Milano Dec 15 '16 at 05:05
This is because you write the code @Override public void onUpgrade(SQLiteDatabase db, int i, int i1) { String query = "DROP TABLE IF EXISTS " + TABLE_NAME; db.execSQL(query); this.onCreate(db); } if you change the version it will call onupgrade call otherwise didnt do any change on database – noobEinstien Dec 15 '16 at 05:05
@DiegoTorresMilano oh okay, i understand now thanks – Jarvis Millan Dec 15 '16 at 05:07
To avoid downvotes here, this should be a comment not an answer. Or you should state out clearly the solution with code example. Only a hint to prevent you from downvotes.... – Opiatefuchs Dec 15 '16 at 05:34
if your app is still only under construction just clear the data of your app and restart it instead of updating database version – sohailehmad Dec 15 '16 at 05:43

xbadal · Answer 2 · 2016-12-15T07:02:36.827

0

try this private static final String TABLE_CREATE = "create table " + TABLE_NAME + " (" + COLUMN_ID + " integer primary key not null, " + COLUMN_TITLE + " text not null, " + COLUMN_DETAILS + " text not null, " + COLUMN_SAVE_DATE + " text not null, " + COLUMN_USER_ID + " integer not null)";

And Use debugCompile 'com.amitshekhar.android:debug-db:0.4.0' to check

edited Dec 15 '16 at 07:02

answered Dec 15 '16 at 06:43

xbadal

1,284
2
11
24

SQLite error creating table

2 Answers2