Connecting SQLite database in Android

Question

I'm trying to create an android app that will read from a existing database table and fetch words. Later I'll show the words on the activity screen. I've saved the database file in ASSETS folder but it's not working. Any help or suggestions will be very much appreciated. This is my Database class where I've tried to set the connection to the database.

public class DBManager extends SQLiteOpenHelper {

private static final String db_Word = "";
private static final String DATABASE_NAME = "SYFYB.sqlite";
private static final String DATABASE_TABLE = "WORDS";
private static final int DATABASE_VERSION = 1;
SQLiteDatabase db = null;

//public void dbCreator(Context dbContext)
//{

   // try {
      //  db = dbContext.openOrCreateDatabase(DATABASE_NAME, DATABASE_VERSION, null);
   // } catch (Exception e){

   // }

//}

public DBManager(Context context)
{
    super(context, "SYFYB.sqlite", null, 1);

}

@Override
public void onCreate(SQLiteDatabase db) {
}

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

}


public void onSelect(SQLiteDatabase db, String selectCommand){

    db.execSQL(selectCommand);
}


private String[] SELECT = {"WORDS"};

private Cursor ShowDATA(SQLiteDatabase dbFromMain){


    SQLiteDatabase db;
    db = this.getReadableDatabase(); // ERROR
    Cursor cursor = db.query("WORDS",SELECT,null,null,null,null,null);

    cursor.moveToFirst();

    return cursor;
}

I'm using this class from main activity as follows,

SQLiteDatabase dbFile = SQLiteDatabase.openDatabase("SYFYB.sqlite",null,SQLiteDatabase.OPEN_READONLY); //ERROR

    String selectCommand = "select * from WORDS;";
    DBManager dbObject = new DBManager(getApplicationContext());
    dbObject.onSelect(dbFile, selectCommand);

Commented ERROR lines are shown as error Could not open Database. I've tried the Database file saved as .db and .sqlite but neither worked.

UPDATE: I'm trying this code now but having a "no such table exists" error.

public class DataBaseHelper extends SQLiteOpenHelper {

private static String DB_PATH = "";

private static final String DB_NAME = "SYFYB.db";

private SQLiteDatabase myDataBase;

private final Context myContext;

public DataBaseHelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;

    System.err.println(context.getApplicationInfo().dataDir);

    DB_PATH = context.getApplicationInfo().dataDir + File.separator
            + "databases" + File.separator;

    System.out.println("-------"+DB_PATH);
}

public void createDataBase() throws IOException {

    boolean dbExist = checkDataBase();

    if (dbExist) {
        System.out.println("=============");
        myDataBase = SQLiteDatabase.openDatabase(DB_PATH+DB_NAME, null,
                SQLiteDatabase.OPEN_READONLY);

    } else {

         myDataBase = 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);
        System.out.println("Database exists."+checkDB);

    } catch (SQLiteException e) {

        System.out.println("Database doesn't exist yet.");

    }

    if (checkDB != null) {

        checkDB.close();

    }

    return checkDB != null ? true : false;
}

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[1024];
    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 arg0) {
    // TODO Auto-generated method stub

}

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    // TODO Auto-generated method stub

}

public String fetchData() {
    String SELECT = "select * from WORDS;";
    System.out.println("------"+myDataBase);
    Cursor cursor = myDataBase.rawQuery(SELECT, null);
    String words = cursor.toString();
    return words;
}
}

Main activity looks like,

DataBaseHelper dataBaseHelper = new DataBaseHelper(getBaseContext());
    try {
        dataBaseHelper.createDataBase();
    } catch (IOException ioe) {
        throw new Error("Unable to create database");
    }

    dataBaseHelper.getWritableDatabase();
    // do your code
    dataBaseHelper.openDataBase();
    String dbWORDS = dataBaseHelper.fetchData();
    textView.setText(dbWORDS);

Sandeep Singh · Answer 1 · 2015-05-05T09:36:11.403

Use this

