Pre-populated SQLite Database not reading properly in Android Studio

Question

I'm new to StackOverflow so I'm not sure how this works, but I really need some help with this app I'm building in Android Studio. I have a pre-populated chemicals.sqlite database that I am trying to search through in Android Studio. Basically, the app I'm designing is going to allow a user to search for a compound by name and it will display information about it. For some reason, my program is saying that it can't display any of the values that I'm trying to search in the app's emulator. It just always shows the "No Match Found".

Here is my DatabaseHelper class:

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DataBaseHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "chemicals.sqlite";
private static final String TABLE_OSHA = "osha";
public static final String COLUMN_COMPOUND = "compound";
public static final String COLUMN_H = "h";
public static final String COLUMN_F = "f";
public static final String COLUMN_R = "r";
public static final String COLUMN_SN = "sn";

public DataBaseHelper(Context context, String name,
                   SQLiteDatabase.CursorFactory factory, int version) {
    super(context, DATABASE_NAME, factory, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_OSHA_TABLE = "CREATE TABLE " +
            TABLE_OSHA + "("
            + COLUMN_COMPOUND + " TEXT," + COLUMN_H
            + " TEXT," + COLUMN_F + " TEXT," + COLUMN_R + " TEXT," + COLUMN_SN + " TEXT" + ")";
    db.execSQL(CREATE_OSHA_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion,
                      int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_OSHA);
    onCreate(db);
}
public Compound findCompound(String compoundname) {
    String query = "Select * FROM " + TABLE_OSHA + " WHERE " + COLUMN_COMPOUND + "= '" + compoundname + "'";
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);
    Compound compound = new Compound();
    if (cursor.moveToFirst()) {
        cursor.moveToFirst();
        compound.setH(cursor.getString(0));
        compound.setF(cursor.getString(1));
        compound.setR(cursor.getString(2));
        compound.setSN(cursor.getString(3));
       // cursor.moveToNext();
        cursor.close();
    } else {
        compound = null;
    }
    db.close();
    return compound;
}
}

Here is my compound class:

public class Compound {

private String  _compound;
private String _h;
private String _f;
private String _r;
private String _sn;

public Compound() {

}

    public Compound(String compound, String h, String f, String r, String sn) {
    this._compound = compound;
    this._h = h;
    this._f = f;
    this._r = r;
    this._sn = sn;
}

public Compound(String h, String f, String r, String sn) {
    this._h = h;
    this._f = f;
    this._r = r;
    this._sn = sn;
}

public void setCompound(String compound) {
    this._compound = compound;
}

public String getCompound() {
    return this._compound;
}

public void setH(String h) {
    this._h = h;
}

public String getH() {
    return this._h;
}

public void setF(String f) {
    this._f = f;
}

public String getF() {
    return this._f;
}
public void setR(String r) {
    this._r = r;
}
public String getR(){
    return this._r;
}
public void setSN(String sn){
    this._sn = sn;
}
public String getSN(){
    return this._sn;
}
}

And here is my main class:

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class oshaSearch extends AppCompatActivity {
TextView txtH;
TextView txtR;
TextView txtF;
TextView txtSN;
EditText txtCompound;
Button btnSearch;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_osha_search);
    txtF = (TextView) findViewById(R.id.txtF);
    txtR = (TextView) findViewById(R.id.txtR);
    txtH = (TextView) findViewById(R.id.txtH);
    txtSN = (TextView) findViewById(R.id.txtSN);
    txtCompound = (EditText) findViewById(R.id.searchCompound);
    btnSearch = (Button) findViewById(R.id.btnSearch);
}
public void lookupCompound (View view) {
    DataBaseHelper dbHandler = new DataBaseHelper(this, null, null, 1);

    Compound compound =
            dbHandler.findCompound(txtCompound.getText().toString());

    if (compound != null) {
        txtH.setText(String.valueOf(compound.getH()));
        txtF.setText(String.valueOf(compound.getF()));
        txtR.setText(String.valueOf(compound.getR()));
        txtSN.setText(String.valueOf(compound.getSN()));
    } else {
        txtH.setText("No Match Found");
    }
}
}

Any and all help is greatly appreciated. Please help me out. Additional information: The database is in the assets folder, All values in the database are text fields, The user is searching by compound name, The database has a .sqlite extension. Thank you all in advanced.

You know that `DataBaseHelper` will always create a new `chemicals.sqlite` on the phone, and ignore whatever you have in your assets, right? Because I don't see where you are populating your database. — David Medenjak, Mar 22 '16 at 18:18
Welcome to Stack Overflow! Thank you for taking the time to use correct grammar and make sure your code is properly formatted. Often times, new users seem to forget their manners when they post. — Matt C, Mar 22 '16 at 18:22
@david Thanks for the reply! I didn't know that... I thought I had it set up so it would search through the .sqlite folder in my assets and pull data from it. That's at least what my intention was... — KatieJRise, Mar 22 '16 at 18:31
You may want to look at http://stackoverflow.com/questions/20857734/reading-sqlite-file-from-asset-folder — OneCricketeer, Mar 22 '16 at 18:38
And linked in that post is [SQLiteAssetHelper](https://github.com/jgilfelt/android-sqlite-asset-helper), which can open files from your `assets` — OneCricketeer, Mar 22 '16 at 18:39

score 0 · Answer 1 · answered Mar 22 '16 at 18:34

0

You have to copy your database which you said is in the assets folder to newly created database -

 private void copyDataBase(String dbname) throws IOException {
        // Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(dbname);
        // Path to the just created empty db
        String outFileName = getDatabasePath(dbname);
        // 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();
    }

answered Mar 22 '16 at 18:34

Shadab Ansari

7,022
2
27
45

Thanks for the code @shadab , but I'm confused on how I would be able to search the database one's it's attached. Do I have to change something in my findCompound() method? – KatieJRise Mar 22 '16 at 18:54
Now that you have copied your assets database to your local db, you will have access to all the values. You don't have to change other methods. – Shadab Ansari Mar 22 '16 at 18:56
So I tried putting in the code, it's giving me errors saying I haven't defined myContext and getDatabasePath. I defined Context myContext, but for getDatabasePath, should I put in the database name there? Sorry, I'm really lost still. – KatieJRise Mar 22 '16 at 20:49
getDatabasePath () should return your application database file path – Shadab Ansari Mar 22 '16 at 20:50
So it's like return '"data/data/"+BuildConfig.APPLICATION_ID+"/databases"'? Got it. So then I just call CopyDataBase() in my findCompound() method? – KatieJRise Mar 22 '16 at 21:03

score 0 · Answer 2 · answered Mar 22 '16 at 18:49

you first need to check if database is properly created or not, for that

public DataBaseHelper(Context context) {
    super(context, Environment.getExternalStorageDirectory()+"/"+DATABASE_NAME, null, 1);
}

use this constructor , this will save your app database in external storage of your phone. before that give permission to external storage in manifest file. and use sqliteManager application from google play store to check database structure.

Or you can use stetho to debug database of android. reference link:http://code.tutsplus.com/tutorials/debugging-android-apps-with-facebooks-stetho--cms-24205

Pre-populated SQLite Database not reading properly in Android Studio

2 Answers2

Linked