CODEWITHSUNDEEP
androidandroid-sqlite
Sniper
Sniper

Reputation: 2452

Caused by: android.database.sqlite.SQLiteException: no such table: (code 1) Android

We have a sqlite database in our Application. Its working fine for all the users but few of them experiencing the Caused by: android.database.sqlite.SQLiteException: no such table: generalSettings (code 1): , while compiling: select * from generalSettings error.

Below is my sqlite helper class to create the db and the error log. In assert/Master.db we have the table generalSettings. But after copying it to the device the table is missing. This is happening only for few users. I searched for the solution but I cant find the exact one. Team please help me to fix this.

Code:

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.StringWriter;

import android.content.Context;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

public class InstallDB extends SQLiteOpenHelper {
    Context ctx;

    String DBNAME;
    String DBPATH;
    Modules modObj = new Modules();

    public InstallDB(Context context, String name) {
        super(context, name, null, 1);
        this.ctx = context;
        this.DBNAME = name;

        this.DBPATH = this.ctx.getDatabasePath(DBNAME).getAbsolutePath();
        Log.e("Path 1", DBPATH);

    }

    public void createDataBase() {

        boolean dbExist = checkDataBase();

        SQLiteDatabase db_Read = null;

        if (!dbExist) {
            synchronized (this) {

                db_Read = this.getReadableDatabase();
                Log.e("Path 2", this.getReadableDatabase().getPath());
                db_Read.close();

                copyDataBase();
                Log.v("copyDataBase---", "Successfully");
            }

            // try {

            // } catch (IOException e) {
            // throw new Error("Error copying database");
            // }
        }
    }

    private boolean checkDataBase() {

        SQLiteDatabase checkDB = null;

        try {
            String myPath = DBPATH;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READWRITE);
        } catch (Exception e) {
            Log.i("SQLite Error", "database does't exist yet.");
        }

        if (checkDB != null) {
            checkDB.close();
        }

        return checkDB != null ? true : false;
    }

    private void copyDataBase() {

        try {
            InputStream myInput = ctx.getAssets().open(DBNAME);
            String outFileName = DBPATH;

            OutputStream myOutput = new FileOutputStream(outFileName);

            byte[] buffer = new byte[1024 * 3];

            int length = 0;

            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }

            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch (Exception e) {
            Modules.stacTaceElement = e.getStackTrace();

            StringWriter stackTrace1 = new StringWriter();
            e.printStackTrace(new PrintWriter(stackTrace1));
            System.err.println(stackTrace1);

            Intent send = new Intent(Intent.ACTION_SENDTO);
            String uriText;

            uriText = "mailto:[email protected]"
                    + "&subject=Error Report"
                    + "&body="
                    + stackTrace1.toString();

            uriText = uriText.replace(" ", "%20");
            Uri uri = Uri.parse(uriText);

            send.setData(uri);
            ctx.startActivity(Intent.createChooser(send, "Send mail..."));
            // TODO: handle exception
        }

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

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

    }
}

Error Log:

java.lang.RuntimeException: Unable to start activity ComponentInfo{palmagent.FidelityAgent.Two/palmagent.FidelityAgent.Two.PassNew}: android.database.sqlite.SQLiteException: no such table: generalSettings (code 1): , while compiling: select * from generalSettings
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2209)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2269)
at android.app.ActivityThread.access$800(ActivityThread.java:139)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1210)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5102)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: android.database.sqlite.SQLiteException: no such table: generalSettings (code 1): , while compiling: select * from generalSettings
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 palmagent.FidelityAgent.Two.masterDatabase.selectquery(masterDatabase.java:59)
at palmagent.FidelityAgent.Two.Modules.checkDatabase(Modules.java:28825)
at palmagent.FidelityAgent.Two.PassNew$LoaduserDetails.onPreExecute(PassNew.java:140)
at android.os.AsyncTask.executeOnExecutor(AsyncTask.java:587)
at android.os.AsyncTask.execute(AsyncTask.java:535)
at palmagent.FidelityAgent.Two.PassNew.onCreate(PassNew.java:120)
at android.app.Activity.performCreate(Activity.java:5248)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1110)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2173)
... 11 more