public class DataBaseHelper extends SQLiteOpenHelper {

// The Android's default system path of your application database.
private static String DB_PATH = "";

private static final String DB_NAME = "DBNAME";

private SQLiteDatabase myDataBase;

private final Context myContext;

/**
 * Constructor Takes and keeps a reference of the passed context in order to
 * access to the application assets and resources.
 * 
 * @param context
 */
public DataBaseHelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;

    System.err.println(context.getApplicationInfo().dataDir);

    DB_PATH = context.getApplicationInfo().dataDir + File.separator
            + "databases" + File.separator;

    System.out.println(DB_PATH);
}

/**
 * Creates a empty database on the system and rewrites it with your own
 * database.
 * */
public void createDataBase() throws IOException {

    boolean dbExist = checkDataBase();

    if (dbExist) {
        // do nothing - database already exist
        // Toast.makeText(myContext, "already exist",
        // Toast.LENGTH_LONG).show();
    } else {

        // By calling this method and empty database will be created into
        // the default system path
        // of your application so we are gonna be able to overwrite that
        // database with our database.
        this.getReadableDatabase();

        try {

            copyDataBase();

        } catch (IOException e) {

            // throw new Error("Error copying database");

        }
    }

}

/**
 * Check if the database already exist to avoid re-copying the file each
 * time you open the application.
 * 
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase() {

    SQLiteDatabase checkDB = null;

    try {
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READONLY);
        // Toast.makeText(myContext, "already exist",
        // Toast.LENGTH_LONG).show();
    } catch (SQLiteException e) {

        // database does't exist yet.
        // Toast.makeText(myContext, "not already exist",
        // Toast.LENGTH_LONG).show();
    }

    if (checkDB != null) {

        checkDB.close();

    }

    return checkDB != null ? true : false;
}

/**
 * Copies your database from your local assets-folder to the just created
 * empty database in the system folder, from where it can be accessed and
 * handled. This is done by transfering bytestream.
 * */
private void copyDataBase() throws IOException {

    // Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    // Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    // transfer bytes from the Input file to the output file
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
        myOutput.write(buffer, 0, length);
    }

    // Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

}

public void openDataBase() throws SQLException {

    // Open the database
    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 arg0) {
    // TODO Auto-generated method stub

}

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    // TODO Auto-generated method stub

}

// Add your public helper methods to access and get content from the
// database.
// You could return cursors by doing "return myDataBase.query(....)" so it'd
// be easy
// to you to create adapters for your views.

}

To use the database

   DataBaseHelper dataBaseHelper = new DataBaseHelper(getBaseContext());
    try {
        dataBaseHelper.createDataBase();
    } catch (IOException ioe) {
        throw new Error("Unable to create database");
    }

    SQLiteDatabase sqld = dataBaseHelper.getWritableDatabase();

   Cursor cursor = sqld.rawQuery("select * from your_table_name", null);

    if (cursor.moveToFirst()) {
        do {

            int id =cursor.getInt(cursor.getColumnIndex("columnName"));

        } while (cursor.moveToNext());
    }

    sqld.close();

Should I save my database file as .db or .sqlite to use here in the main --> Assets folder ??? — ree1991, May 05 '15 at 05:51
ok another confusion I'm having is that my database has 2 tables. How can I mention the particular TABLE NAME that I want to work on? — ree1991, May 05 '15 at 06:05

GrIsHu · Accepted Answer · 2015-05-07T08:03:56.217

You can not directly write into the files of ASSETS folder in your application as the resources,as the folders are read-only. You have to copy your database first from your assets folder to your sdcard and then only you will be able to read & write into it.

Try out below code to read the sqlite database from assests and copy it into sdcard to use it.

public class DataBaseHelper extends SQLiteOpenHelper {
    private Context mycontext;

    //private String DB_PATH = mycontext.getApplicationContext().getPackageName()+"/databases/";
    private static String DB_NAME = "(datbasename).sqlite";//the extension may be .sqlite or .db
    public SQLiteDatabase myDataBase;


