Database Helper class?

Question

I am having trouble getting my database to effectively store information. I am using Android Studio and I am trying to create a sqlite db on the fly and insert information into it.

I am not able to insert information into my table ( I am returning a -1), however, I am not sure if the problem is the insert statement or the table/db creation.

Here is the code. Any help would be appreciated.. I have been banging my head on this for a while now.

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String TAG = "DatabaseHelper";


SQLiteDatabase database;
private static final String Database_Name = "CISP_340.db";
private static final String Table_Name = "ANIMALS";

private static final String col1 = "_ID";
private static final String col2 = "ANIMAL_TYPE_CD";
private static final String col3 = "COUNT_NO";
private static final String col4 = "SEENON_DTM";
private static final String col5 = "COMMENTS_TXT";

public DatabaseHelper(Context context){
    super(context, Table_Name, null, 1);
    database = getWritableDatabase();
}

@Override
public void onCreate(SQLiteDatabase db) {
    String createTable = "CREATE TABLE " + Table_Name + " (ID INTEGER PRIMARY KEY AUTOINCREMENT, "+
            col2 + " INTEGER DEFAULT 0, " +
            col3 +" INTEGER, " +
            col4 + " TEXT, " +
            col5 + " TEXT)";
    db.execSQL(createTable);
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("DROP IF TABLE EXISTS " + Table_Name);
    onCreate(db);
}

public boolean addData(Animal animal){

    ContentValues contentValues = new ContentValues();
    contentValues.put(col5, animal.getComments());
    Log.d(TAG, "addData: Adding " + animal.toString() + " to " + Table_Name);

    long result = database.insert(Table_Name, null, contentValues);

    if (result == -1) {
        return false;
    }else{
        return true;
    }
}

public Cursor getData(){
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "SELECT * FROM " + Table_Name;
    Cursor data = db.rawQuery(query, null);
    return data;
}
}

Here is my animal Class:

public class Animal {

Animal(){
    //Blank
}

Animal(int animalType, int count, String seenOn, String comments){
    this.animalType = animalType;
    this.count = count;
    this.seenOn = seenOn;
    this.comments = comments;
}

private int animalType;
private int count;
private String seenOn;
private String comments;

public int getAnimalType() {
    return animalType;
}

public void setAnimalType(int type){
    this.animalType = type;
}

public int getCount() {
    return count;
}

public void setCount(int count){
    this.count = count;
}

public String getSeenOn() {
    return seenOn;
}

public void setSeenOn(String seenOn){
    this.seenOn = seenOn;
}

public String getComments() {
    return comments;
}

public void setComments(String comments){
    this.comments = comments;
}

@Override
public String toString(){
    return "Animal type: " + animalType + ", Animal count: " + count + ", Animal was seen on: " + seenOn + ", Comments about encounter: " + comments;
}

}

Here is my MainActivity:

public class MainActivity extends AppCompatActivity {

private static final String TAG = "MainActivity";

DatabaseHelper dataBaseHelper;
private Button btnAdd, btnView;
private EditText editText;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    editText = (EditText) findViewById(R.id.editText);
    btnAdd = (Button) findViewById(R.id.btnAdd);
    btnView = (Button) findViewById(R.id.btnView);
    dataBaseHelper = new DatabaseHelper(this);


    Animal myAnimal = new Animal(1, 0, "Yes", "Raining");
    Animal myAnimal2 = new Animal(1, 0, "No", "");
    addAnimal(myAnimal);
    addAnimal(myAnimal2);


    btnAdd.setOnClickListener(new View.OnClickListener(){
        @Override
        public void onClick(View v){
            Animal animal = new Animal();
            animal.setComments(editText.getText().toString());
            if (editText.length() != 0){
                addAnimal(animal);
                editText.setText("");
            }else{
                returnMessage("You must put something into this field");
            }
        }
    });

