SQLite DB table not created in Android while DB created

Question

I am working towards creating an Android app. I am beginner in Android and Java application development while I have good experience of web development.

I am trying to create a DB and then creating my first table in SQLite. It is creating DB but not DB table.

I have created Contaract class.

package com.haafiz.project;

import android.provider.BaseColumns;

public final class ProjectContaract {

    public ProjectContaract(){}

    public static abstract class Site implements BaseColumns{
        public static final String TABLE_NAME = "site";
        public static final String COLUMN_NAME_SITE_NAME = "site_name";
        public static final String COLUMN_NAME_LOGIN = "login_name";
        public static final String COLUMN_NAME_HOST = "host";
        public static final String COLUMN_NAME_PASSWORD = "password";
    }
}

And then DB helper class.

package com.haafiz.project;

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


public class DBHelper extends SQLiteOpenHelper {

    private static final String TEXT_TYPE = " TEXT";
    private static final String COMMA_SEP = ",";
    private static final String SQL_CREATE_ENTRIES =
            "CREATE TABLE " + ProjectContaract.Site.TABLE_NAME + " (" +
                    projectContaract.Site._ID + " INTEGER PRIMARY KEY," +
                    projectContaract.Site.COLUMN_NAME_SITE_NAME + TEXT_TYPE + COMMA_SEP +
                    projectContaract.Site.COLUMN_NAME_LOGIN + TEXT_TYPE + COMMA_SEP +
                    projectContaract.Site.COLUMN_NAME_HOST + TEXT_TYPE + COMMA_SEP +
                    projectContaract.Site.COLUMN_NAME_PASSWORD + TEXT_TYPE + " )";

    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + ProjectContaract.Site.TABLE_NAME;

    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "Project";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }

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

And here my main activity using that DB Helper as below:

package com.haafiz.project;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;


public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        projectDBHelper dbHelper = new projectDBHelper(getApplicationContext());

        // Gets the data repository in read mode
        SQLiteDatabase db = dbHelper.getWritableDatabase();

        // Projection that specifies which columns from the database
        String[] projection = {
                projectContaract.Site._ID,
                projectContaract.Site.COLUMN_NAME_SITE_NAME,
                projectContaract.Site.COLUMN_NAME_LOGIN,
                projectContaract.Site.COLUMN_NAME_HOST,
                projectContaract.Site.COLUMN_NAME_PASSWORD
        };

        // How you want the results sorted in the resulting Cursor
        String sortOrder =
                projectContaract.Site.COLUMN_NAME_SITE_NAME + " DESC";

        Cursor c = db.query(
                projectContaract.Site.TABLE_NAME,  // The table to query
                projection,                               // The columns to return
                null,                                // The columns for the WHERE clause
                null,                            // The values for the WHERE clause
                null,                                     // don't group the rows
                null,                                     // don't filter by row groups
                sortOrder                                 // The sort order
        );

        setContentView(R.layout.activity_main);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

DB is being created but table is not, I check that with adb shell attach with emulator. What am I doing wrong? The table is not being created.

And an important thing is that I am not getting errors related to this in log.

But in logcat with "no filter" in drop down where it was "show only selected application" previously and with "sql" in filter search box, it shows following messages in log cat:

    08-04 00:41:35.494      403-474/android.process.media V/MediaScanner﹕ pruneDeadThumbnailFiles... android.database.sqlite.SQLiteCursor@41302da8
08-04 00:41:35.494      403-474/android.process.media V/MediaScanner﹕ /pruneDeadThumbnailFiles... android.database.sqlite.SQLiteCursor@41302da8
08-04 00:54:02.685      623-632/com.haafiz.project E/SQLiteDatabase﹕ close() was never explicitly called on database '/data/data/com.haafiz.project/databases/project'
    android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here
            at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1943)
            at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1007)
            at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:986)
            at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:1051)
            at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:770)
            at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:221)
            at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:157)
            at com.haafiz.project.MainActivity.onCreate(MainActivity.java:20)
            at android.app.Activity.performCreate(Activity.java:4466)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1920)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1981)
            at android.app.ActivityThread.access$600(ActivityThread.java:123)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1147)
            at android.os.Handler.dispatchMessage(Handler.java:99)
            at android.os.Looper.loop(Looper.java:137)
            at android.app.ActivityThread.main(ActivityThread.java:4424)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:511)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
            at dalvik.system.NativeStart.main(Native Method)
08-04 00:54:02.766      623-632/com.haafiz.project E/System﹕ java.lang.IllegalStateException: Don't have database lock!
            at android.database.sqlite.SQLiteDatabase.verifyLockOwner(SQLiteDatabase.java:2090)
            at android.database.sqlite.SQLiteDatabase$1.entryRemoved(SQLiteDatabase.java:2182)
            at android.database.sqlite.SQLiteDatabase$1.entryRemoved(SQLiteDatabase.java:2178)
            at android.util.LruCache.trimToSize(LruCache.java:197)
            at android.util.LruCache.evictAll(LruCache.java:285)
            at android.database.sqlite.SQLiteDatabase.deallocCachedSqlStatements(SQLiteDatabase.java:2143)
            at android.database.sqlite.SQLiteDatabase.closeClosable(SQLiteDatabase.java:1126)
            at android.database.sqlite.SQLiteDatabase.finalize(SQLiteDatabase.java:1914)
            at java.lang.Daemons$FinalizerDaemon.doFinalize(Daemons.java:182)
            at java.lang.Daemons$FinalizerDaemon.run(Daemons.java:168)
            at java.lang.Thread.run(Thread.java:856)

