Copy my own SQLite DB from Asset folder to

Question

I don't understand why I'm not able to copy my db file (abic_) to the application directory ("/data/data/" + context.getPackageName() + "/databases")

This is my DataBaseHelper class:

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.IOException;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 DataBaseHelper extends SQLiteOpenHelper{

    //The Android's default system path of your application database.
    private String DB_PATH;

    private static String DB_NAME = "abic_";
    private static final Integer DB_VERSION = 1;

    private SQLiteDatabase mydb; 

    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, DB_VERSION);
        this.myContext = context;
        DB_PATH = "/data/data/" + context.getPackageName() + "/databases";
    }   

    @Override
    public void onCreate(SQLiteDatabase db) {;
    }

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

public void apriDatabase() throws SQLException {
    try {
            String percorso = DB_PATH + DB_NAME;
            mydb = SQLiteDatabase.openDatabase(percorso, null, SQLiteDatabase.OPEN_READONLY);
    } catch (Exception e) {
            e.printStackTrace();
    }
}

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) {
                    e.printStackTrace();
            }
    }
}

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;

    // if the path doesn't exist first, create it
    File f = new File(outFileName);
    if (!f.exists()){
            f.mkdir();
            f.createNewFile();
    }

    //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();

}

private boolean checkDataBase(){

    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){

            checkDB.close();

    }

    return checkDB != null ? true : false;
} 

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

}

This is the listview that uses this class:

  import android.app.ListActivity;
import android.content.Intent;
import android.database.Cursor; 
import android.database.sqlite.SQLiteDatabase; 
import android.os.Bundle; 
import android.view.View; 
import android.widget.EditText; 
import android.widget.ListAdapter; 
import android.widget.ListView; 
import android.widget.SimpleCursorAdapter; 

public class abicabList extends ListActivity { 

        protected EditText searchText; 
        protected SQLiteDatabase mydb; 
        protected Cursor cursor; 
        protected ListAdapter adapter; 

    /** Called when the activity is first created. */ 
    @Override 
    public void onCreate(Bundle savedInstanceState) { 
        super.onCreate(savedInstanceState); 
        setContentView(R.layout.main); 
        mydb = (new DataBaseHelper(this)).getWritableDatabase(); 
        searchText = (EditText) findViewById (R.id.searchText); 
    } 

    public void search(View view) { 

     String text = searchText.getText().toString();
     String cap = "CAP";
     // || is the concatenation operation in SQLite 
      if (text.length()14) {      
 cursor = mydb.rawQuery("SELECT _id, ABI, BANCA, CAB, CAP, Filiale, City FROM abicab WHERE ABI || CAB LIKE ? LIMIT 30",  
          new String[]{"%" + searchText.getText().toString().substring(6,10) + "%" + searchText.getText().toString().substring(11,15) + "%"});     
     adapter = new SimpleCursorAdapter( 
                                this,  
                                R.layout.abicab_list_item,  
                                cursor,  
                                new String[] {"BANCA", "Filiale", "City", "CAP"},  
              new int[] {R.id.BANCA, R.id.Filiale, R.id.City ,R.id.CAP});
      setListAdapter(adapter);
    } 

   }  

So when I run the application the db is created in the folder but it doesn't contain the table that is in the db I have in asset folder (see the picture [1]: http://i.stack.imgur.com/AaFj8.jpg). Thanks in advance for your help.

Answer 1

I've solved the issue.

1) I have used the class AlmanacSQLiteDatabaseAdapter.java used in Almanac 0.0.17 (open source project) that you can find here: link. You can fit to your needs (in my case I'have renamed to DatabaseHelper);

2) This is the class that uses the Helper (it is a listview that do sql rawquery reading strings in a EditText - it is an arrangement of this tutorial "coenraets.org/blog/android-samples/androidtutorial/":

public class MiaList extends Activity { 

        protected EditText searchText; 
        protected SQLiteDatabase db; 
        protected Cursor cursor; 
        protected ListAdapter ladapter; 
        protected ListView miaList; 
        private static final String DATABASE_NAME = "miodb.jpg";
/*
L'ESTENSIONE JPG (o mp3, mov) questo perchè android ha il limite di 1 mb per i file di testo 
o simili nella asset, mentre NON HA limiti per i file multimediali! Nel mio caso si tratta di 4mb
*/

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

//qui sotto richiamo la classe DatabaseHelper

        DatabaseHelper dbAdapter = DatabaseHelper.getInstance(this, DATABASE_NAME);
        /*note di Vytek*/
        // OK Dovrei usare aSQLiteDatabaseAdapter.getDatabase(); ma questo crea
        // problemi nella fase di OnPause quando premo Back cosi' invece non
        // ottengo errori
        // e viene fatta la normale copia del DB (13/8/2010 23.18)
        // db = aSQLiteDatabaseAdapter.getWritableDatabase();
        // Per ovviare a questo problema controllo se e' la prima volta che
        // chiamo applicazione
        if (getFirstRun()) {
                db = dbAdapter.getDatabase();
                setRunned();
        } else {
                db = dbAdapter.getWritableDatabase();
        }

        searchText = (EditText) findViewById (R.id.searchText); 
        miaList = (ListView) findViewById (R.id.list); 
    } 


    private void setRunned() {
                // TODO Auto-generated method stub

        }

        private boolean getFirstRun() {
                // TODO Auto-generated method stub
                return false;
        }

        public void search(View view) { 

        String text = searchText.getText().toString();
        // || è l'operatore "concatena" in SQLite 
         if (text.length()<15){
                    cursor = db.rawQuery("SELECT *FROM miatable WHERE AAA || ' ' || BBB || ' ' || CCC LIKE ? LIMIT 30",  
                            new String[]{"%" + searchText.getText().toString() + "%"}); 
                                ladapter = new SimpleCursorAdapter( 
                                                this,  
                                                R.layout.mialist_list_item,  
                                                cursor,  
                                                new String[] {"_id", "AAA", "BBB", "CCC"},  
                                                new int[] {R.id._id, R.id.AAA, R.id.BBB, R.id.CCC}); 
                                miaList.setAdapter(ladapter); 
                                //istruzione da eseguire
                }
         else {         
                   cursor = db.rawQuery("SELECT * FROM miatable WHERE AAA || BBB LIKE ? LIMIT 30",  
                           new String[]{"%" + searchText.getText().toString().substring(6,10) + "%" + searchText.getText().toString().substring(11,15) + "%"});         
                                ladapter = new SimpleCursorAdapter( 
                                                this,  
                                                R.layout.mialist_list_item,  
                                                cursor,  
                                                new String[] {"_id", "AAA", "BBB", "CCC"},  
                                                new int[] {R.id._id, R.id.AAA, R.id.BBB, R.id.CCC}); 
                                miaList.setAdapter(ladapter); 
                                //istruzione da eseguire
                  } 


        }

}

Of course the SQLite DB "miodb.jpg" must be in the asset folder of eclipse's workspace.

PS I have used jpg extension for the db beacause the asset folder has a 1Mb limit for db or txt or xml extension.

Answer 2

Are you using the Emulator or device?

I do the same procedure but i copy the database into the SD card, because the emulator has a limited space size, what's the size of your database?

---

Replace your copy Method with this one, and let me know because i could not find anything weird except the copy method, Good luck.

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();

}