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
– Karakuri, Commented Aug 4, 2015 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
– Hafiz, Commented Aug 4, 2015 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
– Karakuri, Commented Aug 4, 2015 at 19:56
@Karakuri yes I got messages in logcat with how you told and I have updated my question with that output. — Hafiz
– Hafiz, Commented Aug 4, 2015 at 20:11
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
– Johan Lindkvist, Commented Aug 6, 2015 at 22:20

Karakuri · Accepted Answer · 2015-08-10 16:02:02Z

2

+50

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, 2015 at 16:02

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HamidReza Heydari Over a year ago

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

user3334059 · Accepted Answer · 2015-08-07 04:06:04Z

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 7, 2015 at 4:06

user3334059

5073 silver badges5 bronze badges

3 Comments

Karakuri Over a year ago

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.

user3334059 Over a year ago

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 :)

Karakuri Over a year ago

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.

tachyonflux · Accepted Answer · 2015-08-09 20:53:26Z

0

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 + " );";

answered Aug 9, 2015 at 20:53

tachyonflux

20.2k7 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQLite DB table not created in Android while DB created

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related