how to fetch data from database sored in assets folder in android

Question

I had created my database using SQLite database browser and stored it in to assets folder in my project. When I'm trying to fetch data from that folder its showing error

03-28 01:07:39.840: E/AndroidRuntime(7390): FATAL EXCEPTION: main
03-28 01:07:39.840: E/AndroidRuntime(7390): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.pocketdr/com.example.pocketdr.PocketDoc}: android.database.sqlite.SQLiteException: near "/": syntax error: , while compiling: /data/data/com.example.pocketdr/databases/Diseases.db/Diseaseslist/
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1872)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1893)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.ActivityThread.access$1500(ActivityThread.java:135)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1054)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.os.Handler.dispatchMessage(Handler.java:99)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.os.Looper.loop(Looper.java:150)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.ActivityThread.main(ActivityThread.java:4385)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at java.lang.reflect.Method.invokeNative(Native Method)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at java.lang.reflect.Method.invoke(Method.java:507)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:849)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:607)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at dalvik.system.NativeStart.main(Native Method)
03-28 01:07:39.840: E/AndroidRuntime(7390): Caused by: android.database.sqlite.SQLiteException: near "/": syntax error: , while compiling: /data/data/com.example.pocketdr/databases/Diseases.db/Diseaseslist/
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:73)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:53)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1442)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1410)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at com.example.pocketdr.Database.getSymptom(Database.java:215)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at com.example.pocketdr.PocketDoc.onCreate(PocketDoc.java:60)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1072)
03-28 01:07:39.840: E/AndroidRuntime(7390):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1836)
03-28 01:07:39.840: E/AndroidRuntime(7390):     ... 11 more

DATABASE Helper CLASS

public class Database extends SQLiteOpenHelper{
 private static String DB_PATH = "/data/data/com.example.pocketdr/databases/";
private static final String DB_NAME = "Diseases.db";
private static final int DB_VERSION_NUMBER = 1;
public static final String DB_TABLE_NAME = "Diseaseslist";
private static final String  _ID="id";
public static final String COLUMN_1_NAME = "Name";
private static final String COLUMN_2_Symptom="Symptom";
private static final String COLUMN_3_Symptom1="Symptom1";
private static final String COLUMN_4_Symptom2="Symptom2";
private static final String COLUMN_5_Symptom3="Symptom3";
private static final String COLUMN_6_Discription="Discription";
private static final String COLUMN_7_Precaution="Precaution";
String[] str,str1,str2;



private SQLiteDatabase sqliteDBInstance ;
private final Context myContext;
public Database(Context context) {
    super(context, DB_NAME, null, DB_VERSION_NUMBER);
    this.myContext = context;
}

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;
     sqliteDBInstance = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

     }

     @Override
     public synchronized void close() {

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

     super.close();

     }


@Override
public void onCreate(SQLiteDatabase sqliteDBInstance) {

    // TODO Auto-generated method stub



}

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

}

    public long insertDiseases(String Name,String Symptom,String Symptom1,String Symptom2,String Symptom3,String Description, String Precaution)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_1_NAME, Name);
        contentValues.put(COLUMN_2_Symptom, Symptom);
        contentValues.put(COLUMN_3_Symptom1, Symptom1);
        contentValues.put(COLUMN_4_Symptom2, Symptom2);
        contentValues.put(COLUMN_5_Symptom3, Symptom3);
        contentValues.put(COLUMN_6_Discription, Description);
        contentValues.put(COLUMN_7_Precaution, Precaution);
        Log.i(this.toString() + " - insertCountry", "Inserting: " + Name);
        return this.sqliteDBInstance.insert(DB_TABLE_NAME, null, contentValues);
    }

    public boolean removeDiseases(String Name)
    {
        int result = this.sqliteDBInstance.delete(DB_TABLE_NAME, "Name='" + Name + "'", null);

        if(result > 0)
            return true;
        else
            return false;
    }

    public long updateCountry(String oldName, String newName)
    {
        ContentValues contentValues = new ContentValues();
        contentValues.put(COLUMN_1_NAME, newName);
        return this.sqliteDBInstance.update(DB_TABLE_NAME, contentValues, "Name='" + oldName + "'", null);
    }

    public String[] getSymptom()
    {
        String myPath1 = DB_PATH + DB_NAME+"/"+DB_TABLE_NAME+"/" ;

    Cursor cursor = this.sqliteDBInstance.rawQuery(myPath1, new String[]{COLUMN_2_Symptom});//query(myPath1, new String[] {COLUMN_2_Symptom}, null, null, null, null, null);
 Log.i(COLUMN_2_Symptom, "Symptom"+cursor);
        if(cursor.getCount() >0)
        {
             str = new String[cursor.getCount()];
            int i = 0;

            while (cursor.moveToNext())
            {
                 str[i] = cursor.getString(cursor.getColumnIndex(COLUMN_2_Symptom));
                 i++;
             }

            return str;
        }
        else
        {
            return new String[] {};
        }
    }

If you are using an older Android version (<= Android 2.2), there was a known issue with full path (check http://stackoverflow.com/questions/5332328/sqliteopenhelper-problem-with-fully-qualified-db-path-name). What version are you using? P.S. your variable `String myPath1 = ...` might be wrong. — Trinimon, Mar 27 '13 at 20:37

Parvaz Bhaskar · Answer 1 · 2013-03-27T22:05:20.123

 public String[] getSymptom()
    {
        String myPath1 = DB_PATH + DB_NAME+"/"+DB_TABLE_NAME+"/" ;

    Cursor cursor = this.sqliteDBInstance.rawQuery(myPath1, new String[]{COLUMN_2_Symptom});
    }

Your String myPath1 should contain a valid SQLite Query not the path to the database table.

Try something like "Select * from your_tablename Where column_name = value"

or you can use query instead of rawQuery. Refer this

how to fetch data from database sored in assets folder in android

1 Answers1