    btnView.setOnClickListener(new View.OnClickListener(){
        @Override
        public void onClick(View v){
            Intent intent = new Intent(MainActivity.this, ListDataActivity.class);
            startActivity(intent);
        }
    });
}


public void addAnimal( Animal animal ){
    boolean insertData = dataBaseHelper.addData(animal);

    if (insertData){
        returnMessage("Success");
    }else{
        returnMessage("Fail");
    }
}

private void returnMessage(String message){
    Toast.makeText(this, message, Toast.LENGTH_SHORT).show();
}

}

Output in LogCat when attempting to insert in the constructor:

12-02 17:20:23.624 4100-4100/edu.davenport.cisp340.animaltracker E/SQLiteDatabase: Error inserting COUNT_NO=0 ANIMAL_TYPE_CD=1 SEENON_DTM=Yes COMMENTS_TXT=Raining
                                                                               android.database.sqlite.SQLiteException: table ANIMALS has no column named SEENON_DTM (code 1): , while compiling: INSERT INTO ANIMALS(COUNT_NO,ANIMAL_TYPE_CD,SEENON_DTM,COMMENTS_TXT) VALUES (?,?,?,?)
                                                                                   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.SQLiteStatement.<init>(SQLiteStatement.java:31)
                                                                                   at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1472)
                                                                                   at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
                                                                                   at edu.davenport.cisp340.animaltracker.DatabaseHelper.addData(DatabaseHelper.java:59)
                                                                                   at edu.davenport.cisp340.animaltracker.MainActivity.addAnimal(MainActivity.java:65)
                                                                                   at edu.davenport.cisp340.animaltracker.MainActivity.onCreate(MainActivity.java:32)
                                                                                   at android.app.Activity.performCreate(Activity.java:6980)
                                                                                   at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1213)
                                                                                   at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2770)
                                                                                   at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2892)
                                                                                   at android.app.ActivityThread.-wrap11(Unknown Source:0)
                                                                                   at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1593)
                                                                                   at android.os.Handler.dispatchMessage(Handler.java:105)
                                                                                   at android.os.Looper.loop(Looper.java:164)
                                                                                   at android.app.ActivityThread.main(ActivityThread.java:6540)
                                                                                   at java.lang.reflect.Method.invoke(Native Method)
                                                                                   at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
                                                                                   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)

Answer 1

Edit

Now that you added the log. The issue is that column SEENON_DTM doesn't exist.

As per :-

• table ANIMALS has no column named SEENON_DTM

You have probably added the column since running the App. Just amending the SQL used by the onCreate method isn't enough to change the structure of the database. That is onCreate is only run once when the database is created, the database is then saved to a file and hence how it stays around. Subequent instantation of the DatabaseHelper, find the file and doesn't call onCreate.

The fix, if losing any data (no useful data would likely exist) isn't an issue is to do one of the following;

• delete the App's data (from settings), or
• uninstall the App, or
• increase the database version i.e. change super(context, Table_Name, null, 1); to super(context, Table_Name, null, 2); (1 to 2)
  • (this will result in the onUpgrade method running which should drop the table (see note below about using DROP TABLE IF EXISTS and not DROP IF TABLE EXISTS))

and then rerun the App.

---

I believe that you code is not an issue (the only issue in the code is with DROP IF TABLE EXISTS, it should be DROP TABLE IF EXISTS ).

Your code works as expected (see below), so the issue is either how you are checking the data or that the Animal class is the cause.

Below is code that uses you DatabaseHelper code and invokes it from an Activity (the Animal class may be different to yours).

I'd suggest trying the code below, suitably adjusted for your DatabaseHelper class (i.e. change AnimalDBhelper to DatabaseHelper) and then checking the log. The output should be as below (perhaps with more rows if you have added rows and the database hasn't been deleted).

Testing

Using a copy of your code (renaming the class to AnimalDBhelper to suit my testing environment) and then using the following code in an activity (the invoking code) :-

