0

i'm trying to use a SQLite database, it's a database who is already filled, after some research i found i needed to copy this database to use it, so i picked up the code and tested it but the copy is incomplete, all column aren't copied and the data are just not there. When i open the original file with DB Browser for SQLite there is no problem.

here the code of framework databasehelper:

package com.example.chiffrage;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.TextView;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;


public class DataBaseHelper extends SQLiteOpenHelper {

    String DB_PATH = null;
    private static String DB_NAME = "ChiffrageBDD.db";
    private SQLiteDatabase myDataBase;
    private final Context myContext;

    public DataBaseHelper(Context context) {
        super(context, DB_NAME, null, 10);
        this.myContext = context;
        this.DB_PATH = context.getApplicationInfo().dataDir + "/";
        Log.e("Path 1", DB_PATH);
    }


    public void createDataBase() throws IOException {
        boolean dbExist = checkDataBase();
        if (dbExist) {
        } else {
            this.getReadableDatabase();
            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }

    private boolean checkDataBase() {
//        SQLiteDatabase checkDB = null;
//        try {
//            String myPath = DB_PATH + DB_NAME;
//            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
//        } catch (SQLiteException e) {
//        }
//        if (checkDB != null) {
//            checkDB.close();
//        }
//        return checkDB != null ? true : false;
        File databasePath = myContext.getDatabasePath(DB_NAME);
        return databasePath.exists();
    }

    private void copyDataBase() throws IOException {
        InputStream myInput = myContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);
        byte[] buffer = new byte[2068];
        int length;
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.flush();
        myOutput.close();
        myInput.close();

    }

    public void openDataBase() throws SQLException {
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    @Override
    public synchronized void close() {
        if (myDataBase != null)
            myDataBase.close();
        super.close();
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion)
            try {
                copyDataBase();
            } catch (IOException e) {
                e.printStackTrace();

            }
    }

    public List getAllMetal(){
        List returnList = new ArrayList();
        String queryString = "PRAGMA table_info(Metal)";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(queryString, null);
        if (cursor.moveToFirst()){
            do {
                returnList.add(cursor.getString(1));
            } while (cursor.moveToNext());
            } else {
            //failure
        }
        cursor.close();
        db.close();
        return returnList;
    }
}

The response of getAllMetal is "[ID]" i should have a table name "Metal" with "id" And "nom" nom is the name of the metal. there is no data and i used PRAGMA to verify the column and as you see it there is missing "nom" column.

here some resources to where i got code:

[Framework SQLiteOpenHelper] https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper#SQLiteOpenHelper(android.content.Context,%20java.lang.String,%20android.database.sqlite.SQLiteDatabase.CursorFactory,%20int)

[Copy Database] Getting "Failed to open database" error when copying a sqlite database from assets In Android 4.2

ty for your time.

pierre LERICHE
  • 11
  • 2

1 Answers1

0

Using PRAGMA table_info(Metal) will not fail if the table doesn't exist. Instead it will show no rows. Therefore it could be misleading and it probably is misleading you.

What it is showing is that the database itself exists. However, just because the database exists does not mean that the copy itself worked. As you are only showing the database helper and not what use is made of an instance there are various issues that could be the cause.

I suspect that you have had issues and have inadvertently created a database that is empty (as far as user defined tables). This would allow you to invoke the getAllMetal and get the result you have said.

Another issue is that DB_PATH as per using this.DB_PATH = context.getApplicationInfo().dataDir + "/"; will not be the same as File databasePath = myContext.getDatabasePath(DB_NAME);. The they will be

  • /data/data/<the_package_name>/ChiffrageBDD.db and
  • /data/data/<the_package_name>/databases/ChiffrageBDD.db
    • <the_package_name> reflecting the package name used

Accidentally creating the database, and it being empty, is quite easy with Android and hence why I suspect the existence of an empty database is your issue. Once created the database persists(always exists unless you specifically delete it) which may also be part of the issue(s).

Without knowing all that you have done to date and also without knowing how you utilise the DataBaseHelper instance it is only a guess as to the cause.

The DataBaseHelper you have does have an issue in that it does not cater for a fresh install of the App. When an App is installed the /data/data/<package_name> directory exists BUT the databases directory does not. The copyDataBase method will then fail because the databases directory does not exist.

Here's a working database helper, you may wish to compare the differences between it an what you are using. They are subtle but important:-

public class DBAssetHelper2 extends SQLiteOpenHelper {

    static String DATABASE_NAME = "databasefile2";

    public DBAssetHelper2(Context context) {
        super(context, DATABASE_NAME, null, 1);
        String databasePath = context.getDatabasePath(DATABASE_NAME).getPath();
        if (!doesDatabaseExist(databasePath)) {
            copyDatabaseFromAssets(context,databasePath,DATABASE_NAME);
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

    // Checks to see if the database exists, if it does not
    // then checks to see if the directories in the path exist
    // and then makes the directories if they do not
    private boolean doesDatabaseExist(String databasepath) {
        if (new File(databasepath).exists()) return true;
        if (new File(databasepath).getParentFile().exists()) return false;
        new File(databasepath).getParentFile().mkdirs();
        return false;
    }

    private void copyDatabaseFromAssets(Context context, String databasepath, String assetfilename) {
        int bSize = 4096, bytes = 0;
        byte[] buffer = new byte[bSize];
        try {
            InputStream asset = context.getAssets().open(assetfilename);
            FileOutputStream database = new FileOutputStream(new File(databasepath));
            while((bytes = asset.read(buffer)) > 0) {
                database.write(buffer,0,bytes);
            }
            database.flush();
            database.close();
            asset.close();

        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Error copying Asset File " + assetfilename + " to " + databasepath);
        }
    }
}

First when you create the instance of the helper it does the check for the database and if need be the copy of the asset. i.e. there is no need to call any method to copy the database (with your helper you need to call the createDataBase method).

If you also look at the doesDatabaseExist method you will see that if the database file does not exist then a further check is made to see if the directories in the path exist and if not it makes the missing directories.

Exception handling is integral and if the copy fails with a run time exception.

You may wish to amend your DataBaseHelper class accordingly or you could copy and utilise the code above (you will obviously have to make some changes e.g. the database name).

IMPORTANT After making the suggested changes you MUST delete the existing (likely empty) database. The easiest way is to uninstall the App and then run the App.

The following is an example of you you could utilise the above in an Activity :-

public class MainActivity extends AppCompatActivity {

    DBAssetHelper2 dbAssetHelper2;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbAssetHelper2 = new DBAssetHelper2(this);
        Cursor cursor2 = dbAssetHelper2.getReadableDatabase().query("sqlite_master",null,null,null,null,null,null);
        DatabaseUtils.dumpCursor(cursor2);
        cursor2.close();
    }
}

Note that the code following instantiating extracts all rows from the sqlite_master table and thus will show all tables in the database. The output will be displayed in the log.

MikeT
  • 51,415
  • 16
  • 49
  • 68