SQLite Database cannot be opened

Question

I am very new to creating and manipulating databases and I seem to be stuck, my logcat keeps telling me that it cannot open the database?

09-12 13:02:31.823: I/SqliteDatabaseCpp(7213): sqlite returned: error code = 14, msg = cannot open file at line 27701 of [8609a15dfa], db=/data/data/net.learn2develop.Database/databases/database

Im guessing it cannot find it but i am usure why. Thanks to many of you on here i have worked my way this far but am still getting stuck, What my code is doing is importing a database i made from the assets folder. Here is my database helper class:

public class DatabaseHelper extends SQLiteOpenHelper {

public static final String KEY_ROWID = "_id";
public static final String KEY_ALCOHOL = "alcohol";
public static final String KEY_TYPE = "type";
public static final String KEY_BRAND = "brand";
public static final String KEY_PRICE = "price";
private static final String TAG = "DatabaseHelper";

private static String DB_PATH = "/data/data/net.learn2develop.Database/databases/";

private static String DB_NAME = "database";
private static final String DB_TABLE = "booze";

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;

}

/**
 * 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
    }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);

    }catch(SQLiteException e){

        //database does't exist yet.

    }

    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 inputfile to the 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_READONLY);

}

@Override
public synchronized void close() {

        if(myDataBase != null)
            myDataBase.close();

        super.close();

}

@Override
public void onCreate(SQLiteDatabase myDataBase) {

}

@Override
public void onUpgrade(SQLiteDatabase myDataBase, int oldVersion, int newVersion) {
    Log.w(TAG, "Upgrading database from version " + oldVersion 
            + " to "
            + newVersion + ", which will destroy all old data");
      myDataBase.execSQL("DROP TABLE IF EXISTS titles");
      onCreate(myDataBase);

}

//---insert a title into the database---
public long insertTitle(String alcohol, String type, String brand, int price) 
{
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ALCOHOL, alcohol);
    initialValues.put(KEY_TYPE, type);
    initialValues.put(KEY_BRAND, brand);
    initialValues.put(KEY_PRICE, price);
    return myDataBase.insert(DB_TABLE, null, initialValues);
}

//---retrieves a particular title---
public Cursor getTitle(long rowId) throws SQLException 
{
    Cursor mCursor =
            myDataBase.query(true, DB_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_ALCOHOL, 
                    KEY_TYPE,
                    KEY_BRAND,
                    KEY_PRICE
                    }, 
                    KEY_ROWID + "=" + rowId, 
                    null,
                    null, 
                    null, 
                    null, 
                    null);
    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

Here is what I am calling in my activity as a test to see if i can read the DB:

public class DatabaseActivity extends Activity {

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_database);
    DatabaseHelper myDbHelper = new DatabaseHelper(this);

  //---get a title---
    myDbHelper.openDataBase();
    Cursor c = myDbHelper.getTitle(2);
    if (c.moveToFirst())        
        DisplayTitle(c);
    else
        Toast.makeText(this, "No title found", 
                Toast.LENGTH_LONG).show();
    myDbHelper.close();
}

public void DisplayTitle(Cursor c)
{
    Toast.makeText(this, 
            "id: " + c.getString(0) + "\n" +
            "ALCOHOL: " + c.getString(1) + "\n" +
            "TYPE: " + c.getString(2) + "\n" +
            "BRAND:  " + c.getString(3) + "\n" +
            "PRICE:  " + c.getString(4),
            Toast.LENGTH_LONG).show();        
} 

}

The name of my database i created is just "database", the activity is "DatabaseActivity.java" and the helper file is "DatabaseHelper.java"

Here is what my database looks like

Answer 1

You should always post all of your Logcat errors, but I think I see the problem.

You have already opened the SQLiteDatabase behind the scenes in your DatabaseHelper constructor, now you want to access it. Try this:

public void openDataBase() throws SQLException{
    myDataBase = getWritableDatabase();
}

---

Did you ever copy your database from the asset folder?

DatabaseHelper myDbHelper = new DatabaseHelper(this);

if(!myDbHelper.checkDataBase())
    myDbHelper.copyDataBase();
myDbHelper.openDataBase();

If you haven't done this, then the database won't exist in the database/ folder and cannot be opened...

Answer 2

This is sample Databse Helper which i used, it works,

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;

import org.example.db.R;
import org.example.model.contact;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteHelper extends SQLiteOpenHelper {

// Attributes
private Context context;
private SQLiteDatabase database;

@SuppressWarnings("unused")
private interface Table {
    public static final String CONTACT = "contact";
/**
 * Constructor
 * @param context
 */
public SQLiteHelper(Context context) {
    this(context, context.getString(R.string.db_name), null, 33);

    this.context = context;
    database = getReadableDatabase();
}

public SQLiteHelper(Context context, String name, CursorFactory factory,
        int version) {
    super(context, name, factory, version);
}


public Contact getContact(long contactId) {
    String sql = "SELECT * FROM " + Table.CONTACT +
        " WHERE " + Contact.Column.ID + " = " + contactId;


    Contact contact = null;
    Cursor cursor = database.rawQuery(sql, null);
    if(cursor.moveToNext()) {
        contact = getContactFromCursor(cursor);
    }
    cursor.close();

    return contact;
}

public List<contact> getContacts() {
    String sql = "SELECT * FROM " + Table.CONTACT+ " ORDER BY " + 
        Contact.Column.NAME + " ASC";

    List<Contact> contacts = new ArrayList<Contact>();
    Cursor cursor = database.rawQuery(sql, null);
    while(cursor.moveToNext()) {
        Contact contact= getContactFromCursor(cursor);
        if(contact!= null) {
            contacts.add(baby);
        }
    }
    cursor.close();

    return contacts;
}

private Baby getContactFromCursor(Cursor cursor) {
    long id = cursor.getLong(cursor.getColumnIndex(Contact.Column.ID));
    String name = cursor.getString(cursor.getColumnIndex(Contact.Column.NAME));
    int gender = cursor.getInt(cursor.getColumnIndex(Contact.Column.GENDER));

    Contact contact= new Contact(id, name, gender);

    return contact;
}

@Override
public void onCreate(SQLiteDatabase database) {

}

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

}

public void close() {
    if(database != null && database.isOpen()) {
        database.close();
    }
}

public boolean isOpen() {
    return database != null ? database.isOpen() : false;
}

}