Sunday, January 24, 2010

Creating multiple sqlite database tables in Android



Most of the Android database examples you will find on the web will usually contain only one table to demonstrate the basic database concepts.

That's great, the only problem with this is that most non-trivial database implementations will contain more than one table.


The standard database creation string for a single table will probably look a lot like the below:


private static final String CREATE_TABLE_1 =
" create table " + table1 +
" (_id integer primary key autoincrement," +
" title text not null, body text not null);";



Which is called in your DB Adapter class like this:


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE_1);
}


So what to do if you want to create more than one table?
You may do the below.. but will it work?
Note that this is one big string containing three separate create statements...


private static final String DATABASE_CREATE_MULTIPLE_TABLES =
" create table " + ITEMS_TABLE +
" (_id integer primary key autoincrement," +
" title text not null)" +

" create table " + TAGS_TABLE +
" (_id integer primary key autoincrement," +
" tagName text not null)" +

" create table " + LOCATIONS_TABLE +
" (_id integer primary key autoincrement," +
" locationName text not null, gpsCoOrds text);"
;


And then you try calling them in your DB Adapter class like so:


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(DATABASE_CREATE_MULTIPLE_TABLES);
}


So you're trying to create the three tables in one call to db.execSQL.


This appears to compile and run successfully, you can even read and write to the FIRST table that is created, but..
..when you try to read or write to any other table you will see the dreaded

'Sorry! The application Kdkddfblah (process test.Kdkddfblah) has stopped unexpectedly. Please try again'

..error message.


Uh-oh.

If you debug your application, you might see references to syntax errors 'near create', and possible a reference that the table doesn't exist.

Hmm... What went wrong?

The answer is that sqlite, and therefore the db.exec method, only lets you execute one sql command at a time. We were trying to run three sql statements in one go in db.execSQL(DATABASE_CREATE_MULTIPLE_TABLES);.

So what you need to do to fix this is move each of the above table create statements into their own strings, like this (Note that this now creates three seperate strings, unlike above):


private static final String CREATE_TABLE_1 =
" create table " + table1 +
" (_id integer primary key autoincrement," +
" title text not null, body text not null);";

private static final String CREATE_TABLE_2 =
" create table " + TAGS_TABLE +
" (_id integer primary key autoincrement," +
" tagName text not null)";

private static final String CREATE_TABLE_3 =
" create table " + LOCATIONS_TABLE +
" (_id integer primary key autoincrement," +
" locationName text not null, gpsCoOrds text);";


.. and then use these strings in your onCreate method like below, this then works.


@Override
public void onCreate(SQLiteDatabase db) {

db.execSQL(CREATE_TABLE_1);
db.execSQL(CREATE_TABLE_2);
db.execSQL(CREATE_TABLE_3);
}


If you find this doesn't work for you, try dropping all the tables in your database and try again, or give the database a different name, or different version number. The SQLiteOpenHelper seems to have some troubles registering that the database is to be changed. It finds a db with the same name and version number, goes 'meh' and doesn't look to see if the structure is different at all.

You can also pull the sqlite db file right off your device (or emulator) by going into the DDMS perspective in Eclipse (Window menu\ Open perspective \ Other \ DDMS), navigating to the database file which will probably be at \data\data\*Your Application Name*\databases.

There's a 'pull file' button on the top left as seen highlighted below:



.. You can then open the DB in your favourite sqlite manager (I like Sqliteman) and play around. Can can also of course, push the file back to the device if you wish.




