CODEWITHSUNDEEP
androidsqlitepragma
pgsandstrom
pgsandstrom

Reputation: 14399

Detect if a table contains a column in Android/sqlite

So I have an app on the market, and with an update I want to add some columns to the database. No problems so far. But I want to detect if the database in use is missing these columns, and add them if this is the case. I need this to be done dynamically and not just after the update to the new version, because the application is supposed to still be able to import older databases. Normally I would be able to use the PRAGMA query, but Im not sure how to do this with Android. I cant use execSQL since it is a query, and I cant figure out how to use PRAGMA with the query()-function.

Ofcourse I could just catch exceptions and then add the column, or always add the columns to each table before I start to work with it, but that is not a neat solution.

Cheers,

Upvotes: 4

Views: 5758

Answers (4)

Brad Hein
Brad Hein

Reputation: 11047

Pragma works. As an example I just tried it on one of my Android App DBs. 

sqlite> pragma table_info (userCommand);

cid name        type    notnull     dflt_value  pk
0   id      INTEGER     0       1
1   description TEXT    0       0
2   message     TEXT    0       0

As we can see there are three fields in the specified table. id, description, and message.

So in your app, use something like: 

Cursor c = null;
c = RawQuery ("pragma table_info (userCommand)",null); 
// if c has records and is not null then iterate through the records in search of your table name.

Please excuse the poor formatting.

Upvotes: 12

CommonsWare
CommonsWare

Reputation: 1006644

Try using PRAGMA with rawQuery() instead of query().

Upvotes: 3

Rich
Rich

Reputation: 36806

This may be overkill, but you can select a single row from your table into a Cursor and use Cursor.getColumnIndex([column name as string]). This will return -1 if it doesn't exist.

Upvotes: 3

My Other Me
My Other Me

Reputation: 5117

You could issue the following command and go over the results and see if your column is listed.

pragma table_info(table_name);

Edit: I've never done this on Android, but I think it should work

Upvotes: 0

Related Questions