public class MainActivity extends AppCompatActivity {

    AnimalDBhelper mADBHlpr;

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

        // Create two animals
        mADBHlpr = new AnimalDBhelper(this);
        Animal myanimal1 = new Animal(1,0,"Yes","Raining");
        Animal myanimal2 = new Animal(1,0,"No","");

        mADBHlpr.addData(myanimal1); // Add the first animal
        Cursor csr = mADBHlpr.getData(); get the data
        DatabaseUtils.dumpCursor(csr); dump the data (write it to the log)
        mADBHlpr.addData(myanimal2); // Add the 2nd
        csr = mADBHlpr.getData();
        DatabaseUtils.dumpCursor(csr);
        csr.close(); // Done with the cursor so close
    }
}

Result :-

12-02 19:37:54.040 1172-1172/? D/DatabaseHelper: addData: Adding ?.Animal@534af570 to ANIMALS
12-02 19:37:54.044 1172-1172/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534a5df8
12-02 19:37:54.044 1172-1172/? I/System.out: 0 {
12-02 19:37:54.044 1172-1172/? I/System.out:    ID=1
12-02 19:37:54.044 1172-1172/? I/System.out:    ANIMAL_TYPE_CD=0
12-02 19:37:54.044 1172-1172/? I/System.out:    COUNT_NO=null
12-02 19:37:54.044 1172-1172/? I/System.out:    SEENON_DTM=null
12-02 19:37:54.044 1172-1172/? I/System.out:    COMMENTS_TXT=Raining
12-02 19:37:54.044 1172-1172/? I/System.out: }
12-02 19:37:54.044 1172-1172/? I/System.out: <<<<<
12-02 19:37:54.044 1172-1172/? D/DatabaseHelper: addData: Adding ?.Animal@534af5c4 to ANIMALS
12-02 19:37:54.048 1172-1172/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@534ab7d4
12-02 19:37:54.048 1172-1172/? I/System.out: 0 {
12-02 19:37:54.048 1172-1172/? I/System.out:    ID=1
12-02 19:37:54.048 1172-1172/? I/System.out:    ANIMAL_TYPE_CD=0
12-02 19:37:54.048 1172-1172/? I/System.out:    COUNT_NO=null
12-02 19:37:54.048 1172-1172/? I/System.out:    SEENON_DTM=null
12-02 19:37:54.048 1172-1172/? I/System.out:    COMMENTS_TXT=Raining
12-02 19:37:54.048 1172-1172/? I/System.out: }
12-02 19:37:54.048 1172-1172/? I/System.out: 1 {
12-02 19:37:54.048 1172-1172/? I/System.out:    ID=2
12-02 19:37:54.048 1172-1172/? I/System.out:    ANIMAL_TYPE_CD=0
12-02 19:37:54.048 1172-1172/? I/System.out:    COUNT_NO=null
12-02 19:37:54.048 1172-1172/? I/System.out:    SEENON_DTM=null
12-02 19:37:54.048 1172-1172/? I/System.out:    COMMENTS_TXT=
12-02 19:37:54.048 1172-1172/? I/System.out: }
12-02 19:37:54.048 1172-1172/? I/System.out: <<<<<

Answer 2

You haven't really described what's going wrong (are you querying the Cursor and not finding the data you expect?), but you need to close the cursor somewhere (see this answer), and you don't need to get the database in getComment() since you already have it. Here's a version that ought to work in your example class.

public String getComment(){
    String query = "SELECT * FROM " + Table_Name;
    String comments = "";
    Cursor data = database.rawQuery(query, null);
    if( data.moveToFirst() ) { // iterate all the rows of the table
        do {
            String val = data.getString(4); // pull out column 5
            // String val = data.getString(data.getColumnIndex(col5)); // or this way if you prefer
            comments += val; // do something with the data - e.g. rebuild Animal object
        } while (data.moveToNext());
    }
    data.close();
    return comments;
}