A couple of weeks ago I wrote an article about how to browse SQLite data on the Android emulator. But what happens when you need to get
data from an actual Android device? Well, unless that device is rooted, you won’t
be able to get to the data using Eclipse and the Dalvik Debug Monitor Server (DDMS); you’ll need to be able to push the data to the
device’s SD memory. That’s where this tutorial can help.

Follow along with the step-by-step instructions, or download
and import the entire project into Eclipse.

1. Create a new Android project in Eclipse. Target Android
2.2 or higher.

2. In order to access the SD card, your application will
need to request permission in the AndroidManifest.xml file.

AndroidManifest.xml
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.authorwjf.sqliteexport"
android:versionCode="1"
android:versionName="1.0" >

<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="15" />

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

<application
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
android:theme="@style/AppTheme" >
<activity
android:name="com.authorwjf.sqliteexport.MainActivity"
android:label="@string/title_activity_main" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />

<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>

</manifest>

3. In the /res/layout folder, open the activity_main.xml
file. For this demo, we are simply stacking three buttons inside a linear
layout.

activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_gravity="center"
android:layout_width="match_parent"
android:layout_height="match_parent" >

<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="SQLite DB to SD Demo"/>

<Button
android:id="@+id/button2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Export" />

<Button
android:id="@+id/button3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Create" />

<Button
android:id="@+id/button1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete" />

</LinearLayout>

4. Open the /src/MainActivity.java file and create an
activity that implements the on click handler. Wire up the buttons in the on
create override.

MainActivity.java
package com.authorwjf.sqliteexport;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;

import android.os.Bundle;
import android.os.Environment;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Toast;
import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;

public class MainActivity extends Activity implements OnClickListener {

private static final String SAMPLE_DB_NAME = "TrekBook";
private static final String SAMPLE_TABLE_NAME = "Info";

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViewById(R.id.button1).setOnClickListener(this);
findViewById(R.id.button2).setOnClickListener(this);
findViewById(R.id.button3).setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch(v.getId()) {
case R.id.button1:
deleteDB();
break;
case R.id.button2:
exportDB();
break;
case R.id.button3:
createDB();
break;
}
}
}

5. It’s time to implement our three private worker
functions. Let’s start with delete, since it’s the easiest.

private void deleteDB(){
boolean result = this.deleteDatabase(SAMPLE_DB_NAME);
if (result==true) {
Toast.makeText(this, "DB Deleted!", Toast.LENGTH_LONG).show();
}
}

6. Next we’ll implement the create db function. For our
purposes, we are using raw sql to create a canned database with a single table
and data row.

private void createDB() {
SQLiteDatabase sampleDB = this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);
sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
SAMPLE_TABLE_NAME +
" (LastName VARCHAR, FirstName VARCHAR," +
" Rank VARCHAR);");
sampleDB.execSQL("INSERT INTO " +
SAMPLE_TABLE_NAME +
" Values (‘Kirk’,’James, T’,’Captain’);");
sampleDB.close();
sampleDB.getPath();
Toast.makeText(this, "DB Created @ "+sampleDB.getPath(), Toast.LENGTH_LONG).show();
}

7. We come to our export data function. If you read the
documentation for writing to the SD card, you will see my method differs a
little from the recommended technique. Google recommends checking the Environment.getExternalStorageDirectory().canWrite() method
before attempting to copy to the device’s external storage. In my experience,
this is unreliable. The return value of this method is determined by the
hardware manufacturer, and I have found many cases (including the Nexus 4)
where the method returns false, but the write works just fine. My advice is to
just wrap the whole block in a try catch.

private void exportDB(){
File sd = Environment.getExternalStorageDirectory();
File data = Environment.getDataDirectory();
FileChannel source=null;
FileChannel destination=null;
String currentDBPath = "/data/"+ "com.authorwjf.sqliteexport" +"/databases/"+SAMPLE_DB_NAME;
String backupDBPath = SAMPLE_DB_NAME;
File currentDB = new File(data, currentDBPath);
File backupDB = new File(sd, backupDBPath);
try {
source = new FileInputStream(currentDB).getChannel();
destination = new FileOutputStream(backupDB).getChannel();
destination.transferFrom(source, 0, source.size());
source.close();
destination.close();
Toast.makeText(this, "DB Exported!", Toast.LENGTH_LONG).show();
} catch(IOException e) {
e.printStackTrace();
}
}

Now you are ready to give it a try. Remember, this code is
meant to run on a device, not the emulator (though it is possible to make it
work on an emulator if you configure it with emulated external storage).

Figure A

Once you export the database, you will need to connect the
device to a PC in mass storage mode in order to transfer the database. Once you
have the file on your PC, you can follow the instructions in my previous Android article about SQLite data to browse it.