Android

Export SQLite data from your Android device

If you need to debug SQLite tables on an Android device, here is how to create an export function that copies the database to the SD card.

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

sqlite_exp_080713.png

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.

About

William J Francis began programming computers at age eleven. Specializing in embedded and mobile platforms, he has more than 20 years of professional software engineering under his belt, including a four year stint in the US Army's Military Intellige...

4 comments
touaa9
touaa9

hello sir

I am a student I need an elbow source do (research and add and delete) a record in sqlite and updated the base to give that contains a table (name, surname, email) by adt android java help me please my email touaa9@gmail.com
thank you

touaa9
touaa9

bonjour monsieur

je suis un étudiant je besoin un coude source qui fais  (la recherche et ajouter et supprimer ) un enregistrement dans sqlite et mis a jour de la base de donner qui contient un tableau (nom;prénom, émail)  par  adt android java aider moi svp mon email touaa9@gmail.com 

merci
touaa9
touaa9

 bonjour monsieur

je suis un étudiant je besoin un coude source qui fais  (la recherche et ajouter et supprimer ) un enregistrement dans sqlite et mis a jour de la base de donner qui contient un tableau (nom;prénom, émail)  par  adt android java aider moi svp mon email touaa9@gmail.com 

merci
jeffersonkuo
jeffersonkuo

if I import this DB file from the sd card to databases directory again  ,  I cannot read any table data ,

because the permission of theDb file  is -rw------- .

Editor's Picks