Reputation: 5996

Not able to fetch data from Sqlite Database

I want to fetch data from SQLITE database and display it in a LIST.

My database name is AppDB.db and table name is Scrip which contains 3 columns _id(primary key) and symbol&company_name which are text.

I want to fetch only 2nd and 3rd column. I have copied database in Assets folder.

I get force close when I execute following code but I don't know what might be the reason. Please help..

I'm absolute beginner to Android, so any help appreciated...

Contents:

1.) DataAttach.java

2.) DbManager.java

3.) LogCat

4.) .xml in brief

5.) AVD Problem

6.) Snapshot of database

1.) DataAttach.java

public class DataAttach extends Activity {
    private DbManager dbM;
    private Cursor c;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        try {
            dbM = new DbManager(this);
            dbM.createDataBase();
            dbM.openDB();

            try {
                c = dbM.fetchAllData();
            } catch (Exception e) {
                throw new Error("\n\nERROR Fetchin data\n\n");
            }

            if (c != null) {
                SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                        this,
                        android.R.layout.simple_list_item_1,
                        c,
                        new String[] { c.getString(c.getColumnIndex("_id")),
                                c.getString(c.getColumnIndex("symbol")),
                                c.getString(c.getColumnIndex("company_name")) },
                        new int[] { R.id._id, R.id.symbol, R.id.company_name });
                ListView list = (ListView) findViewById(R.id.MainLayout);
                list.setAdapter(adapter);
            }
        }

        catch (Exception e) {
            throw new Error("\n\nERROR DB failure\n\n");
        }

        finally {
            c.close();
            dbM.close();
        }
    }
}

2.) DbManager.java

public class DbManager extends SQLiteOpenHelper {

    private static final String KEY_ROWID = "_id";
    private static final String KEY_SYMBOL = "symbol";
    private static final String KEY_COMPANY_NAME = "company_name";

    private static final String DATABASE_NAME = "AppDB";
    private static final String DATABASE_PATH = "/data/data/com.dbexample/databases/";
    private static final Integer DATABASE_VERSION = 3;
    private static final String DATABASE_TABLE = "Scrip";
    // private static final String TAG = "DbManager";
    private final Context ctx;
    private SQLiteDatabase mDb;

    public DbManager(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.ctx = context;
    }

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

        if (checkDataBase()) {
            // 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 can overwrite
             * that database with our database.
             */
            this.getReadableDatabase();

            try {
                copyDataBase();
            } catch (IOException e) {
                throw new Error("\n\nERROR copying database\n\n");
            }
        }
    }

    /*
     * Check if the database already exist to avoid re-copying the file each
     * time you open the application.
     */
    private boolean checkDataBase() {
        SQLiteDatabase checkDB = null;
        try {
            String myPath = DATABASE_PATH + DATABASE_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null,
                    SQLiteDatabase.OPEN_READONLY);

        } catch (SQLiteException e) {
            throw new Error("\n\nERROR database does't exist yet\n\n");
        }

        if (checkDB != null) {
            checkDB.close();
        }
        return checkDB != null ? true : false;
    }

    /*
     * Copies your DB from your local assets-folder to the just created empty DB
     * in the system folder, from where it can be accessed and handled.
     */
    private void copyDataBase() throws IOException {
        /* Open your local db as the input stream */
        InputStream myInput = ctx.getAssets().open(DATABASE_NAME);

        /* Path to the just created empty db */
        String outFileName = DATABASE_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);
        }

        myOutput.flush();
        myOutput.close();
        myInput.close();
    }

    public void openDB() throws SQLException {
        String myPath = DATABASE_PATH + DATABASE_NAME;
        mDb = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READONLY);
    }

    @Override
    public void close() {
        mDb.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

    public Cursor fetchAllData() {
        return mDb.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_SYMBOL,
                KEY_COMPANY_NAME }, null, null, null, null, null);

        // return mDb.rawQuery("select _id,symbol,company_name from Scrip",
        // new String[] { KEY_ROWID,KEY_SYMBOL, KEY_COMPANY_NAME });
    }
}

3.) LOG CAT

sqlite3_open_v2("/data/data/com.dbexample/databases/AppDB", &handle, 1, NULL) failed

Failed to open the database. closing it.
    
android.database.sqlite.SQLiteCantOpenDatabaseException: unable to open database file
    
at android.database.sqlite.SQLiteDatabase.dbopen(Native Method)
    
at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:1017)

4.) Main.xml

It contains RelativeLayout having 3 TextViews within RelativeLayout.

5.) I ALWAYS GET THIS ERROR

Even after reopening the eclipse, restarting the system, recreating the AVD, trying, kill_server and start_server, I get this message.

Failed to install DataAttach.apk on device 'emulator-5554': device not found
com.android.ddmlib.InstallException: device not found
Launch canceled!

6.) SCREENSHOT of DB :

Screenshot

WHOLE DOC EDITED TO LATEST CODE AND ACC. TO YOUR SUGGESTIONS, but still app doesn't work.

Upvotes: 6

Not able to fetch data from Sqlite Database

Answers (3)

Related Questions