65 comments:

  1. Please help me in database concepts....i tried a lot of examples but it's not clear to me.

    ReplyDelete
  2. Hi Harminder, sure, what would you like to learn?

    Here are some basic database concepts that I've found:
    http://www.atlasindia.com/sql.htm

    http://www.hermit.cc/teach/ho/dbms/dbms.htm

    http://www.expertwebinstalls.com/cgi_tutorial/basic_relational_database_concepts.html

    .. let me know if you find what you're looking for or not.



    Glenn

    ReplyDelete
    Replies
    1. Just wanted to say youre awesome...seriously the tutorial didnt helped me a bit but I saw down below how you answered each and every one here....awesome bro..awesome!

      Delete
  3. Hi,
    I did exactly the same:

    private static final String DATABASE_CREATE_NOTES =
    "create table notes (_id integer primary key autoincrement, "
    + "title text not null, body text not null)";

    private static final String DATABASE_CREATE_ROUTES =
    "create table routes (_id integer primary key autoincrement, "
    + "start text not null, arrival text not null, "
    + "line text not null, duration text not null);";

    private static final int DATABASE_VERSION = 3;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    db.execSQL(DATABASE_CREATE_NOTES);
    Log.d(TAG, "created notes table");

    db.execSQL(DATABASE_CREATE_ROUTES);
    Log.d(TAG, "created routes table");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
    + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS notes");
    onCreate(db);
    }
    }

    ----------
    But when I try to insert something into the routes table, I get an error that saYS: routes table does not exist..... ?!??!?!?

    ReplyDelete
  4. Hi Jayomat,
    .. try increasing your db version or changing the db name and try again.
    This issue caused me a lot of pain! Basically, as I understand it, you get one chance to create the table with that name and version number.

    Perhaps check your logs for errors, and confirm that your db create script successfully can create a sqlite db outside of Android (from the command line, or a sqlite manager app for instance).
    ..It is possible :)
    Also try things like I suggested like pulling the sqlite file off the emulator and confirming that you only have one table being created.
    Let me know if you're still having issues after you've tried these things.

    All the best,
    Glenn

    ReplyDelete
  5. hi,
    i solved my problem by modifying the onUpgrade() method. However now I have a different problem.

    I need to delete only ONE Table of my db. I know there is a method you can call on SQDB like db.delete("dbName",null,null).. but from where I want to call the function, I have no reference to my db to pass as an argument for delete... any suggestions?

    ReplyDelete
  6. THANK YOU! DAYS I've spent debugging this until I decided to Google "android databasehelper create multiple tables" (tagging :) and found your post. THANK YOU THANK YOU!!

    ReplyDelete
  7. Thanks for the tip. I'm just getting to the database portion of my app and would have hit a hard wall if I didn't know that I could only run one command at a time when building tables.

    ReplyDelete
  8. Hi Glenn,

    I would appreciate your help, I am trying to add the db file within my package as you have shown above by navigating to data/data/ but when I go to my package there is only a lib folder.

    Do you have any idea how I can add a database folder.

    Thanks

    ReplyDelete
  9. Hi Jeetin,

    Are you saying that you want to package a pre-made database with your app, rather than creating it with script upon the app running?

    If so, (and I do the same), I found this link immensely useful:
    http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

    .. if not, let me know some more details on what you're trying to achieve and I'll have a further look for you.

    Cheers,
    Glenn

    ReplyDelete
  10. Hi Glenn,

    I appreciate your help it is very valuable.

    I have the db file within the assets folder in my project. When I run the program I get an unexpectedly error in the emulator.

    I also receive this in log cat:
    sqlite3_openv2("/data/data/com.quizzy/databases/quiz", &handle, 1, NULL) failed

    Upon running my application I did not have a database folder when switching to DDMS view and navigating to my package through data/data.

    I then created a webview in my application which then created the database folder and I added the db file within there. Could not find another way of doing so.

    Please help as I need this to work for my final year project. Many thanks for your feedback.

    Cheers,
    Jeetin

    ReplyDelete
  11. Hi Glen,

    I have moved this to a new project, this time it has created the database folder within data/data/mypackage and it has the db file within this.

    When running I still get the same error and on debug this shows up as source not found.

    I'll still keep trying to work around this, appreciate any advice.

    Thanks
    Rajin

    ReplyDelete
  12. Hi Jeetin,

    No worries, you're very welcome. I appreciate you visiting my humble little blog :)

    To business:

    The 'databases' folder won't exist unless a database has been created for your app (you probably are already aware of this).

    In saying that, I can't see anything wrong with creating it manually, you might want to confirm that all the permissions are correct (compare them to an app where you've created the database typically, using a script, based on Google's 'Hello Notepad' for instance).

    The example link I provided in my previous comment uses the approach or creating an empty 'dummy' database and then copying your own over the top of it.

    Can you confirm that you have a table called "android_metadata" in your db and that each table contains an '_id' primary key? I believe these are required or you will receive errors. This is referenced in the prev link also.

    I can vouch for the code in the example link. I've used it on several apps without issue.

    Best of luck, let me know how you go, and if I can be of any further assistance.

    Kind Regards,
    Glenn

    ReplyDelete
  13. Hi Glenn,

    I am very grateful for your help. I have not managed to fix this issue.

    I do have android_metadata and each table does contain a primary key.

    Also please be advised I came across your blog when searching for people who had come across this error before and I am not using the example link or the code above in my project.

    I would be even more grateful if you could help me a little further by allowing me to send you the code I am working with and maybe pointing me in the right direction.

    Cheers,
    Jeetin

    ReplyDelete
  14. Hi Jeetin,

    Yes, if you want to zip up your code and send it to me, I'd be happy to have a look.

    One thing I'd like to confirm is that your db tables have a primary key of '_id'? It has to be that precise field name, having a primary key with another name will not suffice I believe.

    You can email me @ glenn@bluemongo.com and I'll get back to you asap.


    Cheers,
    Glenn

    ReplyDelete
  15. Hi Glenn,
    I have a doubt regarding the _id. Is it necessary that the _id should be an integer primary key. I mean my primary key is of TEXT type.

    Regards,
    Mahesh

    ReplyDelete
  16. Hi Manesh,

    .. I haven't tried it, but I can't see anything in the documentation that says that the _id column needs to be an int.

    I've honestly never really had need for a text primary key, try it out and let me know how it goes.

    Best of luck,
    Glenn

    ReplyDelete
  17. Hey glenn,

    i ve got problems in android database. the database connection has been established but it says that the table does not exist. kindly tell me how to proceed now.. I m not able to proceed for the last 48 hrs.. :(

    i ve put my database in the assets folder of my "package"

    i ve also set the ppath as /data/data/com.banker.www.databases/

    ReplyDelete
  18. Hi Les,

    .. Ok, can your code find any tables in your db? Or can you see one table but not others?

    Your path looks ok, and if you're making a connection then the problem is probably something in the structure of your db.

    How did you create your db? Can you post a schema for us?


    Cheers,
    Glenn

    ReplyDelete
  19. Thank god (and Google) I've found this post... exactly the problem I'm having right now.
    Thanks man!

    Ofir.

    ReplyDelete
  20. Hi i want to know that how can i get data from pre developed database from sqlite Browser 2.0 in android please give me the way how to save a new sqlite database from sqlite database browser..

    ReplyDelete
  21. Hi Raj,

    To package a pre-made database with your app try this:
    http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/.

    Hope that helps.

    Cheers,
    Glenn

    ReplyDelete
  22. Thanks for this, just hit this problem and found your post within minutes.

    ReplyDelete
  23. Hello Glenn,
    what Eclipse color preferences do you have to get such nice dark UI theme?
    All Eclipse themes plugins that I met were able to change syntax coloring only, but not the Eclipse UI elements itself.

    ReplyDelete
  24. Hi Mr.Stablex,

    the color theme you seen here was actually just the dark theme in Ubuntu linux. In the modern version I think it's called 'Ambiance'. This screenshot is from quite a while ago.

    Of course, if you're using Windows this information might not be so useful to you...

    ReplyDelete
  25. Hi Glenn. Is it possible to dynamically create a table at runtime?

    ReplyDelete
  26. Yeah, due to my programming technologies I am using Windows. And unfortunately it isn't so customizable as Linux. Thanks for your answer anyways.

    ReplyDelete
  27. how i do android program which have 10 question set and ans of tht question and after plaing quiz we calculate score.......
    plz m learnng
    help me

    ReplyDelete
  28. how i can devlope android program quiz type
    in which we have 10 questionset and each question have 4 options and after doing tht we have to calculate score..........
    plz m new
    help me out...........

    ReplyDelete
  29. Hi Glenn,

    I have a quick question. I was able to create a table for the database. The tables are created only when the database is created for the first time. That is by calling oncreate method for database. The problem is I want to add tables into the database in the middle of my application. How would i approach this problem. I have been stuck at this point for the past 2 weeks and I thought you have the answer for it.

    Please Help me out. It is my school project and the deadline is approaching very near.

    Sincerely,
    Abhiram

    ReplyDelete
  30. This is great stuff. Thanks for sharing with us.
    This link
    http://mindstick.com/Articles/1f25b4d7-7957-4695-be35-c785e6344cd2/?Create%20Table%20in%20Android%20Database
    also helped me to complete my task.

    ReplyDelete
  31. Hi Glenn,

    For the application that I'm am developing, I need two SQLite tables in one SQLite database. So if I manually open the first table in my program, I want the program to be able to automatically load the second table without the intervention of the user. Do you think there is way to tie tables together in SQLite. Any help would be appreciated.
    Thanks

    ReplyDelete
  32. Hi Divya,
    I'd need some more information on what you're wanting to be doing to be sure, but it sounds like you want to pull some data into one view based on what data is being pulled in another view.
    If that is the case, then yes, absolutely that is possible. If you could give some more specific details that would help.

    ReplyDelete
  33. Really a great blog for beginners.

    ReplyDelete
  34. Thanks for the good work. I created a multiple table in this manner and it worked.


    for (String TABLE_NAME: TABLE_NAMES ){
    db.execSQL("create table " + TABLE_NAME + " ("
    + DATABASE_COLUMN_ID + " integer primary key autoincrement, "
    + DATABASE_COLUMN_DATE + " text, "
    + DATABASE_COLUMN_TITLE + " text UNIQUE not null, "
    + DATABASE_COLUMN_DESCRIPTION + " text, "
    + DATABASE_COLUMN_CONTENT + " text, "
    + DATABASE_COLUMN_LINK + " text);");

    }
    Thought I should share it.

    ReplyDelete
  35. package org.ass.assemblydb;

    import android.content.ContentValues;

    import android.content.Context;
    import android.database.Cursor;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.util.Log;


    public class assdb {



    public static final String KEY_NAME = "name";
    public static final String KEY_DESC = "desc";
    public static final String KEY_ROWID = "_id";
    public static final String KEY_CNAME = "cname";
    public static final String KEY_SNAME = "sname";
    public static final String KEY_COST = "cost";


    private static final String TAG = "assdb";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;



    /**
    * Database creation sql statement
    */
    private static final String CREATE_TABLE_SUBASS =
    "create table subtable (_id integer primary key autoincrement, "
    + "name text , desc text );";


    private static final String CREATE_TABLE_COMP =
    "create table comptable (_id integer primary key autoincrement, "
    + "name text , desc text );";

    private static final String DATABASE_NAME = "assdatabase";
    private static final String SUBASS_TABLE = "subtable";
    private static final String COMP_TABLE = "comptable";
    private static final int DATABASE_VERSION = 3;

    private final Context mCtx;



    private static class DatabaseHelper extends SQLiteOpenHelper {

    DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    db.execSQL(CREATE_TABLE_SUBASS );
    db.execSQL(CREATE_TABLE_COMP);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub


    Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
    + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS notes");
    onCreate(db);

    }



    }

    public assdb(Context ctx) {
    this.mCtx = ctx;
    }

    public assdb open() throws SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();

    return this;
    }
    public void close() {
    mDbHelper.close();
    }


    public long createNote(String name, String desc) {
    ContentValues i1 = new ContentValues();

    i1.put(KEY_NAME, name);
    i1.put(KEY_DESC, desc);
    return mDb.insert(SUBASS_TABLE, null, i1);


    }
    public long createNote1(String cname,String sname,int cost) {

    ContentValues i2=new ContentValues();
    i2.put(KEY_CNAME, cname);
    i2.put(KEY_CNAME, cname);
    i2.put(KEY_COST,cost);
    return mDb.insert(COMP_TABLE, null, i2);


    }

    public Cursor fetchAllNotes() {

    return mDb.query(SUBASS_TABLE, new String[] {KEY_ROWID, KEY_NAME,
    KEY_DESC}, null, null, null, null, null);


    }

    public Cursor fetchAllNotes1() {
    return mDb.query(COMP_TABLE, new String[] {KEY_ROWID, KEY_CNAME,
    KEY_SNAME, KEY_COST}, null, null, null, null, null, null);

    }

    public static Cursor getData() {
    // TODO Auto-generated method stub
    return null;
    }



    }

    here i add two table in single database.first table was worked but i cant add data in second table

    ReplyDelete
  36. how can i check whether one table record present in another table

    ReplyDelete
  37. Hi raji,
    you say you can't add data to the second table.. Can you please debug your code and tell me what the error message is?

    Cheers,
    Glenn

    ReplyDelete
  38. I need a code for converting text file(.txt) into html file in android

    ReplyDelete
  39. hi,
    But now i can able to add data to the second table thank you

    ReplyDelete
  40. hi,
    how to get a file from localdisk in android 1.5

    ReplyDelete
  41. hi,
    how to store the file taking from emulator into localdisk

    ReplyDelete
  42. Hi Raji,

    why don't you just use the pull file option as shown in the bottom of this article to get a file from emulator into localdisk?
    With your question about converting a text file to html, could you provide some more details?

    Cheers,
    Glenn

    ReplyDelete
  43. hi,
    i have to convert text file into html file and send that html file into my email id

    ReplyDelete
  44. Hi raji,
    When you say 'convert text file into html' what do you mean?
    Html files ARE text files with html markup in them and .html as the extension.

    What does your text file contain?

    Sorry, could you please provide more detail on what you're trying to achieve?

    Cheers,
    Glenn

    ReplyDelete
  45. hi my text file contain my resume. It will displayed as html content in my emulator.

    ReplyDelete
  46. Hi raji,
    When you say 'convert text file into html' what do you mean?
    Does your text file actually contain html markup?

    Perhaps my article on sharing might be useful to you?

    ReplyDelete
  47. hi,
    I GOT THE CODE TO CONVERT TXT INTO HTML BUT I WANT TO SEND THAT HTML FILE INTO EMAIL

    ReplyDelete
  48. Hi Raji,
    Perhaps this would help?
    http://stackoverflow.com/questions/2020088/sending-email-in-android-using-javamail-api-without-using-the-default-android-ap

    This will allow your user to send html files also:
    public void shareSplice(String newImagePath) {

    {
    Intent share = new Intent(Intent.ACTION_SEND); share.setType("text/html");
    Uri imageUri = Uri.fromFile(new File(newImagePath));
    share.putExtra(Intent.EXTRA_STREAM, imageUri);

    startActivity(Intent.createChooser(share, "Share File"));
    }
    }

    .. but this also allow sharing of the file to anything else that supports that MIME type.

    If you want to do it automatically, this might help steer you in the right direction too:
    http://www.jondev.net/articles/Sending_Emails_without_User_Intervention_(no_Intents)_in_Android

    Cheers,
    Glenn

    ReplyDelete
  49. hi thank you
    now i can send it to mail
    but i can only save my html file sd card but i want to save my file in internal storage

    ReplyDelete
  50. hi,
    now i need to create forum in android with php help me

    ReplyDelete
  51. Hi Raji,
    It's impossible to develop in php in android. Android development is only java and c (for use with the ndk).

    PHP is mostly used for website development and is outside of the scope of this blog.


    Cheers,
    Glenn

    ReplyDelete
  52. hi,
    But eclipse php is available. In mobile forum android is my front end and phpand mysql is my back end and also i use c2dm for sending message.How can i perform registration in c2dm

    ReplyDelete
  53. Hi Raji, you can use Eclipse to write in many languages, but that doesn't mean it has anything to do with Android.

    For c2dm please refer to one of the tutorials available like http://code.google.com/android/c2dm/
    or
    http://www.vogella.de/articles/AndroidCloudToDeviceMessaging/article.html.

    This post is specifically about creating multiple sqlite database tables, please don't post unrelated topics.

    Cheers,
    Glenn

    ReplyDelete
  54. hey guys...can someone tell me how to insert values into spinner and retrieve it as listview in some other screen,since i am new to android it would be very helpful if u can send me the complete code,you can also mail me at ashwinrao286@gmail.com..

    ReplyDelete
  55. what is the use of push file in the ddms view in the above diagram

    ReplyDelete
  56. Hi Ramalaksmi,
    'push' is used to copy files from your local computer to the file system on your emulator.

    ReplyDelete
  57. So happy i stumbled over this grate blog! it is just for android beginners like me

    ReplyDelete
  58. Hi Glenn

    How do you link the data of the the two tables when you want to display the data of both tables? And how do you filter through the data to display the data that the user wants to display?

    Cheers Ischci

    ReplyDelete
    Replies
    1. Hi Ischci,
      I'm not sure what you mean by 'filter through the data' but hopefully this will help, here's a method copied from my herb and spice app that I think might illustrate what you're looking for.
      In terms of displaying data from both tables, what you're looking for there is a sql join, in this case, an 'inner join'. Once you join the tables together like this, you use the sql 'where' statement to filter specific terms.

      The basic form is this:
      select tableA.value, tableA.value2, tableB.value3 from tableA inner joing tableB on tableA.importantLinkingValue = tableB.importantLinkingValue where someValue = whatImLookingFor

      Here's that concept in action, this method gets the related data for a combination of a herb and a mealstyle which are passed in as parameters. I'm actually joining 5 tables together here:

      public Cursor getHerbsAndSpicesForThisMealStyleAndIngredient(int mealStyleID, int ingredientID)
      {
      StringBuilder sbSql = new StringBuilder();
      sbSql.append("select herb._id, herb.Name, herb.imageName, mainIngredientName, mainIngredient._id as mainIngredientID, mealstyle.Name as mealStyle, mealStyle._id as mealStyleID");
      sbSql.append(" from herb inner join herb_mainIngredient on herb._id = herb_mainIngredient.Herb_id");
      sbSql.append(" inner join mainIngredient on herb_mainIngredient.mainIngredient_ID = mainIngredient._id");
      sbSql.append(" inner join herb_mealstyle on herb._id = herb_mealstyle.herbID");
      sbSql.append(" inner join mealstyle on herb_mealstyle.mealstyleID = mealstyle._id");
      sbSql.append(" where mainIngredient._id = ");
      sbSql.append(ingredientID);
      sbSql.append(" AND mealStyle._id = ");
      sbSql.append(mealStyleID);
      sbSql.append(" order by herb.Name ");

      return helpAndSpiceDb.rawQuery(sbSql.toString(), null);
      }


      Hope that helps in some way.

      Glenn

      Delete
  59. wow!!nice representation.thanks sir!

    ReplyDelete
  60. hi,
    what I need to wright insted of DOUBLE un that line:

    db.execSQL("CREATE TABLE products" +
    "(name TEXT NOT NULL, prise DOUBLE NOT NULL)");
    db.execSQL("INSERT INTO products VALUES('meat',35.5)");

    ReplyDelete