0

I have an Question. Releted SQLite database.

After updating the version number 1 to 2,my database updated by onUpgrade() .

1-I want to know ,i can use the older databases or not.

and onUpgrade() i use db.drop("previous table");

2-so after dropping that table can i take data from that or not.

please explain in detail.

Any good suggestion will be accepted.

Edited:- CAN WE USE OLDER VERSION OF DATABASE OR NOT.

Monty
  • 3,205
  • 8
  • 36
  • 61

3 Answers3

1

No you cannot access anything from the old table after dropping - dropping a table is a like a delete operation - you aren't supposed to easily regain access to it. From the first link I got from my search engine:

Dropping the table removes the table definition as well as all of its rows.

If you still need your previous data I recommend still working with that database and not dropping it until you feel it isn't needed anymore. This SO answer goes into a lot of detail about how to properly upgrade a table. One interesting thing to note is that if you do decide to use ALTER TABLE:

SQLite is limited on the ALTER TABLE command, it allows add and rename only no remove/drop which is done with recreation of the table.

So there are multiple ways of working with this - ALTER TABLE is nice if you have a small thing to change, you don't have to destroy everything, but if you need at least one column/row deleted you're going to want to follow the answer and create a temp table.

Community
  • 1
  • 1
A--C
  • 36,351
  • 10
  • 106
  • 92
  • if i change the version of that database .it will lost the data or it will have – Monty Jan 18 '13 at 04:18
  • ok A-C one more thing what is the right place to create table. – Monty Jan 18 '13 at 04:20
  • @Cobra If you updgrade the version number, it should call `onUpgrade()`, so if you drop, you won't be able to access the data, however if you alter, you can still access the data. [This](http://stackoverflow.com/questions/3424156/upgrade-sqlite-database-from-one-version-to-another) SO question goes into a lot of detail. **edit:** what do you mean the right place to create a table? Your database helper class has some clearly defined methods. – A--C Jan 18 '13 at 04:21
  • @Cobra no problem. I recommend following that SO answer I linked - it's more detailed than I can make my answer. – A--C Jan 18 '13 at 04:34
  • A-C can you please explain .can i get older version of database or not ....suppose i have upgrade1 and upgrade1(new version database)..so i can use upgrade1 or not. – Monty Jan 18 '13 at 12:26
  • @cobra or all depends on how your onUpgrade method works if you drop the previous version no. If you alter instead yes you still have access to previous data. Once you're execute the drop your table is basically deleted. – A--C Jan 18 '13 at 12:36
  • your mean version is nothing just updating the database like add table ,add column in new version...if i do not delete any table it ll be same in new version – Monty Jan 18 '13 at 12:40
  • @Cobra what you have to understand is that changing the certain number will call onUpdrade. You can do whatever you want there. Add I said before if you drop your table you have to make a new one. If you alter, you will basically have the same table with a new version number. Read the links I have in my answer. – A--C Jan 18 '13 at 13:00
1

If you want to continue using that table's data, don't drop it. Use ALTER TABLE statements instead to add the new fields.

Gabe Sechan
  • 90,003
  • 9
  • 87
  • 127
  • Gabe..i want to know what is the right place to create a table and why that place only.....please explain and edit your ans. – Monty Jan 18 '13 at 04:21
  • If you're using an SQLiteOpenHelper, create the table in onCreate. In onUpdate, you should use ALTER TABLE commands to add or delete columns and change the old schema into the new one. By altering the table rather than dropping it you can keep all the old data in it. – Gabe Sechan Jan 18 '13 at 04:37
1

No. What you have to do is to take a copy of the data (either in memory or in another database), recreate your database and then write back the data.

This, of course, only if you need to drop one or more tables for the upgrading process. For simple cases like adding a new column, there is no need to drop/recreate the table; therefore simplifying the process.

SylvainL
  • 3,926
  • 3
  • 20
  • 24
  • how can i copy an older database version..is there any link – Monty Jan 18 '13 at 04:23
  • One way would be to read it into memory. You could also copy the file. There are plenty of examples on how to copy a file; search with Google with something like "sqlite android copy database". Another way would be to create a second database and use it as a temporary store. All in all, reading into memory is the best way to go if the database is small. – SylvainL Jan 18 '13 at 05:53