Using your own SQLite database in Android applications

Android Dev.Most all of the Android examples and tutorials out there assume you want to create and populate your database at runtime and not to use and access an independent, preloaded database with your Android application.

The method I'm going to show you takes your own SQLite database file from the "assets" folder and copies into the system database path of your application so the SQLiteDatabase API can open and access it normally.

1. Preparing the SQLite database file.

Assuming you already have your sqlite database created, we need to do some modifications to it.
If you don't have a sqlite manager I recommend you to download the opensource SQLite Database Browser available for Win/Linux/Mac.

Open your database and add a new table called "android_metadata", you can execute the following SQL statement to do it:

  1. CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')

Now insert a single row with the text 'en_US' in the "android_metadata" table:

  1. INSERT INTO "android_metadata" VALUES ('en_US')

Then, it is necessary to rename the primary id field of your tables to "_id" so Android will know where to bind the id field of your tables.
You can easily do this with SQLite Database Browser by pressing the edit table button Edit Table, then selecting the table you want to edit and finally selecting the field you want to rename.

After renaming the id field of all your data tables to "_id" and adding the "android_metadata" table, your database it's ready to be used in your Android application.

Modified database

Modified database

Note: in this image we see the tables "Categories" and "Content" with the id field renamed to "_id" and the just added table "android_metadata".

2. Copying, opening and accessing your database in your Android application.

Now just put your database file in the "assets" folder of your project and create a Database Helper class by extending the SQLiteOpenHelper class from the "android.database.sqlite" package.

Make your DataBaseHelper class look like this:

  1. public class DataBaseHelper extends SQLiteOpenHelper{
  2.  
  3. //The Android's default system path of your application database.
  4. private static String DB_PATH = "/data/data/YOUR_PACKAGE/databases/";
  5.  
  6. private static String DB_NAME = "myDBName";
  7.  
  8. private SQLiteDatabase myDataBase;
  9.  
  10. private final Context myContext;
  11.  
  12. /**
  13.   * Constructor
  14.   * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
  15.   * @param context
  16.   */
  17. public DataBaseHelper(Context context) {
  18.  
  19. super(context, DB_NAME, null, 1);
  20. this.myContext = context;
  21. }
  22.  
  23. /**
  24.   * Creates a empty database on the system and rewrites it with your own database.
  25.   * */
  26. public void createDataBase() throws IOException{
  27.  
  28. boolean dbExist = checkDataBase();
  29.  
  30. if(dbExist){
  31. //do nothing - database already exist
  32. }else{
  33.  
  34. //By calling this method and empty database will be created into the default system path
  35. //of your application so we are gonna be able to overwrite that database with our database.
  36. this.getReadableDatabase();
  37.  
  38. try {
  39.  
  40. copyDataBase();
  41.  
  42. } catch (IOException e) {
  43.  
  44. throw new Error("Error copying database");
  45.  
  46. }
  47. }
  48.  
  49. }
  50.  
  51. /**
  52.   * Check if the database already exist to avoid re-copying the file each time you open the application.
  53.   * @return true if it exists, false if it doesn't
  54.   */
  55. private boolean checkDataBase(){
  56.  
  57. SQLiteDatabase checkDB = null;
  58.  
  59. try{
  60. String myPath = DB_PATH + DB_NAME;
  61. checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
  62.  
  63. }catch(SQLiteException e){
  64.  
  65. //database does't exist yet.
  66.  
  67. }
  68.  
  69. if(checkDB != null){
  70.  
  71. checkDB.close();
  72.  
  73. }
  74.  
  75. return checkDB != null ? true : false;
  76. }
  77.  
  78. /**
  79.   * Copies your database from your local assets-folder to the just created empty database in the
  80.   * system folder, from where it can be accessed and handled.
  81.   * This is done by transfering bytestream.
  82.   * */
  83. private void copyDataBase() throws IOException{
  84.  
  85. //Open your local db as the input stream
  86. InputStream myInput = myContext.getAssets().open(DB_NAME);
  87.  
  88. // Path to the just created empty db
  89. String outFileName = DB_PATH + DB_NAME;
  90.  
  91. //Open the empty db as the output stream
  92. OutputStream myOutput = new FileOutputStream(outFileName);
  93.  
  94. //transfer bytes from the inputfile to the outputfile
  95. byte[] buffer = new byte[1024];
  96. int length;
  97. while ((length = myInput.read(buffer))>0){
  98. myOutput.write(buffer, 0, length);
  99. }
  100.  
  101. //Close the streams
  102. myOutput.flush();
  103. myOutput.close();
  104. myInput.close();
  105.  
  106. }
  107.  
  108. public void openDataBase() throws SQLException{
  109.  
  110. //Open the database
  111. String myPath = DB_PATH + DB_NAME;
  112. myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
  113.  
  114. }
  115.  
  116. @Override
  117. public synchronized void close() {
  118.  
  119. if(myDataBase != null)
  120. myDataBase.close();
  121.  
  122. super.close();
  123.  
  124. }
  125.  
  126. @Override
  127. public void onCreate(SQLiteDatabase db) {
  128.  
  129. }
  130.  
  131. @Override
  132. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  133.  
  134. }
  135.  
  136. // Add your public helper methods to access and get content from the database.
  137. // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
  138. // to you to create adapters for your views.
  139.  
  140. }

That's it.
Now you can create a new instance of this DataBaseHelper class and call the createDataBase() and openDataBase() methods. Remember to change the "YOUR_PACKAGE" to your application package namespace (i.e: com.examplename.myapp) in the DB_PATH string.

  1. ...
  2.  
  3. DataBaseHelper myDbHelper = new DataBaseHelper();
  4. myDbHelper = new DataBaseHelper(this);
  5.  
  6. try {
  7.  
  8. myDbHelper.createDataBase();
  9.  
  10. } catch (IOException ioe) {
  11.  
  12. throw new Error("Unable to create database");
  13.  
  14. }
  15.  
  16. try {
  17.  
  18. myDbHelper.openDataBase();
  19.  
  20. }catch(SQLException sqle){
  21.  
  22. throw sqle;
  23.  
  24. }
  25.  
  26. ...

This article has also been translated into Serbo-Croatian by Anja Skrba from Webhostinggeeks.com.