Upvotes: 28

Views: 106534

Answers (19)

Mukuldeep Maiti
Mukuldeep Maiti

Reputation: 1

It is becuz the database is already created earlier, so when you attach table creation with database creation, table creation fails.

What you can do is create table seperately

 public class MyDatabaseHelper extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "mydatabase.db";
        private static final int DATABASE_VERSION = 1;
    
        public MyDatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            // Create initial tables
            db.execSQL("CREATE TABLE existing_table (id INTEGER PRIMARY KEY, name TEXT);");
            // Add other initial table creation statements here
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // Handle database upgrade as needed
            // For example, drop and recreate tables
        }
        
        // ##################### HERE ########################
        public void createNewTable(SQLiteDatabase db) {
            // Create a new table
            db.execSQL("CREATE TABLE new_table (id INTEGER PRIMARY KEY, description TEXT);");
        }
    }

create table like this

MyDatabaseHelper dbHelper = new MyDatabaseHelper(this);

// Get writable database
SQLiteDatabase db = dbHelper.getWritableDatabase();

// Create new table
// #################### HERE ############################
dbHelper.createNewTable(db);

// Optionally, close the database connection
db.close();

Upvotes: 0

Seyed Abdolvahab Taghavi
Seyed Abdolvahab Taghavi

Reputation: 11

In my case I open the tab "Device File Explorer" and clear the db file.

Then I run the app.

It worked for me.

Upvotes: 0

Vishal_VE
Vishal_VE

Reputation: 2127

Try with this solution, this one works for me:

  1. Select App

  2. Clear Cache

  3. Clear Data

  4. Uninstall App

And then run the app from Android Studio

Upvotes: -2

Zain
Zain

Reputation: 40820

My scenario is different:

I am using the Room's createFromAsset() to switch between a couple of database files, achieving that requires to first delete the database and recreate it again to be loaded with the other database file..

During the deletion process, the LiveData that I observe from the database raises SQLiteException: no such table and of course it's logical, because the LiveData senses that the underlying data is changed, and tries to get the new data, and finds that the table is gone.

To solve this: either use normal data (no LiveData) or stop observing the LiveData and re-observe it again when the database is recreated successfully.

Note: without deleting the database file, Room continues to use a cached version of the old database file.

Upvotes: 0

Vadim.B
Vadim.B

Reputation: 1

I have had the same issue, when I created an update for app. By google documentation onCreate() from SQLiteOpenHelper is called only when the app is installed first time. So if you want to add a table you need to increment the database version and add 

onUpgrade() { your_table.create(database) }

@Override public void onUpgrade(final SQLiteDatabase database, final int oldVersion, final int newVersion) { YourTable.onCreate(database); }

Upvotes: 0

Muhammad Rafiqi Liputo
Muhammad Rafiqi Liputo

Reputation: 1