    public DataBaseHelper(Context context) throws IOException {
        super(context,DB_NAME,null,1);
        this.mycontext=context;
        boolean dbexist = checkdatabase();
        if (dbexist) {
            //System.out.println("Database exists");
            opendatabase(); 
        } else {
            System.out.println("Database doesn't exist");
            createdatabase();
        }
    }

    public void createdatabase() throws IOException {
        boolean dbexist = checkdatabase();
        if(dbexist) {
            //System.out.println(" Database exists.");
        } else {
            this.getReadableDatabase();
            try {
                copydatabase();
            } catch(IOException e) {
                throw new Error("Error copying database");
            }
        }
    }   

    private boolean checkdatabase() {
        //SQLiteDatabase checkdb = null;
        boolean checkdb = false;
        try {
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            //checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE);
            checkdb = dbfile.exists();
        } catch(SQLiteException e) {
            System.out.println("Database doesn't exist");
        }
        return checkdb;
    }

    private void copydatabase() throws IOException {
        //Open your local db as the input stream
        InputStream myinput = mycontext.getAssets().open(DB_NAME);

        // Path to the just created empty db
        String outfilename = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myoutput = new FileOutputStream("/data/data/(packagename)/databases   /(datbasename).sqlite");

        // transfer byte to inputfile to outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myinput.read(buffer))>0) {
            myoutput.write(buffer,0,length);
        }

        //Close the streams
        myoutput.flush();
        myoutput.close();
        myinput.close();
    }

    public void opendatabase() throws SQLException {
        //Open the database
        String mypath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READWRITE);
    }

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

}

You have commented out both your DB_PATH initializations. May I ask what it should be initialized with?? — ree1991, May 05 '15 at 05:23
I have only commented the second DB_PATH which was the older method to get the path and now its depricated. It directly gives path of your application's folder using the first path only. — GrIsHu, May 05 '15 at 05:45
okay so i tried your piece of code. and edited only a little to write a select query. but the error is now showing as "no such table". I have used a .db file from assets and there is only one table in the database. I created the database and it's table in SQLite-Studio and then exported as a .db file. Any suggestions? @GrlsHu — ree1991, May 05 '15 at 07:37
@ree1991 Make sure you have first made an instance of your database helper class at the starting of an application. So it creates a database an tables also. — GrIsHu, May 05 '15 at 08:20
@GrlsHu i have done that. i'm trying your code exactly as it is. just added a method to perform SELECT query in the DataBaseHelper class and called that method from mainActivity class. Any ideas? — ree1991, May 05 '15 at 09:40
Can you update your question with the updated code and your logcat error? @ree1991 — GrIsHu, May 05 '15 at 09:53
@ree1991 You have missed out to open the database before read content from it. In your code just add the line `dataBaseHelper.openDataBase()` while you get all the records from your table. — GrIsHu, May 05 '15 at 11:06
@GrlsHu Sorry for bothering so much. I followed your advice and added the openDataBase() in my code(can check in my UPDATE part of the post), but still the same error. Can it be due to any export database issue? I'm using SQLite-Studio to export my database in sql format and to a .db file. But when the database is pulled from the emulator(from ADM) and opened in SQLite-Studio the tables and data are gone. — ree1991, May 05 '15 at 11:18
Write the line `dataBaseHelper.openDataBase();` before the line ` SQLiteDatabase db = dataBaseHelper.getWritableDatabase();` . I think there must an issue with copying your database into sdcard. Do debug your code carefully and check where exactly the problem occuring. @ree1991 — GrIsHu, May 05 '15 at 11:20

score 0 · Answer 3 · answered May 05 '15 at 05:02

There are actually two ways to get a db file one is through its name other is through a path. 1.Name method uses the default path 2.the second method can be modified to use a custom path.

String DBpath = context.getAssets()+File.seperator+ "databases" + File.separator+DBName;
SQLiteDatabase db = openOrCreateDatabase(DBpath,MODE_PRIVATE,null);

like this u can open a database within an assest folder either using a helper class or without one.in your case you are using it.hence copy this code into the createDatabase method.

Connecting SQLite database in Android

3 Answers3