Android - Asynctask and Android Database web server database fetch issues

Question

I have an application that fetches a zipped sqlite database dump file and uses that database. I used this tutorial as a guide on how to fetch the database. However, for my purpose, I instead pulled a database hosted on my localhost (192.168.1.4).

The link above also contains the full project which I downloaded for reference purposes but I had to tweak the code in such a way that it used an AsyncTask to fetch the database, otherwise I would get a NetworkOnMainThreadException. I managed to make the AsyncTask work properly, my AsyncTask looks like this:

private class Connection extends AsyncTask {
    @Override
    protected Object doInBackground(Object... arg0) {
        //connect();
        myDroidSQLDatabase = new MyDroidSQLDatabase(MyDBAppActivity.this);
        myDroidSQLDatabase.open();

        return null;
    }

}

And it is called in my main activity as follows:

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    new Connection().execute();

}

And the problem seems to be in the onResume() since logcat shows the error Unable to resume activity:

@Override
protected void onResume() {
    myDroidSQLDatabase.open();
    super.onResume();
}

I am not quite sure why the onResume() triggered when I first installed the application on my device. I think the issue is that after onCreate(), onResume() is called and since the database is still open, it's trying to open already open db.

However, the original example from the website deploys a packaged dump file in the application and it executes properly. Since this method did not require db fetch and execute in an AsyncTask, the onResume did not act up. So my guess right now is that I have to wait for the AsyncTask to finish so that onResume() won't give me problems.

Sorry but I'm totally new to Threads and Async Tasks so I have no idea how to handle the thread when the job is done. I think the issues is that onResume tries to run (as part of the activity life cycle) and since the Async Task isn't done yet, the open() call acts up.

Any ideas?

Also, here are three more classes, it might help. MyDroidSQLDatabase

public class MyDroidSQLDatabase {
    private SQLiteOpenHelper sqlLiteOpenHelper;
    private SQLiteDatabase sqlLiteDatabase;

    public MyDroidSQLDatabase(Context context) {
        sqlLiteOpenHelper = new MyDroidSQLiteOpenHelper(context);       
    }

    public void open() {
        sqlLiteDatabase = sqlLiteOpenHelper.getWritableDatabase();
    }

    public void close() {
        sqlLiteDatabase.close();
    }

    public SQLiteDatabase getSqlLiteDatabase() {
        return sqlLiteDatabase;
    }
}

MyDroidSQLiteOpenHelper

public class MyDroidSQLiteOpenHelper extends SQLiteOpenHelper {
    private Context context;
    private static final String __DB_NAME = "mydbapp.db";
    private static final int __DB_VERSION = 1;

    public MyDroidSQLiteOpenHelper(Context context) {
        super(context, __DB_NAME, null, __DB_VERSION);
        this.context=context;
    }

    @Override
    public void onCreate(SQLiteDatabase sqlLiteDb) {
        try {
            //SQLiteDBDeploy.deploy(sqlLiteDb, this.context, "northwind.zip");

            // if you like to download file from remote site comment above line and uncomment below line.
            SQLiteDBDeploy.deploy(sqlLiteDb,
                    //"http://ingenious-camel.googlecode.com/svn/trunk/SQLiteDBDeployer/assets/northwind.zip");         
                    "http://192.168.1.4/dbtest/android.zip");
        } catch (IOException e) {
            Log.e(MyDBAppActivity.TAG,e.getMessage(),e);
            throw new Error(e.getMessage());
        }
    }

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

    }
}

SQLiteDBDeploy

public class SQLiteDBDeploy {
    private static final String TAG = "SQLiteDBDeploy";
    private static List<String> ignoreSQLs;

    static {
        ignoreSQLs = new LinkedList<String>();
        ignoreSQLs.add("--");
        ignoreSQLs.add("begin transaction;");
        ignoreSQLs.add("commit;");
        ignoreSQLs.add("create table android_metadata (locale text);");
        ignoreSQLs.add("create table \"android_metadata\" (locale text);");
        ignoreSQLs.add("insert into android_metadata values('en_us');");
        ignoreSQLs.add("insert into \"android_metadata\" values('en_us');");
    }