try remove getReadableDatabase 

 if (!dbExist) {
            synchronized (this) {

                db_Read = this.getReadableDatabase();
                Log.e("Path 2", this.getReadableDatabase().getPath());
                db_Read.close();

                copyDataBase();
                Log.v("copyDataBase---", "Successfully");
            }

to 

 if (!dbExist) {
            synchronized (this) {

                //db_Read = this.getReadableDatabase();
              //  Log.e("Path 2", this.getReadableDatabase().getPath());
              //  db_Read.close();

                copyDataBase();
                Log.v("copyDataBase---", "Successfully");
            }

Upvotes: 0

Muhammad Qasim
Muhammad Qasim

Reputation: 11

it is another solution to this problem some time developer didnot knows that there is not space between column name and its end like KEY_ADDRESS, this is wrong approach use space between comma and name of attribute

Upvotes: 0

Muhammad Qasim
Muhammad Qasim

Reputation: 11

kindly initiallize copy database or database helper in main class like that Database helper = new Database(this); helper.execute(sqliteobj)

Upvotes: 0

paul polash
paul polash

Reputation: 1238

Update the DATABASE_VERSION and uninstall the app. Now run the app.

Upvotes: 2

Rashed Zzaman
Rashed Zzaman

Reputation: 123

In my case I changed my db name and its work for me

Upvotes: -2

Blasanka
Blasanka

Reputation: 22437

Even if you have coded for few tables and upgraded database, your app will crash. What you have to do is for each and every table create database changes upgrade the DATABASE_VERSION

If you have few DatabaseHelper classes(read this), you have to add this method to your db helper:

@Override
public void onOpen(SQLiteDatabase db) {
    onCreate(db);
}

Upvotes: 2

Shubham Raitka
Shubham Raitka

Reputation: 1052

Suppose if you run your app with Database Version 1, then if you alter the table structure or add a new table, you must increase your Database Version to 2 and further if you make more changes to it.

public class AppDatabase extends SQLiteOpenHelper {

    // Database Name
    private static final String DATABASE_NAME = "myDatabase";

    // Database Version
    private static final int DATABASE_VERSION = 1;    

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

Increase this DATABASE_VERSION value if any alterations are done.

Upvotes: 4

Babul Mirdha
Babul Mirdha

Reputation: 3902

After spending couple of hours I got this solution:

1) Settings > Application Manager

2) Select App

3) Clear Data

4) Uninstall App

Now Run app from Android Studio

Hope this works properly

Upvotes: 21

Ratul Ghosh
Ratul Ghosh

Reputation: 1500

The problem is because some of device is upgrading your app, so the checkDataBase() returning true, so you are not calling copyDataBase(). So you are using previous database which doesn't have generalSettings table. To solve this try:

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

and also update your constructor:

public InstallDB(Context context, String name) {
    super(context, name, null, DB_VERSION); 
    // DB_VERSION is an int,update it every new build

    this.ctx = context;
    this.DBNAME = name;
    this.DBPATH = this.ctx.getDatabasePath(DBNAME).getAbsolutePath();
    Log.e("Path 1", DBPATH);

}

Upvotes: 36

Catluc
Catluc

Reputation: 1823

If you are using GreenDao and get this error, be sure you are unistalling the app and try again. This solved my problem

Upvotes: 1

Md.Tarikul Islam
Md.Tarikul Islam

Reputation: 1301

1.Change Your DataBase Version Or First Uninstall Your Apps In the Emulator or Phone And Re-install. I In This way Think Your Problem Will be solved.

Upvotes: 0

NoWar
NoWar

Reputation: 37633

Another possible solution is just uninstall an App from the Android emulator and after this run it again.

If you just want to remove a application:

1.Start the emulator.
2.Open the Android settings app.
3.Select "Applications" (Called "Apps" on Android 4.0 or higher)
4.Select "Manage Applications" (Only on Android 3.2 or lower)
5.Select the application you want to uninstall.
6.Click "Uninstall"

Upvotes: 5

Mehdi1991
Mehdi1991

Reputation: 89

This error Occurs Because you are not using DATABASE_VERSION

public class DatabaseHelper extends SQLiteOpenHelper {
    private static SQLiteDatabase sqliteDb;

    private static DatabaseHelper instance;

    private static final int DATABASE_VERSION = 1;

Increase Your version every time you make changes in your database just increase DATABASE_VERSION with +1..

Upvotes: 8

Hideya
Hideya

Reputation: 168

it's an upgrading exception. Make sure you have the table in your previous database. If not, create it. PS: if you're newly dev this app, uninstall it from your emulator or your device and re-install. But its not recommended for the data will ne lost.

Upvotes: 0

Related Questions