764 Comments

  1. Todd Cullen todd | March 3rd, 2009

    Solid writeup. We should take a look at modifying a lightweight ORM to work on Android. Something similar to iBATIS (http://ibatis.apache.org) with special hooks to setup and upgrade the database.

  2. Will | March 7th, 2009

    Wow, much better then reading and executing 7000+ inserts. Brought an 18 second operation down to about 2 seconds.

    Please note that on Firefox 3.0.7 on Ubuntu 8.10 the greater than sign in the while loop shows up as its html code (& g t ;)

  3. burton miller | March 8th, 2009

    Well presented. But not a good solution for large databases (fine for small ones).

    This DOUBLES the footprint of your database. A better solution for any sizeable database, is to download the database when the app is first run, as a secondary installation.

    This, at least, only eats up X precious megabytes once.

    Still suboptimal is the fact that the database must reside in /data/data/YOUR_PACKAGE/databases – because anything bulky should be on the sd card. I guess google will eventually let us access databases in other locations, or install our apps directly on the SD card.

  4. Justin Jaynes | March 8th, 2009

    There is an HTML error in the code preventing a Greater Than sign from appearing and the symbols %gt; instead. It is in the while loop of the copyDataBase method.

  5. Juan-Manuel Fluxà fluxa | March 8th, 2009

    HTML error in the code fixed, thanks for the feedback.

  6. David | March 10th, 2009

    This is exactly what I have been looking for.. But I am having an issue. I keep getting a failed to open the database errors. “sqlite3_open_v2(“/data/data/com.testapp/databases/database.db”, &handle, 1, NULL) failed”. I have tried this on both .db and .db3 files and neither of them are working. I followed you tutorial and placed the db in the assets directory, both in a folder called databases and just in the directory. Is there something else I am doing wrong?

  7. David | March 10th, 2009

    I just realized the issue is not the loading of the database it is actually the first time it is read. Your tutorial is working properly thanks.

  8. Will | March 12th, 2009

    I came across one issue, based on the lack of complaints I think it’s fairly unique. The first SELECT I ran on a table in the copied DB crashed with Android claiming the table did not exist. I checked through adb and sqlite3 and the table *did* exist. Eventually I tried CREATEing the table just before I SELECTed from it (since it allegedly didn’t exist). Android’s response was that the database file was corrupt. The solution was to programmatically open the database like normal, immediatly close it, than open it again. Prior to that I did try copying a completely closed database to /assets; the first SELECT still crashed.

    Here’s the first error for searchability:
    03-12 01:17:22.810: ERROR/AndroidRuntime(512): Caused by: android.database.sqlite.SQLiteException: no such table: tblMyTable: , while compiling: SELECT …

  9. Zek | March 31st, 2009

    To Will :

    Try to add :
    mOpenHelper.close();

    Just before – SQLiteDatabase db = mOpenHelper.getReadableDatabase(); – in query function of your provider.

    That’s solve the same problem for me.

  10. George F. | April 29th, 2009

    For me I get an ioexception thrown on the first
    read statement:

    while ((length = myInput.read(buffer))>0){

    Everything seems to get opened up just fine. I’ve named
    the db ls.db in the assets folder. Should it be in the assets
    folder per se or under some subdir? Any suggestions on how
    to debug the failed read?

    Thanks much

  11. Juan-Manuel Fluxà fluxa | April 30th, 2009

    George,

    Better if you get rid of the extension of your database file ( just “ls” ).

    Your database file in the assets folder is right, then:

    InputStream myInput = myContext.getAssets().open(“ls”);

    it should open your database file, put a break point after this line to see if it’s working.

    Also be sure that the path to your system application folder is right.

    /data/data/your.package.name/databases/

    then your output path should be

    /data/data/your.package.name/databases/ls

    good luck

  12. Hamy | May 13th, 2009

    Thanks for being the first post I have been able to find using multiple tables!

    If you have time for another writeup, or an addition, it would be really useful to see how you are grabbing the data from a multi-table database. Using Cursors is obviously much harder than it is on one table, and I have not really found any help on a recommended way to do that.

    Thanks again!
    Hamy

  13. Wilson L. | May 21st, 2009

    Excelent post! Congratz!

    George F., I was wondering if you have solved your problem reading the assets/your_db_name. I’m facing the same problem now, and I don’t know what is going on, because I can read it in terminal with sqlite command. Also I’m proceeding exactly as fluxa said.

    Thanks in advance!

  14. Juan-Manuel Fluxà fluxa | May 21st, 2009

    Wilson:

    Check you database file size, there is a limitation around 1.2 Mb for files in the asset folder.
    Wilson splitted up his db using Unix split command, added them in the res/raw folder, and then opened them up at install time to read. Then proceeded to splice them back together into the db and all worked fine.
    Thanks George for this.

  15. Wilson L. | May 22nd, 2009

    Thank you very much fluxa!

    It was indeed the file size.. I was able to redesign the database, actually the problem was helpful somehow, because I cleaned the tables (the overall schema).

    I’m dealing with a large amount of data, at first I was going to set up a web service to retrieve the info online, but my tutor didn’t like the idea of using internet… so… Ok, let’s try to downoad a little piece of internet and store it in a sqlite database. Seems absurd (and it is), but he was very reluctant :/

    Thank you again and Thanks George for a clever solution!

  16. BGH | June 29th, 2009

    Thanks for the tut, this is exactly what I need. I’m having a problem though

    When it starts it recognises that there is no db and creates the empty db fine. Then in the copyDataBase method when it gets the the line

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    I get the error

    06-30 00:01:35.901: INFO/WTF(29581): java.io.FileNotFoundException: /data/data/bgh.com.spanishflashcards/databases/spanish

    The path is correct. It has to be because it used the same path when creating the blank db. The blank db definitely exists. If I pause the code before this step I can see the blank db in DDMS.

    Any reason why I would get that error?

  17. Rams | July 9th, 2009

    THanks For the tutorial…

    am also getting the same error like BGH says…have any reason…

    BGH:Did u find the solution?

  18. Tzur | July 11th, 2009

    Hi,

    thanks for addressing this important issue. Many apps need this flow.
    one question,
    what if my table just doesn’t have a column name “id”, what happens in this case?
    here’s a sample schema of my table,
    .schema OPR_CHEMISTRY_TAB
    CREATE TABLE OPR_CHEMISTRY_TAB (ENTRY_ID text, INDEX_LETTER text, INDEX_ORD int, ENAME text, DESCR text, HAS_IMAGES int);
    CREATE INDEX ENTRY_ENTRY_ID_IX on OPR_CHEMISTRY_TAB(ENTRY_ID asc);
    CREATE INDEX ENTRY_ENTRY_LETTER_ORD_IX on OPR_CHEMISTRY_TAB(INDEX_LETTER asc, INDEX_ORD asc);

    what does the native android table structure expects in this case?
    thanx!
    –tzurs

  19. dennie | August 13th, 2009

    Hi, I just want to know how can I delete the table after I created it?

  20. Jimmy | September 10th, 2009

    Is the android_metadata table necessary?

  21. Juan-Manuel Fluxà fluxa | September 10th, 2009

    Hi Jimmy, yes it is.

  22. p6majo | September 19th, 2009

    Hi,

    would it be possible to have a database that is kept on the sdcard?
    I would like to have access to my database with other applications as well and I cannot access it, when it is stored in the /data/data/… directories. I’m not a superuser on my phone and I’m a little bit scared of loosing all my data when doing this goldcard business to become a su.

    Therefore, I’m looking for a possibility of using the sql database in my applications but storing it’s data on the sdcard.
    Will it be possible?

    Johannes.

  23. minhbu | October 2nd, 2009

    Thanks for the tut, this is exactly what I need. But I having a problem when I try to store file Image. I try to use some sqlite manager but not work. Please! Help me to solve this problem.
    Anyway, thanks a lot.

  24. Serg Podtynnyi | October 3rd, 2009

    I had problems with this code, it was not properly copied file into internal storage.
    A added
    this.close();
    before copyDataBase();
    and it works like charm.

  25. Daniel Lew | October 16th, 2009

    @BGH:
    This is a little late now, but I had the same problem, but I figured out the problem.

    The issue comes up when this db is the FIRST db you try to create. If that’s the case, then data/data/YOURPACKAGE/databases doesn’t exist yet. You have to create the /databases directory or the method will fail. It’s pretty easy though:

    File f = new File(DB_PATH);
    if (!f.exists()) {
    f.mkdir();
    }

  26. cousinHub | November 16th, 2009

    There is also a table called “sqlite_sequence”
    in a Android SQLite DB…

    This table has 2 columns : name and seq.

    Do we need to take care of it ?

    If I open the DB with:

    public void openDataBase() throws SQLException{

    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    instead of using (OPEN_READONLY):

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

    to be able to add records in the DB.

    I then noticed that “seq” within the table “sqlite_sequence” is not changing,
    even as I add or delete records successfully from the DB ???

    I viewed this by doing a “pull a file from the device” within the DDMS view /data/data/PACKAGE_NAME/databases/
    (and then opening this file with SQLite Browser.

    Is that normal/ ok ?

    _id is set to INTEGER PRIMARY KEY (works fine to add records)
    but should I also take care of “seq” an increase it by 1,
    everytime a record is added to the DB ???

    Txs for any advice on this.

  27. deep | November 20th, 2009

    hi,
    I have more then 1.5 MB of DB so can anybody tell me how to connect the DB with the android because i cant put my DB in assests folder(Assets can take only upto 1.2 MB of DB).

  28. David Weaver | November 21st, 2009

    Hello,

    The database apparently loads perfectly but when I come to try and run a query I get the message:

    11-17 20:54:03.126: ERROR/AndroidRuntime(749): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.allergycookbook/com.allergycookbook.AllergyCookBook}: android.database.sqlite.SQLiteException: no such table: recipes: , while compiling: SELECT _id, recipe_name FROM recipes

    I double checked and the table exists in the database (as does the field). I think it might be the same problem that Will posted above, but I tried to close and then open the database and it didn’t work.

    Perhaps it’s my shaky knowledge of SQL to blame? The code I am using to run the query was cribbed from the Android notepad tutorial and then modified accordingly. See below:

    private void fillData()
    {
    // Get all of the rows from the database and create the item list
    mNotesCursor = myDbHelper.fetchAllNotes();
    startManagingCursor(mNotesCursor);

    // Create an array to specify the fields we want to display in the list (only TITLE)
    String[] from = new String[]{DataBaseHelper.KEY_TITLE};

    // and an array of the fields we want to bind those fields to (in this case just text1)
    int[] to = new int[]{R.id.text1};

    // Now create a simple cursor adapter and set it to display
    SimpleCursorAdapter notes =
    new SimpleCursorAdapter(this, R.layout.recipe_row, mNotesCursor, from, to);
    setListAdapter(notes);
    }

    If anyone can help me I’d be most grateful.

  29. Alocaly | November 21st, 2009

    Hi Deep,

    Actually I had this issue too. The anwser is simple : cut your big database file as several sub 1 Mo files !
    I gave some explanations on this subject here :
    http://androidblogger.blogspot.com/2009/05/how-to-ship-application-with-pre-baked.html

    Hope it helps !

  30. Deep | November 26th, 2009

    Hi Alocaly,

    Thanks for your help but the issue is that my DB is in SQLite manager so how to cut SQLite DB so that we can able to connect it to our Android application. Please help me out of this problem.

    Thanks
    Deep

  31. Pieter Bonne | December 3rd, 2009

    Thank you for this article! I succesfully use an embedded an sqlite database in my application, but at first I had many issues with android not wanting to open the database. I tried using most of the available opensource sqlite editors but they all seemed to produce incompatible sqlite files. In the end I ended up building up my database programmatically using a jdbc sqlite driver (http://www.zentus.com/sqlitejdbc) which seems to produces files android has no problem with!

  32. Poson | December 8th, 2009

    The DB is not found… I have met the same problem too.Mybe we should think about changing permissions of the DB file. But how to do it in code?

    Please help me out of the problem.

  33. Alocaly | December 12th, 2009

    For Deep :
    In my blog article, I explained how I cut my database, and remerge it at the first launch of the game !
    http://androidblogger.blogspot.com/2009/05/how-to-ship-application-with-pre-baked.html

  34. Michael | December 21st, 2009

    Hi Everyone,

    I have the same error in the logcat as David… “sqlite3_open_v2(”/data/data/com.testapp/databases/database.db”, &handle, 1, NULL) failed”.. I have the method running on the initial activity that runs in my app and it force closes. I close, then open the app up and no error. I can’t figure out why I’m crashing only on the initial run…

    Any help would be great!

  35. rabbit | December 28th, 2009

    Hi everyone,

    i have follow the article, but i got some error message shows : java.lang.IlleagalStateException: database not open
    but i did open the database before i access it.
    anyone can help me ???
    thanks .

  36. jim | January 20th, 2010

    I found a simplier way.
    Just copy a good one and start with that.
    Details:

    1. Run the example called Events1. Its a bunch of example code (my Bible) from The Pragmatic Bookshelf.
    2. From eclipse->DDMS, get the file in data/data/Events/databases/events.db
    3. eclipse->DDMS->top corner, Click on “Pull a file from the device”, put it on your windows desktop, then click on “Push a file into the device”.

  37. Mark Sherman | January 26th, 2010

    Thanks for this post. It really helped us with our project.

    Mark

  38. vantan | January 26th, 2010

    Hi all !
    I have got a file /sdcard/data.db .How to insert record into file data.db

  39. Joakim Lodén | January 28th, 2010

    In order to waste less space, you should first compress your database file.
    Then just reaplce:

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    with

    //Open your local db as the input stream
    GZIPInputStream myInput = new GZIPInputStream( myContext.getAssets().open(DB_NAME) );

    for gzip or if you use zip

    //Open your local db as the input stream
    ZipInputStream myInput = new ZipInputStream( myContext.getAssets().open(DB_NAME) );
    myInput.getNextEntry();

  40. Joakim Lodén | January 28th, 2010

    My previous post on compressing the database to save storage space will not do much, since the apk is compressed anyway.
    But you can still use this method as a convenient method to shrink the file under the 1MB limit (or you can just split the file in 1MB chunks and put it together when copying).

    Cheers!

  41. Achie | February 2nd, 2010

    Hello,

    It is a nice and a very helpful tutorial. Thank you.

    I have seen a couple of replies here which say that we can split large database files and then join them on the device. I need to implement it since my database size after compression is around 3MB.

    Can some one also let me know how to split and join large files?
    Thank you.

  42. darkdusky | February 3rd, 2010

    Hi, I’ve followed the tutorial but cannot get it working. It crashes in the method copyDataBase(), on the line:
    OutputStream myOutput = new FileOutputStream(outFileName);

    I tried the 2 suggestions of checking directory exists (if (!f.exists()) {f.mkdir();}) and this.close();. But neither worked. How do I check the error logs in emulator? This is my first Android app, so sorry for basic question.

  43. darkdusky | February 3rd, 2010

    RE: size of db file – I thought this link might be useful:
    http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html

    If the db file contains any fragmentation the easiest way is to copy its contents to a new file (data is copied into a new unfragmented db)
    (DOS / Win prompt)
    > echo .dump | sqlite file1.db > file1.sql
    > sqlite file2.db sqlite file1.db .dump > file1.sql
    > sqlite file2.db < file1.sql

  44. darkdusky | February 3rd, 2010

    Found how to read log “adb logcat >C:templog.txt”
    The error during copydatabase is:
    W/System.err( 704): Can’t dispatch DDM chunk 4d505251: no handler defined

    E/Database( 704): sqlite3_open_v2(“/data/data/MyApp/databases/main”, &handle, 1, NULL) failed

    Using Google I found 4d505251 is hex for MPRQ, or Method PRofiling Query. But still have no solution.

  45. Mike Stubber | February 9th, 2010

    Hi, thank you very much for this great tutorial!

  46. sephy | February 11th, 2010

    Thx for the tuto, but is there another way than with adb shell to see the folder containing the database with the emulator?
    Because, I can’t find it on disk…and to use SQLite browser i need to specifit the place of the folder…
    Help plz

  47. Dman | February 15th, 2010

    I have some Newb questions:

    The second half of the tutorial says declare the new helper like this:

    DataBaseHelper myDbHelper = new DataBaseHelper();

    However the error I get is that there is no constructor that takes no arguments.

    So then I create my object all in one line:

    DataBaseHelper myDbHelper = new DataBaseHelper(this);

    Then I try to invoke this function:

    myDbHelper.createDataBase();

    And it says:

    Syntax error on token “createDataBase”, Identifier expected after this token

    What am I doing wrong?

    Thanks!
    Dman

  48. Mikey | February 23rd, 2010

    Just so that you know, god kills a kitten every time you write code such as this:

    return checkDB != null ? true : false;

    All you actually need to write is this:

    return checkDB != null;

  49. Barhoumi | March 6th, 2010

    Hi!!
    i’ve followed all the stapes you’ve indicated
    i’ve created a database baseSQLite.db contain’ 3 tables and the meta one
    i’ve COPIED the code of dataHELPER in a class called BDAccess
    i’ve created an activity containin’ a textViw label that will indicate the establishement of connexion to the database or the error msg
    i’ve placed the database file into the asset folder
    changed the name of DB_NAME (without puttin the extension .db”
    in the activity i’ve created an instance of the BDClass , in one line cause i’ve not a constructor that accept no arguments
    but i got an error while launchin, the app is not respondin’ and i’ve got to force it to close

    i’ve checked the /DATA/DATA…. it contains the database but i don’t know if it’s empty (have you an idea how to check this)???

    what shall i do
    and THANKS!!!!!!

  50. dalf | March 21st, 2010

    Hi,
    I got the same kind of error “android.database.sqlite.SQLiteException: no such table: recipes: , while compiling: SELECT _id, recipe_name FROM recipes”
    When debugging, I could see that “checkDataBase()” return true when running the application for the first time.
    Or it should return false!!

  51. Benjamin Orchard | March 24th, 2010

    I am trying to implement your method above, and it gives me a serious error: When I try to access the activity that calls the DataBaseHelper class, it tells me the app has stopped unexpectedly and crashes.

    Here is my code that I used in DataBaseHelper

    package com.TBOM;

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

    import android.content.Context;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteException;
    import android.database.sqlite.SQLiteOpenHelper;

    public class DataBaseHelper extends SQLiteOpenHelper{

    private static String DB_PATH = “/data/data/TBOM/databases”;
    private static String DB_NAME = “BOM”;
    private static SQLiteDatabase myDB;
    private final Context myContext;

    public DataBaseHelper(Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext=context;

    }

    public void createDataBase() throws IOException{
    boolean dbExist = checkDB();
    if(dbExist){

    }else{
    this.getReadableDatabase();
    try{
    copyDB();
    }catch (IOException e) {
    throw new Error(“Database could not be copied”);
    }
    }
    }

    private boolean checkDB(){
    SQLiteDatabase checkDB = null;
    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }catch (SQLiteException e) {
    //database does not yet exist
    }
    if(checkDB != null){
    checkDB.close();
    }
    return checkDB != null ? true : false;
    }

    private void copyDB() throws IOException {

    //open local as input stream
    InputStream myInputStream = myContext.getAssets().open(DB_NAME);

    //path to newly created && empty db

    String outFileName = DB_PATH + DB_NAME;

    //open empty db as output stream

    OutputStream myOutputStream = new FileOutputStream(outFileName);

    //transfer bytes

    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInputStream.read(buffer))>0){
    myOutputStream.write(buffer, 0, length);
    }

    myOutputStream.flush();
    myOutputStream.close();
    myInputStream.close();
    }

    public void openDB() throws SQLiteException{
    String myPath = DB_PATH + DB_NAME;
    myDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }

    @Override
    public synchronized void close(){
    if(myDB != null)
    myDB.close();
    super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

    }

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

    }

    }

    I am calling like such:

    public class Game extends Activity {

    @Override
    public void onCreate(Bundle Game) throws SQLException{
    super.onCreate(Game);
    setContentView(R.layout.game);
    DataBaseHelper myDbHelper = new DataBaseHelper(this);
    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDB();

    }catch(SQLException sqle){

    throw sqle;

    }

    }

    }

    ANY suggestions on this would be extremely helpful.

    Thanks.

  52. chouk | March 26th, 2010

    at this line : DataBaseHelper myDbHelper = new DataBaseHelper();
    I found an error and precisely at semicolon

  53. Ayub Malik | March 26th, 2010

    Hi, thanks for this article was useful as I was struggling to get my database up and running.

    Ayub Malik

  54. Vikram | March 31st, 2010

    I am using this and the db works fine. I am facing problems with the upgrade. i added a piece of code so that the db is set with the version #, the first time its created. Even after changing the VERSION #, onUpgrade never gets called. Any clue why ?

  55. tony ob | April 2nd, 2010

    Hi,

    I moved the testDB into the projects “assets” folder but I get “FileNotFound” on the getAssets().open(DBName) line.

    I am using the Eclipse and the Emulator. If I getAssets().list(“/”) — the file is not shown (but there are entries for “res” and “assets”) but
    getAssets().list(“/assets”) is empty.

    Any ideas would be greatly appreciated.

    tob

  56. tony ob | April 2nd, 2010

    Never mind … I found this (finally) elsewhere …

    To get an ASSETS folder into your APK:

    In /nbproject/project.properties, change
    assets.dir=

    to

    assets.dir=assets
    assets.available=true

    In /nbproject/build-impl.xml, there is line in the “if=assets.available” target that reads

    that needs to be changed to

  57. Ashish | April 8th, 2010

    i m getting problem when i m trying to copy database from the assets folder…
    i got warning like “Table “Table_Name” not exist…”
    i am using eclipse IDE…
    Thnkks for help

  58. Anders | April 12th, 2010

    You can use SQLite Manager Eclipse Plugin here http://code.google.com/p/questoidsqlitemanager/

  59. iantila | April 14th, 2010

    Can you give your code ,we cannot help you winthout code

  60. khanhDQ | April 20th, 2010

    This is my code for import android database from mysql database. I’m using SQL Data Browser tool to import but it don’t work. Please help me.

    CREATE DATABASE IF NOT EXISTS andorid;
    USE andorid;

    DROP TABLE IF EXISTS `category`;
    CREATE TABLE `category` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(45) DEFAULT NULL,
    `id_province` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_category_1` (`id_province`),
    CONSTRAINT `FK_category_1` FOREIGN KEY (`id_province`) REFERENCES `province` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    )

    DROP TABLE IF EXISTS `item`;
    CREATE TABLE `item` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) DEFAULT NULL,
    `address` varchar(200) DEFAULT NULL,
    `image` varchar(45) DEFAULT NULL,
    `url` varchar(200) DEFAULT NULL,
    `tel` varchar(45) DEFAULT NULL,
    `fax` varchar(45) DEFAULT NULL,
    `id_category` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_item_1` (`id_category`),
    CONSTRAINT `FK_item_1` FOREIGN KEY (`id_category`) REFERENCES `category` (`id`)
    )

    DROP TABLE IF EXISTS `province`;
    CREATE TABLE `province` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`id`)
    )

  61. kondortek | April 20th, 2010

    Thanks, this is a really useful method, it populates the db very quickly as compared to using a bunch of sql statements. Has anybody figured out how to upgrade after using this method though? I figured out how to get the onUpgrade method to fire, but then what? If I try to just call createDataBase again it blows up. Any ideas?

  62. kondortek | April 20th, 2010

    to clarify, I’m trying to completely recreate the db from a new file in assets. What I would like to happen is, if the user already has my db, I want to delete it completely and start over with the new one.

  63. kondortek | April 20th, 2010

    ok I think I solved my own problem, just in case anybody else is wanting some direction I’ll post my changes:

    /**
    * Creates a empty database on the system and rewrites it with your own database.
    * */
    public void createDataBase() throws IOException {

    boolean dbExist = checkDataBase();

    if (dbExist) {
    Log.d(TAG, “db exists”);
    // By calling this method here onUpgrade will be called on a writeable database,
    // but only if the version number has been bumped
    this.getWritableDatabase();
    }

    dbExist = checkDataBase();

    if (!dbExist) {
    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.

    this.getReadableDatabase();

    try {
    copyDataBase();

    } catch (IOException e) {
    throw new Error(“Error copying database”);
    }
    }
    }

    then in onUpgrade:

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    myContext.deleteDatabase(DB_NAME);
    }

  64. Keith | April 22nd, 2010

    When attempting to open the database, I get Database not found.

    private static String DB_PATH = “/data/data/HelloListView/databases/”;
    private static String DB_NAME = “ListDb”;

    The db is in the assets directory and is showing in the package explorer window.

    I saw above the post about changing the assets= , however I dont see in eclipse any places to make these changes. “/nbproject/project.properties” is this a Unix thing.. anyway, I cant find where to make the changes.

    If this is the solution, can someone post a bit more infor on making these updates in Eclipse (on a windows system). Ive done a google search on how to update these and come up blank.

  65. nike jordan shoes | April 23rd, 2010

    Hello everyone thanks for

    good information.

  66. kondortek | April 24th, 2010

    Keith: are you sure this path is right? private static String DB_PATH = “/data/data/HelloListView/databases/”;

    this should be /data/data//databases

    so, for example, if your package is com.example.hellolistview, the path would be “/data/data/com.example.hellolistview/databases/”

    hth,
    Evan

  67. kondortek | April 24th, 2010

    edit: should be /data/data/fullpackagename/databases

    sorry the brackets didn’t show up

  68. harry | April 24th, 2010

    I have used this code and it throws an IOException when it reaches
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    in the copyDataBase() method

    any suggestions? im stuck :(

  69. harry | April 24th, 2010

    I checked the number of bytes available with myInput.available() and it gives the correct number of bytes in the asset file. But when I call the myInput.read(buffer) it throws IOException. How to sort this out?

  70. Pablo | April 26th, 2010

    kondortek can you explain how can i trigger onupgrade methods ? And can you explain your solution ? Thanks !

  71. ulf | April 26th, 2010

    Odd, I followed the instructions but when I do my rawQuery like “SELECT title FROM titles WHERE title LIKE searchKeywords LIMIT 7″, I keep getting an error 04-26 09:39:33.408: INFO/System.out(236): android.database.sqlite.SQLiteException: no such table: titles: , while compiling: SELECT title FROM titles WHERE title LIKE hy% LIMIT 7. The primary key of table titles has been renamed _id, the dtabase is found in assets and copied nicely. What could be wrong?

  72. Martin | April 26th, 2010

    I’m facing the same problem as Ulf.
    Connecting to the database works fine as when i Log at some points it says that db is opend correctly.
    But when i start retrieving some data it gives errors back.

    This is my get all users functie that I made in the dbhelper

    public Cursor getAllUsers()
    {
    return myDataBase.query(DATABASE_TABLE_USERS, new String[] {
    KEY_ROWID,
    KEY_EMAIL,
    KEY_PASS,
    KEY_NAME},
    null,
    null,
    null,
    null,
    null);
    }

    In my mainActivity file i run this code under my db connection wich is explained in the above tutorial :

    Cursor users = myDbHelper.getAllUsers();
    users.moveToFirst();
    int gebruikers = users.getCount();

    Log.w(“TAG”,”Users”+ gebruikers);

    It already gives an error on the first line Cursor users, the error is :

    ActivityThread.PerformlaunchActivity
    Source not found.

    Anyone got a fix for this or some tips on how to get the data out of the database?

    Thanks in advance

  73. Rohit | April 27th, 2010

    Hi All,
    I want to upgrade the sqlite database to SQLite 3.6.23 to avoid a database corruption problem. How do i do this?

    Above we are copying a sqlite database file from assets to actual location, what I want to know is that how can I change the sqlite version?

    Following is the site and the extract to avoid database corruption

    http://osdir.com/ml/sqlite-users/2010-04/msg00090.html

    Statically link your application against SQLite 3.6.23 instead of
    using the SQLite 3.5.9 that is found on Android. The bug you are
    hitting was fixed in SQLite 3.6.2.

    Cheers,
    Rohit

  74. JIm | April 30th, 2010

    I’m with Harry on this one…. I get IOException on the read of the assets file. I’ve tried variations of getAassets().open and myInput.read – all to no avail. I suspect this is somewhat off topic for the primary purpose of the thread – namely copying a database – but seems to have something to do with reading assets in general from an apk. Any suggestions here?

  75. JIm | April 30th, 2010

    Well I solved it…

    The problem is that my DB is sort of largish (> 1MB). It seems that it is compressed and that causes confusion in the Android read on the InputStream. The trick is to rename your asset to a file that the packager will NOT try to compress. Renaming my db file from xxx.db to xxx.mp3 did the trick :)

    GAG!

  76. The Orz | May 1st, 2010

    Thanks Jim! That was also my problem and your solution solved it for me.

  77. Arun Pachauri | May 19th, 2010

    Hello everyone………..
    anyone can tell me how can we execute insert , select , delete & update query from own database.

  78. Dan | May 23rd, 2010

    How do you manage database version upgrades? This solution doesn’t seem to call the onUpgrade() method when the database version number changes…
    Thanks!

  79. Kiran | May 25th, 2010

    Hi,
    I am new to android,I am trying to do database apps.I followed the instructions as specified you people.I am not able to run my applications.
    I want to know where can i find the db file weather created or not.If not so,What should I do?

    package com.softforceapps.FinPlanner;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

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

    public class DataHelper extends SQLiteOpenHelper{

    private static final String TAG = null;
    private static String DB_PATH = “/data/data/myapps/databases”;
    private static String DB_NAME = “myapps”;
    private static SQLiteDatabase myDB;
    private final Context myContext;

    public DataHelper(Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext=context;
    }
    public void createDataBase() throws IOException{
    boolean dbExist = checkDB();
    if(dbExist){
    Log.d(TAG, “db exists”);
    this.getWritableDatabase();
    }else{
    this.getReadableDatabase();
    try{
    copyDB();
    }catch (IOException e) {
    throw new Error(“Database could not be copied:”);
    }
    }
    }

    private boolean checkDB(){
    SQLiteDatabase checkDB = null;
    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }catch (SQLiteException e) {
    //database does not yet exist
    }
    if(checkDB != null){
    checkDB.close();
    }
    return checkDB != null ? true : false;
    }

    private void copyDB() throws IOException {

    //open local as input stream
    InputStream myInputStream = myContext.getAssets().open(DB_NAME);

    //path to newly created && empty db
    String outFileName = DB_PATH + DB_NAME;

    //open empty db as output stream
    OutputStream myOutputStream = new FileOutputStream(outFileName);

    //transfer bytes
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInputStream.read(buffer))>0){
    myOutputStream.write(buffer, 0, length);
    }

    myOutputStream.flush();
    myOutputStream.close();
    myInputStream.close();
    }

    public void openDB() throws SQLiteException{
    String myPath = DB_PATH + DB_NAME;
    myDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }

    @Override
    public synchronized void close(){
    if(myDB != null)
    myDB.close();
    super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    myContext.deleteDatabase(DB_NAME);
    }
    }

    and i am calling this DataHelper class methods as

    DataHelper myDbHelper = new DataHelper(this);
    myDbHelper = new DataHelper(this);
    try {
    myDbHelper.createDataBase();
    } catch (IOException ioe) {
    throw new Error(“Unable to create database”);
    }
    try {
    myDbHelper.openDB();
    }catch(SQLException sqle){
    throw sqle;
    }

    I want to create a table I have to do insert/select/update tasks in my table

    CREATE TABLE preferences ( id INTEGER PRIMARY KEY, inflation NUMBER, pmntNotificationDays INTEGER, date_format INTEGER, roi NUMBER)

    INSERT INTO preferences (id,inflation,pmntNotificationDays,date_format,roi) VALUES (1,1.85,1,3,3.5)

    SELECT * FROM preferences;

    I have to do these 3 operations

    Could any help me……Please.
    TQ…

  80. petershine | May 28th, 2010

    Hi! Everyone,

    I’am trying to use a large DB file. It’s as big as >8MB
    I built it using sqlite3 in Mac OS X, inserted UTF-8 data(for I am using Korean),
    added android_meta table with ko_KR as locale, as instructed above.

    However, When I debug, it keeps showing IOException at
    length=myInput.read(buffer).

    I suspect it’s caused by trying to read a big file. If not, I have no clue why.
    I tested the same code using much smaller text file, and it worked fine.

    Can anyone help me out on this? I’ve searched many places, but no place gave me the clear answer, or good solution.
    Good meaning efficient or easy.

    I will try use BufferedInput(Output)Stream, but if the simpler one cannot work, I don’t think this will work either.

    Can anyone explain the fundamental limits in file input/output in Android, and the right way around it, possibly?
    I will really appreciate anyone’s considerate answer. Thank you.

  81. yoppy | May 29th, 2010

    @petershine

    the maximum asset file that the “InputStream” read is 1024Kb,
    so if your DB file big as >8MB it’s seem like impossible to push the DB to your APP

    CMIIW

  82. lenin | June 2nd, 2010

    @darkdusky: I had the same problems and tried all the same things as you did, but it turned out that in my case, opening FileOutputStream threw an exception because my package name (after /data/data/) was wrong in DB_PATH.

  83. mm | June 3rd, 2010

    how to call onUpgrade method and replace existing database with new version..????

  84. Lorenz | June 8th, 2010

    Hi, thanx for the tutorial. It really helps for beginners like me on how to create preloaded sqlite db.

    Keep up the good work.

  85. kumar reddy | June 8th, 2010

    hi good morning,
    i want learn android.can you seggest what are prerequisite to android and i would like to know best institute.
    thanx,

    please suggest me to learn android.
    have a good day.

  86. ganesh | June 8th, 2010

    thanks for sharing this informative post.In your post you have accessed the DataBaseHelper class by below code
    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);
    try

    myDbHelper.createDataBase();
    ….
    try
    ….
    myDbHelper.openDataBase();
    …..

    //start using to query db
    myDbHelper.query(…..);

    In few other sample programme which i came across uses code like below

    DataBaseHelper myDbHelper = new DataBaseHelper();
    SQLiteDatabase db = myDbHelper.getReadableDatabase();
    //stat using to query
    db.query(..);
    instead of calling DataBaseHelper’s createDataBase and openDataBase

    can you point out the difference between those two ,and while importing db you asked to add underscore to “id” column name ,i cannot guess the reason for that. I will be glad to know the answer .
    thanks
    ganesh

  87. Deaviato | June 11th, 2010

    can anyone site a solod example on how to import SQLite database from android to my local server..thanx

  88. Amol Pathak | June 12th, 2010

    NICE ARTICLE

  89. Desmond | June 17th, 2010

    Future classic.

  90. fred pepito | June 23rd, 2010

    This is a great post/article. The code works for me. It save me a lot of time. Thank you very much.

  91. david | June 24th, 2010

    Peter shine posted that his db was large (>8mb)

    Mine is about 5 mb.

    Yopi said:

    May 29, 2010 at 2:43 am
    @petershine

    the maximum asset file that the “InputStream” read is 1024Kb,
    so if your DB file big as >8MB it’s seem like impossible to push the DB to your APP

    CMIIW

    on the file explorer, on my /data/data/package_name/databases the file size is 3072 bytes

    Why is not the file being moved completely to the assets folder in the package?

    also:

    tony ob says:
    April 2, 2010 at 11:00 pm
    Never mind … I found this (finally) elsewhere …

    To get an ASSETS folder into your APK:

    In /nbproject/project.properties, change
    assets.dir=

    to

    assets.dir=assets
    assets.available=true

    In /nbproject/build-impl.xml, there is line in the “if=assets.available” target that reads

    that needs to be changed to

    But using exclipse i can’t see where to modify this info

    PLEASE HELP

  92. david | June 24th, 2010

    tony ob says:
    April 2, 2010 at 11:00 pm
    Never mind … I found this (finally) elsewhere …

    To get an ASSETS folder into your APK:

    In /nbproject/project.properties, change
    assets.dir=

    to

    assets.dir=assets
    assets.available=true

    In /nbproject/build-impl.xml, there is line in the “if=assets.available” target that reads

    that needs to be changed to.. (posts ends like that)

    ———————
    but i can’t see where to modify this eclipse

    Please help a poor beginner

    ~dh

  93. moon | June 25th, 2010

    Good morning everybody thank you for your help but I have a really problem I can’t open my database yet I used the same program .If there is someone who solved the problem could send me his program.
    Please help me it’s urgent

  94. androidboy7 | June 28th, 2010

    You ROCK!!! Instructions worked FLAWLESSLY!

    @dalf
    Your return statement should be the query itself. In my project, it looks like this:

    return myDataBase.query(TABLE_NAME, FROM_FREE, null, null, null, null, null);

    Don’t forget to declare your objects. In my case it looks like this:

    public final String TABLE_NAME = “museums”;

    And for the query, it looks like this:

    private static String[] FROM_FREE = {KEY_ROWID_01, KEY_MUSEUM, KEY_ADDRESS, KEY_ADMISSION};

  95. androidboy7 | June 28th, 2010

    @moon
    Send me an email at androidboy7@yahoo.com

  96. Tim | June 29th, 2010

    I keep getting the following error. Sounds like a few others are having the same issue.

    06-28 21:29:39.556: ERROR/AndroidRuntime(5701): Uncaught handler: thread main exiting due to uncaught exception
    06-28 21:29:39.556: ERROR/AndroidRuntime(5701): android.database.sqlite.SQLiteException: no such table: Wine: , while compiling: SELECT DISTINCT _id, varitial FROM Wine WHERE type=1

    I run the same query in SQLite Database Browser and it works fine.

    Database is in the assets folder.

    Any help would be greatly appreciated.

    Eclipse IDE 3.4
    Android 2.1

  97. androidboy7 | June 29th, 2010

    I think I know where you’re problem is. Here’s my code with my query. Documentation kinda blows on querying SQLite with Android. KEY_TYPE is the same as your “type”. Instead of using “like” you would use ” = “.

    Let me know if it works. If it doesn’t, send me the code snippet at androidboy7@gmail.com

    public Cursor listArt(){

    return myDataBase.query(TABLE_NAME, FROM_MANSIONS, KEY_TYPE + ” like ‘%art%'”, null, KEY_MUSEUM, null, KEY_MUSEUM);
    }

  98. mahesh | July 3rd, 2010

    To make this code work on an Upgrade, we need to call this.getWritableDatabase()

    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    this.getWritableDatabase();
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

  99. Joe Masilotti | July 6th, 2010

    Thanks SO much for this great post! Is really helping me move along with the application I have been making.

    If it helps anyone else, I got the onUpgrade to work.

    In the DataBaseHelper class:

    Add a global variable:
    private static final int DATABASE_VERSION = 1;

    Change the constructor to:
    public DataBaseHelper(Context context) {
    super(context, DB_NAME, null, DATABASE_VERSION);
    this.myContext = context;
    }

    Change the createDataBase to (thanks @kondortek):

    public void createDataBase() throws IOException {
    boolean dbExist = checkDataBase();
    if (dbExist) {
    Log.v(“DB Exists”, “db exists”);
    // By calling this method here onUpgrade will be called on a
    // writeable database, but only if the version number has been
    // bumped
    this.getWritableDatabase();
    }
    dbExist = checkDataBase();
    if (!dbExist) {
    // By calling this method and empty database will be created into
    // the default system path of your application so we are gonna be
    // able to overwrite that database with our database.
    this.getReadableDatabase();
    try {
    copyDataBase();
    } catch (IOException e) {
    throw new Error(“Error copying database”);
    }
    }
    }

    Change onUpgrade to:
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion > oldVersion)
    Log.v(“Database Upgrade”, “Database version higher than old.”);
    myContext.deleteDatabase(DB_NAME);
    }

    Now if you attach a newer version of your database and change DATABASE_VERSION, it should re-copy the database.

    Work for anyone else?

  100. harry | July 8th, 2010

    How can I put my larget DB( 5MB) into my android?

    @petershine & @david
    Do you solve this problem??

  101. Rodney | July 10th, 2010

    Hi this page is really helpful. Thank you all

  102. Karl | July 15th, 2010

    This was really helpful. Thank you.

  103. SteveC | July 18th, 2010

    Thanks for this helpful article. Perhaps I’m missing something but shouldn’t the original assets be deleted to avoid duplicate/redundant data storage utilization?

  104. jiqqaman | July 19th, 2010

    I dont see example on reading

  105. androiddev | July 20th, 2010

    Thanks for this wonderful example.This is example was very helpful to me.plz help me hw can i copy large DB(30MB) from assests folder to database folder.Above example is giving problem.plz..help me

  106. Victor | July 20th, 2010

    Thank you for your post. It was helpful so far. E encountered a problem when trying to query the DB and the application simply crashes. I only added a line at the end of your code:

    Cursor result = dbHelper.myDataBase.rawQuery(“SELECT * FROM Networks”, null);

    and it seems to crash the entire app. Everything goes well until the program reaches that line. Do I have to add any permission to the manifest file to be able to query the DB?

  107. Christina | July 24th, 2010

    I keep getting the “no such table” error when I try to perform a select, update, or insert statement against the database. I’ve read all the comments on this page regarding this error, but none have solved the problem. Any suggestions?

  108. James | July 24th, 2010

    How do you handle tables with an aggregate key?

  109. James | July 24th, 2010

    Disregard…

  110. Prerna | July 26th, 2010

    Hi When I am trying to delete the database the -id value is not resetting to 0. Hence when I add data again the values are added to the inceemented _id and Hence when I am trying to access the first row value I am not able to do so as there is no _id by that number.

  111. Nemanja | August 10th, 2010

    I had same problem with application crash when trying to operate with database.
    I think I have a solution (at least it works for me):

    at the end of createDataBase() method I put “this.close()”.

    I think this is forcing the app to reaopen (copied) database, cause without this, it’s working with empty database
    (opened before copying with this.getReadableDataBase()) and that’s why it can’t find (existing) tables.
    Hope this helps
    Cheers

  112. Zayar | August 13th, 2010

    It’s really useful.I put my database file (.sqlite) into assets folder. Umm, but i got error message: that file encrypted or not a database.
    I hope your help..

  113. SkyDiver | August 15th, 2010

    That hit the spot! Excellent article!!!

    (*) My app runs with no problems even if I don’t add the extra table or change to _id.

  114. Kenny | August 22nd, 2010

    I noticed that the methods added don’t over ride or in anyway hook into the OpenHelper. Wy don’t you hook into the onCreate() over-ride?

  115. Jake | August 30th, 2010

    Hey guys/girls,

    I was able to get this working and after reading the follow-up, thought I would post a couple things here:

    Not sure if it is the most efficient but it works without issues and was tested in HW.

    1) Reading: Get the database and set the cursor using query. This example just displays the returned database values into a listview

    SQLiteDatabase db = myDbHelper.getReadableDatabase();
    Cursor cursor = db.query(TABLE_NAME, FROM, null, null, null, null, ORDER_BY);
    startManagingCursor(cursor);

    SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.fourteener_item, cursor, FROM_OUT, TO);
    setListAdapter(adapter);
    registerForContextMenu(getListView());

    2) Updating database. I would like to keep updating this pre-loaded database with fields and thus needed a way to update the database without copying it over everytime the app loaded. I created a static int representing the database version and then checked to see if was updated:

    private static final int DATABASE_VERSION = 3;

    In checkDataBase(), I added the following lines:

    if(checkDB != null){

    if (checkDB.getVersion() != DATABASE_VERSION) {
    checkDB.execSQL(“DROP TABLE IF EXISTS ” + TABLE_NAME);
    checkDB.close();
    return false;
    } else {
    checkDB.close();
    return true;
    }

    } else {
    return false;
    }

    This way, the database doesn’t get copied over everytime. However, the updated DB will replace the current one if the version
    was updated. I tried doing this by overriding onUpgrade() but could never get it to work.

    Hope this helps someone. If there is a more efficient way to do this, I would love to hear about it.

  116. Ace | September 1st, 2010

    Hello Jake

    Could you explain how I can implement the code example you have implemented above?
    I Dont understand how you can use FROM_OUT, TO and still get the code to compile. Are these supposed to be strings?

    Does anybody know how I can query the database once I have set up as above?

  117. Jake | September 1st, 2010

    Hello Ace,

    Those are strings.

    ORDER_BY = FIELD_NAME + ” ASC”; // also a string that states how to order the list
    private static String[] FROM_OUT = { FIELD_NAME }; // this is the field name you want to display in a listview
    private static int[] TO = { R.id.title}; // this maps to the textview which corresponds to each item in the list

    Hope this helps.

    Jake

  118. gundesli | September 3rd, 2010

    To Will and David Weaver i had same error too (no such table …).The error is occuring because of you are not using the name of your database correctly.You need to include file extention in the name, for example DB_NAME = “test.db”.

  119. Michelle | September 9th, 2010

    I guess I don’t really understand databases after all. In order to just create a database using
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.database.sqlite.SQLiteStatement;
    am I supposed to have SQLite installed on my computer?

  120. Björn L | September 26th, 2010

    Thanks for a great tutrial. I really like this approach instead of dealing with actual SQL-code. I wonder though if the copy db approach is recommended when doing an upgrade of database, or it’s better to do a drop table/create table/insert into procedure then? Does anyone have any thoughts on this?

  121. ravi | September 27th, 2010

    hi can u provide sample code for retrieving the EditText data stored in DB sqlite android

  122. Max | September 29th, 2010

    Nice post, helped me some way.
    I like to work simple and lazy so the ideo of building a new private class was quite frustrating to me.
    I tried to use the static member of SQLiteDatabase openOrCreateDatabase(string path, CursorFactory)
    but could not make it work simply providing the name of the database. I guess, android tried to create it at the root :(.
    Thanks to you it occured to me that I had to provide the package database path and tried this way:
    say that I called my package app.myApp

    string DB_PATH = “/data/data/app.myApp/databases/”
    string DB_NAME = “theDatabase”
    SQLiteDatabase myDB = SQLiteDatabase.openOrCreateDatabase( DB_PATH + DB_NAME, null );

    then you can continue accessing your database with classical SQLite queries, creating a table for exemple:

    myDB.execSQL(“CREATE TABLE IF NOT EXISTS myTable” +
    ” (ID INTEGER PRIMARY KEY AUTOINCREMENT,” +
    “name TEXT)”);

    adding data to the table:

    string aName = “harry”;
    myDB.execSQL(“INSERT INTO myTable(name)” +
    “VALUES(“” + aName + “”)”);

    fetching information:

    Cursor cTemp = null;
    try{
    cTemp = myDB.rawQuery(“SELECT ID, Name FROM myTable ORDER BY ID”,null);
    }
    catch (SQLiteException exception){
    Log.e(“DatabaseAccess”, exception.getLocalizedMessage());
    return null;
    }

    the Log.e command is usefull to debug the program, it is part of the package imported with “import android.util.Log;”
    the exception is launched when there is an access issue, it is also very usefull.
    after playing with you database, you may want to close it:

    myDB.close();

    there my be some code mistakes, but I know that I would have be happy to find those informations a month before.

  123. keith | September 30th, 2010

    Max I liked your idea but I can’t get it to work. The program won’t read the database file directly from the asset folder the way you wrote things. The DB file in assets still has to be moved into the database folder of the program. I was hoping your way would work because it’s much simpler and the whole idea of using bytestreams just seems like fancy overkill, but so far I can’t find a simpler way to do this.

  124. kailash | September 30th, 2010

    great it worked on my motorola phone. thanks for help

  125. Abhishek | October 4th, 2010

    Hello Guys,

    Can anybody help me with the updatation of the Sqlite DAtabase file. I can access the file for fetching the querys. I can also get the data after inserting.
    But the problem is that the database file is not getting updated. I can see the data after inserting.

    Can any body help me with this…………

    Inserting the data with the following code….

    public void onInsertTable(String strInsert)
    {
    try
    {
    this.getWritableDatabase();
    // getWritableDatabase().execSQL(strInsert);
    sdbDatabase.execSQL(strInsert);

    }
    catch (Exception e)
    {
    e.printStackTrace();
    // TODO: handle exception
    }
    }

  126. Abhishek | October 7th, 2010

    I there anybody solve the above problem

  127. Ralf | October 9th, 2010

    In my version of the programme, the checkDataBase() always yielded “true”. I then used this instead, which works:

    private boolean checkDataBase2() {
    SQLiteDatabase checkDB = null;
    boolean isnull = false;
    try {
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null,
    SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e) {
    // database does’t exist yet.
    }
    if (checkDB != null) {
    isnull = true;
    checkDB.close();
    }
    return isnull;
    }

  128. Ralf | October 9th, 2010

    Sorry folks, the reason why checkDataBase() always yields ‘true’ was that I put

    db = myDbHelper.getReadableDatabase();
    somewhere before myDbHelper.createDataBase();

    I didn*t know that myDbHelper.getReadableDatabase() already creates an empty database (almost-empty, except the android_metadata table) of the same name.
    Anyhow it works now. Cheers.

  129. Björn L | October 11th, 2010

    Have anyone got overridden function onUpgrade() to be called when upgrading your application and increasing the database_version?

    Also, Juan-Manuel maybe you should include a database_version and not only version 1 in the constructor of the class? If you have a way to implement the onUpgrade() override, please post it. Would be highly appreciated.

  130. Txema | October 15th, 2010

    Great article!
    Why don’t you just check if the database file exists?

    private boolean checkDataBase() {
    String myPath = DB_PATH + DB_NAME;
    return new File(myPath).exists();
    }

  131. Txema | October 15th, 2010

    Another improvement.
    You can use myContext.getDatabasePath(DB_NAME) to dynamically get database path instead of hardcoded DB_PATH.

  132. Jörg | October 20th, 2010

    Hi,

    if you run into the following error:
    Caused by: java.io.IOException
    E/AndroidRuntime( 7315): at android.content.res.AssetManager.readAsset(Native Method)
    E/AndroidRuntime( 7315): at android.content.res.AssetManager.access$700(AssetManager.java:35)
    E/AndroidRuntime( 7315): at android.content.res.AssetManager$AssetInputStream.read(AssetManager.java:540)

    … then take a look at http://ponystyle.com/blog/2010/03/26/dealing-with-asset-compression-in-android-apps/

    The bottom line: just give the DB a file extension of files that won’t be compresses (e.g. *.jpg, etc.)

  133. kishore | October 21st, 2010

    Hi I tried your code, there is an error while creating instance for DataBaseHelper class and call the createDataBase() and openDataBase() methods,
    it says “The constructor DataBaseHelper() is undefined”,

    DataBaseHelper myDbHelper = new DataBaseHelper();

    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;
    }
    }
    }

    wat to do, help me……….

  134. Glenn | October 24th, 2010

    /*
    I found that Db in a lot of cases was being recopied from the assets folder every time it was accessed. This was because the copied db wasn’t updated to reflect the new (in this case hard-coded) db version.

    The way I manage the upgrade of the db is by manually changing my db version class constant, and then creating a reference to the db file after a successful copy and using ‘checkDB.setVersion(DATABASE_VERSION)’ to set the version to the new DATABASE_VERSION.

    This works for me: */

    private static final int DATABASE_VERSION = 2; //change this value manually to force recopy of db
    …..

    private void copyDB() throws IOException
    {
    //Open your local db as the input stream
    InputStream myInput = context.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0)
    {
    myOutput.write(buffer, 0, length);
    }

    SQLiteDatabase checkDB = null; //get a reference to the db..

    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

    // once the db has been copied, set the new version..
    checkDB.setVersion(DATABASE_VERSION);
    }
    catch(SQLiteException e)
    {
    //database does’t exist yet.
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
    }

    //hope that helps someone out there.

  135. Robb | October 27th, 2010

    This page comes up a lot in Android sql searches, so I wanted to add some feedback that hasn’t yet been posted. First of all, if you pass the SQLiteDatabase.NO_LOCALIZED_COLLATORS flag to the SQLDatabase.openDatabase call (in addition to OPEN_READONLY), it will avoid the need to add the Android-specific table “android_metadata” into your database. Useful if you’re trying to keep your database as platform-independent as possible. I can’t speak to whether renaming your first table field to “_id” is really necessary.

    Secondly, I was getting exceptions when stepping through copyDatabase when the InputStream was being read from the assets. Code was fine, database was fine, so I couldn’t for the life of me figure out what was wrong. It turns out that if your database is somewhere around 1 MB or larger, Android will automatically compress it if it recognizes your file extension as a text file (.db, .sql, .sqlite, for instance). The solution for me was to use an extension that the operating system won’t compress, such as mp3.

    Now everything works just fine, although it took several hours to debug. I hope this saves someone else some time!

  136. Thomas | October 27th, 2010

    If I publish an update to an app that uses a database, will the update replace the users current database? I’m not sure how the updates work in the android market, but I hope that they just “patch” the users current version of the app and don’t replace it.

  137. Toby | October 27th, 2010

    kishore, try adding ‘null’ as argument to ‘new DataBaseHelper();’ on first line i.e. ‘DataBaseHelper myDbHelper = new DataBaseHelper(null);’. Worked for me.

  138. Team Roster | October 30th, 2010

    Maybe you should make changes to the webpage name title Using your own SQLite database in Android applications | ReignDesign Blog to more better for your content you make. I enjoyed the the writing yet.

  139. Mobalick | November 1st, 2010

    Hi can anyone please send me a working exemple of this cuz i cain’t get it working there always something wrong

  140. miktr | November 3rd, 2010

    I have the same problem as ‘kishore’, I tried what ‘Toby’ suggested but it doesnt work for me.
    DataBaseHelper myDbHelper = new DataBaseHelper(null);

    I get the following error:
    Syntax error on token “;” ,{exptected after this token

  141. slee89 | November 3rd, 2010

    Hey Guys,
    I am completely new to Android development (just started yesterday). I was wondering how would you retrieve data from a database and show it in a spinner?

    Your help would be very much appreciated!

  142. soni | November 3rd, 2010

    Hi.
    When i use this code i have same problem so please tell mi below code where we put……
    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createDataBase();

    }
    catch (IOException ioe)
    {

    throw new Error(“Unable to create database”);

    }

    try
    {

    myDbHelper.openDataBase();

    }
    catch(SQLException sqle)
    {

    throw sqle;

    }

  143. sanjeev | November 4th, 2010

    thanks a lot.. :)

  144. sanjeev | November 4th, 2010

    hi soni..
    place it where u want ur database to be opened, generally its d main activity of ur app.
    n den u can simply create function in ur databasehelper class n call dem from db object

  145. Steven | November 6th, 2010

    Hi really interesting post, now i’m a noob and couden’t find an example on how to build queries base on form input anyone got an example or tutorial i could see the code and get an idea?? would be much apriciated.

  146. kane | November 10th, 2010

    Im also very interested about using a database to populate a spinner activity. can someone help out with this code. It’s so hard to find information about using your own database in the app as opposed to creating the database in the app.

    So when you say
    ————-
    2. Copying, opening and accessing your database in your Android application.

    Now just put your database file in the “assets” folder of your project and create a Database Helper class by extending the SQLiteOpenHelper class from the “android.database.sqlite” package.
    ————-

    what exactly do you mean by “put your database in the “assets” folder?

    Thanks

    kane

  147. miktr | November 11th, 2010

    kane: Take your database e.g myDb.db and copy it to …/workspace//assets/myDb.db
    Now you will see the database in the assets-folder in your project..

  148. Noliuz | November 16th, 2010

    very helpful. thank you

  149. kane | November 17th, 2010

    Hey all, this is a great article but it’s still not very clear for a newbie.
    Can someone take this code and improve and test on Eclipse and post the working code with all the .java and .xml code from their Eclipse. So we can understand how this database could for instance display the database info in a simple list view, or populate a spinner. This site is very popular in all forums so I think it would be nice if it actually help newbies understand what they are doing.

    I don’t know where to add this code for instance. Is it a separate class from the main oncreate() class?

    Thanks, I bet you that a lot of people would appreciate it.

  150. thefsfempire | November 22nd, 2010

    @kane I had the same problem as you. The article shows you how to transfer the database from assets to the device in /data/data but does not show you how to query the database and populate a listView. I learned how to do this by following the guide at Sai Geetha’s blog (link below). The guide shows you how to populate a listView using data retrieved from a database.

    http://saigeethamn.blogspot.com/2009/10/android-developer-tutorial-part-12.html

  151. Shwetha | November 22nd, 2010

    Hi all
    I tried executing few programs which extends Activity but am getting error has The method setListAdapter(SimpleCursorAdapter) is undefined for the type Class name

    My problem is I have an datbase in an seperate class and i want listView inside an Activity not inside ListActivity .Has my Activity has even other things to display so i dont want to display in an other screeen

    Can anyone please help with sample code

    Thanks a lot

  152. Adam Bartlone | November 24th, 2010

    Lovely just what I was searching for.

  153. Leeran | November 25th, 2010

    Great tutorial!!!!! really helped me out a lot. do you have a tutorial for reading/writing from/to said database?

  154. AswiniKumar | November 25th, 2010

    thanku

  155. albedo | November 27th, 2010

    I got error message on querying table that table does not exist although it definetely exists

  156. quelcom | November 30th, 2010

    thanks for the post.

    what is the point of..

    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);

    is it actually a typo?

    I got it working by doing DataBaseHelper myDbHelper = new DataBaseHelper(this);

    DataBaseHelper constructor expects the context anyway.

    BR,

  157. Mark | December 2nd, 2010

    I am new to Android but know database fairly well. I am trying to understand why we have to copy the SQLite DB from the assets folder. Why can’t it stay in assets?

  158. Vukasin | December 3rd, 2010

    Hi,

    I have big problem to implement you class.

    I’m receiving error in initialization process:

    ERROR/AndroidRuntime(280): java.lang.ClassCastException: com.dict2go.Dict2GO$1
    ERROR/AndroidRuntime(280): at com.dict2go.DataBaseHelper.(DataBaseHelper.java:37)
    ERROR/AndroidRuntime(280): at com.dict2go.Dict2GO$1.onClick(Dict2GO.java:64)
    ERROR/AndroidRuntime(280): at android.view.View.performClick(View.java:2408)
    ERROR/AndroidRuntime(280): at android.view.View$PerformClick.run(View.java:8816)
    ERROR/AndroidRuntime(280): at android.os.Handler.handleCallback(Handler.java:587)
    ERROR/AndroidRuntime(280): at android.os.Handler.dispatchMessage(Handler.java:92)
    ERROR/AndroidRuntime(280): at android.os.Looper.loop(Looper.java:123)
    ERROR/AndroidRuntime(280): at android.app.ActivityThread.main(ActivityThread.java:4627)

    Here is my implementation:

    public class Dict2GO extends Activity {
    private EditText text;
    public TextView textView_translation;
    public WebView WebView_translation;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    text = (EditText) findViewById(R.id.Input_word2translate);

    textView_translation = (TextView) findViewById(R.id.textView_translation);
    Button Button_translate = (Button) findViewById(R.id.Button_translate);

    Button_translate.setOnClickListener(new OnClickListener() {
    public void onClick(View v) {
    Editable inputText = text.getText();
    SQLiteDatabase db = null;
    File f = new File(“/data/data/com.dict2go/databases/dict2go.db”);

    Boolean checkIfDbExist = f.isFile();

    if (checkIfDbExist.equals(false)) {
    try {
    DataBaseHelper myDbHelper = new DataBaseHelper(this);
    myDbHelper.createDataBase();
    } catch (IOException e) {
    e.printStackTrace();
    }
    }
    }
    });

    Any Idea ?

  159. Neeraj | December 3rd, 2010

    I already created a database using SQLite and try to access it using android project.Unfortunately its not working for me.Will anybody help me for the .As I followed the tutorial an error occurred-“Error in copying Database.”.I already copied the database to assets folder.please give me clarification about the DB_path.

  160. carbi84 | December 7th, 2010

    @Neeraj:

    same for me, did you solve the problem?
    anybody else?

    any help is highly appreciated

  161. carbi84 | December 7th, 2010

    @Neeraj:

    for me the second comment on this article helped:

    http://huuah.com/using-sqlite-from-your-android-app/

    cheers

  162. Gurpreet | December 12th, 2010

    I have a sqlite database created with the required tables as mentioned in the start of this article. I am trying to create a database and then copy all the contents from database to newly created database. The same way as mentioned in this article.

    But I am having a strange problem. When I uninstall my app from android device, /data/data/myfoler also gets deleted. This is normal. That means mydatabase file doesn’t exist in android device anymore.

    When I try to run the app again on device after installing it, the following line always can open the database. Even if it didn’t exist before.

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

    Because of above problem, code doesn’t reach to copydata function.

    Has anyone faced such issue? Is there any problem with above code?

  163. Sinha | December 14th, 2010

    i want to save the data entered in an edittext, in to the database .Can anybody help me for this.

  164. void | December 15th, 2010

    Gurpreet: I have the same issue, am trying to figure it out. do you have solution yet?

  165. Rodrigo Guilfoil | December 16th, 2010

    Good post. I recently happened upon your web page and needed to convey that I have certainly relished reading your webpage posts. Anyways I will be opting-in to your feed.

  166. Chang | December 25th, 2010

    Great post! One question. How to upgrade the database schema in future versions? It looks like OnUpgrade is not called.

  167. AEHP | January 1st, 2011

    Thanks a lot.

  168. Björn L | January 7th, 2011

    I recently have experienced some troubles with this database distribution method/usage for users running Android 2.2.1. It works fine with Android 2.2 on my own device and fine on both 2.2 and 2.3 in the emulator. Is there anyone else that experienced this and found out what the problem is? I haven’t had the possibility to debug it on a 2.2.1-device yet but I’ll follow up here if when I find out what’s wrong.

  169. nirmal | January 10th, 2011

    How can we perform a comparison between the data entered through an edittext and that stored in a database,as in the case of a password checking.Can anybody help me for this.

  170. William | January 10th, 2011

    Hi,

    I have the same problem as Björn L. My application works fine but on the HTC Desire HD version 2.2.1 (1.72.405.3), the database created but the copy doesn’t work.
    It works fine with Android 1.6, 2.2 and 2.2.1 on my own device (HTC Magic) and fine on both 2.2 and 2.3 in the emulator.
    I search a solution but it is difficult without the device.
    I hope that we will find a solution.

    Best Regards

  171. Björn L | January 10th, 2011

    William: Too bad you have the same problem but good to hear that I’m not the only one. It would be really awesome if someone with a Desire HD could try to do some debugging and see if they find what’s wrong. I’ll get hold of a Desire HD hopefully this weekend and do some debugging then.

    I think I’ll post to Stackoverflow since there’s alot of skilled Android developers there that might have a clue what might be the cause of the problem.

    William and others: I’ll post the link to the Stackoverflow-post when it’s done. Drop me a line if you want to do som thinking togeather on gtalk or you want me to let you know directly if I find a solution. Cheers! b dot lindahl (at) gmail dot com

  172. andy80 | January 11th, 2011

    Hi,

    I’m an italian android application publisher.
    I have the same problem that you describe in your post. I always use this method to manage database files in my android applications.
    These applications work fine on Acer Be Touch E400 (android 2.1), Samsung GalaxyTab (Android 2.2), HTC Magic (Android 2.2.1), but not on HTC Desire HD (Android 2.2.1). I’ve received many error advices and bad comments about my application from this kind of device.
    The stack trace is the same: “android.database.sqlite.SQLiteException: no such table: mydata, while compiling: SELECT….”;
    Can you help me? I don’t try to emulate this kind of device.

    Bjorn: i wait your post on Stackoverflow. Can you put its link in this post?

    Thanks! and sorry for my english.
    Bye

  173. Björn L | January 11th, 2011

    I’ve posted a question to Stackoverflow now. I hope it’s accepted despite it’s very specific nature. Here’s the link to it as requested:
    http://stackoverflow.com/questions/4651797/database-handling-stoped-working-on-android-2-2-1-desire-hd-1-72-405-3

  174. Will | January 13th, 2011

    +1 for the Android 2.2.1 database copy not working. Desire HD users and custom 2.2.1 ROM users get the FC on my app. Any suggestions to fix appreciated. I have a relatively big populated db that I distribute this way with my app, it would be crazy to try and script the whole database!

  175. Craig Lurey | January 14th, 2011

    having similar problem. I have a lot of Android users and only HTC desire is causing problems. I believe there is a mismatch between our code and the libraries in this version of Android. It’s creating database files that cannot be read elsewhere.

  176. Mark | January 14th, 2011

    Hi,

    at first thanks for this tutorial. I tried to use the DataBaseHelper with a DB larger than 1MB and i get an error when it comes to copy the file from assets to database folder:

    ERROR/Database(3565): sqlite3_open_v2(“/data/data/XXX/databases/test.db”, &handle, 1, NULL) failed
    DEBUG/asset(3565): Data exceeds UNCOMPRESS_DATA_MAX (3780608 vs 1048576)

    Can anybody give me a hint on how to avoid that?

  177. Björn L | January 14th, 2011

    @Mark: There are different ways to solve it. Either you can name the database with a file extension (mp3 for instance) which corresponds to a already compressed file format, which will tell Android not to uncompress the file.

    It seem to work if you put the database in the raw folder as well. See this question for instace: http://stackoverflow.com/questions/2860157/load-files-bigger-than-1m-from-assets-folder

  178. hyunjungsoh | January 15th, 2011

    Wow! This has been helpful! Thank you so much!
    I’ll be sure to study this.

    ~hyunjungsoh

  179. William | January 15th, 2011

    Hi,
    I have found a solution thanks to a user of my application.

    Here the solution:

    http://www.anddev.org/networking-database-problems-f29/missing-table-in-sqlite-with-specific-version-of-desire-hd-t50364.html

    Thanks.

    Best Regards

    William

  180. Björn L | January 15th, 2011

    That doesn’t solve it for me. I’m debugging on a Desire HD right now but the error is when calling getReadableDatabase(). It render in:
    getReadableDatabase SQLiteDiskIOException disk I/O error

    Not sure why this is but I’ll continue to see if I find out what’s the problem. If anyone have an idea I should try, don’t hesitate to contact me on gtalk or mail on: b dot lindahl at gmail dot com
    or through the comments here. Cheers!

  181. William | January 16th, 2011

    Hi,

    warning your error is not the same problem.
    you : disk I/O error
    me : android.database.sqlite.SQLiteException: no such table: LISTE: , while compiling: select _id from LISTE

    Best Regards

    William

  182. nirmal | January 17th, 2011

    Hi,

    I got the following error while trying to select a record from database table.How can i solve this.thanks in advance.

    01-17 05:08:23.933: ERROR/CursorWindow(767): Bad request for field slot 0,-1. numRows = 1, numColumns = 5
    01-17 05:08:23.951: ERROR/AndroidRuntime(767): Uncaught handler: thread main exiting due to uncaught exception
    01-17 05:08:23.972: ERROR/AndroidRuntime(767): java.lang.RuntimeException: Unable to start activity : java.lang.IllegalStateException: get field slot from row 0 col -1 failed

  183. Johanovski | January 17th, 2011

    Greetings!

    Thanks for the tutorial! I’m trying to get it working in my app, but I’m facing some problems. Maybe there’s quite obvious, but I’m a newbie on Java and Android programming: where is the class “Context”? It can’t be found when I create the DataBaseHelper class, and I don’t which which package I should import…

    Thanks in advance! :)

  184. Johanovski | January 18th, 2011

    Buf, seems that there are some important errors here… I’ve coded the whole example and it seems that works well (no errors are thrown after all), but when I try to access the DB through a query it crashes as the table doesn’t exist (when in fact it does). The only table that I can access is the “android_metadata” table and it shows the default value “en_US”, when my DB has the “es_ES” value (so it proves that in fact it’s creating a new database)…

    I’ve tried to access the DB in the “checkDataBase” method and it seems that the SQLiteDatabase.openDatabase(…) method creates an empty database, so it’ll always return true as if the database really exists, when in fact it should return that the database doesn’t exist and it should be copied in a new DB…

    Any help with this?

  185. Abhinav | January 21st, 2011

    Apparently some people have been able to solve the issue of the SQLiteException by following the pointers given on this link –

    http://www.anddev.org/networking-database-problems-f29/missing-table-in-sqlite-with-specific-version-of-desire-hd-t50364.html

    I’m trying to see if it works for me !

  186. Björn L | January 21st, 2011

    For me the error occurs already when trying to create an empty database that I want’t to copy over. Each call to either
    this.getReadableDatabase();
    or
    this.getWritableDatabase();
    seem to cause an exception for me.

    Anyone else experiencing this?

  187. Nelson F | January 24th, 2011

    I was really wasting a lot of time on this problem of my table not being found. Your fix worked perfectly and there is absolutely no chance that I would have worked this out for myself, and the folks on Basic4Android (which I’m using) didn’t seem to know what was wrong.

    Thanks a million! I owe you big time.

  188. Nicolas | January 26th, 2011

    Well guys, I’ve modified this helper class a little bit. Seems to work just fine if you have or not the database created on the O.S (it’s in portuguese sorry, if anyone has trouble to understand please leave me a note that i’ll translate it ok?). Here it goes:

    package org.me.mydroidapp;

    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteException;
    import android.database.sqlite.SQLiteOpenHelper;

    public class ControleBD extends SQLiteOpenHelper {

    //Constantes
    private static final String DB_NAME = “mydroidapp”; //change this to your database name
    private static final String DB_PATH = “/data/data/org.me.mydroidapp/databases/” + DB_NAME; //change the package name to suit your needs
    private static final int DB_VERSION = 1; //leave it this way
    private static final String AGENDA_TABLE_CREATE = “CREATE TABLE agenda (id INTEGER PRIMARY KEY AUTOINCREMENT, nome TEXT);”; //an example table

    ControleBD(Context c) {
    super(c, DB_NAME, null, DB_VERSION);

    //if the database does not exists, creates it
    if(!checkDB()){
    criaDB();
    }
    }

    //Creates the database
    public void criaDB(){
    //Cria DB
    this.getWritableDatabase();
    }

    //check if the database is already created
    public boolean checkDB(){
    SQLiteDatabase db = null;
    try{
    db = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READONLY);
    db.close();
    return true;
    }catch(SQLiteException e){
    return false;
    }
    }

    //An insert method example
    public void insereNome(String nome){
    SQLiteDatabase db = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);
    db.execSQL(“INSERT INTO agenda(nome) values ( ‘”+ nome +”‘)”);
    db.close();
    }

    //A select method example
    public Cursor selectNome(){
    SQLiteDatabase db = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);
    Cursor c = db.rawQuery(“select * from agenda”, null);
    db.close();
    return c;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    //Cria as tabelas
    db.execSQL(AGENDA_TABLE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //Nothing to do
    }
    }

    Simple isn’t it? Any contribuitions?

    Cya

    Nicolas
    http://www.cacaiada.info (help me to start my forum lol)

  189. Joemarie Amparo | January 26th, 2011

    Hello,

    I had made a a DB named “dbPractice” with the table named “User” and its attributes: “_id”,”firstName”, “middleName”, and “lastName”

    I save this DB inside my /res folder of the project. My problem is I don’t know how to use/access this DB through codes.

    Is this right that I put in

    //since i put the db inside the /res folder of the project
    private static String DB_PATH = “/res/”;
    //db name
    private static String DB_NAME = “dbPractice”;

    ?
    How am i going to go after here.

    Thanks.

  190. Björn L | January 28th, 2011

    I finally seem to have got my app to work on Android 2.2.1 on Desire HD. I haven’t had the chance to debug on it personally to try out exactly what’s causing the problem.

    One thought that popped up is the hard coded path to where the database file is located.
    DB_PATH = “/data/data/YOUR_PACKAGE/databases/”;

    Isn’t it possible that this isn’t valid on Android 2.2.1 for Desire HD? I exchanged this path with:
    Environment.getDataDirectory() + “/data/YOUR_PACKAGE/databases/” + DB_NAME;

    which might be a solution to my problem. I’ll try to pin point the issue so I can clear up alot of test code I put in my code up to the point where I got it to run. I’ll post back on this thread when I had the chance to test on the actual phone myself. If anyone test this solution, please let me know if it solves it.

  191. Brad | February 2nd, 2011

    Firstly AWESOME post, this is exactly what I’m looking for, and for the complexity of the problem this solves it is such a simple solution. For those of you having issues with this code here are some “gatchas” I ran into.

    I passed the Contex object required from an activity into this class (trying to be clever), but I did so by calling the SqlLiteHelper class on instantiation of the activity. (I know that’s muddy….here’s what I mean):

    public class PlayGame extends Activity {

    private SQLHelperClass sqlHelper = new SQLHelperClass(this);

    }

    The problem is that even though ‘this’ will pass as a valid Context object, it is not properly instantiated for the purposes of the SQLiteHelper class (Sorry…I did not look into specifically why). The solution looked like this:

    public class PlayGame extends Activity {

    private SQLHelperClass sqlHelper;

    public void onCreate(Bundle savedInstanceState) {
    sqlHelper = new SQLHelperClass(this.getApplicationContext());
    }

    // …..etc
    }

    I also ran into the same issue mentioned above with the tables not being found on a droid 2, however I think my issue may have been the result of a file extension. The sqlite database manager mentioned in the tutorial will automatically add the sqlite extension to the file in the assets folder, and I stupidly thought android would just read the file anyway…I was wrong. (I know this is stupid…I’m not suggesting any one else will make the same stupid mistake but if they do I hope this saves you the 4 hours I spent figuring it out)

    Lastly, in conjunction with the previously mentioned issue, I found with debugging that I needed to delete the database that had been incorrectly created w/out the sqlite file I provided before the app would load the file I wanted it to. This is very straight forward but here is how I did that:

    public void createDataBase() throws IOException {

    boolean dbExist = checkDataBase();
    SQLiteDatabase db_read = null;

    Log.d(“DEBUG”, “Does the database exist already? ” + dbExist);

    if (dbExist) {
    // DONT!!!!!! do nothing – database already exist
    myContext.deleteDatabase(DB_NAME);
    // This will surely throw an error…but your app will no longer be
    // aware of the database and you can re-run the app and try again to get
    // the sqlite file to load.
    } else {

    ……etc

    Again, I’ll re-iterate that I am in no way suggesting anyone has made the same mistakes…but if you do I hope this helps a little.

    AND THANK YOU FOR A GREAT POST!!!!! A nice solution to a problem I did not want to solve alone :)

    Brad

  192. Richard | February 2nd, 2011

    Hi Guys

    DOES NOT WORK. Example code required please.

    I have got my database and I run it through the process as above, however, when i try to access the database it comes up with an error message saying that the source file not found.

    Can someone put some example code up of how they can use their predefined sqlite database in their android application???

    Thanks

  193. Anna | February 4th, 2011

    Hi! I am a beginner and I can’t get the implementation to work.

    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }
    THis part above should be entered in the .java file right? Or from where should I implement it? I keep getting an error on the first line, have you who have gotten this to work, figured it out another way?
    Best regards, A

  194. Connor Boade | February 6th, 2011

    Hello,

    The fore-mentioned code does in fact work. It required a couple of tweaks here and there though, I’ll try and get the changes listed by tomorrow.

  195. Ken | February 7th, 2011

    Something seems amiss. I’ve followed the tutorial accurately (copy-paste and adjust for my own case).

    I can query the “android_metadata” table, but when I try to query my own “words” table from the same file I get a table does not exist exception. The table is there, and it’s primary integer key column is called _id.

  196. Ken | February 7th, 2011

    The problem is the data from the database file only gets copied in if a database does not exist at DB_NAME+DB_PATH. However, it certainly does after checkDatabase is called beforehand; SQLiteDatabase.openDatabase(…) creates a database if one does not exist. Consequently copyDatabase() does not get called.

    A fairly immediate fix, with added care, is to just call copyDatabase() in your createDatabase() method, nothing more.

    This should help other posters I noticed who had the same problem.

    A database existing and the data being loaded already are not the same thing.

    Sorry, I (personally) found this code misleading, frustrating, and it wasted a chunk of my time.

  197. Thomas | February 9th, 2011

    Ken can you give me Ur email, I’ve this problem.

  198. Ken | February 9th, 2011

    @Thomas: The following works for me, give it a shot. (Don’t forget to adapt package names to suit you etc.) Comment here and I’ll get notified. I’ll be glad to help!

    //////////////////////////////////////////////////////////////////////////////
    package com.SpecialK.MyApp;

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

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

    public class GameDatabaseHelper extends SQLiteOpenHelper {

    private final Context mContext;

    private static String DB_PATH = “/data/data/com.SpecialK.MyApp/databases/”;
    private static String DB_NAME;

    private SQLiteDatabase myDatabase;

    public GameDatabaseHelper(Context context, String dbName) {
    super(context, dbName, null, 1);
    mContext = context;
    DB_NAME = dbName + “.sqlite”;
    try {
    createDatabase();
    openDatabase();
    } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }

    public void createDatabase() throws IOException {
    System.out.println(“creating”);
    //************Create an empty database if one doesn’t exist, and always overwrite regardless!
    if (this.checkDatabase()) {
    this.copyDatabase();
    } else {
    System.out.println(“error creating database”);
    }
    }

    public String queryString (String word) {
    String resultString = null;
    Cursor c = myDatabase.rawQuery(“SELECT * FROM words WHERE name='” + word + “‘;”, null);
    if (c != null ) {
    if (c.moveToFirst()) {
    do {
    String age = c.getString(c.getColumnIndex(“age”));
    if (resultString == null) {
    resultString = age;
    } else {
    resultString += ” ” + age;
    }
    } while (c.moveToNext());
    }
    }
    return resultString;
    }

    private boolean checkDatabase() {
    SQLiteDatabase db = null;
    try {
    String myPath = DB_PATH + DB_NAME;
    db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }
    catch (SQLiteException e) {
    //An SQLite database file does not exist at myPath
    db = null;
    db = getReadableDatabase();
    }
    if (db != null) {
    db.close();
    }
    return db != null ? true : false;
    }

    private void copyDatabase() throws IOException{
    //Open local db as the input stream
    InputStream myInput = mContext.getAssets().open(DB_NAME);
    //Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;
    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);
    //Transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }
    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
    }

    public void openDatabase() throws SQLException {
    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    }

    @Override
    public synchronized void close() {
    if (myDatabase != null) {
    myDatabase.close();
    }
    super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

    }

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

  199. Bob | February 9th, 2011

    Shouldn’t you / can you use the File class – File.exists() – to determine if the database already exists? It would seem a more logical way to do this.

  200. Ken | February 9th, 2011

    (I should add that the code I posted is not the final code I’ve used. It’s just enough to solve the problem I encountered. Please keep that in mind.)

  201. Malsy | February 11th, 2011

    Hi folks,

    I used this guide, to use an existing database on my device (HTC desire). I had a load of problems (SQLiteException:unable to open databse) on my device, but not the emulator. Solved it by surrouding the bulk of the copy database code with a try/catch.
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    try{
    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
    }
    catch (IOException e)

    Also if your databse does not copy over a new version, it’s because the checkDataBase() will check, see a databaser and return true, copy() will never be called. For a hack, throw copy() in:
    if(dbExist){
    copy()
    }

    Thats all I’ve got, good luck.

  202. Steve | February 14th, 2011

    I’m quite new to Android and I found this article very helpful because I have a number of tables with static data that I want to use with my Android app. I followed all the steps outlined and can open my database successfully but when I try to read from the first table using the following code –

    private Cursor getData() {
    try{
    SQLiteDatabase db = myDbHelper.getReadableDatabase();
    Cursor cursor = db.query(“exhibitor”, FROM, null, null, null,
    null, ORDER_BY);
    startManagingCursor(cursor);
    return cursor;
    }
    catch(SQLiteException e){
    String err = e.toString();
    return null;
    }
    }

    It throws an error saying no such table: exhibitor:. Yet when I open the database in assets using the Sqlite Database Browser it shows the table is there. What am I missing?

  203. JamesCR | February 15th, 2011

    Hi Everyone,

    I have be working on this project for hours now and i believe i have gotten every error you can get. I am not sure what i did wrong so i will explain what i did and see if anyone can spot the problem. I have a database that has 2 tables, each has the ID field “_id” . I followed the instructions on how to add the extra table. When i saved the database it had no file extension so i left it like that. I was able to load the database but it could not find my tables. It found the android table but the others said that they did not exist. Is there a solution to this?

    After that i changed the database and removed some values to make it under 1 mb. After that it was unable to open database, i tried to go back to the file that it opened and it now cant load that. The first error it says is sqlite3_open_v2(“data/data…) failed, Select locale From android_metadata failed. Can you change the database file after you have run the program with another file? Should you add the “.sqlite” extension to the file?

    I am unable to get it working at all and it seems like i am going backwards now. Any thoughts about my problem? Thanks

    James

  204. Chris | February 18th, 2011

    Great example. I have everything working except one thing. How do I update the database in the future after the app is downloaded? This should never need to be done but in case there is an error with the data I would want to include a new db file with the app update. What can I do to get the onUpgrade() method to trigger after an update to the app and is there a way to test the code with the Eclipse emulator?

  205. Mathieu | February 18th, 2011

    This use to work for me but since Gingerbread (Android 2.3 – SDK 9), it doesn’t work anymore.
    It’s still working on the emulator but not on the Nexus One running CyanogenMod-7.0.0-RC1 for example.

    I’ve read that it may be related to the WAL feature of SQLite.
    I’ve been working on this issue for a couple of hour and I can’t make it work.

  206. Jon | February 20th, 2011

    I have the same problem as JamesCR.

    please help!

    I noticed that the old database does not change the folder “/ data / data / / database”.

    Can some one help me update the database?

  207. Chris | February 21st, 2011

    I am having the same problems on devices and ROMS with WAL feature enabled, the copy simply does not work and am totally stuck ! – help!

  208. Mathieu | February 21st, 2011

    @Chris I’ve decided to ship a SQLite dump of my database with the app and deploy the database on the first start of the app.

    To create the dump file:
    > sqlite3
    > .output dump.sql
    > .dump .quit
    You may need to remove those lines from the dump:
    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;

    CREATE TABLE “android_metadata” (“locale” TEXT DEFAULT ‘en_US’);
    INSERT INTO “android_metadata” VALUES(‘en_US’);
    ..
    COMMIT;

    Then, I’ve spitted the SQL dump file in 2 files because raw/assets files larger than 1 MB are compressed during the compilation and that causes a problem on Android 1.5. You can also just rename the dump file with .mp3 or .jpg extension but the dump files wont be compressed at all and you APK will be much larger.

    I have use this method http://notes.theorbis.net/2010/02/batch-insert-to-sqlite-on-android.html to run 18000 SQL queries faster… well kinda… it takes around 30seconds on my Nexus One (Android 2.3) and a couple of minutes on my Dream/G1 (Android 1.5).

    I suggest you show a progress bar and initialize the database in an AsyncTask.

    My app is open-source, feel free to copy what you need.
    Here is the initDatabase() method:
    http://code.google.com/p/montrealtransit-for-android/source/browse/trunk/MonTransit/src/org/montrealtransit/android/provider/StmDbHelper.java?r=393#206
    Here is the progress bar and the AsyncTask:
    http://code.google.com/p/montrealtransit-for-android/source/browse/trunk/MonTransit/src/org/montrealtransit/android/activity/MainScreen.java?r=393

    Hope that helps everyone.

  209. Maria | February 21st, 2011

    hi,
    I m having the same probleme that Chris and Steves : “android.database.sqlite.SQLiteException: no such table: cutomers ….”
    Please help us…. I m totaly stuck.

    I translate the message in French,
    J’ai suivi le tutoriel ci-dessus, le déploiement de la base de données à eu lieu avec succès, mais il est impossible d’accéder aux données de l’une des tables de la base (à part celle des méta-données android_metadata).
    ça m’affiche le message suivant : “android.database.sqlite.SQLiteException: no such table: cutomers ….”

    Merci de nous apporter votre aide (s’il y a un lecteur francophone qui passe par ce tutoriel).
    Je suis bloquée…

  210. Chris | February 21st, 2011

    Fixed it :-)
    See below for new code. I also do a delete of all associated files in the “files” directory on new version upgrade simply in my “whats new” pop up code.

    —–

    public SQLiteDatabase loadDb(Context context) throws IOException,SQLiteException{
    //Close any old db handle
    if (db != null && db.isOpen()) {
    db.close();
    }
    File fileTest = context.getFileStreamPath(DATABASE_NAME);
    boolean exists = fileTest.exists();
    if(exists==false)
    {

    // The name of the database to use from the bundled assets.
    InputStream myInput = context.getAssets().open(DATABASE_NAME, Context.MODE_PRIVATE);

    // Create a file in the app’s file directory since sqlite requires a path
    // Not ideal but we will copy the file out of our bundled assets and open it
    // it in another location.
    FileOutputStream myOutput = context.openFileOutput(DATABASE_NAME, Context.MODE_PRIVATE);

    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer)) > 0) {
    myOutput.write(buffer, 0, length);
    }

    // Close the streams
    myOutput.flush();
    // Guarantee Write!
    myOutput.getFD().sync();
    myOutput.close();
    myInput.close();
    }
    // Not grab the newly written file
    File fileObj = context.getFileStreamPath(DATABASE_NAME);
    // and open the database
    return db = SQLiteDatabase.openDatabase(fileObj.getAbsolutePath(), null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
    }

  211. Siju | February 22nd, 2011

    Hai Chris,
    I’m new to android application.I try to retrive a values from Sqlite DB(using ur code).
    But still i got ‘android.database.sqlite.SQLiteException: no such table: Book.. ‘ the samr error..
    What can i do to fix this error..

  212. ankita | February 22nd, 2011

    hi chris,I got stuck with this problem. Thanks
    ur code works for me..

  213. Xiaoyong Wu | February 22nd, 2011

    @JamesCR, are you creating your sqlite db using sqlite3? I had similar issue when using sqlite2 database but things are working better after I switched to sqlite3, especially if you are generating the db with Php sqlite_?? functions instead of using the PDO version.

  214. Chris | February 22nd, 2011

    @Siju – impossible to tell without seeing your code, think you just need to spend more time debugging it.
    The above solution works in an app with over 10,000 paying users without a hitch
    I assume you have stepped through the copy, checked the file has copied, checked the file is in new directory and all paths are ok ?

    Also I found that before copy DELETE the old file, along with -wal & -sbh files

  215. LC | February 22nd, 2011

    In Froyo, the dB is probably opened using a read only connection. GingerBread uses WAL and WAL does not support read-only mode. So try and change the opening mode to read-write ; it may work

  216. Siju | February 24th, 2011

    Hai How to Sync android db with other server db..
    Is any free tool available to do sync?
    Plz guide me to complete this task.. Thanks in advance..

  217. Karan | February 25th, 2011

    Hi Chris,

    I try to retrive a values from Sqlite DB(using ur code).
    But i get ‘android.database.sqlite.SQLiteException: no such table: Tube.. ‘

    However no error obtain when the table name is android_metadata!
    Please advise what can i do to fix this error..Plz Plz help

  218. Chris | February 25th, 2011

    That means your path is incorrect, double check the path you are using against the path you can see in DDMS

  219. Chris | February 25th, 2011

    I am unsubcribing from this, the solution I have provided is working in a distro of over 20,000 users now.
    Any issues with incorporating it into your problem is for you guys to resolve.

  220. Karan | February 25th, 2011

    Thanks Chris.

    Am sorry if you annoyed. Am not saying your code is wrong. In fact it’s a great tutorial.I have been trying to solve my bug since past 5 hrs and am so desperate.

    Thanks!

  221. Siju | February 28th, 2011

    Hai karan,
    I also got this error.. but I used GetReadableDatabase() to read db this leads to problem bcoz it’s try to read the tables from the db stored in databases folder in DDMS. But we r put our database into files folder so u return the database in opendatabase method and use it for queries.. Hope it helps.

  222. Apoorv | March 1st, 2011

    Where do i have to include the DataBaseHelper class exactly ?
    Is it assets folder or is it www folder inside assets ?

    Thanks

  223. lOc | March 2nd, 2011

    Hi!

    Rhe problem is at the “no such table” error that the android system caches the SQLiteDatabase pointer. So simply swap the line:
    this.getReadableDatabase();

    to:

    this.getReadableDatabase().close();

    and then it immediately closes the db and at the next openDatabase() it isn’t use the cached one instead create a new one.

  224. Siju | March 2nd, 2011

    Hai anybody know How to Sync android emulator db with other server db..
    Is any tool available to do sync?

  225. Hades | March 5th, 2011

    Nice article, worked well. Thank you

  226. angel | March 6th, 2011

    Hi

    Nice tutorial

    need one more help from u..
    m not able to add databases table into DDMS.
    can u pls provide steps for tht??

  227. gbon | March 6th, 2011

    I have spent 1 full day on this issue, and can’t solve it. The problem should be very simple.

    – I have created a sqllite db and stored it nto assets.
    – I have copied your DataBaseHelper source, and got no errors from Eclipse
    – I have created a main.hava file and copied there the second part of your coed (the one starting with

    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);

    I get from Eclipse the error

    The constructor DataBaseHelper() is undefined

    What’s wrong?

    Gbon

  228. Manish Singla | March 6th, 2011

    1.Please check the package whether you are importing DataBaseHelper package.
    2.When you Copy paste the code of DataBaseHelper class first save it and clean the project after that it will work.

    just try thes two steps it might the help full.

  229. gbon | March 6th, 2011

    Package is OK, and I have cleaned the code in DataBaseHelper

    In Demo.kava I have the followinf code:

    package com.handergy.topsparkle2011;

    import java.io.IOException;

    import android.app.Activity;
    import android.database.SQLException;
    import android.os.Bundle;

    public class Demo extends Activity {

    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);
    try {
    myDbHelper.createDataBase();
    } catch (IOException ioe) {
    throw new Error(“Unable to create database”);
    }
    try {
    myDbHelper.openDataBase();
    }catch(SQLException sqle){
    throw sqle;
    }
    }
    }

    Unfortunately DataBaseHelper is unknown to Eclipse …

    Gbon

  230. Manish Singla | March 6th, 2011

    You have to pass context in DataBaseHelper();

    The code will be:-
    setContentView(R.layout.main);
    DataBaseHelper myDbHelper = new DataBaseHelper(this);
    try {
    myDbHelper.createDataBase();
    } catch (IOException ioe) {
    throw new Error(“Unable to create database”);
    }
    try {
    myDbHelper.openDataBase();
    }catch(SQLException sqle){
    throw sqle;
    }
    }
    }

  231. Rasmus | March 7th, 2011

    We used the code from this blog to handle our import of database and we ran into the same problem as many of you describe. We found out that Android 2.1 and previous version can’t handle files of larger than 1MB, so you have to split up your DB if its larger than 1MB. Furthermore, we found out that some versions of Android can’t handle DB-imports from resources, but you have import from assets.

    That meant our copyDatabase() function looked like this:

    private void copyDataBase(Context myContext) throws IOException {
    File fileTest = myContext.getFileStreamPath(DB_NAME);
    boolean exists = fileTest.exists();
    if (!exists) {
    // Open the empty db as the output stream
    OutputStream databaseOutputStream = new FileOutputStream(DB_PATH + DB_NAME);
    InputStream databaseInputStream;

    databaseInputStream = myContext.getAssets().open(DBpart1);
    byte[] buffer = new byte[1024];
    int length;
    while ((length = databaseInputStream.read(buffer)) > 0) {
    databaseOutputStream.write(buffer);
    }
    databaseInputStream.close();

    databaseInputStream = myContext.getAssets().open(DBpart2);
    while ((length = databaseInputStream.read(buffer)) > 0) {
    databaseOutputStream.write(buffer);
    }

    // Close the streams
    databaseInputStream.close();
    databaseOutputStream.flush();
    databaseOutputStream.close();
    }
    }

  232. hassan | March 8th, 2011

    how i can retrive the data on the database ? is it like sql as select statement
    i import the database on the assets and the database have the country column i need to fetch it and put it in spinner

  233. Micile | March 11th, 2011

    Hello,

    I found this code to update my dataBase :

    In res/values/strings.xml I put this :

    1

    This integer must be incremented every time you need to update your database.

    In DataBaseHelper :

    public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();

    int version = 0;

    SQLiteDatabase checkDB = null;

    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

    }catch(SQLiteException e){

    //database does’t exist yet.

    }

    if(checkDB != null){

    version = checkDB.getVersion();

    }

    if(dbExist && myContext.getResources().getInteger(R.integer.databaseVersion) <= version){
    //do nothing – database already exist

    }else{

    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
    this.getReadableDatabase();

    try {

    copyDataBase();

    } catch (IOException e) {

    throw new Error("Error copying database");

    }

    checkDB.setVersion(myContext.getResources().getInteger(R.integer.databaseVersion));
    }

    checkDB.close();

    }

    This code checks the version of the database in the device and if it is smaller than the integer stored in strings.xml, it will proceed to a copy.

    Sorry for my english level, i'm french :)

  234. Micile | March 11th, 2011

    Sorry, the tags don’t work here so

    “In res/values/strings.xml I put this :

    1″

    Before 1 put a tag integer with name=”databaseVersion” and close the tag after

  235. Justin | March 14th, 2011

    @Mathieu – Thanks for sharing a solution to a problem that has affected my app which used this database copy. Since upgrading my N1 to 2.3 I had noticed it FC’s and won’t allow the database to copy over on the device (works fine on emu tho). Luckily not many people are using 2.3 yet!

    To anyone thinking of using the method described in the article to copy your DB over, make sure to test this on a 2.3 DEVICE to avoid future problems as more devices release with 2.3.

  236. eekld | March 23rd, 2011

    I want to say thanks for all who give any knowledge that will be useful for all. I am beginner in android programing so I have some questions, specially about sqlite database in android.

    one of the question is this: Are there any limit for sqlite in android system for number of column ?

    because I get some error when I insert more column in my own sqlite when i follow the code above …

  237. Siju | March 24th, 2011

    Hi anybody know how to create a sqlite db from xml in android?

  238. Jon | March 25th, 2011

    IF you been having a lot of errors, one thing that really helps is to pass the application context, not the activity context when calling these methods. Other than that, GREAT STUFF!!! Thanks so much!

  239. fahad | March 31st, 2011

    Can someone please confirm this works for 2.3? Comments above seem to suggest otherwise. What if the app itself was compiled for v2.2, would that still fail? Does it fail completely when trying to copy over the db? What option do we now have for SDK 2.3 and above? Any comments?

  240. Marco | March 31st, 2011

    Is there a way to make this work for Android 2.1 update 1? In this version “/data/data/YOUR_PACKAGE/databases/” is an illegal argument because it contains a path separator (dot).

  241. Rasmus | March 31st, 2011

    I have it working on 1.6, 2.0, 2.1, 2.1-r1, 2.2, 2.3 and 3.0 with the code i posted earlier. We have tested with Samsung, LG, HTC, SE phones and none FC on us so far.

  242. Fahad | March 31st, 2011

    I didn’t want to go with something that could potentially change at some point. So I instead used the NDK to compile sqlite as a library and interface it with my app. I can now ship my own database and it works great!

  243. Rama | April 1st, 2011

    Hey, I’ve successfully been able to create a database and let my app open it. I just have problems retrieving data. Honestly i just don’t know how. How do i retrieve a value from a certain column??

    Does anybody have a an example?? (code preferably)

    Thanks

  244. Engi | April 1st, 2011

    Hi Rasmus,

    When the line :
    OutputStream databaseOutputStream = new FileOutputStream(dbFilePath);
    is executed, I obtain a FileNotFoundException !
    I don’t understand : it is to create db file, so why the system tells me that the file is not found !

  245. Rama | April 1st, 2011

    I’ve been trying to use cursor but still get shit load of errors. It is not clear in this tutorial how i am supposed to get something out of my database. I have simple database name Cafes and it contains:
    id
    Name
    Latitude
    Longitude.

    All i want my app to do is to retrieve the latitude and longitude of a certain cafe and display that on my map. But this is not working because i simply cant retrieve anything from my database. Could someone plasee help me or guide me into the right direction.

    Thank you

  246. kamal | April 5th, 2011

    I want to synchronized the local database on the mobile device with the remote database in the webservice, so, can you suggest the technique.. if possible code example will be greatly appreciable…..

  247. Mitch | April 5th, 2011

    Rama,
    I’m working on a project much like yours, and have many of the same problems. Let’s email.
    mitch,

  248. Mitch | April 5th, 2011

    Rama,
    GPSvisulizer.com may do what you want done.
    Again let’s email.
    Mitch

  249. Rama | April 6th, 2011

    Mitch,

    Thanks for the link. Well it does almost what I want. I actually simply want to retrieve data from my database and use it in my MapActivity, thats it.

    But because I’ve have difficulty retrieving data ( with tis tutorial) I’m kinda stuck with my project. What exactly is your app suppose to do? Email is fine by me.

    Rama

  250. kamal | April 7th, 2011

    suppose we need to do synchronization between the mobile database and the remote database… so, anybody can help for these…

  251. Cosmo | April 7th, 2011

    I have created DB by external manager and I want to my app work on that DB. Only possible way is that I must create another DB in program and copy information between them ?? Every time when I want to change something in the first DB??

  252. kamal | April 9th, 2011

    when I create other database in assets and change the path and database name.. in the above mention code then I got the error as mention below :-

    sqlite returned: error code = 1, msg = no such table: android_metadata
    SELECT locale FROM android_metadata failed
    04-09
    Failed to setLocale() when constructing, closing the database

  253. Mitch | April 10th, 2011

    RE: Rama 1apr11,

    I’ve made good progress on my very similar project. That you use “_id” indicates that you are going to be using an auto- increment column. I don’t like auto increment columns because of what happens when ordered by or grouped by selects are mad and the results are fed into other databases. “_id” is required for content providers, at least as far as my current understanding is concerned.

    The POIfactory.com does list of Points Of Interest for GPSs. I think that they are doing some stuff for I-phone/pads. There is some info about Android GPS uses on the site.

    Best luck,

  254. Ellen | April 10th, 2011

    Thanks for the tutorial. Is there any significance to your using double-quotes in some places and single quotes in others, as in: CREATE TABLE “android_metadata” (“locale” TEXT DEFAULT ‘en_US’)

  255. Ali | April 10th, 2011

    Hi,
    thank you this such nice tutorial , but I really a newbe at android, and I need to do what you show in this tutorial, but I followed your steps But I get every time a couple of errors and app crashes every time. this is the code

    package myDB.demo;

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

    import android.app.Activity;
    import android.content.Context;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteException;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.os.Bundle;

    public class MyDB extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    public class DataBaseHelper extends SQLiteOpenHelper{

    //The Android’s default system path of your application database.
    private static String DB_PATH = “/data/data/myDBdemo/databases/”;

    private static String DB_NAME = “myDBName”;

    private SQLiteDatabase myDataBase;

    private final Context myContext;

    public DataBaseHelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase arg0) {
    // TODO Auto-generated method stub

    }

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

    }

    public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();

    if(dbExist){
    //do nothing – database already exist
    }else{

    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
    this.getReadableDatabase();

    try {

    copyDataBase();

    } catch (IOException e) {

    throw new Error(“Error copying database”);

    }
    private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){

    //database does’t exist yet.

    }

    if(checkDB != null){

    checkDB.close();

    }

    return checkDB != null ? true : false;
    }
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length
    ;

    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    @Override
    public synchronized void close() {

    if(myDataBase != null)
    myDataBase.close();

    super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }

    }
    }

    Hope you can give me hand.
    Thanks mate.

  256. Ali | April 10th, 2011

    Hi Juan-Manuel, maybe it would be easier and more helpfull for people like me that you upload an application demo with the example above of a database SQLElite and android aplicacion so we could explore all files manifest and java and layout so we could discover you so good code.
    Thank you a lot for you help.

  257. Akshay | April 12th, 2011

    Thanks for the tutorial
    I have done all the things which you have mension but i am not able to retrive or update my database.
    what should i do so that i will be able to insert the record.

  258. alikyo | April 16th, 2011

    thanks for this code but i have a problem in this specific line :

    OutputStream myOutput = new FileOutputStream(outFileName);

    error : no such file or directory

    can some one help me please?

  259. Aditya | April 18th, 2011

    Hi,
    I am getting Source Not Found Exception.How to get rid of that.Help..

  260. Ayan | April 22nd, 2011

    Just had to give you a shout-out. Thanks for the great tutorial, definitely helped my newb Android butt out today. *Exactly* what I needed (that doesn’t happen often).

  261. Ayan | April 22nd, 2011

    FYI: If you need to download your database from your Android app (only works on local emulator and possibly SD Card based debugged applications), use this tutorial: http://www.designerandroid.com/?p=54

    This tutorial + the designer android link gives you a complete solution if you need to save an existing database as an “initial” state of an application, which is what I needed to do.

    Happy coding.

  262. Sindre | April 23rd, 2011

    Hi

    It would be really helpful if you could give away the source of the project in a zip file.

    Thanks

  263. etud21 | April 24th, 2011

    thanks for help…it’s great and work very well just i want to retrieve data? how to use requet to do this?
    thanks a lot

  264. Mitch | April 25th, 2011

    Aizaz,
    One copies the database to work on it Pull the database off the Android app, fix it, add to it, whatever, THEN push the better database back on to the app?
    I’m just a novice, guessing
    Regards,,,
    CactusMitch,

  265. etud21 | April 25th, 2011

    yes, it is easier to create table and insert data with graphical editor than copy this to our application…now i want to retreive data and display them into spinner if someone have an idea please help me

  266. rika | April 26th, 2011

    hi..

    i got these messages when i run the project

    ActivityThread.performLaunchActivity(ActivityThread$ActivityRecord, Intent) line: 2585
    ActivityThread.handleLaunchActivity(ActivityThread$ActivityRecord, Intent) line: 2679
    ActivityThread.access$2300(ActivityThread, ActivityThread$ActivityRecord, Intent) line: 125
    ActivityThread$H.handleMessage(Message) line: 2033
    ActivityThread$H(Handler).dispatchMessage(Message) line: 99
    Looper.loop() line: 123
    ActivityThread.main(String[]) line: 4627
    Method.invokeNative(Object, Object[], Class, Class[], Class, int, boolean) line: not available [native method]
    Method.invoke(Object, Object…) line: 521
    ZygoteInit$MethodAndArgsCaller.run() line: 868
    ZygoteInit.main(String[]) line: 626
    NativeStart.main(String[]) line: not available [native method]

    my code:

    package com.rika;

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

    import android.content.Context;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteException;
    import android.database.sqlite.SQLiteOpenHelper;

    public class retrieve extends SQLiteOpenHelper{

    //The Android’s default system path of your application database.
    private static String DB_PATH = “/data/data/com.rika/databases/”;

    private static String DB_NAME = “databaru”;

    private SQLiteDatabase myDataBase;

    private final Context myContext;

    /**
    * Constructor
    * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
    * @param context
    */
    public retrieve(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;
    }

    /**
    * Creates a empty database on the system and rewrites it with your own database.
    * */
    public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();

    if(dbExist){
    //do nothing – database already exist
    }else{

    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
    this.getReadableDatabase();

    try {

    copyDataBase();

    } catch (IOException e) {

    throw new Error(“Error copying database”);

    }
    }

    }

    /**
    * Check if the database already exist to avoid re-copying the file each time you open the application.
    * @return true if it exists, false if it doesn’t
    */
    private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){

    //database does’t exist yet.

    }

    if(checkDB != null){

    checkDB.close();

    }

    return checkDB != null ? true : false;
    }

    /**
    * Copies your database from your local assets-folder to the just created empty database in the
    * system folder, from where it can be accessed and handled.
    * This is done by transfering bytestream.
    * */
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    @Override
    public synchronized void close() {

    if(myDataBase != null)
    myDataBase.close();

    super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // Add your public helper methods to access and get content from the database.
    // You could return cursors by doing “return myDataBase.query(….)” so it’d be easy
    // to you to create adapters for your views.

    }

    i hope someone can help me, im very new in android :(

  267. Shaun | April 27th, 2011

    I’m using this code as a base for an app I’m writing (and thank you, I couldn’t figure out the examples to create the db in code). I’m wondering if anyone has found out how to create a copy of a template table in the DB (i.e. I want to add a table that is a copy of one I have named template in the DB) and store info in that so I can have a seperate table for each item I am storing data about (I have my reasons for this).

  268. Nehal | April 27th, 2011

    Hi!
    just follow the simple steps buddy
    1. by SQLiteManager (of wamp) create database and a table within it.
    2. create a new class file within your project.
    3. and write code

    DataBaseHelper myDbHelper = new DataBaseHelper();
    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }

    within onCreate() method of your app

  269. Shaun | April 27th, 2011

    Yep,
    I have that to copy the full database. But within my database I have a table named template. I want to create a copy of that table for each vehicle I am tracking items for.

  270. rika | April 28th, 2011

    hi Nehal..

    thx there is no error anymore, but data doesn’t show up :(
    should i modify the xml file?

  271. Nehal | April 28th, 2011

    I think you should check you AndroidManifest.xml file

  272. rika | April 28th, 2011

    @nehal: i already registered all the activities.

    this is my manifest code

    or, the code doesnt provide to display the database? cos i just got the retrieve string

  273. Nehal | April 28th, 2011

    Ok Rike, you can take help from Android SDK notepad tutorial
    http://developer.android.com/resources/tutorials/notepad/index.html
    just follow exercise1,

  274. Bhavna | April 29th, 2011

    hi

    I created the DB using SQLite Browser and pushed the file in the data/data/database in DDMS.

    The problem i am facing is when i install the apk file onto the Galaxy tab device its creating a new DB and the file i pushed later on is not being used.

    On the device i cant push the DB file as i Dont know the location of the DBfile as on the device data/data folder is empty.

    I have been stuck on this issue for a long time. Please Help.

  275. Jon | April 29th, 2011

    This is actually a relatively straightforward answer though finding it was a pain in the ass. It has to do with versioning. When you push your DB onto the device the manner in which you do so it critical. Do you: A) download the entire DB or B) push the contents of the DB then update what you have. If you do A, you then need to copy over the DB to the one that is associated with the program using code such as: //Open your local db as the input stream
    AssetManager assetManager = mCtx.getAssets();
    InputStream myInput = assetManager.open(DATABASE_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DATABASE_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    where DB_PATH is “/data/data/YOURAPPPROJECT/databases/”

    If you choose B, it shouldn’t matter and you can just do inserts populating your db.

    HTH!

  276. fox88 | May 6th, 2011

    Thanks, good tutorial! It helps a lot!

  277. Naveen Shrivastva | May 9th, 2011

    Hello sir,

    Good morning,

    This code is perfect to copy the databases from Asset folder to databases folder.
    ================================================
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }
    but i have a heavy database size 2.77 MB (2,908,160 bytes). i am getting exception: java.io.IOException.Please give me idea how to handle that one.

  278. Matzk | May 14th, 2011

    I have followed the steps and coded this example with my SQLite database in the assets folder and my app works absolutely fine on Android version 2.3.3.

    However when the app is installed on a device with say Android 2.2, the app ‘Force Closes’ and as far as I have debugged it, the error happens in “public void createDataBase()”

    Why is this? Any ideas? How come code works fine on some devices and not others?

    Any help is appreciated.

    Thanks,
    M.

  279. EmaBorsa | May 16th, 2011

    Hi, does this tutorial work also if i’m using C++ classes with JNI?

  280. Mitch | May 16th, 2011

    Matzk,
    You may be ontosomething. I’ve been trying to get my progrm running, but only on the emulator using 2.2! I always seem to get the “forced close” situation. I will try the 2.3 as soon as I can.
    Cm.

  281. User | May 17th, 2011

    if my program is in visual studio(2005),then how can i compiled those program in eclipse,suggest me just a simple way

  282. User | May 17th, 2011

    this is for Android or simply i need to convert code from vs 2005 to Android code

  283. Matzk | May 18th, 2011

    @Mitch,

    Thanks for your reply! Any ideas at all? Have you tested anything?

    Looking forward to hearing from you.

    M.

  284. stevanicus | May 19th, 2011

    Yea I’m using android 2.2 and get an error on attempting to open the database.

    I check the adb shell and everthing is copied over fine. Just its telling me

    sqlite3_open_v2(“/data/data/[MY PROJECT]/databases/trivia”, &handle, 1, NULL) failed

  285. stevanicus | May 19th, 2011

    I take it back :) found out what the problem was…..

    I forgot to add the table “android_metadata”

    Oops

  286. Erik | May 20th, 2011

    The reason why your database copy is working in 2.3 and not 2.2 is because in 2.2 you can’t copy over a file larger then 1mb. it sucks. look in logcat and you’ll get a UNSUPPORTED etc size error.

  287. Mitch | May 20th, 2011

    Thanks, Erik,
    Does the size limitation apply to the APK files as well? I’ve cut the number of entries in my database way down. The java files are less that 110k. I haven’t figured how to get a db in my app as a in the “res” directory yet
    CactusMitch.

  288. Matzk | May 20th, 2011

    @Eric

    Thanks for your reply, this opens up alot of confusions! Any solutions to this at all?

    Thanks, M.

  289. Rain | May 21st, 2011

    On onCreate() method i put this code:
    try {
    mDbHelper.createDataBase();
    } catch (IOException ioe) {
    throw new Error(“Unable to create database”);
    }
    try {
    mDbHelper.openDataBase();
    } catch (SQLException e) {
    Log.e(“QuestionActivity SQL Open”, e.getMessage(), e);
    }
    populateFields();

    Then call populateFields() like that:

    private void populateFields() {

    String test = “1”;
    Cursor reminder;
    try {
    reminder = mDbHelper.fetchQuestion(Long.parseLong(test));
    startManagingCursor(reminder);
    mQuestionText.setText(reminder.getString(reminder
    .getColumnIndexOrThrow(DataBaseHelper.QUESTION)));
    } catch (SQLException e) {
    Log.e(“QuestionActivity PopulateFields”, e.getMessage(), e);
    }
    }

    But i always get force close screen and the error is this one (there is a database with my data created in the project):
    ERROR/AndroidRuntime(10997): Caused by: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1

    How can i fix that? Or if you can supply little example how to read the data from the database (i’ve been through some examples but all of them are if you created the database inside android)

  290. Rain | May 21st, 2011

    Sorry for double posting but i fix it. Hope it’ll be useful for somebody like me.

    This is the code for fetch:

    public Cursor fetchQuestion(long rowId) throws SQLException {

    Cursor mCursor =

    myDataBase.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
    QUESTION, OPTION_A, OPTION_B, OPTION_C, OPTION_D,
    ANSWER, HINT, BONUS}, KEY_ROWID + “=” + rowId,
    null, null, null, null, null);
    if (mCursor != null) {
    mCursor.moveToFirst();
    }
    return mCursor;

    }

    The error came from missing this lines:
    if (mCursor != null) {
    mCursor.moveToFirst();
    }

    Thanks for this really useful guide Juan-Manuel Fluxà

  291. nun | May 21st, 2011

    better use:
    DB_PATH = “/data/data/” + context.getApplicationInfo().packageName + “/databases/”;

  292. Noa | May 22nd, 2011

    hello,
    i was created a db file on sqllite , i dont know how to openadd it to the eclipse .

    can someone please tell me how to do it ?

    thanks a lot!

  293. Vivek Rathaur | May 23rd, 2011

    Can you please help me at my problem.
    http://stackoverflow.com/q/6094692/765665

    I’ll be very greatful to you.
    Thanks

  294. Jamen | May 25th, 2011

    Hi , thank you for the explanation in the tutorials , helped me figure out quite a fair bit and as it seems most of the questions in Stack Overflow are offered answers that links back to here. This is great.

    I’ve used the code and I have problems accessing the methods I seem to have some problems about accessing the createDatabase() and openDatabase() method of the nested class in DatabaseHelper which is nested in another class DrugsDbAdapter.

    I’m not sure if i’m doing the right way in calling the nested method within the onCreate method in my main file. Can someone help me see if i’m calling the right way below ?

    ——————————————————–

    private DrugsDbAdapter mDbHelper1;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    DrugsDbAdapter.DatabaseHelper mDbHelper1 = new DrugsDbAdapter.DatabaseHelper(this);

    try {

    mDbHelper1.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    mDbHelper1.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }

    ——————————————————–

  295. Robin | May 27th, 2011

    I keep getting a table not found exception. Ive tried looking for an answer but no luck.

    Please help,
    Thanks,
    Robin

  296. Rain | May 30th, 2011

    @Noa – drag it to assets folder in your Eclipse project and choose copy. This way you will have you DB (you can open it in eclipse but will see only characters). From there on use the class from this article.

    @Robin – if you get table not found, it means that there is no such table. Probaby something went wrong in your DB copying. Use DDMS and copy the database of your app from there and look it through SQLite Database Manager. I bet it’s empty. So go around and look at your code also delete it from DDMS and run the app again, this will create it again, hopefully correct this time (or create method for upgrading your DB).

    @Jamen – i also tried with nested classes but went messy as well (that’s what i read in one book but there i creater the db inside my class). Try to use only the class from this article if you can, or if someone else can help…

  297. stevanicus | May 31st, 2011

    any one know how to create this database so that it can update itself?

    I.e using the database version?

    OnUpgrade()…

    Thanks

  298. Ror_hooda | June 3rd, 2011

    Really it amazing for me…Thanks

  299. umer | June 7th, 2011

    that is really working nice

  300. Justin Copley | June 7th, 2011

    Hi,

    I am having a small problem when using the code above. I get the message that the table I want to query doesn’t exist.

    When I watch the File Explorer in the Eclipse DDMS while running my app I can see the /data/data/com.example.testform/databases/database.db file come in to existence, however if I Pull that file off, it only contains the android_metadata table, my other created tables seem to disappear.

    I am using SQLite format 3.

    thanks

  301. Karlo | June 9th, 2011

    Great blog. This code was exactly I needed first, but now I would like to add an ‘Update’ button to my app which would copy database from some host to apps database file.
    I guess I should make changes on line:
    InputStream myInput = myContext.getAssets().open(DB_NAME);
    in DataBaseHelper class.
    I tried to replace this line with:
    URL u = new URL(“http://www.something.com/database.db”);
    HttpURLConnection c = (HttpURLConnection) u.openConnection();
    c.connect();
    InputStream myInput = c.getInputStream();
    there my app stopped.
    I also changed DB_NAME properly (just to be sure I will not get too simple answers :) ).
    Can anybody solve my problem?

    Thank you.

    And sorry for my bad english :)

  302. Hikmat Khan | June 10th, 2011

    Great Work
    Thanks for Time saving.

    Regards.

  303. simon | June 10th, 2011

    Very nice.

    One point to add, it may be useful since this is typically dealing with rather large databases:

    Assets are limited to 1Mo (2^20 octets), which implies to split the db file first (the split unix command does this), and later to copy the files in sequence to the application database.

  304. Mark | June 11th, 2011

    Tried out the code and it worted like a champ! Much thanks for contributing it.

  305. Ronnie | June 12th, 2011

    Hey Super this was very helpful..keep the good work up

  306. MGK | June 12th, 2011

    Hi,

    I need an application which will update its data from a server in a regular periodic basis and in event trigger basis. The application will not keep a copy of the database in its own memory.

    If you people could help me it would be very nice of you.

    Thank You

  307. Chad | June 15th, 2011

    For the life of me I can not get this code to work. I have been trying to get this to work for over a week. When I get to this part: this.getReadableDatabase();

    try {

    copyDataBase();

    } catch (IOException e) {

    throw new Error(“Error copying database”);

    }

    it stops working.

    Anyone have any suggestions?

  308. Larrybud | June 17th, 2011

    Nice code, but a better way to get the full path of the file would be to do:

    File fdb=getDatabasePath(DATABASE_NAME);
    return fdb.getAbsolutePath();

  309. paul | June 17th, 2011

    Great article
    If you wanted to update both the app and the database an a device, how would you do this ?
    I thought that the onCreate() and onUpgrade() methods were only applicable if you had the database
    wrapped in a Content Provider ?
    Thanks very much
    Paul

  310. AVEbrahimi | June 22nd, 2011

    Thank you, worked!

  311. Jiahao | June 22nd, 2011

    @Justin Copley
    By default, SQLite creates an metadata table. It is present on all the SQLite databases. You can use the command .schema to see all the tables in the database. If your table doesn’t appear with this command, it means something went wrong and your db hasn’t been created.

  312. Tony | June 24th, 2011

    I’m having the same issue as Chad on June 15/2011…it crashes here:

    this.getReadableDatabase();

    My workaround is using DDMS to push the DB to databases folder. Works fine in emulator, but does not work on my android phone (SGS).

    Help!!!

  313. Graham | June 26th, 2011

    @Justin Copley
    Did you solve your problem? I have the same issue (the table exists and can be seen using the SQLite browser but Eclipse says ‘no such table’) and would be grateful for any advice.

  314. Evariz | June 27th, 2011

    @Justin Copley
    Please i’m also facing the same issue as Graham, have found a fix or an explanation ?
    Thanks

  315. Piraba | June 27th, 2011

    Hi;

    I have android app. If there are no wifi/GPRS then data will store in sqlite,after getting GPRS connection it need to transfer data to ms sql sever.please suggest any solution for this.How we can transfer sql lite data to ms sql sever? please help me

    Thank you

  316. Justin Copley | June 27th, 2011

    Unfortunately I haven’t had a chance to look at the problem again, I have been busy playing computer games instead :)

    @Jiahao
    I can confirm that a blank database is created when this.getReadableDatabase(); is called. I can also confirm that the copyDataBase() method is being called, it just doesn’t overwrite the existing one with mine from assets.

  317. Urban | June 28th, 2011

    This is probably the only solution i cud find…and it aint working :(
    Caught the error… “Error copying database”

    Cud some1 plz help…ill bake you a cake if u do..

  318. Rain | June 28th, 2011

    Guys, it’s working. Just see for some errors in your project and try to open DDMS find the empty database in your app directory (package name) and delete it. Start the app again and it should create it. If again it’s empty something is messed up. I had the same issue like you and I fix it that way (can’t remember exact step but look everything and delete every time the empty database from DDMS.And don’t forget to put your own path -> private static String DB_PATH = “/data/data/YOUR_PACKAGE/databases/”; as well as putting your database in assets folder.

  319. Crueta | June 28th, 2011

    One thing which I discovered and helped my database to work was one line after the query with Cursor c, which I forgot:
    c.moveToFirst();

    Input this after the query and before reading the String/int or anything else from the database. So before working with the Cursor it has to be put to the first element first.
    Maybe it is going to help a few of you…

  320. Urban | June 28th, 2011

    Hi,
    Thanks for replying to my question…After some debugging, the program actually did work…and as you suggested I pulled out my database from the DDMS view…it did create the database BUT none of the values were copied from the database in my assets folder…This database had the same values that it had when it was created (death star and gravity stuff). No errors were reported but somehow none of my values were copied.
    Any idea where im doing it wrong? Are we specifying the table name that i want to copy of my assets database anywhere in this program?
    Id really appreciate any replies..

  321. Urban | June 28th, 2011

    another weird thing i just noticed…if i run the program fresh(without any database in data/data/package/database) my program crashes, giving me the error in copying database, but it does create the database with the initial values. Now if I run the program again, with this database in data/data/package/database, the application will run without any errors, but without copying the values that had to be copied….basically no change in the database…i have no idea why it isnt giving me any errors in the second run…

  322. Urban | June 28th, 2011

    another question just popped up…its kinda silly..but cant i just push my sqlite database into the databases folder…the same way i pulled the database out of it? Instead of putting it into assets and copying it….just push it into the device?

  323. Urban | June 28th, 2011

    Ok it friggin worked! :D

    For those who cudnt get it to work…id suggest that u remove that “Error copying database” message on ctaching an exception and instead let it throw the actual error message itself:
    Catch(IOException e){
    throw new Error(e);
    }
    This shud tell u exactly what the problem is instead of the “Error copying database message.

    Also, make sure that ur DB_NAME has the .db in it. As in: DB_NAME=”AssetsDBNameHere.db” I guess this must be obvious to most, but thought id still point this out.. :)

    Thx.
    PS: Great post! Thanks a lot!

  324. Rain | June 29th, 2011

    @Crueta – One thing which I discovered and helped my database to work was one line after the query with Cursor c, which I forgot:
    c.moveToFirst();

    This is really good advice. I didn’t know that and took me some time to figure it out :)

    @Urban my database work without .db =) And i guess you can replace directly your database in DDMS but that’s not the point if you want to distribute your app to other people.

  325. Urban | June 29th, 2011

    hmm..well it took me a long time to figure out that .db was needed…and yea i realized that pushing it directly to the device wasnt a good idea :)

  326. piraba | June 30th, 2011

    in the assets folder can not we put databaseName.sql file?

  327. piraba | June 30th, 2011

    in the assets folder can’t we put databaseName.sql file or should put databaseName.db? I put sql file i got error message.Please help me how we can create db file?

  328. Urban | June 30th, 2011

    U have to use sqlite only. Sqlite files will be .db

  329. piraba | June 30th, 2011

    then how we can put mssql sql to here?

  330. piraba | June 30th, 2011

    In the assest folder we should put sqlitedatabse.db file. so how we can import MSSQl sql file(that is .sql)? please help me.

  331. Urban | June 30th, 2011

    Well im no pro…but as far as i know…u can only use sqlite…Android only supports sqlite….its not very different from mssql, so give it a try.

  332. Urban | June 30th, 2011

    You cannot use mssql file. Only sqlite(.db) file

  333. piraba | June 30th, 2011

    Android will not support MSSQl.Thats correct. I have mssql file .Its contain data structure only. can not we do this? what is the solution for this “I have to setup database before running android application”. some data I have That is in Mssql file.Please help me

  334. Urban | June 30th, 2011

    Im sorry but i dont really know how you can do that.

  335. Chad | June 30th, 2011

    If you google mssql to sqlite converter, you can find something that will help you. The ones I found wont keep any stored reports or queries, but it will keep the table structure and information.

  336. Chad | June 30th, 2011

    I am still having problems getting this to work.

    It gets to this point;

    this.getReadableDatabase();

    and spits out this error;

    06-30 15:22:28.665: ERROR/Database(215): sqlite3_open_v2(“/data/data/com.database1/databases/testdata.db”, &handle, 1, NULL) failed

    I dont know if there is something in the code I need to change or if there is something wrong with the database, but from what I can find, the getReadableDatabase should only create a blank database that can be replaced with mine in the assets folder. I can not figure out why it will not do this.

  337. Urban | June 30th, 2011

    The db in ur assets folder will not be replaced by a new one. Only the data in it will get copied onto the database in data/data/etc etc
    Make sure that DB_NAME is the name of the database in ur assets folder.

  338. Chad | June 30th, 2011

    I know that. What I mean is that when I check the DDMS in Eclipse, there is no database created. The getReadableDatabase should create a blank one there that will get replaced with mine. My problem is that the blank one is not getting created and there is an error and a Force Close.

  339. Urban | July 1st, 2011

    no idea due…im pretty sure if everything is exactly like the code in this post…it works…

  340. Urban | July 1st, 2011

    no idea due…im pretty sure if everything is exactly like the code in this post…it works…

  341. 4ucai | July 1st, 2011

    for thos having trouble working with their database….

    http://stackoverflow.com/questions/3780001/sqlite-database-creation-for-android-app

    answer by: McStretch

  342. 4ucai | July 1st, 2011

    for thos having trouble working with their database….

    http://stackoverflow.com/questions/3780001/sqlite-database-creation-for-android-app

    answer by: McStretch

  343. NaveenKumar | July 1st, 2011

    Hi sir,

    i want to insert bulk of data fastly what to do now.

    db.beginTransaction();

    i want to use this statement please help me

  344. NaveenKumar | July 1st, 2011

    Hi sir,

    i want to insert bulk of data fastly what to do now.

    db.beginTransaction();

    i want to use this statement please help me

  345. piraba | July 1st, 2011

    Hi;

    I have converted Mssql file to sqlite.db file(using this site http://www.codeproject.com/KB/database/convsqlservertosqlite.aspx ).Now I put my db file in to assets folder & run the application , it say “ERROR/AndroidRuntime(325): java.lang.Error: Error copying database” .Please help me.

  346. piraba | July 1st, 2011

    Hi;

    I have converted Mssql file to sqlite.db file(using this site http://www.codeproject.com/KB/database/convsqlservertosqlite.aspx ).Now I put my db file in to assets folder & run the application , it say “ERROR/AndroidRuntime(325): java.lang.Error: Error copying database” .Please help me.

  347. burhan cerit | July 4th, 2011

    hi thanks for the post using a browser is an easy way to create and modify the database how can we add data into this database from another class any example would be great. thanks again..

  348. burhan cerit | July 4th, 2011

    hi thanks for the post using a browser is an easy way to create and modify the database how can we add data into this database from another class any example would be great. thanks again..

  349. ald33 | July 7th, 2011

    It’s veru useful but, i can’t do that and we just find android_metadata copied into my emulator…
    so i change the code like this and it can do it..
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    File outFileName = myContext.getDatabasePath(DB_NAME);

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

  350. ald33 | July 7th, 2011

    It’s veru useful but, i can’t do that and we just find android_metadata copied into my emulator…
    so i change the code like this and it can do it..
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    File outFileName = myContext.getDatabasePath(DB_NAME);

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

  351. saurabh daga | July 7th, 2011

    i am getting an error saying sqlite3_open_v2(/data/data/com.database1/databases/datasau.db, &handle, 1, NULL) failed
    and when i checked my DDMS it did not even create an empty database(MyDatBase)…. like it should have… and i am not getting any error on the emulator …. i have specified my DB_NAME = datasau.db and saved a database as datasau.db in my assets folder …. and i have done exactly how u have said it in the tutorial … pls help ….
    :(
    :(
    urgently…

    thanks…
    for the awesoke tutorial ….:)

  352. Justin Copley | July 8th, 2011

    I tried ald33’s suggested changes but still have the same problem. A database with only android_metadata is created but my database is not copied over.

  353. Graham | July 9th, 2011

    @Justin, I too tried ald33’s suggestion and it worked for me. All the tables came across, not just the sqlite as the Reign original code does on my PC/emulator. I put the sub in opening (Main..) activity and changed mycontext.get… to this.get…
    @ald33, many thanks.

  354. Justin Copley | July 12th, 2011

    Ahhh. I got it working! It was a context issue with the getAssets().Open function.

    I’m now going to fully read up on Context’s and understand what they are for and how they work, instead of just using them without fully knowing why.

    Thanks to all who replied, and of course to Juan-Manuel Flux for his post!

  355. douard Lopez | July 12th, 2011

    I got this issue (related log: http://pastebin.com/cCxdrBv5f):
    sqlite3_open_v2(“/data/data/com.cfdict.android/databases/cfdict.db”, &handle, 1, NULL) failed

    The class where it happens: http://pastebin.com/KS459wRD, see around line 115, I use:
    checkDB = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READONLY | SQLiteDatabase.NO_LOCALIZED_COLLATORS | SQLiteDatabase.CREATE_IF_NECESSARY);

    Anyone solve that ?

  356. T0B1 | July 12th, 2011

    Hello,

    Thanks for this tutorial it works great for me. I only have a curious problem: If I call the copyDataBase()-Function twice, I get a NullPointerException (“At AssetManager.readAsset(native Method)”).

    The Function is this one:

    final static void copyDataBase() throws IOException {
    // Open your local db as the input stream
    InputStream myInput = StartScreen.AssetDB;
    // Path to the just created empty db
    String outFileName = StartScreen.DB_PATH + StartScreen.DB_NAME;
    // Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);
    // transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;

    while ((length = myInput.read(buffer)) > 0) {
    myOutput.write(buffer, 0, length);
    }

    // Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

    And I call

    copyDatabase();
    copyDatabase();

    Can anyone help me?

  357. ald33 | July 12th, 2011

    so why it call a twice for copying…
    @Justin: i modified it because i can’t copy database…many thanks to Juan Manuel Fluxa who posting it first…

  358. Sam | July 14th, 2011

    Hi thanks for the great post,my app is already in the market now and i want to release the next version of it, mostly the next version have DB changes so how can upgrade the existing users DB with this approach.

  359. Chad | July 15th, 2011

    Ok, I am still completely stuck and I have no idea why.

    I get to getReadableDatabase() and then this error comes up;

    07-14 16:05:43.350: INFO/Database(3713): sqlite returned: error code = 14, msg = cannot open file at source line 25467
    07-14 16:05:43.350: ERROR/Database(3713): sqlite3_open_v2(“/data/data/com.chad/databases/JADB.db”, &handle, 1, NULL) failed

    I have been pulling my hair out trying to figure out why this error comes up. I am sure it is something with my set up or something really simple that I am missing, but for the life of me I cannot figure it out.

    Any suggestions?

  360. Ayesha | July 15th, 2011

    A very simple and effective tutorial that helped me alot.

    https://www.sites.google.com/site/androiddevelopment2011/my-calendar

  361. Robert Schoenburg | July 18th, 2011

    Thank you for the tutorial.

    Coming from iOS and Xcode I am dismayed that it is not possible to add files such as images and databases to a project without jumping through a bunch of hoops. For example, my databases are created using the SQLite Manager in FireFox. I import data to tables using CSV files from Excel. Adding the db to an iOS project in Xcode is done by simply choosing to add a file to the project. Where the db is does not matter as long as it is the project It does not have to be in a folder or in any special place. Isn’t there a simple way to add a db to an Android project without writing a lot of code and “massaging” the db for Android?

  362. Peter | July 19th, 2011

    Hi,

    Thanks for the tutorial..

    When I run this program I get the following exception,

    I/Database( 425): sqlite returned: error code = 14, msg = cannot open file at source line 25467
    E/Database( 425): sqlite3_open_v2(“/data/data/org.me.mdbtest/databases/MyDB.db”, &handle, 1, NULL) failed
    W/System.err( 425): java.io.FileNotFoundException: MyDB.db
    W/System.err( 425): at android.content.res.AssetManager.openAsset(Native Method)
    W/System.err( 425): at android.content.res.AssetManager.open(AssetManager.java:313)
    W/System.err( 425): at android.content.res.AssetManager.open(AssetManager.java:287)

    Could you please let me know how to resolve this?? BTW I’ve moved the database file “MyDB.db” to “assets” folder..

    Any pointers would be quite helpful

    Peter

  363. Peter | July 19th, 2011

    Just noticed that @Chad is also facing the same issue as me… Any help??

    Peter

  364. Chad | July 20th, 2011

    @Peter

    I think I may have fixed my problem. After going back and reading through all of the older comments, I changed

    this.getReadableDatabase()

    to

    this.getReadableDatabase().close()

    and it worked.

    Three weeks of pulling hair out and it was this simple fix.

    Hope this helps you.

  365. Peter | July 20th, 2011

    Hi Chad,

    I changed the code as you mentioned but it didn’t work :(

    Could you please send me your sample project to buzznaveen@gmail.com? I’ve been breaking my head for the past few weeks to fix this issue.. Any help would be greatly appreciated!!

    Peter

  366. Praveen | July 20th, 2011

    Hi!
    its not working for me may because my device is not rooted. Please confirm to run this code device shuld be rooted ???

    – Praveen

  367. alyanm | July 21st, 2011

    Thanks for the article, you helped me figure out why my sqlite database wasn’t reading right — it was missing the android_metadata. Sure would like to see documentation on this in the Android Developer site!

    @Peter, perhaps you should post the relevant piece of your code — its hard to tell what’s wrong just from the error messages.

  368. Thiago | July 21st, 2011

    Why it is necessary to update my existing ID fields to _id? Wont it work if I leave my ID fields with the same name? Thank you

  369. Piraba | July 21st, 2011

    Hi;

    I have a problem on copy database. When call DatabseHelper class , It throw “ERROR/AndroidRuntime(7193): at com.android.xont.db.DataBaseHelper.createDataBase(DataBaseHelper.java:43)”. My databse size is 4.55MB ? is it possible to push those size? please guide me? In emulator also it 3072 kb size file only transfer to there. please guid me

  370. chis | July 23rd, 2011

    Hi, I followed this tutorial but I’m having a problem. The file copies and that seems to work fine, but when I try to read my newly copied database in my application or even in the next activity, I get SQL errors that the tables don’t exist. Then, I re-run my program and everything’s great. My DB is as it should be and does not error out on the second run. But always on the first run of installing my app, not updating, just first run after install (because afterwards my db file is in the /data/data/package/databases/ folder).
    I thought it was an issue of not giving enough time for the file to copy, but there’s something else that might be going on because waiting/adding delays didn’t help. Any suggestions as to what this might be?

  371. John Peat | July 27th, 2011

    Great article – the idea works well but a couple of questions…

    What’s the difference between using the OpenDatabase() method and just using getReadableDatabase()?

    Renaming keys to _id seems to be unnecessary – at least I’ve not run into problems with that yet??

    Upgrading databases – I assume you could create a table ‘VERSION’ with a single column and check this against a DB Version stored in the App – how would you go about deleting/recopying the DB cleanly tho – anyone tried this??

    Are there any issues with this database ‘copying’ technique on any handsets? I know there’s an issue with some HTC handsets/read-only databases and certain types of transactioning (WAL?) – anyone run into any other issues???

    Thanks again for the info!!

  372. S | July 29th, 2011

    Heyy thank you for the post. I have a problem when I tried to add this code:

    public class StationsDatabase extends Activity{

    public static String DB_PATH = “/data/data/com.android.stations/databases/”;

    public static String DB_NAME = “test”;

    private static DataBaseHelper myDbHelper;
    private static SQLiteDatabase myDataBase;

    @Override
    public void onCreate(Bundle savedInstanceState){

    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    Context con = getApplicationContext();
    myDbHelper = new DataBaseHelper(con);
    myDbHelper = new DataBaseHelper(this);

    try {

    myDbHelper.createRewriteDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    System.err.println(“Problem! “);
    sqle.printStackTrace();
    //throw sqle;
    }

    try {

    myDataBase = myDbHelper.getReadableDatabase();

    try{

    ArrayList results = new ArrayList();
    Cursor c = myDataBase.rawQuery(“SELECT * FROM stations”, null);

    if (c.getCount() > 0){

    c.moveToFirst();

    do{
    results.add(new Stations(c.getString(c.getColumnIndex(“Name”))));
    }

    while (c.moveToNext());
    }

    System.out.println(“RESULTS ” + results);
    }

    finally{

    if (myDataBase != null)
    myDataBase.close();
    }

    }

    catch (Exception e) {
    System.err.println(“Got an exception! “);
    System.err.println(e.getMessage());
    }

    }

    public class Stations {

    public String Name;

    public Stations(String name) {

    Name = name;
    }

    at this line Cursor c = myDataBase.rawQuery(“SELECT * FROM stations”, null); it gives me an error saying no such table: stations while when i run the database in the console it shows me the table and i can do any queries from there, but in eclipse it gives me this error.

    Anyone any ideas??

    Thank you

  373. Peter | July 29th, 2011

    Thanks for a nice tutorial!

    I too, had some problems getting this to work. I got a FileNotFound exception, and thought i might be a context error.

    My solution was to right click on the assets folder in Eclipse and choose Build Path > Use as Source Folder.

  374. Justin | August 1st, 2011

    Hi,

    Here is what I am trying to do: Pre-load a database and display searchable results.

    First, “DataBaseHelper myDbHelper = new DataBaseHelper();” tells me DataBaseHelper() is undefined. I just combine those two lines int: “DataBaseHelper myDbHelper = new DataBaseHelper(this);”.

    Second, how the heck do I activate this database as a SQLiteDataBase in the main program? The Sai Geetha tutorial uses these two commands “SQLiteDatabase sampleDB = null;” and “sampleDB = this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);”, after which it can use sampleDB.rawquery() to create returns.

    The DataBaseHelper variable cannot use the rawquery command, nor can I figure out how to open the saved database as a SQLLiteDataBase that can use the command.

    Thanks!

  375. Ritesh | August 13th, 2011

    Hi there,

    First of all, great tutorial – this saved me a lot of time.

    I am running into a problem where my text field is being considered a blob. I have an existing database that contains only two tables – android_metadata and images. In the images table, I have a “name” text field and some other fields (_id as integer, image as blob etc.). I used the above sample code to bring this database into android. It worked fine. I can run queries and get desired results.

    The problem is that when I try to get the “name” field as cursor.getString(nameColumnIndex), I get an exception – cannot convert blob to string. But it is a text field and not blob! I am sure that I am getting it from the name column and not the image column.

    Okay, so I tried to get it as a blob and then convert to a string – that works but only partially – after my string, there are some special characters that get appended which is unacceptable for my application.

    Is this happening because during copyDatabase() we are copying the whole file as a byte array? Has anyone else run into this issue? What did you do to resolve it?

    Many thanks!

  376. Ritesh | August 14th, 2011

    Refer to my problem described above.
    Although I couldn’t find a solution, I implemented an easy work-around which others may find useful.

    I get the text field as a blob, then convert it to a string and then get rid of all non-ASCII characters using regular expressions. I wrote a helper function to do this which looks like this:

    public String GetStringFromBlob(byte[] blob)
    {
    if (blob == null)
    return null;

    String s = new String(blob);
    s = s.replaceAll(“[^\\x20-\\x7e]“, “”);
    return s;
    }

    Hope this helps.

  377. F. | August 14th, 2011

    Hi there,

    Thanks for this fantastic tutorial. I followed the instructions, but I get the following error:
    “Unable to open database file”. Would you please send me a copy of project ?

    It would be highly appreciated.

    Regards.

  378. Graham | August 16th, 2011

    you definitely can’t invoke copyDatabase from onCreate(); The context is not set at that point. It seems like it would simplify things, but it leads to NPEs.

  379. Selvaraj | August 19th, 2011

    Hi thanks for the post,my app is already in the market now and i want to release the next version of it, mostly the next version have DB changes so how can upgrade the existing users DB with this approach.and add one more table in existing db.

    Thanks in advance..

    Regards,
    Selva

  380. AsilTR | August 21st, 2011

    JIm: I must thank you for this reply. I have been working for one week to solve this problem. I am really appreciated. Thank you so much :)

    Well I solved it

    The problem is that my DB is sort of largish (> 1MB). It seems that it is compressed and that causes confusion in the Android read on the InputStream. The trick is to rename your asset to a file that the packager will NOT try to compress. Renaming my db file from xxx.db to xxx.mp3 did the trick

    GAG!

  381. pankaj | August 24th, 2011

    hey i want basic program of database in eclipse

  382. DonnaLea | August 24th, 2011

    Great little tutorial. Covered exactly what I was looking for! :)

  383. Fer | August 30th, 2011

    All it’s great, my DB is copied from the assets folder to the data/data/packaged/databases/ folder, and if the DB doesn’t exist, it’s created, and I can see the .db file, BUT… inside the .db I don’t have the tables.

    If I open the .db in the assets folder of my PC, with the sqlite browser I can see my 3 tables, but if I open the .db of the databases folder of my mobile (with root explorer) I only see 1 table, the android_metadata table.

    I’ve tried creating my own android_metadata table in my .db, and using keys fields called like _id, but the results are the same….KO

    If somebody could help my…

    thanks, best regards and sorry about my english

  384. sups | September 2nd, 2011

    Great Tutorial. I had the similar problem where the database was not been seen in my mobile. You need to specify the path properly
    Instead of this
    String myPath = DB_PATH + DB_NAME;

    try using following it worked for me

    String myPath = Environment.getExternalStorageDirectory() + “/” + DB_NAME;

    Thanks,

  385. ared | September 2nd, 2011

    Hi,

    Firstly, this is a great tutorial. However, I am facing some issues. My database file in my android device only contains the android_metadata table I know this because I pulled it from eclipse after it was transfered to the device. I don’t understand why it is not copying my other table named words into the database. Any help would be appreciated. Thanks in advance.

  386. ketan | September 2nd, 2011

    Hello,

    Really good tutorial, It resolve one problem but many time we came across another problem we need to pull database from the device. for the security reason we can not access /data/data folder on device. I found very simple and good solution for that. We need to create one function in databasehelper class which copy our database from /data/data to /sdcard. check following link for more detail

    http://kpbird.blogspot.com/2011/09/android-copy-sqlite-database-from.html

  387. Darjan | September 6th, 2011

    Hi guys, while I’m trying to use one of the methods createDataBase() or openDataBase(), I receive an error Syntax error on token “openDataBase”,
    identifier expected after this token. Can someone tell what shall I do ?

  388. Joo | September 7th, 2011

    Hi,
    I have my app working fine, but now I’m trying to encript the data when creating the DB, because my app is a quiz and I don’t want everyone to see the answers from the SQLite DB.
    I guess I could use the javax.crypto lib, but not sure how to use it with this example.

    Anyone can help me?

    Thanks in advance,

    Joo

  389. Joo | September 8th, 2011

    Hi ared,

    maybe that’s because you already have there the db, if you check the code, when the db already exists, nothing will happen.
    If this is the problem, you can delete de db the same way you pulled it from de device. This way, the next time this code runs there will be no db there, and then is created.

    If you already tryed this, please discard my answer, maybe it will help someone else.

    Best regards,

    Joo

  390. raj | September 8th, 2011

    Hi i get this exception. please some one help.

    8-31 13:10:56.942: INFO/Database(328): sqlite returned: error code = 14, msg = cannot open file at source line 25467
    08-31 13:10:56.942: ERROR/Database(328): sqlite3_open_v2(“/data/data/com.test.ui/databases/pcm.sqlite”, &handle, 1, NULL) failed
    08-31 13:10:56.984: WARN/System.err(328): java.lang.StringIndexOutOfBoundsException
    08-31 13:10:57.042: WARN/System.err(328): at android.app.ContextImpl.validateFilePath(ContextImpl.java:1579)
    08-31 13:10:57.051: WARN/System.err(328): at android.app.ContextImpl.openOrCreateDatabase(ContextImpl.java:539)
    08-31 13:10:57.061: WARN/System.err(328): at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:203)
    08-31 13:10:57.091: WARN/System.err(328): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
    08-31 13:10:57.142: WARN/System.err(328): at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:158)
    08-31 13:10:57.162: WARN/System.err(328): at com.tycoon.db.controller.SQLiteDBAdapter.createDataBase(SQLiteDBAdapter.java:63)
    08-31 13:10:57.191: WARN/System.err(328): at com.tycoon.db.controller.DBController.createDataBase(DBController.java:69)
    08-31 13:10:57.201: WARN/System.err(328): at com.tycoon.ui.UiVariables.(UiVariables.java:57)
    08-31 13:10:57.242: WARN/System.err(328): at com.tycoon.ui.UiVariables.initUiVariable(UiVariables.java:33)

  391. G-S French user | September 9th, 2011

    To S,

    I’m not so sure but you may not extends database of activity.

    It’s will be easier to make your database extends of SQLiteDatabaseHelper, then create an adapter to get or set datas from table (one for one).

    Now, I wich, you can create your Activity and intents using your datas.

    May be,

    StF

  392. Alex | September 10th, 2011

    Thank you a lot!!!

  393. Binu Paul | September 12th, 2011

    Thank you for this excellent tutorial on using a pre-loaded sqlite db (very few out there!)

  394. Darjan | September 13th, 2011

    Hey guys i’m getting an error the table doesn’t exists, I also make the assets folder as source, but I still get the same error. Can some help ?

  395. geeta | September 15th, 2011

    hey i want to know that how to download sqlite ,how to install and from which site i ll get sqlite .
    my project is on location finder i need to create database and i m totaly new about this s/w.
    plz guide me………

  396. Joo | September 15th, 2011

    to geeta:

    download it from here:

    http://sourceforge.net/projects/sqlitebrowser/

    It’s really easy to use, give it a try.

  397. Dennis | September 17th, 2011

    Thanks for the article!

    Can’t get it to work, I get a ‘file not file exception’. I don’t have a ‘assets’ folder, should I creeate it?, or should I place my database in the raw folder?, and ow yea, my file is bigger than 1mb, known bug, so I renamed it to .jpg, is that a problem. (another retarded issue with android development, thats something you expect in 1997 1mb limit)

    I totally agree with Robert Schoenburg, f*ing sad that you have to write allmost 100 lines of code just to open a database, this whole stuff up here is done with 3 lines in .net:

    SQLconnect.ConnectionString = “Data Source=” & SQLiteDatabaseFile & “;”
    SQLconnect.Open()
    SQLcommand = SQLconnect.CreateCommand

  398. codaniel | September 19th, 2011

    I would love to see the full source. I’m using phonegap and not too good at Java. I’m not sure where to put some of the code.

  399. Hanry | September 20th, 2011

    Great and clean way to explain run on first start…………………Thanks a lot!!!!!!

  400. Android | September 20th, 2011

    Nice post, like this for adding, inserting and deleting from the table i get crossed with this post http://android-codes-examples.blogspot.com/2011/09/using-sqlite-to-populate-listview-in.html

  401. Nikunj | September 20th, 2011

    package com.sqlitedatabase;

    import android.app.Activity;
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.os.Bundle;
    import android.view.View;
    import android.view.View.OnClickListener;
    import android.widget.AdapterView;
    import android.widget.ArrayAdapter;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.ListView;
    import android.widget.TextView;
    import android.widget.Toast;
    import android.widget.AdapterView.OnItemClickListener;

    public class sqlitedatabase extends Activity
    {
    SQLiteDatabase db;
    EditText ed1,ed2;
    Button b1,b2,b3,b4;
    ListView lv1,lv2;
    String str[];
    public void onCreate(Bundle savedInstanceState)
    {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    final Databasehelper dh=new Databasehelper(this);
    dh.getWritableDatabase();

    ed1=(EditText)findViewById(R.id.EditText01);
    ed2=(EditText)findViewById(R.id.EditText02);

    b1=(Button)findViewById(R.id.Button01);
    b1.setOnClickListener(new OnClickListener()
    {
    public void onClick(View v)
    {
    String name=ed1.getText().toString();
    String sem=ed2.getText().toString();

    db=dh.getWritableDatabase();
    ContentValues cv=new ContentValues();
    cv.put(“stu_name”, name);
    cv.put(“stu_sem”,sem);
    db.insert(dh.tablename, null,cv);
    Toast.makeText(getApplicationContext(),”Data Inserted”,5000).show();

    }
    });
    b2=(Button)findViewById(R.id.Button02);
    b2.setOnClickListener(new OnClickListener()
    {
    public void onClick(View v)
    {

    db=dh.getReadableDatabase();
    String name=ed1.getText().toString();
    String sem=ed2.getText().toString();
    db.execSQL(“Delete from ‘”+dh.tablename+”‘ where stu_name='”+name+”‘ or stu_sem='”+sem+”‘”);
    Toast.makeText(getApplicationContext(),”Data Deleted”,5000).show();

    }
    });
    b3=(Button)findViewById(R.id.Button03);
    b3.setOnClickListener(new OnClickListener()
    {
    public void onClick(View v)
    {
    db=dh.getReadableDatabase();
    String name=ed1.getText().toString();
    String sem=ed2.getText().toString();
    db.execSQL(“update ‘”+dh.tablename+”‘ set stu_name='”+name+”‘,stu_sem='”+sem+”‘ where stu_name='”+name+”‘ or stu_sem='”+sem+”‘”);
    Toast.makeText(getApplicationContext(),”Data Updated”,5000).show();
    }
    });
    b4=(Button)findViewById(R.id.Button04);
    b4.setOnClickListener(new OnClickListener()
    {
    public void onClick(View v)
    {
    String temp=” “;
    db=dh.getReadableDatabase();
    String select=”select stu_name from ‘”+dh.tablename+”‘”;
    Cursor c=db.rawQuery(select,null);
    if(c.moveToFirst())
    {
    do
    {
    temp=temp+c.getString(0).concat(“/”);
    }while(c.moveToNext());

    }

    str=temp.split(“/”);
    lv1=(ListView)findViewById(R.id.ListView01);
    ArrayAdapter adpter=new ArrayAdapter(getApplicationContext(),android.R.layout.simple_list_item_checked,str);
    lv1.setAdapter(adpter);
    lv1.setTextFilterEnabled(true);
    lv1.setOnItemClickListener(new OnItemClickListener()
    {
    public void onItemClick(AdapterView parent, View view,int position, long id)
    {
    ed1.setText(((TextView)view).getText());

    }
    });
    //=====================================
    String temps=” “;
    db=dh.getReadableDatabase();
    String selects=”select stu_sem from ‘”+dh.tablename+”‘”;
    Cursor c1=db.rawQuery(selects,null);
    if(c1.moveToFirst())
    {
    do
    {
    temps=temps+c1.getString(0).concat(“/”);
    }while(c1.moveToNext());

    }

    str=temps.split(“/”);
    lv2=(ListView)findViewById(R.id.ListView02);
    ArrayAdapter adpters=new ArrayAdapter(getApplicationContext(),android.R.layout.simple_list_item_checked,str);
    lv2.setAdapter(adpters);
    lv2.setTextFilterEnabled(true);
    lv2.setOnItemClickListener(new OnItemClickListener()
    {
    public void onItemClick(AdapterView parent, View view,int position, long id)
    {
    ed2.setText(((TextView)view).getText());

    }
    });

    }
    });

    }

    class Databasehelper extends SQLiteOpenHelper
    {

    final String tablename=”student”;
    public Databasehelper(Context context)
    {
    super(context,”stu.db”, null,1);

    }

    public void onCreate(SQLiteDatabase db)
    {
    db.execSQL(“CREATE TABLE ‘”+tablename+”‘(stu_id INTEGER PRIMARY KEY AUTOINCREMENT,stu_name TEXT,stu_sem INTEGER)”);
    System.out.println(“table is Created”);

    }
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)
    {
    //db.execSQL(“DROP TABLE IF EXISTS”+tablename);
    //onCreate(db);
    }

    }
    }

  402. Nikunj | September 20th, 2011

    xml file above database
    ========================

  403. Nikunj | September 20th, 2011

    /*

    */

  404. Joseph | September 22nd, 2011

    While searching through different database tutorials; I found this tutorial very interesting and helpful in understanding the basics of Sqlite.

    https://www.sites.google.com/site/androiddevelopment2011/my-calendar

  405. Pingback: Online SQLite-Datenbank auf das Handy - Android-Hilfe.de

  406. Christos | October 1st, 2011

    @Rain: Thank you for this nice tutorial!
    Solutiion for “no such table” problem when database is first opened.

    In the method

    createDataBase()

    //after
    this.getReadableDatabase();
    //insert this line
    this.close();

    This releases all database objects and allows to copy the database from the assets to the newly created database file.

  407. Tyler | October 7th, 2011

    to Micile or any other that used his solution.
    What happen with the old version of the database when the update occurs? ll it continue to exist (using storage memory)? Or ll it be replaced with the new version?

  408. Glenn | October 8th, 2011

    Hi,

    Why does you class extends SQLiteOpenHelper. It does not have to, and you have implemented emty onCreate() and onUpdated()

    I am not sure how this implementation would work for a content provider.

  409. Roy | October 10th, 2011

    Hi Guys,

    Copied what was here so my already created database would load and it works great.

    But now that i want to test query my database to make sure i was getting this correct so i used the following line of code:

    Cursor testString = myDbHelper.rawQuery(“some arguments…”);

    Yet Eclipse is telling me that rawQuery() is undefined for the type DataBaseHelper. Why am i getting this error for? Im new to Android (especially interacting with SQLite databases) so any help would be greatly appreciated because im obviously missing something.

    Thanks for your help

    Roy

  410. Roy | October 11th, 2011

    Hi guys (again)

    Ignore my previous comments. My brain has thankfully come back online now and its quite obvious why haha

    Thanks again

    Roy

  411. Hans | October 13th, 2011

    Hi, I have done for showing data in listView from database. I want to know if I change the test platform ,such as change the AVD, how the data can still be seen when the app launches in new AVD ?

  412. jowett | October 15th, 2011

    Million thanks to you. i have integrate this pre-add solution into my application.

  413. Pingback: Code improvement and new features added « Haley Software

  414. sachin | October 18th, 2011

    i want to write data of my table into text file
    ho i can do that please give me solution.

  415. Pingback: Datenbank im Ordner assets? - Android-Hilfe.de

  416. Akhil | October 22nd, 2011

    Please help a poor beginner

    Help Needed To, XML parsed data s insertion and selection to the Sq Light DB . Please Feel Free To Reply For This With A helping Mind

  417. Hal | October 25th, 2011

    After I got my version of the code working correctly, I tried commenting out the getReadableDatabase() call so that no blank database file was created before the copy occurred. Apparently the blank database is not necessary and this is simply a file copy. Although, it may be that calling getReadableDatabase() is the only way the subdirectory “databases” will be created.

  418. Hal | October 25th, 2011

    Just a followup on that. I created a new application, one which had not previously had a successful copy, hence no “databases” subdirectory under /data/data/. No SQLiteOpenHelper was created. The source database was put in assets and all that was done was to do the file copy to the empty “databases” subdirectory. Initially an exception was thrown. However, when I went to the Eclipse DDMS aspect and created the “databases” in the emulator file system, the copy worked and a copy of the database appeared in “databases”. The conclusion is that all the call to getReadableDatabase() accomplishes is the creation of the required subdirectory. No need for it, and for that matter the class derived from SQLiteOpenHelper.

  419. Dennis | October 25th, 2011

    I use this all the time, thanks, it’s great.

  420. Hal | October 26th, 2011

    Step by step:
    1. Create the database as shown above, and put it in your Assets folder.
    2. In the try part of a try/catch block, attempt to open the database. First time through an exception is thrown.
    3. In the catch part, create a java.io.File object with the argument in the constructor being the path to the database: /data/data//databases/.
    4. Call the exists() method of this File object to verify that it does not yet exist, then create the directory structure with a call to the File object’s mkdirs() method. Now the directory is there for the copy.
    5. Do the copy from Assets to the databases diretctory as shown above. In the DDMS perspective you will see that it has been copied.
    6. Now when you try to open the database, it will open.

    There is no need for deriving a class from SQLiteOpenHelper.

  421. suzan | October 30th, 2011

    thxx alot for yor help

  422. tuan | November 3rd, 2011

    one thing to note, it works with android version 2.3 but does not work with version 2.2 :(

  423. Tomas Marny | November 5th, 2011

    I might be stating the obvious, but it took me some time to figure this out :-( I wanted to use the .sqlite extension in order to directly modify my database from the Eclipse project. The project was failing due to the file compression applied by Android at compile. You have to use the database file with no extension, so the easiest way to edit your data is by modifying Eclipse preferences. Go to Window>Preferences>General>Appearance>Editors>File Associations and link your database filename (not the extension) with your favorite sqlite editor.

  424. Pingback: how to copy large database which occupies much memory from assets folder to my application? | Software development support, software risk,bugs for bugs, risk analysis,

  425. Pingback: Android sqlite from pc | Software development support, software risk,bugs for bugs, risk analysis,

  426. Pingback: Android sqlite from pc

  427. Isabell | November 8th, 2011

    hello, thanks for the informative article. i was wondering though, i want a db that is functional across several apps, meaning that when i update something in this local sqlite, that is then also updated in some sort of global db on a server. is there a possibility for that besides creating my own webservice and figuring out how to keep data consistent on my own?
    It would be great if you could answer on this :)

  428. Andy | November 18th, 2011

    Thank you for this. A little tweaking and it works for me.

    One thing;

    FOR THE LOVE OF GOD man, update the code on the page to fix the database open error;

    this.close(); //<— ADD THIS LINE!!! :-)

    To prevent this;
    sqlite3_open_v2("/data/data/ ___ databases/ ___.sqlite", &handle, 1, NULL) failed

    Do so here;
    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.

    Log.i("DataBaseHelper.createDataBase", "run this.getReadableDatabase()");
    this.getReadableDatabase();
    this.close(); //<— ADD THIS LINE!!! :-)

  429. yazid | November 20th, 2011

    where we must put new instance in my code

  430. Ahmed Shaban | November 23rd, 2011

    Wonderful and complete guide, Thank you. I have a question though, Do we have to copy the database to the phone storage?? Can’t we use the database directly from the APK ? I mean if we copy the DB then we have it stored twice once inside the apk and once more, and if the DB is very big this is not very efficient.

  431. gsmari | November 23rd, 2011

    Hi everyone! First of all, thank you very much for this brilliant tutorial.
    I have had the same problem as others and always got a NULL pointer and the application crashed in emulator with no good explanation, just like the database did not exist.
    I have solved my issue by changing the following line:
    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.CREATE_IF_NECESSARY);
    //myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }

    Good luck everyone. Hopefully, this helps.
    Thanks again!

  432. Nagarjuna Lingala | November 24th, 2011

    Thank you dude,

    It’s really helpful for me,

    Thanks a lot

  433. tai | November 28th, 2011

    hi, i am trying out ur code here….actually is ur code really can work?because i am following ur step..but it seem like keep occuring error when i am running debug…any 1 can help?is it the correct way if i define like this?:
    private String DB_PATH = “/data/data/com.tlscode.databasetest/databases/”;
    private static String DB_NAME = “testing.db”;
    private static String DB_TABLE=”android_metadata”;

    public SQLiteDatabase myDataBase;

  434. Pingback: Андроид: Как добавить в приложение готовую базу SQLite

  435. milton | November 30th, 2011

    Finally a post that works. Thanks for the tip!

  436. mahi | December 2nd, 2011

    Hi, I am new to android. I have one project i.e alredy developed in java, Now i want to develop that project in android. I need some help about this project. the java project didn’t contain any webservices. I want to access database for android. java contain database. i want to use that in android. can any one help how to do that.

  437. QY | December 5th, 2011

    Hi, I’ve used your code and its of great help ! but i would like to ask what if i wanted my database to support other font such as Chinese character or Japanese,Korean etc ?

  438. Bert | December 5th, 2011

    Thanks :) I used it to preship my database in Android’s assets folder.

  439. Pingback: Error when Accessing SQLite database file in Android Phone | Software development support, software risk,bugs for bugs, risk analysis,

  440. huzefa | December 8th, 2011

    i followed step by step but i have got errors for the databasehelper class and i m clueless about where does the instance code go?

    thnx in advance!!!

  441. Pingback: Anonymous

  442. Denny | December 16th, 2011

    I tried everything in here but in my case its not working on Android 2.3 +
    On adroid 2.2 it works fine ,but when i switch to android 2.3 i get error “no suitable driver”
    public void OpenConnection() {
    try {
    Class.forName(DbDriver);
    } catch (java.lang.ClassNotFoundException e) {
    DbError = e.getMessage();
    }

    try {
    DriverManager.setLoginTimeout(120);
    con = DriverManager.getConnection(DbName, DbUserName, DbPassword);
    } catch (java.sql.SQLException e) {
    DbError = e.getMessage();
    }

    }
    the Class.forName gives me that error
    tried everything ,added sqlite-v056.jar to libraries ,changed the driver and nothing
    did anyone run into this same problem as me ?

  443. Mark | December 19th, 2011

    To those who have the following error :

    sqlite3_open_v2(“/data/data/project/databases/DB”, &handle, 1, NULL) failed

    I found it was because I wasn’t actually putting the correct path into the variable.
    If you go to ‘Windows – > Open Perspective -> DDMS ‘
    you can then browse to the correct folder in ‘file explorer view ‘ (data/data/projectname/databases) to ensure you have the correct path.

  444. Pingback: Final Project – xiongchu « CS *

  445. sravanthi | December 20th, 2011

    hi,
    please post an example clearly using sqlitebrowser and how to import file.with what extension it should be saved

  446. Tugrul | December 26th, 2011

    Really good job. Thanks a lot. Great !

    Tugrul

  447. worked | December 28th, 2011

    Just couldn’t get this to work on my emulator running 2.3.3. I figured I would create the db from scratch and populate it with data from the strings.xml, when the application is first loaded, and the database is created the first time. Save yourself from torture and move on from this tutorial.

  448. Crash | December 30th, 2011

    I spent a day debugging an issue with my code which was attempting to replace an old database with a new one from an apk. Most of the time the new database would not be moved to /data/data/XXXX/databases

    I followed the examples above that included code such as:
    public void createDataBase() throws IOException {

    if(dbExist){
    // Need to have the system call onUpgrade if the database in this apk
    // is newer than the one in the DB_PATH directory. onUpgrade should
    // be called by the system if needed by a call to getWritableDatabase().
    SQLiteDatabase db_Write = this.getWritableDatabase();
    }

    }

    Debugging showed that the call to getWritableDatabase() resulted in a call to onCreate() which is just a shell in this construct. OnUpgrade was never called by the system, so the old database was never purged which would have allowed createDataBase() to call copyDataBase() and hence move the new data to the /data/data/XXX/database directory. Worst of all, after the call to getWritableDatabase() was completed the system thought the database was now up to my latest DB_VERSION constant.

    I got around this by using the below code:
    if(dbExist){
    // Need to have the system call onUpgrade if the database in this apk is newer than
    // the one in the DB_PATH directory. onUpgrade should be called by the system
    // if needed by a call to getWritableDatabase().
    //SQLiteDatabase db_Write = this.getWritableDatabase();

    // Debugging showed that on an upgrade to the database in the apk, that the above
    // call to this.getWritableDatabase() only resulted in a call to onCreate(). No
    // call to onUpgrade was performed. In addition, a subsequent call to
    // getVersion() returned the new DB_VERSION value in the latest apk.
    //
    // The below code manually determines the active database’s version. And if the
    // latest installed database version is greater, it directly calls onUpgrade
    String mPath = DB_PATH + DB_NAME;
    SQLiteDatabase db_Read2 = SQLiteDatabase.openDatabase(mPath, null, SQLiteDatabase.OPEN_READONLY);
    int versionOfActiveDatabase = db_Read2.getVersion();
    db_Read2.close();

    if (DB_VERSION > versionOfActiveDatabase) {
    // Force call to upgrade the database
    // SQLiteDatabase parameter is not used so passing in referenced to closed db not an issue
    onUpgrade(db_Read2, versionOfActiveDatabase, DB_VERSION);
    }
    }

    Hope that helps.

  449. Pingback: Developing Android Apps: Connect to an existing SQLite database « Baking Websites

  450. Ahmed | January 2nd, 2012

    Please, could you link to a working sample project

  451. Pingback: How to retrieve a column from sqlite and store it into an array?

  452. Rakesh | January 4th, 2012

    Hi when I use this path for copying database from assets
    /mnt/sdcard/data/com.testapp/databases/
    or
    /sdcard/data/com.testapp/databases/
    application is giving me SQL Exception
    sqlite3_open_v2(“/mnt/sdcard/data/com.testapp/databases/newDB.sqlite”, &handle, 2, NULL) failed

    But if I use:
    /data/data/com.testapp/databases/ application launches without any errors

    So does this mean that I cannot copy database from assets to SD Card?

    Thanks for your help.

    Regards,
    Rakesh

  453. Joe | January 5th, 2012

    Why go through the hassle of the whole checkDatabase( ) method as is? Why not just use File.Exists( ) to see if the database is already there?

  454. Pingback: Datenbanktabelle existiert nicht - Android-Hilfe.de

  455. Pingback: Android: SQLException – no such table – android metadata | aD@Y D3s!GN .com

  456. Pingback: Android: SQLException – no such table – android metadata | aD@Y D3s!GN .com

  457. Pingback: Android – Populate Listview Using Preloaded Database | Software development support, software risk,bugs for bugs, risk analysis,

  458. Al | January 13th, 2012

    I spent a little while staring at this code and wondering how to make it easier and still use onCreate and onUpdate.

    this is what i did:
    http://nak5.com/post/15727055743/using-your-own-sqlite-database-in-android

  459. Pingback: Populate database data into program android

  460. Pingback: Fastest and most efficient way to pre-populate database in Android

  461. Pingback: Fastest and most efficient way to pre-populate database in Android | Software development support, software risk,bugs for bugs, risk analysis,

  462. kalp | January 24th, 2012

    while executing create table command, i am getting error like ‘SQLite database browser 2.0 b1.exe has stopped working…’
    how to solve this problem???

  463. kamatchi | January 31st, 2012

    i have 200 images in my app.how can i store these images in database and how can i retrieve this image.can u help me sir?

  464. Ram | January 31st, 2012

    hai juan, currently i m doing my final year project.
    my project is related to deaf and dum people.that project i m using sqlite.
    in that i m create a database and store text and its corresponding sign language snapshot.
    i dont know how i m do this .plz give me the idea to develop that task

  465. Jovin | February 1st, 2012

    Great….

  466. Pingback: How to fetch the data from the database by using this demo?

  467. Vordok | February 3rd, 2012

    It didnt help me I make my project using Qt and I can paste and access the db in the emulator without issues, but with the device I have, a samsung gio, I dont have access to move anything to/from /data/data/MY_Project is there any way to do it without rooting the devica.
    Thanks in advance

  468. bala | February 6th, 2012

    hello friends…. i m developing mono android application in visual stdio 2010(c#)….the problem is i dont know how to connect database…. pl give some idea…..

  469. mohsin | February 7th, 2012

    Error while copying the database on device..
    I have tried this example this is working smoothly in emulator but when i am going to test it on device it gives me error while copying the database. I tried to solve this problem by various method’s but i couldn’t find any way to solve this problem..

  470. Pingback: How to retrieve data from database in android Creating database on SqliteBrowser

  471. Vignesh | February 11th, 2012

    Probably this post is too old, but any help is very much appreciated.

    I am using this code and I am experiencing a NullPointerException when

    this.getReadableDatabase(); is called.

    Any hints or pointers towards the error might help.

    Thanks and Best Regards

    Vignesh SK

  472. Vignesh | February 11th, 2012

    I got my previous query resolved, I had actually used

    new DataBaseHelper(null); in my main activity, however changing it to new DataBaseHelper(this) helped me solve the error.

    Thanks and Best Regards

    Vignesh

  473. prash | February 13th, 2012

    Hey it’s a fantastic stuff.

    But i m having problem while fetching data using cursor.
    Giving nullpointerexception for the cursor

  474. Mural | February 15th, 2012

    There may be database incompatibility issues with this method. Another way to create tables and data without writing Java code could be found in this blog http://ingenious-camel.blogspot.com/2012/02/deploy-sqlite-database-on-android.html

    It allows to create database from remote file.

  475. Prasildas | February 16th, 2012

    Can you provide the information, I have my data base value is changing very frequently, I am getting the DB file from an external source, so that I want to put that file to the sd card and access the values from the SD card. In short I don’t want the db file to be stored in to the data/data/pacagename/database, I want the default location of the database to the SD card

  476. Pingback: Simply populate database table names in Android ListView - Tech Forum Network

  477. ryan | February 24th, 2012

    thanks for the tutorial, really helped. i’ve implemented it into my app with working read and write methods.
    When i open the emulator it creates a db in dadt/data/package/database, but when ive pulled it from this and opened it in sqlite browser there is only the metadata table… is this a problem with my db file or the code i’ve followed the instuctions to the letter.

    heres my DBhelper class

    package com.team4german.pro;

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

    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.SQLiteDatabase.CursorFactory;
    import android.database.sqlite.SQLiteException;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.database.sqlite.SQLiteStatement;

    public class DBhelper extends SQLiteOpenHelper{

    //The Android’s default system path of your application database.
    private static String DB_PATH = “/data/data/com.team4german.pro/databases/”;

    private static String DB_NAME = “myDB”;

    private SQLiteDatabase myDataBase;

    private final Context myContext;

    /**
    * Constructor
    * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
    * @param context
    */
    public DBhelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;
    }

    /**
    * Creates a empty database on the system and rewrites it with your own database.
    * */
    public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();

    if(dbExist){
    //do nothing – database already exist
    }else{

    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
    this.getWritableDatabase();

    try {
    this.close();

    copyDataBase();

    } catch (IOException e) {

    throw new Error(“Error copying database”);

    }
    }

    }

    /**
    * Check if the database already exist to avoid re-copying the file each time you open the application.
    * @return true if it exists, false if it doesn’t
    */
    private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){

    //database does’t exist yet.

    }

    if(checkDB != null){

    checkDB.close();

    }

    return checkDB != null ? true : false;
    }

    /**
    * Copies your database from your local assets-folder to the just created empty database in the
    * system folder, from where it can be accessed and handled.
    * This is done by transfering bytestream.
    * */
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

    }

    @Override
    public synchronized void close() {

    if(myDataBase != null)
    myDataBase.close();

    super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long createEntry(String name){
    ContentValues cv = new ContentValues();
    cv.put(“name”, name);

    return myDataBase.insert(“Users”, null , cv);

    }

    //reads from the db, can make a new method to read from different tables, possibly we
    // can add parametres in the future…
    public String getData() {
    // TODO Auto-generated method stub
    String[] columns = new String[] { “_id”, “name”};//array of columns
    Cursor c = myDataBase.query(“Users”, columns, null, null, null, null, null);//ERROR HERE
    //cursor looks through db
    String result = ” “;

    int iRow = c.getColumnIndex(“_id”);
    int iName = c.getColumnIndex(“name”);

    for (c.moveToFirst(); !c.isAfterLast();c.moveToNext())
    {
    result = result + c.getString(iRow) + c.getString(iName) + “\n”;
    }
    return result;
    }

    // Add your public helper methods to access and get content from the database.
    // You could return cursors by doing “return myDataBase.query(….)” so it’d be easy
    // to you to create adapters for your views.

    }

    ————————————————————————–
    and here is the class that tests it

    package com.team4german.pro;

    import java.io.IOException;

    import android.app.Activity;
    import android.app.Dialog;
    import android.content.Context;
    import android.content.Intent;
    import android.database.SQLException;
    import android.database.sqlite.SQLiteDatabase;
    import android.os.Bundle;
    import android.view.View;
    import android.view.View.OnClickListener;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.TextView;
    import android.widget.Toast;

    public class TestSQLite extends Activity implements OnClickListener {

    Button get, update;
    EditText name;
    TextView tName;

    //static Context con = getApplicationContext();

    public void messagemethod()
    {
    Toast.makeText(getApplicationContext(), “fail”, 10000).show();
    }

    protected void onCreate(Bundle savedInstanceState) {

    super.onCreate(savedInstanceState);
    setContentView(R.layout.testdb);
    get = (Button) findViewById(R.id.bGet);
    tName = (TextView) findViewById(R.id.tvName);
    name = (EditText) findViewById(R.id.etName);
    update = (Button) findViewById(R.id.bUpdate);

    DBhelper myDbHelper = new DBhelper(TestSQLite.this);
    SQLiteDatabase db = myDbHelper.getWritableDatabase();
    //myDbHelper = new DBhelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    update.setOnClickListener(this);

    get.setOnClickListener(new View.OnClickListener() {

    public void onClick(View v) {
    // TODO Auto-generated method stub
    //Intent openStartingPoint = new Intent(“com.team4german.pro.TESTVIEW”);
    //startActivity(openStartingPoint);
    /*DataBase dbv = new DataBase(TestSQLite.this);
    dbv.open();
    String data = dbv.getData();
    dbv.close();
    tName.setText(data);*/

    //code to be used with existing db

    DBhelper myDbHelper = new DBhelper(TestSQLite.this);
    SQLiteDatabase db = myDbHelper.getWritableDatabase();
    //myDbHelper = new DBhelper(this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }
    myDbHelper.close();
    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }
    String data = myDbHelper.getData();
    myDbHelper.close();
    tName.setText(data);
    }
    });

    }
    public void onClick(View arg0) {
    // TODO Auto-generated method stub
    switch (arg0.getId()){

    case R.id.bUpdate:

    //for existing db
    DBhelper myDbHelper = new DBhelper(TestSQLite.this);
    SQLiteDatabase db = myDbHelper.getWritableDatabase();
    myDbHelper = new DBhelper(this);

    myDbHelper.openDataBase();
    myDbHelper.createEntry(name.getText().toString());
    myDbHelper.close();

    /*boolean didItWork =true;
    try{
    String nm = name.getText().toString();
    DataBase ent = new DataBase(TestSQLite.this);

    ent.open();
    ent.createEntry(nm);
    ent.close();
    }catch(Exception e)
    {
    didItWork = false;
    }finally{
    if (didItWork)
    {
    Dialog d = new Dialog(this);
    d.setTitle(“YES”);
    TextView tv = new TextView(this);
    tv.setText(“Success”);
    d.setContentView(tv);
    d.show();
    }
    }*/
    break;

    }
    }

    }

    get an error that the table doesnt exist, my methods do work, if i just push the db onto the device it works perfect.
    please help!

  478. vitor | February 26th, 2012

    when I try to query my db I always get this error:

    02-26 05:33:47.098: I/Database(1660): sqlite returned: error code = 1, msg = no such table: cp

    There is no error on db opening but when I query it, it blows. I tried to open my db via adb and i get another error

    unable to open database “base.db”: unable to open database file

    ls data/data/vitor.rosa.md/databases
    sqlite3 base.db
    .tables

    Any ideia?

    Many thanks,
    Vitor.

  479. João | February 28th, 2012

    HI!

    Can anyone please show me how to do a simple query?
    for example: “SELECT name from tabe1 WHERE id=1″

    and then print it in the console???

    thanks in advance.
    Cheers

  480. Waltenio | February 28th, 2012

    How execute a SQLITE file XXXX.sql exported to NetBeans Android project with thousands of INSERTS??

  481. Lam Do | February 29th, 2012

    Hi all,

    Firstly, this is really good tutorial and it saved much time for beginner :) Thanks the author for your sharing :)

    I also got 2 most problems that you guy have been posted here, and tried to read over 400 comments to find the way to make it works. Hope this would be help you guy :)

    Here are 2 most problems and my solution to fix it

    1. sqlite3_open_v2(”/data/data/com.testapp/databases/database.db”, &handle, 1, NULL) failed
    My solution:
    Make sure your database file has extension or not. For my project, the database file is created by SQLite Database Browser and it has NO EXTENSION. it means DB_NAME should not include “.db”.

    2. android.database.sqlite.SQLiteException: no such table: your_own_table , while compiling: SELECT _id, field FROM your_own_table
    My solution
    – modify checkDataBase function:
    String path = DATABASE_PATH + DATABASE_NAME;
    checkDB = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY );

    -modify openDataBase function:
    public void openDataBase() throws SQLException {
    String path = DATABASE_PATH + DATABASE_NAME;
    database = SQLiteDatabase.openDatabase(path, null,
    SQLiteDatabase.OPEN_READWRITE);
    }

    -modify createDataBase function
    public void createDataBase() throws IOException {
    boolean dbExist = checkDataBase();
    if (dbExist) {
    // do nothing – database already exist
    } else {
    this.getReadableDatabase();
    this.close();
    try {
    this.close();
    copyDataBase();
    } catch (IOException e) {
    throw new Error(“Error copying database”);
    }
    }
    }

  482. Kojoe | March 1st, 2012

    vitor
    How large is your database?
    I was having this problem because my database was 2.3Mb and the copydatabase method threw an exception without my knowledge when it tried to write the data. If you step through it with the debugger you will probably see the same.
    Basically, If the db is over 2MB, you will have to rename the extension from .sqlite to something else like .mpg. That worked for me.

  483. kishu | March 1st, 2012

    Going by this approach, let’s say we have created methods to add a new row in the DB. How would you suggest getting a last_insert_rowid?

    should “Select last_insert_rowid()” would work fine?

  484. bjay | March 2nd, 2012

    hòla Juan,

    thank you for this post. seems like everyone loves it :)

  485. ryan | March 3rd, 2012

    ive tried with no luck my db is only 3000kbs just using it to test functionallity, could there be a problem with creating the table, this is my creation statement

    DBhelper myDbHelper = new DBhelper(TestSQLite.this);
    SQLiteDatabase db = myDbHelper.getWritableDatabase();

    myDbHelper = new DBhelper(TestSQLite.this);

    try {

    myDbHelper.createDataBase();

    } catch (IOException ioe) {

    throw new Error(“Unable to create database”);

    }

    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }
    myDbHelper.close();
    try {

    myDbHelper.openDataBase();

    }catch(SQLException sqle){

    throw sqle;

    }
    String data = myDbHelper.getData();
    myDbHelper.close();
    tName.setText(data);
    }

  486. ryan | March 3rd, 2012

    get an error when i do it like this:

    DBhelper myDbHelper = new DBhelper();
    SQLiteDatabase db = myDbHelper.getWritableDatabase();

    myDbHelper = new DBhelper(this);

  487. utsav | March 6th, 2012

    how to display in alertdailog

  488. Michael | March 8th, 2012

    Thanks you very much for your tutorial, it helped me out loads, as I’m quite new to android and databases in general. THUMBS UP!

  489. Pingback: Android 开发中,如何将 SQLite 和 APK 一起打包发布(附 Android 上的天气预报项目) | akasuna.com

  490. Pingback: Android – proper place to put my Sqlite3 database in Eclipse?

  491. Raj | March 20th, 2012

    Hi,

    I am using phonegap to build my application, will i still be able to use the above code? If so how would i be able to implement this within my actual app (javascript)?

    Finally, Will i be able to use the same code for a Blackberry version?

    Thanks,

  492. pk | March 22nd, 2012

    @ ryan :

    write like this

    DBhelper myDbHelper = new DBhelper()this;

    SQLiteDatabase db = myDbHelper.getWritableDatabase();

  493. pk | March 22nd, 2012

    @ ryan :

    write like this

    DBhelper myDbHelper = new DBhelper(this);

    SQLiteDatabase db = myDbHelper.getWritableDatabase();

  494. halilenver | March 23rd, 2012

    I do not know why but it works in some phones and does not work in others.
    i have a 400kb prefilled .sqlite file.
    I used same codes but it does not work in some phone. please help me

    here is my log
    03-22 20:52:49.401 I/Database( 2487): sqlite returned: error code = 11, msg = database corruption at line 46886 of [42537b6056]
    03-22 20:52:49.401 I/Database( 2487): sqlite returned: error code = 11, msg = database disk image is malformed
    03-22 20:52:49.401 I/FFLOG ( 2487): FF_CORRUPTION:database disk image is malformed: , while compiling: SELECT Names FROM MemberList WHERE PageNo=1

  495. zak666 | March 23rd, 2012

    hi every bady i have an application to do it consists of three button that displays three different list each Ellement of the list displays an image found in a database with sqlite first establishes please how i can do this help me

  496. Pingback: android 数据库打包发布 | Android同路人

  497. Pingback: cant able to collect data from a .db file of SQLite in android

  498. Ibad | March 31st, 2012

    Hi, I’m having a problem with the code.. in the LogCat it says “no such table:android_metadata” but SQLite browser android_metadata is there.. what could b the prob here? plz suggest

  499. Pingback: Android, SQLite and a few Problems | banani.de

  500. LordMarty | April 1st, 2012

    Hi,
    I have the same problem as many above.
    First time it’s run I get the error “sqllite returned: error code = 14, msg = cannot open file at line…”
    Then it’s force-closed. Next time I open it I get the error “no such table..”
    I’ve tried every solution posted, still no luck..
    Did anyone figure this out?

    I have a feeling that the database file doesn’t get copied as it should.

    *I’ve tried to see what the android_metadata contains, and it only gives me the phones “locale”, not what I typed in the table myself.*

    I’m stuck for now. Hope someone can help a poor bastard.. ;)

  501. John Nugent | April 1st, 2012

    Great tutorial, but Is there a way to set foreign keys?

  502. LordMarty | April 2nd, 2012

    A few hours of hate, a break, and a few more hours made it work.
    I used the code from this guy and that did it:
    http://stackoverflow.com/questions/2387421/how-to-use-my-own-sqlite-database

  503. dilemma | April 2nd, 2012

    Does anyone know how to do set the foreign keys
    for example i have got 4 tables. these are “category, y, m, ym”.

    CREATE TABLE “Categories” (“category_id” INTEGER PRIMARY KEY NOT NULL, “CategoryName” TEXT, check (typeof (“CategoryName”) = ‘text’))

    CREATE TABLE “y” (“y_id” INTEGER NOT NULL PRIMARY KEY,
    “yName” TEXT, “yEx” TEXT, “yImage” BLOB,
    “Category_id” INTEGER,
    FOREIGN KEY (category_id) REFERENCES Category (category_id))

    CREATE TABLE “m” (“m_id” AUTOINCREMENT INTEGER NOT NULL PRIMARY KEY, “mname” TEXT)

    CREATE TABLE “ym” (“m_id” INTEGER NOT NULL, “y_id” INTEGER NOT NULL, “Comment” TEXT,
    PRIMARY KEY (“m_id” “y_id”))

    if I change all the primary key fields as _id , How can i set foreign key fields and composite key fields

  504. kodeks pracy | April 3rd, 2012

    THanks For the tutorial… i just use it.

  505. saeed | April 7th, 2012

    thanksssss, i just used it tooo! :)

  506. Joe | April 8th, 2012

    Hello all, I had the same table not found problem. I realized the issue was a problem with my database (using phpmyadmin to create a sql database instead of sqllite ) hope that helps

  507. Debby@reigndesign.com | April 9th, 2012

    What if my database (like millions of others) only needs to be read-only?

  508. Pingback: How to access an existing sqlite database in Android? | Android Development tutorial | Android Development tutorial

  509. Aiden fry | April 18th, 2012

    Thanks for this! Great lil example proves to be very useful!

  510. ramizmoh | April 19th, 2012

    @dilemma: Please have a look at
    http://www.codeproject.com/Articles/119293/Using-SQLite-Database-with-Android
    on how to enforce referential integrity (foreign key constaints)

  511. Pingback: Creating database from SQLite file in Android | android പള്ളികൂടം

  512. NF25 | April 23rd, 2012

    Cool! 1st sqlite example that I successfully copy pasted AND worked

  513. Pingback: Reading from a pre-populated database file for android

  514. shmoolca | April 25th, 2012

    Many thx, GR8 stuff!!!

  515. rahul | April 25th, 2012

    hello sir,,, i hv following prblm in switching from pages

    package co.research;

    import android.app.Activity;
    import android.content.Context;
    import android.content.Intent;
    import android.os.Bundle;
    import android.widget.Button;
    import android.view.View;
    import android.view.View.OnClickListener;

    public class ResearchActivity extends Activity {
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    addListenerOnButton();
    }

    public void addListenerOnButton() {

    final Context context = this;

    Button button1 = (Button) findViewById(R.id.btn_reg);
    Button button2 = (Button) findViewById(R.id.btn_submit);

    button2.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View arg0) {

    Intent intent = new Intent(context,register.class);
    startActivity(intent);
    }
    });

    button1.setOnClickListener(new OnClickListener() {

    @Override
    public void onClick(View arg0) {

    Intent intent = new Intent(context,success.class);
    startActivity(intent);
    }
    });
    }
    }

    the prblm is both button not working properly means at time of switching.. plz help so dat both button work properly

  516. Hylus | May 1st, 2012

    My app used to crash in emulator when I tried to read data from the database (no such table error). I tried renaming the database and the table etc. no luck. I eventually fixed it by making a new database. The problem was that I had been using the same database in another app, and I hadn’t uninstalled that app from the emulator. I thought I had, but I hadn’t. When I finally uninstalled the earlier app, both the old and new databases seemed to work. Hope that helps some of you. Cheers, Hylus

  517. Mark | May 2nd, 2012

    NO SUCH TABLE FIXED.

    I spent a long time on this one, but as someone else mentions – the fix is here.

    http://stackoverflow.com/questions/2387421/how-to-use-my-own-sqlite-database

  518. Pingback: Android l10n / i18n database. Is the table android_metadata intended for this? | Build Future Repository

  519. Tomas | May 6th, 2012

    Hi,

    I used to get same ERROR as some of you :

    SqliteDatabaseCpp(5800): sqlite3_open_v2(“/data/data/com.example.example/databases/”DB_NAME”.db”, &handle, 1, NULL) failed

    SQLiteDatabase(5800): Failed to open the database. closing it.

    It happened due to bad location of database file. The exact route to place your file is: “YOUR_DRIVE”:\..\workspace\”YOUR_PROJECT”\assets\”YOUR_DB_NAME”

    To verify that tables and data are in place run:
    [code]
    adb shell
    cd /data/data/"YOUR_PROJECT"/databases/
    sqlite3 "YOUR_DB_NAME"
    .tables //there should be your tables
    // then just type
    SELECT * FROM "YOUR_TABLE_NAME";
    //and the data should be present
    [/code]

    This is why these errors occured to me and how I solved it, hope that this help some of you

    btw. the errors are shown in my logcat, but copyDataBase() works, just check yourself

  520. ano | May 7th, 2012

    is it the same for an application in eclipse RCP? not an android application

  521. J-Mic | May 8th, 2012

    I keep getting a failed to open the database errors. “sqlite3_open_v2(“/data/data/com.testapp/databases/database.db”, &handle, 1, NULL) failed”. I have tried this on both .db and .db3 files and neither of them are working. I followed you tutorial and placed the db in the assets directory, both in a folder called databases and just in the directory. Is there something else I am doing wrong

  522. Pingback: using your own .sqlite database android « Hilmi Oğuz

  523. Pingback: using your >1mb (bigger than 1 mb) .sqlite database android « Hilmi Oğuz

  524. Rob | May 10th, 2012

    Thank you so much for this. This helped me out tremendously, however I still wonder if this is the only way.

    Seems ridiculous that you need to carry two files on the .apk in order to utilize a pre-made database.

    Cheers.

  525. Varun | May 10th, 2012

    Thank u very much for this! It proved to be very useful!

  526. Andrew Tipton | May 12th, 2012

    I’ve just started developing for Android, and was surprised to discover that bundling a static database is *not easy to do*. So I did the only reasonable thing: created a library which does just that. Example usage:

    import android.database.sqlite.SQLiteDatabase;
    import kiwidrew.staticdb.StaticDatabase;

    public class BlahBlah extends Activity {
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    SQLiteDatabase db = StaticDatabase.openDatabase(this, “assets/foobar.db”);
    // Do normal database stuff with ‘db’…
    }
    }

    You get back a standard SQLiteDatabase object, with the only restriction being that it doesn’t support writing. (Obviously!)

    Note that this will fail unless the database is stored in your .apk without compression. Add the sqlite database using the `aapt -0` command or modify your `build.xml` to pass the “ flag to the “ tag…

    **Get the code at http://bitbucket.org/kiwidrew/android-staticdb.**

    *Note: I’ve literally just finished writing this, and have only done very basic testing so far. Bug reports would be appreciated!*

  527. Jef Martens | May 16th, 2012

    The above code is great, however it requires your database to be prepared to be used.

    My data came from a PHP/MySQL combination and required some updates. The first indication something was wrong, was the error:
    sqlite returned: error code = 14, msg = cannot open file at line 25467 or 27701 or the like.

    The workaround was looking at the data again – I found two issues:
    The table structure created columns with integers – int(11) in my case. The introduction picture shows that particular columns such as the _id column are created with INTEGER instead of int(##). Changing this solved the error code 14 at line 25467.

    Furthermore, another issue was found with the other error code 14 at line 27701 – this was in the database itself.
    Some entries had brackets, i.e.: “name (description)” which rendered issues creating sql exports with SQLite database browser, making wrong SQL statements. I tried to escape the brackets, however to no availl: “name \(description\)”. The final solution is to check your content in the database and make sure that it has no special characters in it, used by SQL.

    The hint comes from:
    http://stackoverflow.com/questions/5388855/android-sqlite-returned-error-code-14

  528. Pingback: How to access an existing database : android application?

  529. Lourembam Lenin | May 19th, 2012

    This is really awesome tutorial. I was looking every possible way to read external database in android project. Thanks a lot for this tutorial.

  530. Pingback: Android SQLite Database: Creating db at runtime or using prepopulated db? | PHP Developer Resource

  531. Pingback: Installation of an android application with a sqlite database in a HTC phone from a PC running Ubuntu OS | PHP Developer Resource

  532. Pingback: Fastest and most efficient way to pre-populate database in Android | PHP Developer Resource

  533. Eric | May 29th, 2012

    When I try to use it, I get:

    Syntax error on token “;”, { expected after this token

    at the end of

    DataBaseHelper myDbHelper = new DataBaseHelper();

  534. Eric | May 29th, 2012

    Nevermind…. paste error. :(

  535. Ischci | May 30th, 2012

    Hi Guys

    I have to create a app with multiple tables but i need to filter them and i really don’t know how to do it. I have to display the data in 7 columns and the number of rows is dependent on the data in the tables and when u click on e.g. students in the list it has to bring up a dialog to update the students details.

    Cheers
    Ischci

  536. Pingback: How to retrieve a column from sqlite and store it into an array? | PHP Developer Resource

  537. sam | June 3rd, 2012

    hi please check this link…

    http://stackoverflow.com/questions/10784992/display-db-content-in-gridview

    im not able to display sqlite DB contents on the GRIDVIEW…. there r no errors in the code but when i run my app the emulator shows “Force Close”…
    please help im stuck…

  538. hanaa | June 4th, 2012

    what if i had a foreign key, can i use sqlite database browser in this case!!

  539. Zardaloop | June 5th, 2012

    HI Guys,

    I have finally figured out why my table does not fully copy to the application.

    I was keep getting this error : android.database.sqlite.SQLiteException: no such table:

    simply follow these steps:
    1- right click on the database you would like to transfer, click on the properties(in windows) or on info ( in mac) then right don the size in byte.

    2- then simply change this like to the file size:
    byte[] buffer = new byte[1024];

    foe example change 1024 —> 4096

    Thats solved my issue I hope that fix the issue for everyone elses :)

    Stay Cool, Stay awesome …

  540. Érica Hidemi Mitsuishi | June 6th, 2012

    how can I make a simple query? when I tried, the log warned “no such table: mytable”.

    The query method in DataBaseHelper class is this.

    public Cursor consultarMet() throws SQLException {
    return myDataBase.query(TB_MET, null, null, null, null, null, null);
    }

  541. NewB | June 11th, 2012

    Hi all,
    Those who r getting “no such table” error
    check database through DDMS….
    nd dont forget to put .db extension to database name…
    I got this error first but when i changed name and give .db extension it worked for me. ..

  542. Avik | June 13th, 2012

    Thank u so much i had the same error before: could not find table.. just found that initially i didn’t create the table android_metadata inside my db. then i cleaned existing data from Setting > Manage Application and run again, this time all perfect :)
    thanks u sooo much :)

  543. Xin Liang | June 13th, 2012

    This tutorial and all community are very helpful and excellent.
    if you are going to use your own database built already via third party apps like Navicat, this is very right entry point.

    There is only one issue here.
    if the database file size is larger than 1MB, you can’t your db file into the assets folder of the project.
    you should put your own db file into the /res/raw and rename that because Android compresses the db file (this is the issue of copyDatabase).

    Thanks Fuluxa.

  544. Chirag | June 14th, 2012

    thnks CRASH finally i am able to upgrade my database…
    just added this code in my createDataBase function

    if (checkDataBase()){
    String myPath = DB_PATH + DATABASE_NAME;
    SQLiteDatabase db_Read = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    int versionOfActiveDatabase = db_Read.getVersion();
    db_Read.close();

    if (DATABASE_VERSION > versionOfActiveDatabase) {
    context.deleteDatabase(DATABASE_NAME);
    this.getReadableDatabase();
    try{
    copyDataBase();
    }
    catch (IOException e) {
    throw new Error(“Error In COPYING OF DB”);
    }
    }
    }

  545. Simon | June 20th, 2012

    Nice tut …… For me Every thing works fine in the emulator but when i install the app in the phone (HTC Desire S ) the app crashes giving an error
    “The application androidz (process com.me.androidz) has stopped unexpectedly.Please try again”
    I suspect that it is the database coz when i input values manually via the create and insert code the app does well. Any help will highly be appreciated.

  546. Allen | June 22nd, 2012

    Thanks for Juan’s posts, it is very helpful to me.

    I have one questions:

    Is there any chance to convert an exist database to a new one that enabled FTS (Full Text Search)?

    Thanks a lot!!!

  547. isiah | June 27th, 2012

    I got a this error in my log cat

    06-27 14:43:37.826: E/AndroidRuntime(14579): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.frux.kfcmobile/com.frux.kfcmobile.KFCmobileActivity}: android.database.sqlite.SQLiteException: Can’t upgrade read-only database from version 0 to 1: /data/data/com.frux.kfcmobile/databases/data

    can anyone help me?

  548. isiah | June 27th, 2012

    I still got this error
    even I just follow the tutorial

    06-27 16:18:43.097: E/AndroidRuntime(20925): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.frux.kfcmobile/com.frux.kfcmobile.KFCmobileActivity}: android.database.sqlite.SQLiteException: near “/”: syntax error

  549. isiah | June 28th, 2012

    Now it works perfectly thanks for this great tuto.

  550. John | June 29th, 2012

    Stupid question, perhaps, but I’ve been stumped for a while on this now….

    How can I execute a custom querty (ex: SELECT * FROM table)?

    Do I need to make a new method in the DatabaseHelper class or just call a method from my main class?

  551. Simone | June 30th, 2012

    i’ve solved the crash of IO Error and Database not found with this fix:
    private static String DB_PATH = “”;

    and:

    public dbHelper(Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext = context;
    //fix
    DB_PATH=myContext.getExternalFilesDir(null).toString();
    }

  552. Simon | July 2nd, 2012

    Hey
    this is the best example in the whole internet! ;)
    but i have a question:
    i copied the first code (the long) my db is copied in the right path, but how i could return a cursor? is it enough when i writ the query aufter “dd your public helper methods to access and get content from the database.
    // You could return cursors by doing “return myDataBase.query(….)” so it’d be easy
    // to you to create adapters for your views.” or what kind of public methods do i have to implement? (it is a database with 3 tables and i only want to read the data!

    thanks Simon

  553. Pingback: 如何在android中使用你自己的数据文件 – 紫辉很忙 | 南龙的小站

  554. Toby | July 5th, 2012

    Wow, you know why so many people get the table not found errors. It is pretty simple. If you start the application up the first time with say only one table in it to see if it runs, and then come along after and try to add in your new tables it isn’t going to work. Look at the start of this method from the sample:

    public void createDataBase() throws IOException
    {
    boolean dbExist = checkDataBase();

    if(dbExist)
    {

    so what that means, is if the database already is installed on the device then skip everything else which means your database will never get recopied from your res file onto your device after the first time.
    There are a bunch of fixes that would resolve this the very simpest (not best) is do this while you are building your app in test mode:
    public void createDataBase() throws IOException
    {
    boolean dbExist = checkDataBase();

    if(!dbExist)
    {
    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
    this.getReadableDatabase();

    // close up the database after we have created it.
    this.close();
    }

    try
    {
    copyDataBase();
    }
    catch (IOException e)
    {
    throw new Error(“Error copying database”);
    }
    }

  555. Moses Ocela | July 8th, 2012

    thanx for the post it helped me a lot

  556. Raymond | July 21st, 2012

    I’ve followed the tutorial but keep getting an error in Logcat: “failed to open the database. Closing it”.

    The database is on my phone, I checked with a root explorer.

    Anyone who has a solution?

  557. Rayner | July 23rd, 2012

    I’m have the no such table error, does anyone have a solution?

    Code:

    package com.myevents.onsiteregistration;

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;

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

    public class DataBaseHelper extends SQLiteOpenHelper{

    //The Android’s default system path of your application database.
    private static String DB_PATH = “/data/data/com.myevents.onsiteregistration/databases/”;
    private static String DB_NAME = “onSiteRegistration.sqlite”;

    private static final String DB_TABLE = “Delegates”;

    public static final String KEY_ROWID = “_id”;
    public static final String KEY_ID = “ID”;
    public static final String KEY_F_NAME = “First_Name”;
    public static final String KEY_L_NAME = “Last_Name”;
    public static final String KEY_EMAIL = “Email”;
    public static final String KEY_COUNTRY = “Country”;
    public static final String KEY_COMPANY = “Company”;
    public static final String KEY_DATE_TIME = “Date_Time_Stamp”;
    public static final String KEY_PAY_STATUS = “Payment_Status”;
    public static final String KEY_CHECK_IN = “Check_In_Status”;

    private SQLiteDatabase myDataBase;
    private DataBaseHelper myHelper;
    private final Context myContext;

    /**
    * Constructor
    * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
    * @param context
    */
    public DataBaseHelper(Context context) {

    super(context, DB_NAME, null, 1);
    this.myContext = context;
    }

    /**
    * Creates a empty database on the system and rewrites it with your own database.
    * */
    public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();

    if(!dbExist)
    {
    //By calling this method and empty database will be created into the default system path
    //of your application so we are gonna be able to overwrite that database with our database.
    this.getReadableDatabase();

    // close up the database after we have created it.
    this.close();
    }

    try
    {
    copyDataBase();
    }
    catch (IOException e)
    {
    throw new Error(“Error copying database”);
    }
    }

    /**
    * Check if the database already exist to avoid re-copying the file each time you open the application.
    * @return true if it exists, false if it doesn’t
    */
    private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){

    //database does’t exist yet.

    }

    if(checkDB != null){

    checkDB.close();

    }

    return checkDB != null ? true : false;
    }

    /**
    * Copies your database from your local assets-folder to the just created empty database in the
    * system folder, from where it can be accessed and handled.
    * This is done by transfering bytestream.
    * */
    private void copyDataBase() throws IOException{

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
    myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

    }

    public void openDataBase() throws SQLException{

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myHelper = new DataBaseHelper(myContext);
    myHelper.close();
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    @Override
    public synchronized void close() {

    if(myDataBase != null)
    myDataBase.close();

    super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    try {
    createDataBase();
    } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(“DROP TABLE IF EXISTS” + DB_TABLE);
    onCreate(db);
    }

    // Add your public helper methods to access and get content from the database.
    // You could return cursors by doing “return myDataBase.query(….)” so it’d be easy
    // to you to create adapters for your views.
    public String getData() {
    String [] columns = new String [] {KEY_ROWID, KEY_ID, KEY_F_NAME, KEY_L_NAME, KEY_EMAIL, KEY_COUNTRY, KEY_COMPANY, KEY_DATE_TIME, KEY_PAY_STATUS, KEY_CHECK_IN};
    Cursor c = myDataBase.query(DB_TABLE, columns, null, null, null, null, null);
    String result = ” “;
    int iRId = c.getColumnIndex(KEY_ROWID);
    int iId = c.getColumnIndex(KEY_ID);
    int iFName = c.getColumnIndex(KEY_F_NAME);
    int iLName = c.getColumnIndex(KEY_L_NAME);
    int iEmail = c.getColumnIndex(KEY_EMAIL);
    int iCountry = c.getColumnIndex(KEY_COUNTRY);
    int iCompany = c.getColumnIndex(KEY_COMPANY);
    int iDateTime = c.getColumnIndex(KEY_DATE_TIME);
    int iPay = c.getColumnIndex(KEY_PAY_STATUS);
    int iCheckIn = c.getColumnIndex(KEY_CHECK_IN);

    for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
    result = result + c.getString(iId) + ” ” + c.getString(iFName) + ” ” + c.getString(iLName) + “\n” + c.getString(iCompany) + ” ” + c.getString(iCountry) + “\n ” + c.getString(iEmail) + “\n” + c.getString(iPay) + ” ” + c.getString(iCheckIn) + ” ” + c.getString(iDateTime) + “\n\n”;
    }
    return result;
    }

    }

    Need help asap!! Thanks in advance

  558. Pingback: [REQUEST] Simple SQLite app - Android Forums

  559. Pingback: Writing to pre-existing SQLite Database Android

  560. Rich | July 26th, 2012

    Does anyone have a zip file of this project in working order?

  561. nitin | July 26th, 2012

    Hi, i make database using SqliteBrowser. Now i want to alter a table. I want to add a column in a table. can anyoneone suggest me how it is possible.

  562. Davide | August 1st, 2012

    Hey, thanks for this tutorial. i’ve used it and it works fine.
    But i have a BIG problem: i use this database in an app with google maps (and so with google api and google api 10 emulator)
    in this situation, the app crash because of the database! what the hell is the problem? if i use a normal api and a normal emulator it works but obviously the map don’t.
    I need you help!

  563. Pingback: What is wrong with this simple java code to access SQLite database in Android? - feed99

  564. Alejandro | August 12th, 2012

    I have a question, Im trying to do a multilanguage application and that includes a multilanguage database. I can notice that when you create the android_metadata you add a field called “locale”and in there you add a record called “en_US”. My question is: can that table help me to add multilanguages to the database, even though i have to add all the information or anything?, I mean does that help the android device to recognize what language you are using and depending on that use the information of the language? Thank you. Great information about databases by the way.

  565. Pingback: New approach to SQLite / Skype with Matthias « Lloyd's Project

  566. Pingback: New approach to SQLite / Skype with Matthias « Lloyd's Project

  567. Milja | September 4th, 2012

    Thanks a lot for your article .

    can you help me too add a progressDialog for createDataBase ??

  568. jatin patel | September 5th, 2012

    The project worked for me after making 2 changes.

    1. Debugging application in emulator. In DDMS perspective, i have inserted database manually.

    Thanks Juan for your help.

  569. Thomas | September 5th, 2012

    Thanks for this. I am trying to encrypt the database using http://guardianproject.info/code/sqlcipher/ .. SQL Cipher for Android. Is this possible with this method please? THanks

  570. Pingback: openingjars.co.uk » Romantic Messenger

  571. hareshj | September 10th, 2012

    i want to upgrade my pre-build database with new table and columns,docreate new database with new tables and columns and call it from asset folder again?.or is there any way to update the database using onUpgrade() …if i create a new database i will lose my data in old database which i dont want ..how can i upgrade my database without losing my olddata…

  572. AndreaF | September 12th, 2012

    This code is wrong or unclear… I have followed all the steps but I get the error “unable to open database file”

    http://stackoverflow.com/questions/12387715/error-when-i-try-to-open-sqlite-database-pre-created-in-the-assets-folder

  573. Sheldon Goldberg | September 15th, 2012

    Hi – In checkDatabase = checkDB comes back true, but
    none of my tables can be found in queries.
    Please help.
    Thanks, Sheldon

  574. joj | September 15th, 2012

    Thanks for this simple post. Solved my problem perfectly!

  575. Simon | September 16th, 2012

    NO SUCH TABLE SOLUTION?
    Hey guys,
    After much messing about I found that by removing the .sqlite suffix from the file name my app recognised the file. Windows complains but android doesn’t.
    So if your database is saved as myDB.sqlite on your computer, save it as myDB in the assets file.
    Hope that helps :)

  576. naina | September 25th, 2012

    Can you plz guide me through the steps to insert the web page data into a database ???

  577. abhishek | September 25th, 2012

    hey ,i am using the codes above,but i am getting the error .
    java.lang.runtime exception
    can’t instantiate the class DatabaseHelper ,,no empty constructor.
    why this is happening ?
    plz help .this problem is killing me

  578. abhishek | September 28th, 2012

    and where should i put new instance of this DataBaseHelper class and call the createDataBase() and openDataBase() methods.

  579. Pingback: Using your own SQLite database in Android applications « Cungkringblog

  580. mirko | October 3rd, 2012

    Why did you create a method “createDataBase()” instead of placing the code in “onCreate()” ??

  581. Tausif | October 13th, 2012

    This is a great tutorial, but in my case it can copy only android_metadata table not others. How can I get others tables with their data?

  582. Lino Barreca | October 19th, 2012

    for those with the “database not found” when creating the database….
    You just have to ensure the db path is existing…

    private boolean checkDataBase()
    {
    SQLiteDatabase checkDB = null;

    try
    {
    String myPath = DB_PATH + DB_NAME;

    File DBFolder = new File(DB_PATH);
    if (!DBFolder.exists()) DBFolder.mkdir();

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

    ……

  583. Pingback: Android Programlama Öğrenmek için Faydalı Kaynaklar | Emin Can Sümer

  584. Pingback: Connecting to an existing SQLite DB - feed99

  585. Andrea | October 22nd, 2012

    Good Tuts! I’m thinking about a question, this way is not dangerous for security of data?
    The assets remain clearly readable in the apk!But after installation?
    This would mean that no sensible data can be insert in this DB. It’s correct?
    Is’t this database cripted?

  586. Maria | October 26th, 2012

    I have implemented this code but there is a small problem ! when i modify the database i can’t see the changes ( as if the db was static !) any solutions please?

  587. Don | November 1st, 2012

    Very good stuff, but why do I keep finding programmers writing code like this:

    if(dbExist){
    //do nothing – database already exist
    }else{
    }

    I’ve never understood that?

  588. Cah | November 2nd, 2012

    I have two table in my DB, but i can not access the information that way, presents error “NullPointerException”…. Somebody did get to access the tables in the db and show the information?

  589. Cah | November 2nd, 2012

    I debug my code and it’s myDataBase that is null, somebody can help me?

  590. aftab | November 7th, 2012

    No Xml file is defind for mainActivity plz help me

  591. Navid | November 12th, 2012

    very well thans mate

  592. Pingback: Android SQLite database seems to clear every time i open a n | Android Development tutorial | Android Development tutorial

  593. BobV | November 20th, 2012

    Thanks a bunch for the instructions. Worked like a charm, except that I had my head in the nether regions and got the database name wrong the first time that I tried it. As soon as I learned how to spell, there was no problemo.

  594. davidjcd | November 26th, 2012

    Thomas: This method is invalid when you use SQLCipher, because the structure of both files are diferents (the final one is encripted).

  595. ariefspekta | November 26th, 2012

    Wow.. Work for me..
    Glenn is a solution for force close.. Thanks dude!

  596. BobV | December 3rd, 2012

    I got elated over my success far too soon. Everything functions fine on the Nexus 7 emulation, but not on the device itself. I get an sqlitecantopendatabaseexception when I install using adb, then try to run it.

  597. Lee Nolasco | December 3rd, 2012

    Thanks for this tutorial! But how can i make it over write my existing database? I got a situation where i edited my database from sqlitebrowser and when i re-run the project the database is not updated.

  598. Pingback: Application database related activity works in emulator but not working in device : Android Community - For Application Development

  599. Pingback: Overwrite existing database : Android Community - For Application Development

  600. Pingback: Using Existing SQLite Database with android : Android Community - For Application Development

  601. Pingback: Using Database in android application : Android Community - For Application Development

  602. Jayant Shinde | December 11th, 2012

    Hi Juan,
    I am trying to draw a bar chart taking the values from a database. and i need to refresh the bar chart every 1 minute and redraw with new values if any. I am new to android so any help would be greatly appreciated. I am using achartengine to draw the bar chart right now but i am passing static values.

  603. Ala | December 11th, 2012

    Will how can I display the data from my database in Listview
    Can anyone help me?

  604. Renato Chea | December 13th, 2012

    For the :sqlite3_open_v2 ERROR:
    In your CreateDatabase put

    try {
    this.getReadableDatabase();
    this.close();
    copyDataBase();

    }

    in your checkDatabase()

    File dbFile = new File(DB_PATH+DB_NAME);
    return dbFile.exists();

  605. Pingback: how to embed the sqlite-database-file in android app : Android Community - For Application Development

  606. coder | December 22nd, 2012

    hey! thank you for your code, it will be very useful for me, but the current problem I’m facing is how to move the database from the device (where I have created it) to assets folder. Any hint would be appreciated.
    Thank u

  607. Goutam | December 29th, 2012

    i had done the database creation process as shown above .The database is creating but when from my activity i want to get some data from the table the cursor return null.If the code require let me know.
    Thanks in advance

  608. Goutam | December 29th, 2012

    I had the error while applying the above all process

    12-29 12:54:14.335: E/SqliteDatabaseCpp(657): sqlite3_open_v2(“data/data/com.goutam.projecttauky/databases/Tauky_DB”, &handle, 2, NULL) failed
    12-29 12:54:14.355: E/SQLiteDatabase(657): Failed to open the database. closing it.
    12-29 12:54:14.355: E/SQLiteDatabase(657): android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1013)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:986)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:962)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.goutam.projecttauky.DataBaseHelper.DBExists(DataBaseHelper.java:92)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.goutam.projecttauky.DataBaseHelper.createDB(DataBaseHelper.java:48)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.goutam.projecttauky.DataBaseHelper.createDatabase(DataBaseHelper.java:37)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.goutam.projecttauky.HomeActivity.creatdatbase(HomeActivity.java:107)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.goutam.projecttauky.HomeActivity.onCreate(HomeActivity.java:29)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.Activity.performCreate(Activity.java:4465)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1920)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1981)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.ActivityThread.access$600(ActivityThread.java:123)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1147)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.os.Handler.dispatchMessage(Handler.java:99)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.os.Looper.loop(Looper.java:137)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at android.app.ActivityThread.main(ActivityThread.java:4424)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at java.lang.reflect.Method.invokeNative(Native Method)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at java.lang.reflect.Method.invoke(Method.java:511)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
    12-29 12:54:14.355: E/SQLiteDatabase(657): at dalvik.system.NativeStart.main(Native Method)
    12-29 12:54:14.355: W/System.err(657): android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file
    12-29 12:54:14.355: W/System.err(657): at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
    12-29 12:54:14.355: W/System.err(657): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1013)
    12-29 12:54:14.366: W/System.err(657): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:986)
    12-29 12:54:14.366: W/System.err(657): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:962)
    12-29 12:54:14.375: W/System.err(657): at com.goutam.projecttauky.DataBaseHelper.DBExists(DataBaseHelper.java:92)
    12-29 12:54:14.375: W/System.err(657): at com.goutam.projecttauky.DataBaseHelper.createDB(DataBaseHelper.java:48)
    12-29 12:54:14.375: W/System.err(657): at com.goutam.projecttauky.DataBaseHelper.createDatabase(DataBaseHelper.java:37)
    12-29 12:54:14.375: W/System.err(657): at com.goutam.projecttauky.HomeActivity.creatdatbase(HomeActivity.java:107)
    12-29 12:54:14.375: W/System.err(657): at com.goutam.projecttauky.HomeActivity.onCreate(HomeActivity.java:29)
    12-29 12:54:14.375: W/System.err(657): at android.app.Activity.performCreate(Activity.java:4465)
    12-29 12:54:14.375: W/System.err(657): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1049)
    12-29 12:54:14.385: W/System.err(657): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1920)
    12-29 12:54:14.385: W/System.err(657): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1981)
    12-29 12:54:14.385: W/System.err(657): at android.app.ActivityThread.access$600(ActivityThread.java:123)
    12-29 12:54:14.385: W/System.err(657): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1147)
    12-29 12:54:14.385: W/System.err(657): at android.os.Handler.dispatchMessage(Handler.java:99)
    12-29 12:54:14.385: W/System.err(657): at android.os.Looper.loop(Looper.java:137)
    12-29 12:54:14.385: W/System.err(657): at android.app.ActivityThread.main(ActivityThread.java:4424)
    12-29 12:54:14.385: W/System.err(657): at java.lang.reflect.Method.invokeNative(Native Method)
    12-29 12:54:14.385: W/System.err(657): at java.lang.reflect.Method.invoke(Method.java:511)
    12-29 12:54:14.385: W/System.err(657): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:784)
    12-29 12:54:14.385: W/System.err(657): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:551)
    12-29 12:54:14.395: W/System.err(657): at dalvik.system.NativeStart.main(Native Method)
    12-29 12:54:14.395: V/db log(657): database not found
    12-29 12:54:14.666: I/dalvikvm(657): threadid=3: reacting to signal 3
    12-29 12:54:14.686: I/dalvikvm(657): Wrote stack traces to ‘/data/anr/traces.txt’
    12-29 12:54:15.095: I/dalvikvm(657): threadid=3: reacting to signal 3
    12-29 12:54:15.115: I/dalvikvm(657): Wrote stack traces to ‘/data/anr/traces.txt’
    12-29 12:54:15.437: V/db log(657): DATABASE CREATED
    12-29 12:54:15.445: V/folder log(657): FOLDER CREATED

  609. Pingback: Android in-memory value pairs vs. SQLite : Android Community - For Application Development

  610. Pingback: Populate database data into program android | Android Development tutorial | Android Development tutorial

  611. Eman Gaber | January 3rd, 2013

    Steps to read Access files in android
    1-Creat Acces Database then,Export the access database into text files, semicolon or comma delimited.
    2-Open the SQLite database browser version 1.1 and chose creat new DATABASE then enter it’s name ,then file menu ->import->table from csv file. Browse for your text file and choose the proper delimiter. Click create.
    3-Done.
    Then you would need to make some modifications to that database and those tables to make it usable by Android to populate listviews and other widgets.
    1) The database must contain a table called “android_metadata”
    2) This table must have the column “locale”
    3) There should be a single record in the table with a value of “en_US”
    4) The primary key for every table needs to be called “_id” (this is so Android will know where to bind the id field of your tables)
    Then you put the DB in your assets folder and when your app starts copy it to your apps data directory.

    Now :
    Using your own SQLite database in Android applications example here

  612. Mohd Sadiq | January 5th, 2013

    Thank you very very much Mr. Fluxa I have no words to describe that how much this blog helped me. I have one issue, when I retreive the data from database in the list view I am getting error like this com.addressPro@44f0f510

  613. Pingback: Java – Android SQLite No Such Table Exists video

  614. Pingback: Does Database Exist [how to call class]

  615. Pingback: Android – SQLite Database Browser not updating properly video

  616. prashanth | January 13th, 2013

    Hey , this is a very nice tutorial indeed,i hav a small prob . i uploaded the database with few records.However in wen i query i able nt able to retrive any records . wat could be the prob.
    here’s my code :
    public Cursor getAllRecords()
    {
    return myDataBase.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE
    }, null, null, null, null, null);
    }

  617. Kurukshetran | January 15th, 2013

    Hi,

    I followed your tutorial and just changed the database and table name and ran the application.In emulator it runs correctly but when i installed it in my phone, it show “unfortunately stopped working”.could you give me any solution.

  618. Vids | January 19th, 2013

    Hi. I loved your post. Please help me to solve my problem. I am trying to execute a query in the same class. But i’m getting an error saying that no such table “XYZ” found. My code is

    {
    String query=”SELECT _id,Name,PhoneNo FROM ContactDtls “;
    database=SQLiteDatabase.openDatabase(DB_PATH+DB_NAME,null,SQLiteDatabase.OPEN_READWRITE);
    if(database!=null)
    database.rawQuery(query, null);
    if(result!=null)
    {
    if(result.moveToFirst())
    {
    do
    {
    System.out.println(“The Name is”+result.getString(result.getColumnIndex(“Name”)));
    }while(result.moveToNext());
    }
    }

    }

  619. shantanu | January 21st, 2013

    Hi, prashant use this method

    public Cursor data(){

    Cursor c;
    c=myDataBase.query(DB_TABLE, null, null,null,null,null,null);
    return c;
    }

  620. Pingback: Android in-memory value pairs vs. SQLiteCode and Programming

  621. Kashyap | February 3rd, 2013

    Hi ,

    I don’t have a primary key in my table . How can I copy the database in the application . I have tried this code , but it is giving error ” can’t open the database”

  622. jtnks | February 11th, 2013

    Great man. Awesome solution, Thanks a lot.! everything comes down to bytes :! :)

  623. AlexAndro | February 11th, 2013

    One simple question:
    The file in the /assets folder which extension has? .db or .sqlite ?

  624. matjaz | February 11th, 2013

    Thanks, works great!

  625. Rina End | February 13th, 2013

    Sorry, may I ask?
    Can the data inside the tables be copied when copying the database?

  626. Pingback: Android development « Eikonal Blog

  627. Engin | February 24th, 2013

    Thanks for the great tutorial.

    I get “close () was never explicitly called on database” error even though I close it. I only use the “search” method in above class and open/close database only in that method but nowhere else, I open the db when I need it and right after i am done with it I close it but still get that error.

    Any suggestions?

  628. dina | March 1st, 2013

    One of NO SUCH TABLE:

    I know this is an old question, but I lost a lot of time figuring it out, with the help of all the replies.

    The issue is that a device stores a database in his data/data/…/databases folder. So, when you change sth about the database (name, add android metadata table, size..) it wont make any difference because of the stored database and the method that checked for existing database.

    To get the newest database, after changing it, you must run the program without checking for existing databses (e.g. insteadof dbExist = checkDataBase();, make it just false).

    dbExist = checkDataBase();

    Change to:

    dbExists = false;

    After you picked up the “new” databse you can return to checking the existing ones.

    Hope it helps someone, dina

  629. Zemo | March 3rd, 2013

    Idk if someone had same problem as me.

    After first run, application has crashed. Error with opening DB at copyDataBase(). After second run it goes ok… Almost ;)

    Here is writen

    InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

    and

    private static final String DATABASE_NAME = “yourDBName”;

    it should be

    private static final String DATABASE_NAME = “yourDBName.sqlite”;

    then it works :)

  630. Ni3 Narale | March 5th, 2013

    Thank you very much for this Tutorial..
    Very nice navigation while creating DB and
    Import our Own Databases ..
    Thanks Once again…

  631. Mallikarjun | March 11th, 2013

    Nice Tutorial

  632. cnrblm | March 17th, 2013

    Great tutorial. Thank you

  633. Gurucharan | March 17th, 2013

    This code is not working in mobile but running in Emulator copying to sdcard, Any Idea to fix it ?

  634. Pingback: Copy Sqlite database for android application

  635. Arise rayamangalam | March 21st, 2013

    A Siple tutorial using this method is avilable in this website. Click the link below,
    http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html

  636. Arise rayamangalam | March 21st, 2013

    A Siple tutorial using this method is available in this website with source code . Click the link below,
    http://android-helper4u.blogspot.com/2013/03/d-databse-and-spinner-tutorial.html

  637. NEHALKUMAR | March 22nd, 2013

    how to update particular raw and insert in particular table

  638. NEHALKUMAR | March 22nd, 2013

    thanks in advance

  639. ekaitz | March 22nd, 2013

    hi great tutorial it’s works fine for me.

    how I can put multilenguages in sqlite database browser?? and Do I have to fill the tables for every language?

  640. Joe | March 24th, 2013

    thank you so much. saved my life

  641. Joe | March 24th, 2013

    After much pain, I stumbled upon a solution to sqlite3_open_v2(“/data/data/[PACKAGE]/databases/[DBASE]“, &handle, 1, NULL) failed. Replace the code in checkDataBase() {
    …..
    }