    /**
     * Deploys given zip file in SQLiteDatabase
     * 
     * @param sqlLiteDb
     *            the database
     * @param context
     *            to use to open or create the database
     * @param dbName
     *            dump zip file
     * @throws IOException
     */
    public static void deploy(SQLiteDatabase sqlLiteDb, Context context, String dbName) throws IOException {
        Log.i(TAG, "reading zip file: " + dbName);
        InputStream dbStream = context.getAssets().open(dbName);

        deploy(sqlLiteDb, dbStream);

        dbStream.close();
    }

    /**
     * Deploys given zip file url in SQLiteDatabase
     * 
     * @param sqlLiteDb
     *            the database
     * @param dbUrl
     *            dump zip file url
     * @throws IOException
     */
    public static void deploy(SQLiteDatabase sqlLiteDb, String dbUrl) throws IOException {
        Log.i(TAG, "reading url: " + dbUrl);

        HttpURLConnection c = (HttpURLConnection) new URL(dbUrl).openConnection();
        c.setRequestMethod("GET");
        c.setDoOutput(true);
        c.connect();
        InputStream dbStream = c.getInputStream();

        deploy(sqlLiteDb, dbStream);

        dbStream.close();
        c.disconnect();
    }

    /**
     * Deploys given dump  file stream in SQLiteDatabase
     * 
     * @param sqlLiteDb the database
     * @param dbStream stream to read dump data 
     * @throws IOException
     */
    private static void deploy(SQLiteDatabase sqlLiteDb, InputStream dbStream) throws IOException {
        ZipInputStream zis = new ZipInputStream(new BufferedInputStream(dbStream));
        ZipEntry entry = null;
        while ((entry = zis.getNextEntry()) != null) {
            Log.i(TAG, "deploying zip entry: " + entry);
            InputStreamReader dbReader = new InputStreamReader(zis);
            deploy(sqlLiteDb, dbReader);
        }
    }

    /**
     * Deploys given stream in SQLiteDatabase
     * 
     * @param sqlLiteDb
     *            the database
     * @param dbReader
     *            stream to read dump SQL statements
     * @throws IOException
     * @throws SQLException
     */
    private static void deploy(SQLiteDatabase sqlLiteDb, InputStreamReader dbReader) throws IOException {
        String sqlLine = null;
        StringBuffer sqlBuffer = new StringBuffer();
        BufferedReader bufferedReader = new BufferedReader(dbReader);

        sqlLiteDb.beginTransaction();
        try {
            while ((sqlLine = bufferedReader.readLine()) != null) {
                String sql = sqlLine.trim();
                if (!isIgnoreSQL(sql)) {
                    if (sql.endsWith(";")) {
                        sqlBuffer.append(sql);
                        String execSQL = sqlBuffer.toString();
                        Log.d(TAG, "running sql=>" + execSQL);
                        sqlLiteDb.execSQL(execSQL);
                        sqlBuffer.delete(0, sqlBuffer.length());
                    } else {
                        if (sqlBuffer.length() > 0) {
                            sqlBuffer.append(' ');
                        }
                        sqlBuffer.append(sql);
                    }
                }
            }
            sqlLiteDb.setTransactionSuccessful();
        } finally {
            sqlLiteDb.endTransaction();
        }
    }

    /**
     * Returns true if the given SQL statement is to be ignored
     * @param sql SQL statement
     * @return
     */
    private static boolean isIgnoreSQL(String sql) {
        if (sql.length() == 0) {
            return true;
        }

        String lowerSQL = sql.toLowerCase();
        for (String ignoreSQL : ignoreSQLs) {
            if (lowerSQL.startsWith(ignoreSQL)) {
                return true;
            }
        }
        return false;
    }
}

Answer 1

I am not quite sure why the onResume() triggered when I first installed the application on my device.

The onResume() method is always called when an Activity is first created - not just when an Activity has been paused. See Activity Lifecycle

I think the issue is that after onCreate(), onResume() is called and since the database is still open, it's trying to open already open db.

No, that shouldn't matter. Multiple calls to getWriteableDatabase() are allowed.

The most likely reason you get an error is that the AsyncTask doInBackground(...) method hasn't completed. That's the point of an AsyncTask - it's asynchronous and the call to execute() in your onCreate(...) method will return immediately. You should be making use of the onPostExecute(...) method of the AsyncTask.