Writing to pre-existing SQLite Database Android

Question

Okay so my android app makes use of a pre-existing database, which I access using this

http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

Now I have no problem reading data from the database, but I cannot seem to add to it. The problem is the database file is never updated, only the instance of the database I have running at the time. I need the changes I make to the database to be permanent, so I tried to serialize the instance of the database, but found that it cannot be done. I think that after I add to the instance of my database, I need to export this to a file, an with it overwrite the database file. I'm not sure how I would go about and have read every comment from the site above and have gone through countless post here as well.

I could be wrong but that's the only way I can imagine it being done.

public void addTenantWithoutProperty(String name, String email, String phone){

    //This method reads from my database
ArrayList<Tenants> tenants = (ArrayList<Tenants>) getAllTenants();

//Checks data in the database before I try add to it
for(int i = 0;i < tenants.size();i++){
 String name;
 name = tenants.get(i).getName();

 System.out.println(i + " before " + names);
}

//I have also tried adding to it using ContentValues, but it makes no difference
String tenantsql = "INSERT INTO tenants (name, phone, email) VALUES ('" + name + "','"        
+ phone + "','" +  email + "')";


myDataBase.execSQL(tenantsql);


tenants = (ArrayList<Tenants>) getAllTenants();



 //checks data in database after insertion 
 for(int i = 0;i < tenants.size();i++){
     String names;
     names = tenants.get(i).getName();

     System.out.println(i + " after " + names);
 }


 }

This is called on a button click, and the output shows that the data is being added, but it does not cause a permanent change in the database. How can I get around this? Any help at all would be greatly appreciated

EDIT: Resolved! The if statment in the createDataBase() read if(!dbExist), changed to if(dbExist) and works fine. Problem was that even if the database existed, it was being overwritten by the database file in my assets folder. Thanks for your replies regardless

Has your database `myDataBase` been opened with WRITE permissions? The example seems to be opening a read only instance. — mhradek, Jul 25 '12 at 17:52
Yes, here is the code in the openDataBase method: myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE); — dunika, Jul 25 '12 at 18:16
As a workaround, have you tried creating your own database and copying the data from the one provided into the one you've created? — Tanaki, Jul 25 '12 at 18:25
@Tanaki no I have not but that sounds like it could work. I'm not really sure how I would go about that, I'm fairly new to android and sqlite but I will look into it and give it a shot, thanks! — dunika, Jul 25 '12 at 18:38
@Tanaki Okay so I was thinking I could use something like if(newdatabase != null) - set newdatabase = mydatabase and then I can use that instead to read from and make updates to, but when I close and reopen my program, newdatabase = null again and all the changes I made will be lost. I need a database that is saved permanently for what I need in my app — dunika, Jul 25 '12 at 18:51
@user1552404 Unfortunately I do not have much Android experience, just Java and database experience. I feel that the tutorial you're following does a very strange way of copying the database, by copying the file data itself. I would suggest you get a new database (this.getWritableDatabase()), assign it the proper schema, and try to copy the values through SQL. — Tanaki, Jul 25 '12 at 19:03
Follow this sample code: http://stackoverflow.com/questions/9109438/using-already-created-database-with-android/9109728#9109728 — Yaqub Ahmad, Jul 25 '12 at 22:28

score 1 · Answer 1 · edited May 23 '17 at 10:34

As mhradek said your SQLite DataBaseHelper class seems to be opening the database with READONLY permission here:

checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

You may want to try to open the database with READ and WRITE permissions changing the OPEN_READONLY flag to OPEN_READWRITE. This would result in the following code:

checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

Google's Android documentation explains the two flags here Android OPEN_READONLY Flag

This may explain why the button click appears to be adding data, but the database is not permanently changed because it cannot be written to if opened that way.

Let me know if changing the flag works for you.

Cheers!

Nope my code read like so: checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);, I went through the whole thing and changed all the READ_ONLY I could find. — dunika, Jul 25 '12 at 18:18

Writing to pre-existing SQLite Database Android

1 Answers1