how to check if a SQLite database table exists

Put your problem here if it does not fit any of the other categories.

how to check if a SQLite database table exists

Postby barodapride » Fri Nov 05, 2010 7:21 am

Hey all, I've run into a problem with my program that seems to be a catch-22. The first time the user runs my program it should create a table an initialize all values of that table. The user is then able to modify and save the table. The next time the app is run I want it to see that a table already exists and not initialize/overwrite the user's saved data. The problem exists because the command myDB.execSQL("CREATE TABLE IF NOT EXISTS " doesn't return anything. Functionally it does the right thing for me but I cannot tell if the table already existed or not. I tried creating a test entry to the table to read from to determine if the table was already there but the program will crash when it tries to read it and it's not there. Is there a SQL command I need maybe?
barodapride
Junior Developer
Junior Developer
 
Posts: 23
Joined: Sat Sep 25, 2010 4:43 am

Top

Re: how to check if a SQLite database table exists

Postby Schermvlieger » Fri Nov 05, 2010 8:53 am

Try:

Code: Select all
myDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                  YOURTABLE +
                  " (" +
                  "column1 TYPE, column2 TYPE );");

Schermvlieger
Senior Developer
Senior Developer
 
Posts: 159
Joined: Fri Feb 26, 2010 1:37 pm

Re: how to check if a SQLite database table exists

Postby urbantrad » Fri Nov 05, 2010 10:17 am

In your SQLiteOpenHelper, you should put the creation of tables in the onCreate() method. This method is only called the first time you use the database (and you should call it again when you change the database, in the onUpgrade() method). This way you don't have to worry about overwriting any data that you want to keep.
urbantrad
Senior Developer
Senior Developer
 
Posts: 104
Joined: Thu Sep 09, 2010 10:19 pm

Re: how to check if a SQLite database table exists

Postby Schermvlieger » Fri Nov 05, 2010 10:51 am

I don't understand what the problem really is, barodapride; if you are happy that "IF NOT EXISTS" functionally does the right thing, why would you be worried about the user's data being lost?
Schermvlieger
Senior Developer
Senior Developer
 
Posts: 159
Joined: Fri Feb 26, 2010 1:37 pm

Re: how to check if a SQLite database table exists

Postby barodapride » Fri Nov 05, 2010 4:40 pm

Thanks for the replies. The problem is when the app is killed completely and restarted it goes back through the onCreate method which does the "Create table if not exists" (which is fine) but it also goes through the initialization of the table (not good) because I have no way of seeing if the table exists already or not. If I don't initialize the table, things dont work so well the first time the app is loaded. Sometimes writing these questions give me ideas...maybe I can use an "insert or abort" instead of an "insert or replace" to insert data the first pass through so nothing is overwritten? I'm not sure if that command would work as I'm not too familiar with mysqlite but i'll try it later today.
barodapride
Junior Developer
Junior Developer
 
Posts: 23
Joined: Sat Sep 25, 2010 4:43 am

Re: how to check if a SQLite database table exists

Postby Schermvlieger » Fri Nov 05, 2010 5:22 pm

I think you can do a quick select * from mytable and check the number of rows in your cursor to find out if the table had just been created or not, would that not work? This is the way I do it and it works fine.
Schermvlieger
Senior Developer
Senior Developer
 
Posts: 159
Joined: Fri Feb 26, 2010 1:37 pm

Top

Re: how to check if a SQLite database table exists

Postby barodapride » Fri Nov 05, 2010 7:36 pm

What if the table doesnt exist? Wouldnt you get a force close? I think I was able to solve my problem by using "insert or ignore into" on the initialization of the table. That way if something is already there it doesnt overwrite it. I'm just not familiar with sqlite so I didnt know that option was there. Thanks for the help guys!
barodapride
Junior Developer
Junior Developer
 
Posts: 23
Joined: Sat Sep 25, 2010 4:43 am

Re: how to check if a SQLite database table exists

Postby Schermvlieger » Fri Nov 05, 2010 7:58 pm

But the table will exist, because you query it after you called "create table if not exists" statement...
Schermvlieger
Senior Developer
Senior Developer
 
Posts: 159
Joined: Fri Feb 26, 2010 1:37 pm

Re: how to check if a SQLite database table exists

Postby potatoho » Wed Nov 10, 2010 4:58 am

sqlite_master contains queryable schema information. You could use a query such as..

SELECT count() FROM sqlite_master WHERE type='table' AND name='xxx'

Use it with DatabaseUtils.longForQuery(..), testing for a count of 0 or 1.
potatoho
Experienced Developer
Experienced Developer
 
Posts: 61
Joined: Fri May 21, 2010 9:49 pm

Top

Return to Other Coding-Problems

Who is online

Users browsing this forum: Google [Bot] and 12 guests