Other than first two lines, other lines in output of log cat, all other lines are highlighted in red color.

Do you see any messages in logcat related to your sqlite statement? — Karakuri, Aug 04 '15 at 19:52
@Karakuri I have just now updated question with statement: And an important thing is that I am not getting errors related to this in log. — Hafiz, Aug 04 '15 at 19:54
Try filtering logcat for "sql". Include all processes (not just your application's process) and include messages with lower severity than error. — Karakuri, Aug 04 '15 at 19:56
@Karakuri yes I got messages in logcat with how you told and I have updated my question with that output. — Hafiz, Aug 04 '15 at 20:11
What happens if you completely uninstall and reinstall the app? — Karakuri, Aug 04 '15 at 22:23
What do you mean by completely uninstall? I am not installing on actual device but on emulator. So should I close the emulator and then restart it? Are you asking about that? or something else I need to do? — Hafiz, Aug 05 '15 at 11:43
Uninstall the app from whatever device/emulator it is installed on and reinstall it. — Karakuri, Aug 05 '15 at 16:12
@Karakuri then no message is in logcat with verbose, search option=sql and with no filters. — Hafiz, Aug 06 '15 at 20:03
I have tested running your code that you have provided and all I can say it that it works for me. Are you trying to run the code exaclty as you have provided? (there is some errors in the code you provided. Example projectContaract should be ProjectContaract). Did you ever try a clean install? (meaning you unintstall the app from the emulator then try again, as @Karakuri suggested) — Johan Lindkvist, Aug 06 '15 at 22:20
@JohanLindkvist Yep I did so. Errors you are facing is just a result of my ctrlH (search replace) with project It is also working for me but not creating table named site. I am using console to try to see table as it is told in this thread: http://stackoverflow.com/questions/17529766/see-database-filecontent-via-android-studio/18280430#18280430 — Hafiz, Aug 06 '15 at 22:33
@JohanLindkvist yes you are right, it is now working, last time I checked, I ignore case sensitivity while selecting DB and sqlite let me do it without giving error and gave error when I selected from table. But with right DB selection it is working fine. — Hafiz, Aug 06 '15 at 22:47
JohanLindkvist and @karakuri can any one you post your last comments as answer as it helped me solve the problem and making sure that everything is working fine. So you guys helped more than answers post in this post so I want you guys to award 50 points of bounty so any of you post answer and I mark it. — Hafiz, Aug 10 '15 at 15:49

score 2 · Accepted Answer · answered Aug 10 '15 at 16:02

2

Seems like the database didn't get closed correctly. Sometimes this can happen during development when you are often reinstalling builds of the app.

Uninstall the app and reinstall it. Alternatively open Settings > Apps > [your app] and tap the Clear Data button. Either of these will remove the existing database file so the next attempt to open it will create a new one.

answered Aug 10 '15 at 16:02

Karakuri

38,365
12
84
104

1

its a lower Way! Because you can't say to app users go to Setting>apps>..., it's not Good for Developers. **JUST TRY THIS** : Change `DATABASE_VERSION` – HamidReza Heydari Jun 10 '17 at 05:37

score 0 · Answer 2 · answered Aug 07 '15 at 04:06

0

SQLLite operates in many modes, one of which is that everything is stored in memory and not flushed to disk unless certain conditions are met (like high mem usage or db being closed). You are not closing the db which is why you are not able to see the tables in the sqlite file.
For your example to work, store dbHelper as a class property, assign new object to it when activity initializes, and then close it before the activity is closed/destroyed. That should solve your issue.

answered Aug 07 '15 at 04:06

user3334059

437
3
5

I have never had to do what you suggested, and I have worked with SQLite databases on Android for years and done just about every imaginable thing you can think of with them. – Karakuri Aug 10 '15 at 16:07
Well, I'm surprised it works without closing the db. Your stack trace also says "close() was never explicitly called on database '/data/data/com.haafiz.project/databases/project'". Maybe the OS does it for you. Anyways, you should be closing your db as a good practice :) – user3334059 Aug 10 '15 at 18:14
I never said I wasn't closing the database. I've never had to structure my Activities the way you suggest. And I'm pretty sure most database writes will make it to disk unless your app terminates at a really bad time. – Karakuri Aug 10 '15 at 19:40

score 0 · Answer 3 · answered Aug 09 '15 at 20:53

You're missing a semicolon at the end of your create table SQL statement. Also, your primary key should be autoincrement.

private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + ProjectContaract.Site.TABLE_NAME + " (" +
                projectContaract.Site._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                projectContaract.Site.COLUMN_NAME_SITE_NAME + TEXT_TYPE + COMMA_SEP +
                projectContaract.Site.COLUMN_NAME_LOGIN + TEXT_TYPE + COMMA_SEP +
                projectContaract.Site.COLUMN_NAME_HOST + TEXT_TYPE + COMMA_SEP +
                projectContaract.Site.COLUMN_NAME_PASSWORD + TEXT_TYPE + " );";

SQLite DB table not created in Android while DB created

3 Answers3