Import and Use External Database in Android

This article explains the usage of an Android library which is helpful to import and use external SQLite database into your Android application. It has been a problem for Android developers to release an application with some existing rows of data in the database. For example if you are developing a static application which is used to display some tourist spots in your country, probably you need to release the database with predefined details about the tourist places inside it. However, there are no native ways to import external database easily into your Android application.


The Android SQLiteAssetHelper library allows you to build your SQLite database in your desktop computer, and to import and use it in your Android application. Let's create a simple application to demonstrate the application of this library.

Update: If you want to import database either from assets folder as described here or from an external location like SD card, a new approach is shared in Deploy and Upgrade Android Database From External Directory.

Step 1:
Create a database quotes.db using your favorite SQLite database application (DB Browser for SQLite is a portable cross platform freeware, which can be used to create and edit SQLite databases). Create a table 'quotes' with a single column 'quote'. Insert some random quotes into the table 'quotes'.


Step 2:
The database can be imported into project either directly as it is, or as a compressed file. The compressed file is recommended, if your database is too large in size. You can create either a ZIP compression or a GZ compression.

The file name of the compressed db file must be quotes.db.zip, if you are using ZIP compression or quotes.db.gz, if you are using GZ compression.

Step 3:
Create a new application “External Database Demo” with a package name “com.javahelps.com.javahelps.externaldatabasedemo”.

Step 4:
Open the build.gradle (Module: app) file and add the following dependency.
dependencies {
    compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'
}


Once you have saved the build.gradle file click on the 'Sync Now' link to update the project. You can synchronize the build.gradle, by right clicking on the build.gradle file and selecting "Synchronize build.gradle' option as well.

Step 5:
Right click on the app folder and create new assets folder.


Step 6:
Create a new folder 'databases' inside the assets folder.


Step 7:
Copy and paste the  quotes.db.zip file inside the assets/databases folder.



Step 8:
Create a new class 'DatabaseOpenHelper'
package com.javahelps.externaldatabasedemo;

import android.content.Context;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class DatabaseOpenHelper extends SQLiteAssetHelper {
    private static final String DATABASE_NAME = "quotes.db";
    private static final int DATABASE_VERSION = 1;

    public DatabaseOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
}
Notice that rather than extending SQLiteOpenHelper, the DatabaseOpenHelper extends  SQLiteAssetHelper class.

Step 9:
Create a new class DatabaseAccess and enter the code as shown below. More details about this class is available at Advanced Android Database tutorial.
package com.javahelps.externaldatabasedemo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class DatabaseAccess {
    private SQLiteOpenHelper openHelper;
    private SQLiteDatabase database;
    private static DatabaseAccess instance;

    /**
     * Private constructor to aboid object creation from outside classes.
     *
     * @param context
     */
    private DatabaseAccess(Context context) {
        this.openHelper = new DatabaseOpenHelper(context);
    }

    /**
     * Return a singleton instance of DatabaseAccess.
     *
     * @param context the Context
     * @return the instance of DabaseAccess
     */
    public static DatabaseAccess getInstance(Context context) {
        if (instance == null) {
            instance = new DatabaseAccess(context);
        }
        return instance;
    }

    /**
     * Open the database connection.
     */
    public void open() {
        this.database = openHelper.getWritableDatabase();
    }

    /**
     * Close the database connection.
     */
    public void close() {
        if (database != null) {
            this.database.close();
        }
    }

    /**
     * Read all quotes from the database.
     *
     * @return a List of quotes
     */
    public List<String> getQuotes() {
        List<String> list = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT * FROM quotes", null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            list.add(cursor.getString(0));
            cursor.moveToNext();
        }
        cursor.close();
        return list;
    }
}
In this class only the getQuotes method is implemented to read the data from the database. You have the full freedom to insert, update and delete any rows in the database as usual. For more details, follow this link Advanced Android Database.

All the database related setups are completed and now we need to create a ListView to display the quotes.

Step 10:
Add a ListView in your activity_main.xml.
<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity">

    <ListView
        android:id="@+id/listView"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_gravity="center" />
</FrameLayout>

Step 11:
Find the object of ListView in the onCreate method of MainActivity and feed the quotes which are read form the database.
package com.javahelps.externaldatabasedemo;

import android.os.Bundle;
import android.support.v7.app.ActionBarActivity;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import java.util.List;


public class MainActivity extends ActionBarActivity {
    private ListView listView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        this.listView = (ListView) findViewById(R.id.listView);
        DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
        databaseAccess.open();
        List<String> quotes = databaseAccess.getQuotes();
        databaseAccess.close();

        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, quotes);
        this.listView.setAdapter(adapter);
    }
}

Step 12:
Save all the changes and run the application.


This library also allows you to upgrade the database using an external SQL script. For more details about this library, visit to its official page.

Find the project at Git Hub.

Update: To store images in this database, read Import Database with Images in Android
Previous
Next Post »

188 comments

Write comments
Joel || GetAvenue
AUTHOR
September 6, 2015 at 3:47 PM delete

I think you skipped a part in this tutorials, How do I add download library from GitHub to my project

Reply
avatar
Joel || GetAvenue
AUTHOR
September 6, 2015 at 4:04 PM delete

I did everything correctly, but the project is still showing error (I cannot simply use the SQLiteAssetHelper. class from the library.

and I added the dependencies under the build.gradle(Module:app) directory.

dependencies {
compile fileTree(dir: 'libs', include: ['*.jar'])
compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'

}

Please, how can I add the SQLite Library downloaded from GitHub to my project?

Reply
avatar
Gobinath
AUTHOR
September 7, 2015 at 9:06 PM delete

Hi,
Please make sure that you are connected to Internet while synchronizing the project. If it is not working, download the JAR file of the library and place it inside the libs folder as shown below. (You may required to change the Android perspective to Project perspective to see the libs folder)


If you still have any issues, please comment below.

Reply
avatar
Ko Phone
AUTHOR
October 5, 2015 at 12:26 AM delete

In Step 8
Need to
change private static final String DATABASE_NAME = "quotes.db"; to change this
private static final String DATABASE_NAME = "quotes.db.zip"; code are really work thank.

Reply
avatar
charles
AUTHOR
October 5, 2015 at 11:10 AM delete

no part II for CRUD? thanks

Reply
avatar
Gobinath
AUTHOR
October 5, 2015 at 3:23 PM delete

You can import the database in this way and add CRUD operations in the DatabaseAccess class. CRUD operations are explained in the following article.

http://www.javahelps.com/2015/03/advanced-android-database.html

Reply
avatar
libi
AUTHOR
October 21, 2015 at 2:20 AM delete

Hi , if I added new column to the qoutes table and named it "Bitim" , and I added Some strings to it (like he "qoute" column) when I try to retrive this column strings with
"list.add(cursor.getString(1))"
the cursor doesnt recognize this column ,what should I do ?

Reply
avatar
Gobinath
AUTHOR
October 21, 2015 at 7:23 AM delete

Hi,
I guess that your previous application with single column database is in the emulator, while you are deploying your updated application. The reason is: when you install an updated application, it will not replace the existing database. To ensure the replacement, go to the settings and uninstall the existing application first and then deploy your new version.
If this is not your problem, please comment below the exact error message. I will try my best to fix it.

Reply
avatar
Paul
AUTHOR
October 27, 2015 at 2:39 AM delete

Hey, how can I update database, that after I change something in DB I don`t need to uninstall application and install it once more ?

Reply
avatar
Gobinath
AUTHOR
October 27, 2015 at 7:28 AM delete

Hi Paul,
The recommended way is:
1. Update the DATABASE_VERSION to 2
2. Add an SQL script in assets folder to upgrade the existing database.
3. Update(Deploy again) the application
For more details check the official site: https://github.com/jgilfelt/android-sqlite-asset-helper#database-upgrades


However, there is a quick and dirty way to change the database of an installed application. Use it only for testing purposes.
1. Make sure that your emulator is already running
2. Open the Android Device Monitor
3. Expand the application folder in File Explorer
4. Delete the existing database
5. Push the modified database
For detailed steps, check this answer in StackOverflow: http://stackoverflow.com/a/24809016/4382663

In this answer, instead of Step 4, Delete the database and push the new one.


If it is not clear enough, please let me to know, I will write a separate tutorial on upgrading database.

Reply
avatar
Raza Akhter
AUTHOR
December 29, 2015 at 11:11 AM delete

Good tutorial. Please upload CRUD operations example with external databse.

Reply
avatar
Mirny Line
AUTHOR
January 24, 2016 at 1:13 AM delete

Ahh great tutorial thanks,
but if i have more that 1 field at my table, how to show them all or the column i select?
Maybe something like name, address and phone and i just want to show name and phone, how to do that?

Reply
avatar
Gobinath
AUTHOR
January 24, 2016 at 6:25 PM delete

Hi Mirny Line,

There is no difference between an internal database and external database in the CRUD operations. Please check the Step 7 in the following article:

http://www.javahelps.com/2015/03/advanced-android-database.html

Reply
avatar
Ruddi Aagaard
AUTHOR
March 3, 2016 at 2:37 PM delete

how to use custom listview with this eksampel ?

Reply
avatar
Gobinath
AUTHOR
March 3, 2016 at 2:53 PM delete

For custom ListView, have a look at this post:
http://www.javahelps.com/2015/02/android-customized-listview.html

Reply
avatar
Ana Faria
AUTHOR
March 24, 2016 at 7:25 PM delete

Hello.
I followed all the instructions but I can't make it work... I'm getting a "android.database.sqlite.SQLiteException: no such table" error. The problem is that the database file is not being passed to the device. Any advice?
Thanks in advance,
Ana

Reply
avatar
Gobinath
AUTHOR
March 27, 2016 at 6:58 AM delete

Hi Ana,
Uninstall the existing application from the emulator/device and run again. If it is not working, let me to know.

Reply
avatar
Ana Faria
AUTHOR
March 27, 2016 at 8:54 PM delete

Yes, that solved it! Thanks!
Is there an "automatic" way to prevent this, meaning, some code that deals with this so I don't need to worry?

Reply
avatar
Gobinath
AUTHOR
March 28, 2016 at 6:58 AM delete

Here the problem is: your previous application was installed with a different table name or no table at all. Reinstalling the application does not overwrite database (To avoid data loss during app updates) If you are releasing a new version of your application, override the 'onUpgrade' method of SQLiteAssetHelper and write your logic to upgrade the database. But this method is not intended to be used during developing and/or testing your application.
You can also use Context.deleteDatabase() method to delete the existing database based on some conditions.

API of deleteDatabase method is given below:
https://developer.android.com/reference/android/content/Context.html#deleteDatabase%28java.lang.String%29

Reply
avatar
Ana Faria
AUTHOR
March 28, 2016 at 3:06 PM delete

Ok, so the good practice in this case is, during development, to uninstall the app whenever I make changes in the database, right?

Reply
avatar
Gobinath
AUTHOR
March 28, 2016 at 3:38 PM delete

That is what I do :)

Reply
avatar
Felix
AUTHOR
April 5, 2016 at 5:47 PM delete

Hi, thanks for your code really" helpfull :)
but i have one proble, if i want to retrieve image from SQLite how to do that?
and how is thebest choice, store image on SQLite (address images) or save on asset folder? Because i have more than hundred images, and how to do that? thanks

Reply
avatar
Gobinath
AUTHOR
April 5, 2016 at 6:35 PM delete

If your images are small in size, you can store them into the database as BLOB objects. If you want, store some images in your desktop computer and read them as byte[] in Android and convert them to Bitmap using BitmapFactory. If you need further details, feel free to comment below.

Reply
avatar
Felix
AUTHOR
April 7, 2016 at 7:04 AM delete

Hi thanks for the answer, i have an hundreed images with size average 1.5mb, so can u give an example for retrieve data from sqlite and images too from asset folder? , and how to read them as a byte and convertinto bitmap using blop? Thanks for your time

Reply
avatar
Gobinath
AUTHOR
April 7, 2016 at 9:35 PM delete

Hi,
If you prefer to store the images in the application itself, store the image name in the database and open it at the runtime using those names. If you want to insert the images into the database, check my new tutorial on importing database with images[1].

[1] http://www.javahelps.com/2016/04/import-database-with-images-in-android.html

Reply
avatar
Felix
AUTHOR
April 8, 2016 at 5:53 AM delete

OMG, you're really awesome sir. Thanks for your example! :) , im so thankfull for your help so ill try using your example for store image, and if i have some problem, ill ask again :) thanks, if you has easiest way to contact you please email your contact on me ty

Reply
avatar
Marko Curlinoski
AUTHOR
April 12, 2016 at 3:04 AM delete

Hello. Am lost in here. I never in the code put the Data Base path (in assets folder). My app crush but idk why? All i change is the QUERY (SELECT colum FROM table WHERE colum2='value'; )

Reply
avatar
Marko Curlinoski
AUTHOR
April 12, 2016 at 3:06 AM delete

oh, and i put lib manualy its: sqliteassethelper-2.0.1.jar

Reply
avatar
Gobinath
AUTHOR
April 12, 2016 at 6:31 AM delete

Hi Marko,
Could you please share the logcat error message here?

Reply
avatar
Mohammad Asaduzzaman
AUTHOR
April 19, 2016 at 11:57 PM delete

Hi Gobinath, I went through as you said, I just changed my package name, and my app is crushing every time i run it.

Reply
avatar
Gobinath
AUTHOR
April 20, 2016 at 6:21 AM delete

Hi,
Uninstall the app from your emulator/phone and reinstall it again. If you still get the error, share the logcat error message.

Reply
avatar
Mohammad Asaduzzaman
AUTHOR
April 20, 2016 at 4:00 PM delete

Hi Gobinath,

Thanks for your reply. I tried as you said. But the app is not starting at all and crashing every time I run it. Please Check, THANKS.

Reply
avatar
Mohammad Asaduzzaman
AUTHOR
April 20, 2016 at 4:01 PM delete

But look, I have quotes.db in my assets folder.

Reply
avatar
Gobinath
AUTHOR
April 20, 2016 at 4:30 PM delete

I hope I have identified the bug :).
Your folder name is "database" not "databases" (Note the 's' at the end). That's why you get 'Missing databases/quotes.db' error.

Reply
avatar
Marko Curlinoski
AUTHOR
April 26, 2016 at 12:19 AM delete

here.. sorry for late answer :(

Reply
avatar
Gobinath
AUTHOR
April 26, 2016 at 6:02 AM delete

Hi,
Try these steps and let me to know the result:
1. Uninstall the existing app from your emulator or device.
2. Make sure that your DATABASE_VERSION is 1 in DatabaseOpenHelper.
3. Run the application again.

Reply
avatar
Marko Curlinoski
AUTHOR
April 26, 2016 at 2:31 PM delete

still samе

Reply
avatar
Gobinath
AUTHOR
April 28, 2016 at 9:47 AM delete

Hi,
Could you share the project in some way. Then I can have a look at the project and try my best to help you.

Reply
avatar
Marko Curlinoski
AUTHOR
May 20, 2016 at 6:45 PM delete

I don't know what i'v done wrong, but i remade all project and its working now, greath example, and good support, thank you friend!!!

Reply
avatar
Gobinath
AUTHOR
May 23, 2016 at 8:41 AM delete

Nice to hear that. You are welcome :)

Reply
avatar
Dimivrn
AUTHOR
June 5, 2016 at 8:05 PM delete

Hello, this is a great tutorial but i have one question. I have a database with more that 1000 rows and 5 different tables. Do i need a splash Activity in order to initialize and load the database before opening the main activity?

Reply
avatar
Gobinath
AUTHOR
June 5, 2016 at 8:47 PM delete

Hi,
Of course. Even Google now recommends to use splash screen during time taking initialization. Size of your database has nothing to do with sqliteassethelper. But loading data from database to memory may take time. Show a splash screen during that time.
Apart from your question, consider using an external database and access it using web services if your database is too large.

Reply
avatar
Dimivrn
AUTHOR
June 5, 2016 at 9:23 PM delete

Thank you for the fast reply! So i will use the splash Activity and I only have to create a new DatabaseOpenHelper object in order to initialize the DB is that correct? I will check also the access through web services

Reply
avatar
Gobinath
AUTHOR
June 6, 2016 at 6:15 AM delete

What do you meant by initialize? When you use sqliteassethelper, it will extract the zip file into the default database location if the database is not available. It happens only one time so don't worry about the delay. If you mean extracting the zip file as "initializing the database", no need for splash screen (Because it happens only one time). If you mean reading lot of data from database as "initializing the application", you need a splash screen.

If you still have doubts on it, feel free to comment below.

Reply
avatar
vvp
AUTHOR
June 7, 2016 at 2:10 AM delete

Hi,

1) I was able to import the database using this method but when I tried adding more columns to the database it keeps giving me the error message that no emulator can be found.

2) I am trying to import a database with multiple tables consisting of over 100 records. What would be the ideal way to do this? Any pointers?

Thank You

Reply
avatar
Gobinath
AUTHOR
June 7, 2016 at 6:48 AM delete

Hi,

1) Redeploy an Android application does not overwrite the existing database. If you have modified the same application with a different database, manually uninstall the existing application from the emulator/device and install the new app later.

2) Here the problem is nothing related to Android but it is quite boring to import 100 rows manually. Better solution is writing your own script to automate this task. I would recommend Python for this purpose because it has libraries for SQLite and easy to write such a script. (If this not what you are expecting, feel free to comment below)

Reply
avatar
vvp
AUTHOR
June 7, 2016 at 8:36 PM delete

My aim is just to import two databases. I do not want the records to be displayed. After importing the databases, I am working on other functions like merge in Android. So would you still recommend using Python?

Reply
avatar
Gobinath
AUTHOR
June 7, 2016 at 9:27 PM delete

Hi,
I recommended Python just to insert data into the database from your PC. I find inserting 100 records into a database using 'DB Browser' or any other GUI tools boring. Other than that there is nothing to specify as an ideal ways. Just create the database, insert into the application and do what ever you want :-)

Reply
avatar
Dimivrn
AUTHOR
June 7, 2016 at 10:03 PM delete

The db has over a thousand rows and five tables. Do i need to read all these data from the database first and after specify the adapter and cursor or by using the sqliteassethelper the db will be copied in the default database location once and i wont have to do anything else other than set the adapter? I intend to pull a specific number of rows every time the user chooses to interact. Thanks again i hope i made myself understandable

Reply
avatar
vvp
AUTHOR
June 8, 2016 at 1:35 AM delete

Thank you. Do you have any article with CRUD operations for external databases?

Reply
avatar
Gobinath
AUTHOR
June 8, 2016 at 8:30 AM delete

There is nothing special for a database imported in this way except DatabaseOpenHelper. You can do any CRUD operations on this database as usual. For your reference, check this article about CRUD operations in Android: Advanced Android Database (Follow from Step 6 since you already have the DatabaseOpenHelper and DatabaseAccess)

Reply
avatar
Gobinath
AUTHOR
June 8, 2016 at 9:29 AM delete

Hi,
I hope this is what you have to do:
1. Import the database into the application using sqliteassethelper
2. Create DatabaseAccess class as in Step 9 and write all the CRUD operations in that class.
3. Using methods define in DatabaseAccess class, read the content and feed them to adapters whenever/wherever you need.

For a better idea, have a look at this post: Import Database with Images in Android. In this post, an application is developed to display an image of 7-Wonders of the world depending on user selection. I hope this scenario is something similar to yours.
There I have imported the database using sqliteassethelper and then read and display the images whenever user picks a place in Spinner. If I still failed to answer your question, please let me know :-)

Reply
avatar
vvp
AUTHOR
June 10, 2016 at 12:08 AM delete

Thank You. I am trying to import two different databases in the sample project. Is that possible? If so, how would I go about doing that?

Reply
avatar
Dimivrn
AUTHOR
June 12, 2016 at 6:32 PM delete

Thank you very much for all your help! I will get to it..

Reply
avatar
Hasan Konya
AUTHOR
June 13, 2016 at 2:54 PM delete

Hello, I want fetch to sqlite and display in textview. How I edit code ? Please help me that my homework .Thanks :)

Reply
avatar
Hasan Konya
AUTHOR
June 13, 2016 at 3:16 PM delete

And I try this code and I take many error :

Error:Error converting bytecode to dex:

Cause: com.android.dex.DexException: Multiple dex files define Lcom/readystatesoftware/sqliteasset/SQLiteAssetHelper;

:app:transformClassesWithDexForDebug FAILED

Error:Execution failed for task ':app:transformClassesWithDexForDebug'.

> com.android.build.api.transform.TransformException: com.android.ide.common.process.ProcessException: java.util.concurrent.ExecutionException: com.android.ide.common.process.ProcessException: org.gradle.process.internal.ExecException: Process 'command 'C:\Program Files\Java\jdk1.8.0_92\bin\java.exe'' finished with non-zero exit value 2

thanks.

Reply
avatar
Gobinath
AUTHOR
June 14, 2016 at 8:51 AM delete

Hi Hasan,

There can be several reasons for this problem. Make sure that you have only entry for asset helper dependency and try to rebuild the project. If it does not resolve your problem, please share your build.gradle file.

Reply
avatar
Hasan Konya
AUTHOR
June 14, 2016 at 12:06 PM delete

Hello this my gradle :

apply plugin: 'com.android.application'

android {

compileSdkVersion 23

buildToolsVersion "23.0.3"

defaultConfig {

applicationId "konya.hasan.kpssquiz"

minSdkVersion 14

targetSdkVersion 23

versionCode 1

versionName "1.0"

}

buildTypes {

release {

minifyEnabled false

proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'

}

}

}

dependencies {

compile 'com.readystatesoftware.sqliteasset:sqliteassethelper:+'

compile fileTree(include: ['*.jar'], dir: 'libs')

testCompile 'junit:junit:4.12'

compile 'com.android.support:appcompat-v7:23.4.0'

compile project(':android-sqlite-asset-helper')

compile 'com.google.android.gms:play-services-appindexing:8.1.0'

compile files('libs/GenAsync.jar')

}

Reply
avatar
Gobinath
AUTHOR
June 14, 2016 at 2:43 PM delete

Remove the following line from Gradle build and try to rebuild the project.
compile project(':android-sqlite-asset-helper')

Reply
avatar
Ami
AUTHOR
June 21, 2016 at 12:03 PM delete

I used this code but my application is 'unfortunately stopped'. what can i do now?

Reply
avatar
Gobinath
AUTHOR
June 21, 2016 at 3:57 PM delete

Hi Ami,
Could you comment below the Logcat error message when your application terminates?

Reply
avatar
Bemby Aditya
AUTHOR
June 24, 2016 at 1:34 AM delete

how to update database anyway. I used code to add record to the database, but the database didn't update/still same from the first time i insert it.

Reply
avatar
Gobinath
AUTHOR
June 24, 2016 at 8:20 AM delete

Hi
There should not be any problems in updating the database. Could you please check your code to add new record and also make sure that you haven't overwrite anything during startup. If your problem still remains, please share your code (and logcat outputs if there are any errors).

Reply
avatar
Fahrie
AUTHOR
June 25, 2016 at 11:53 PM delete

Thank you, and i added user input to add a new record thats work. The problem is when i "clear data" on my phone to get a new fresh database from this app it works fine and all databases load as usually like before i costumize it with a new record, but when i inserted a new record , the app is force close . I start again the app and the new record was there. How to resolve this ? Thanks

Reply
avatar
Gobinath
AUTHOR
June 26, 2016 at 8:53 AM delete

Hi,
Could you share the logcat error message when your app terminates? Then I can try my best to find out the exact reason for this problem.

Reply
avatar
bayu
AUTHOR
June 27, 2016 at 7:08 AM delete

Hi, very helpfull tutorial, i have question, why when i running it using emulator, the app is force close when i open it, is that any error?

Reply
avatar
Gobinath
AUTHOR
June 27, 2016 at 9:08 AM delete

Hi,
Thanks you. There should be something wrong in the application or emulator. If you share the logcat error message, I can help you to find out the problem :-)

Reply
avatar
bayu
AUTHOR
June 27, 2016 at 10:28 AM delete

actually i using different database from sqlite manager from firefox, with 5 column in the database. here the error from logchat
"Could not find class 'android.app.SharedElementCallback', referenced from method rsgisteq.kitabsholat_bukhari.MainActivity.access$super"

Reply
avatar
Gobinath
AUTHOR
June 27, 2016 at 11:07 AM delete

Hi,
I hope this error is something related to MultiDex problem. Try to run the app with an emulator using API version >= 22. If it is working, configure your application according to the standards given in this documentation. If you still have the problem, share your gradle.build file.

Reply
avatar
Fahrie
AUTHOR
June 27, 2016 at 10:17 PM delete

Thank you for reply, when i plug in my phone into computer to find error in the logcat, the problem is missing. And the app works fine when i clear data. Thats weird. But thank you for your tutorial and reply :D

Reply
avatar
Comentarista
AUTHOR
June 28, 2016 at 3:20 PM delete

Sir kindly help me i just copy the codes in step 10 and im getting this error

Reply
avatar
Comentarista
AUTHOR
June 28, 2016 at 3:37 PM delete

I already fix this sir i just have to chagne theme but i have a new problem.There were no errors on the code and i build an apk and installed it on my phone. The problem is the application open and it automatically close with a message"Unfortunately Mydatabase has stopped".How to fix this error?

Mydatabase is the name of the app

Reply
avatar
mukesh223
AUTHOR
June 28, 2016 at 8:57 PM delete

Sir I am trying to import an external database using the above program. I keep getting the error message " android.database.sqlite.SQLiteFullException: database or disk is full." My database is about 125MB large and my emulator has an internal storage of 533MB. Also, I have tried increasing the internal storage space but it doesn't work. Can you assist me with this issue please?

Reply
avatar
Gobinath
AUTHOR
June 29, 2016 at 7:28 PM delete

Hi,
Please share the logcat error message when your application terminates.

Reply
avatar
Gobinath
AUTHOR
June 29, 2016 at 7:40 PM delete

Hi,
Try to open the database in your PC and make sure it is not corrupted. Also make sure that you have enough RAM (Start from highest possible memory) allocated for the emulator. By the way, an Android application with 125MB database is not a good idea. I highly recommend you to deploy the database in a server and access it through web services.

Reply
avatar
Gobinath
AUTHOR
June 29, 2016 at 7:42 PM delete

:-D
If possible, give a try with an emulator :-)

Reply
avatar
mukesh223
AUTHOR
June 29, 2016 at 7:47 PM delete

I opened the database and it is not corrupted. How can I verify RAM in my emulator and set it to its highest value?

Reply
avatar
saba ch
AUTHOR
June 29, 2016 at 10:56 PM delete

slaam sir....i have an error "configuration default not found" while importing the library can u solve it plzzz....i wiil be thankful :)

Reply
avatar
bayu
AUTHOR
June 30, 2016 at 5:11 AM delete

Hi, i still facing this issue, i attached the snapshot of my android studio structure screenshot, also the build,gradle (Module App).
Kindly need your help

Reply
avatar
Gobinath
AUTHOR
June 30, 2016 at 9:02 AM delete

Hi,
Did you import the library as a dependency shown in Step 4 or as an external library project? Please make sure that your build.gradle structure is similar to the file shared in this link (Android default libraries and versions can be different).

Reply
avatar
Gobinath
AUTHOR
June 30, 2016 at 9:25 AM delete

Hi,
Follow these two steps:
Step 1: Add the following dependency and multiDexEnabled true in module level build.gradle.
android {
...
defaultConfig {
...
// Enabling multidex support.
multiDexEnabled true
}
...
}
dependencies {
...
compile 'com.android.support:multidex:1.0.0'
}

Step 2: Add the following application name in manifest file.
<?xml version="1.0" encoding="utf-8"?>
<manifest ...>
<application
...
android:name="android.support.multidex.MultiDexApplication">
...
</application>
</manifest>


Now try to run your application. It it does not solve your problem, please share your project with me. I will run and test it in my system.

Reply
avatar
bayu
AUTHOR
July 1, 2016 at 3:57 AM delete

hi, i found below status on log chat :
"Could not find method android.view.Window$Callback.onProvideKeyboardShortcuts, referenced from method android.support.v7.view.WindowCallbackWrapper.onProvideKeyboardShortcuts"

"VM with version 1.6.0 does not have multidex support"

btw, i cannot attach the project on this post, it is not allowed by the host

Reply
avatar
Gobinath
AUTHOR
July 1, 2016 at 8:49 AM delete

Hi,
I didn't get such an issue until now. Somewhere I found JDK 8 might be a problem for this multidex related error. You cannot attach the project in this blog itself. Upload it to Google drive/Dropbox and send the link using contact form. (Please do not share the link in comment, because if you delete the project later from the uploaded place, your comment will become unusable for others)

Reply
avatar
vvp
AUTHOR
July 1, 2016 at 8:48 PM delete

Hello.. Can you please assist me with importing the database from sdcard? I am trying to load the database from a sdcard(My input database always has the same schema but the records keep changing - so I cannot load the database in assets folder) .. @Gobinath

Reply
avatar
bayu
AUTHOR
July 2, 2016 at 8:37 AM delete

hi Gobinath, i have send the dropbox link using contact form, kindly need your help to review itu

thanks and appreciate it

Reply
avatar
Gobinath
AUTHOR
July 2, 2016 at 8:33 PM delete

Hi,
After the following changes, the app runs without any issues:
1. Modify the MainActivity as show below:
public class MainActivity extends Activity {
...
}

2. Remove the multiDexEnabled support which I asked to add earlier (Not needed)

3. Database in assets folder must be a zip file containing quotes.db with a name quotes.db.zip

Also I have updated the Android SDK versions to 23 since I don't have the updated SDK version. However, it has nothing to do with your problem.

Reply
avatar
Gobinath
AUTHOR
July 2, 2016 at 8:37 PM delete

Hi,
Sqliteassethelper cannot help for your problem. Try to manually copy the database from SD card and paste in //data////databases//.

Reply
avatar
bayu
AUTHOR
July 3, 2016 at 4:46 AM delete

Hi Gobinath,

it is wonderfull, very helpful, i will recomend this to my community.

thanks a lot

Reply
avatar
Gobinath
AUTHOR
July 3, 2016 at 6:40 AM delete

It's my pleasure to help you out :-)

Reply
avatar
vvp
AUTHOR
July 6, 2016 at 1:48 AM delete

Can you please walk me through on how I can do the same? I am trying to load my file as a .apk file on my Android device and then import the database.

Reply
avatar
mukesh223
AUTHOR
July 6, 2016 at 1:50 AM delete

How to deploy in server sir.. I dont understand.. pls help me to deploy using server...

Reply
avatar
Alexis Corney
AUTHOR
July 22, 2016 at 4:50 PM delete

Hi, I am using this tutorial to try and use a database I have created (peopleDatabase.db), I changed the name of the database and the query: 'select * from quotes' to 'select * from Employee'

I am getting the error 'no such table' when trying to run the line

Please could you help?

Thank you, Alexis

Reply
avatar
Leonid Ustenko
AUTHOR
July 22, 2016 at 9:26 PM delete

Great, thanks!

Reply
avatar
Leonid Ustenko
AUTHOR
July 22, 2016 at 9:41 PM delete

peopleDatabase.db - it is the name of database, you have to change it DatabaseOpenHelper.
When you type query 'select * from quotes' - here quote - is the name of the table.
So you also have to change table name to Employee ono your PC

Reply
avatar
Gobinath
AUTHOR
July 23, 2016 at 6:10 AM delete

Adding to Leonid Ustenko,
If you do not have any such mismatches, another possible reason could be that you haven't uninstall the app before deploying it with new database. In Android, reinstalling an application does not replace the existing database. Therefore, unistall the existing application from your emulator/device and then install the application with new database.

Reply
avatar
Gobinath
AUTHOR
July 23, 2016 at 6:10 AM delete

You are welcome :-)

Reply
avatar
Dani
AUTHOR
July 23, 2016 at 12:10 PM delete

Nice tutorial. It works for me.
I have a column in integer and I want to count the column's value and save to a variable in other layout. Please could you help? Thanks!

Reply
avatar
Gobinath
AUTHOR
July 23, 2016 at 12:12 PM delete

Hi,
Sorry for the late response. Here is a new library with sample tutorial for you :-) :
http://www.javahelps.com/2016/07/deploy-and-upgrade-android-database.html

Reply
avatar
Gobinath
AUTHOR
July 23, 2016 at 6:33 PM delete

Hi,
Do you want to count the number of rows or the sum of all values stored in that row?

Reply
avatar
Dani
AUTHOR
July 24, 2016 at 11:18 AM delete

I want sum all values stored in a row,and show it in another xml.

Reply
avatar
Gobinath
AUTHOR
July 24, 2016 at 4:23 PM delete

Hi,
You need to get the sum using SQL query and pass that value to the other layout using Intent. These two StackOverflow answers provide the solution for both:
[1]. Get sum of a column
[2]. Passing data using Intent

If you still, have issues, feel free to comment below.

[1] http://stackoverflow.com/a/20582538/4382663
[2] http://stackoverflow.com/a/7325248/4382663

Reply
avatar
Dani
AUTHOR
July 26, 2016 at 10:08 AM delete

thank's for ur help, Sir! keep working..

Reply
avatar
shafeeque
AUTHOR
August 2, 2016 at 8:35 PM delete

i have an apk file.i can't increase the coloumn of the .db file(Example: a phone directory).Let me know how i do the same?

Reply
avatar
Gobinath
AUTHOR
August 3, 2016 at 9:52 AM delete

Hi Shafeeque,
According to the SQLite documentation: Limits In SQLite, by default SQLite allows maximum 2000 columns per table. This can be changed if you compile the SQLite database by yourself. However, in the context of Android, you cannot do that. I would suggest an external MySQL database in a web server to store your data and use web services to access it.

Reply
avatar
shafeeque
AUTHOR
August 4, 2016 at 3:01 AM delete

thank you Sir for your valued reply..May I have to split in to 1x2000 1x2000 1x1000 (phone numbers&Names) rows on same .db file instead of using webserver?

Reply
avatar
Gobinath
AUTHOR
August 4, 2016 at 9:05 AM delete

Hi,
I hope that may work. However, it is not a good practice. Even though, this article discusses about importing external databases, still the database must be small in size. Importing large databases into the Android application will cause to severe performance problems.

Reply
avatar
Dani
AUTHOR
August 4, 2016 at 11:23 AM delete

Sir,I have another issues.
May I get CRUD operations tutorial for database in this assets folder?
I want to know about inserting or deleting column value.
Sorry for my bad English.
Thank's!

Reply
avatar
Gobinath
AUTHOR
August 6, 2016 at 11:28 AM delete

Hi,
Here you can find the tutorial for CRUD operations: [1]. There is nothing special in modifying a database which is imported in this way except the OpenHelper implementation.

[1] http://www.javahelps.com/2015/03/advanced-android-database.html

Reply
avatar
ahmet urun
AUTHOR
August 12, 2016 at 4:26 PM delete

Hi Gobinath,
that's a good tutorial but as it mentioned before on the comments by Bemby Aditya my database doesn't update after the Insert query i added to DatabaseAccess class. I hecked that in the same function i use(sign_user) the insert query, i can show the data i added on a toast message with using select query in the same function. but when i try to receive the data i added from another function(login_to_user) with select query it doesn't return the data. Here are my function:

public boolean login_to_user(String user_name,String password){
boolean login_status = false;
Cursor user_info = database.rawQuery("SELECT * FROM "+DatabaseHelper.TABLE_LOGIN+" WHERE user_name like '"+user_name+"' AND password like '"+password+"'",null);
int row_count = user_info.getCount();
user_info.close();
if(row_count > 0){
login_status = true;
}
return login_status;
}

public void sign_user(String user_name, String password, Context context){
database.rawQuery("INSERT INTO "+DatabaseHelper.TABLE_LOGIN+" (user_name,password) VALUES ('" + user_name + "','" + password + "')",null);
Cursor user_info = database.rawQuery("SELECT * FROM "+DatabaseHelper.TABLE_LOGIN+" WHERE user_name like '"+user_name+"' AND password like '"+password+"'",null);
while(user_info.moveToNext()){
String username=user_info.getString(0);
}
Toast toast = Toast.makeText(context,user_name,Toast.LENGTH_SHORT);
toast.show();
}

Reply
avatar
Gobinath
AUTHOR
August 15, 2016 at 11:10 AM delete

Hi,
Your sign_user method uses rawQuery method to insert the into the database. Use execSQL method instead.
If you still have issues, please comment below.

Reply
avatar
Justin
AUTHOR
August 15, 2016 at 11:22 PM delete

Hey Gobinath,
awesome tutorial but for some reason its not working for me. The first error appearing when I try to run it is this:

08-15 13:49:02.351 3692-3692/com.javahelps.com.javahelps.externaldatabasedemo E/SQLiteLog: (1) no such table: quotes

Would you happen to know what the problem is?

Reply
avatar
Gobinath
AUTHOR
August 16, 2016 at 6:47 AM delete

Hi,
The error says that you do not have databases/quotes.db.zip in the assets folder. Make sure that you have the file. If it does not work, please share the screenshot of the error log with the assets folder.

Reply
avatar
Justin
AUTHOR
August 16, 2016 at 7:27 AM delete

Do these help? I'm pretty sure I have the files setup correctly but I might be wrong.

Reply
avatar
Gobinath
AUTHOR
August 16, 2016 at 12:26 PM delete

In this error message, it says "Successfully opened database quotes.db" so the database deployment is fine. But the table is not available in the database.
1. Make sure that you have a table named quotes in your database
2. Create the zip file
3. Copy and paste the zip file in the assets/database folder
4. Manually uninstall the existing application from emulator/device (To remove the existing database)
5. Deploy and run the application.

If this does not work, please share the new logcat error message.

Reply
avatar
Justin
AUTHOR
August 16, 2016 at 9:38 PM delete

It worked! The problem was when I first ran it I made the name of the table quote instead of quotes and when I changed it to quotes I never uninstalled the app on the emulator. Thanks so much!

Reply
avatar
saba ch
AUTHOR
August 17, 2016 at 12:42 PM delete

hello sir....i apply this code,there is no error but my application crashes again and again...can u help me plz???????

Reply
avatar
saba ch
AUTHOR
August 17, 2016 at 12:56 PM delete

sir ans me soon plz..i m waiting.its urgnt.plzzz

Reply
avatar
Gobinath
AUTHOR
August 18, 2016 at 5:49 AM delete

Hi,
Share the Logcat log when your application crashes.

Reply
avatar
Dani
AUTHOR
August 26, 2016 at 8:01 AM delete

Sir,may I ask you more question?
[1]. I wanna call the method from DatabaseAccess class to another class.I've try declared : {final DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this); }, but it can't implement the method.what should I do?
[2]. If I have more function with some query in DatabaseAccess class and I want to call them more than once, do I need to open and close the connection?

Reply
avatar
saba ch
AUTHOR
August 31, 2016 at 11:39 AM delete

slaam..sir plz can u tell me how to show more than one fields in list view.for example quotes and also author names from second field from sqlite

Reply
avatar
Gobinath
AUTHOR
September 1, 2016 at 6:22 AM delete

Hi,
You have to use customized ListView with more than one TextViews. For more details, go through this tutorial: http://www.javahelps.com/2015/02/android-customized-listview.html

Reply
avatar
Gobinath
AUTHOR
September 1, 2016 at 6:23 AM delete

Sorry for the late reply. The error says "Missing databases/newdb.sqlite file (or .zip, .gz archive) in assets" . Make sure that you have "newdb.sqlite.zip" file in the assets/databases folder.

Reply
avatar
John Romiel Pantinos
AUTHOR
September 9, 2016 at 1:40 PM delete

Hi sir, i've used your code and it works perfectly great, thank you for this! BTW I copied your code and added a editxt field above the listview could you please help me to filter the listview when I input a text on edittxt. I would appreciate you help. Thank you sir

Reply
avatar
Piero Mollica
AUTHOR
September 9, 2016 at 2:14 PM delete

Hi. I have an issue. I'm trying to open a .db file and according with my logcat this database is "successfully opened". But i can't see anything in my list. I don't have any error. I have tried to put some line to understand where the programm is blocked, and i think the problem is after i call open method in the mainActivity. Any idea?

Reply
avatar
Gobinath
AUTHOR
September 9, 2016 at 4:26 PM delete

Hi,
If you deployed an empty database earlier, it may happen. Try to uninstall the application from the device or emulator manually and then install the app again. If it does not help, try to print the data you received instead of showing them in ListView. Then you can identify whether the problem is in UI related code or in DB related code.

Reply
avatar
Piero Mollica
AUTHOR
September 12, 2016 at 6:55 PM delete

Hi. Thank you for your answer. I solved that problem. Now i'd like to do all this process in background, using an IntentService or a Service. So i want that my application updates the list if for example i add a row in a table. i tried with an intent service but i'm not able to trasmit the result to the activity. I'm able to transmit just a single string. I'm trying to send an ArrayList. Do you have any suggest?

Reply
avatar
MHD
AUTHOR
October 14, 2016 at 1:50 AM delete

Exception!! :(
I do this step-by-step

but I got this error:
java.lang.NullPointerException: Attempt to invoke virtual method 'int android.database.sqlite.SQLiteDatabase.getVersion()' on a null object reference

Reply
avatar
Gobinath
AUTHOR
October 14, 2016 at 5:34 AM delete

Hi,
It seems to be an internal error in the library. Could you please share the complete error log so that I can go through it and try to point out the problem.

Reply
avatar
hend ahmed
AUTHOR
October 16, 2016 at 7:26 PM delete

thanks sir, this code is awesome,
i have database with to columns and i wanna to show them,
when i call the column name in cursor.getColumnIndex(word), cursor.getColumnIndex(translation)
it doesn't declared yet! why this happen?!

Reply
avatar
Gobinath
AUTHOR
October 17, 2016 at 9:39 AM delete

Hi,
I couldn't reproduce the error in my system. Could you please share the complete error log below?

Reply
avatar
Topof Learning
AUTHOR
October 18, 2016 at 11:32 PM delete

Awesome tutorial,
Just I have question how can I add new colunm or new table the existing database that I added to assets.

Reply
avatar
Gobinath
AUTHOR
October 19, 2016 at 5:17 AM delete

Add it as usual. Then uninstall the application manually and install it again. If you are asking about new version release(with a new column in the database), have a look at https://github.com/jgilfelt/android-sqlite-asset-helper

Reply
avatar
Topof Learning
AUTHOR
October 19, 2016 at 7:34 PM delete

Thank you Gobinath for your prompt reply. I saw Jeff gifelt page before, and it doesn't explain how to do it exactly. The question is as follow:

How can I add a new column or new table to the database that pre-populated (in the assets) using SQLiteAssetHelper.
I originally preload a simple table with 3 columns:


id
question
answer

Once installed by the user, the app add automatically in the first usage some columns or tables that will be
populated with user data, like marked as favorite, seen before,
edited...
Hope my question is quite clear now.

Reply
avatar
Hemant Salunkhe
AUTHOR
November 19, 2016 at 10:54 AM delete

sir i have done all things as you have expain above , but i got error after installing apk is unfortunately your app stop. replay me as soon as possible ,i dont know what to do ?

Reply
avatar
Amgad muhammad
AUTHOR
November 21, 2016 at 8:23 PM delete

Here is mine;
11-21 16:48:20.883 8996-8996/com.amgad.hayy E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.amgad.hayy, PID: 8996
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.amgad.hayy/com.amgad.hayy.MainActivity}: java.lang.NullPointerException: Attempt to invoke virtual method 'int android.database.sqlite.SQLiteDatabase.getVersion()' on a null object reference
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3319)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3415)
at android.app.ActivityThread.access$1100(ActivityThread.java:229)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1821)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:7331)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)
Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'int android.database.sqlite.SQLiteDatabase.getVersion()' on a null object reference
at com.readystatesoftware.sqliteasset.SQLiteAssetHelper.getWritableDatabase(SQLiteAssetHelper.java:178)
at com.amgad.hayy.data.DatabaseAccess.open(DatabaseAccess.java:47)
at com.amgad.hayy.MainActivity.onCreate(MainActivity.java:40)
at android.app.Activity.performCreate(Activity.java:6904)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1136)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3266)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3415)
at android.app.ActivityThread.access$1100(ActivityThread.java:229)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1821)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:7331)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)

Reply
avatar
Gobinath
AUTHOR
November 30, 2016 at 5:44 AM delete

Hi,
Sorry for the late response. Could you please check whether you are passing a valid context to the DatabaseAccess constructor. If it doesn't help you, please share your project using contact us page. (You need to upload the project somewhere and send the link to me)

Reply
avatar
Gobinath
AUTHOR
November 30, 2016 at 5:44 AM delete

Hi,
Sorry for the late response. Could you please share the detailed logcat error message.

Reply
avatar
rightleft
AUTHOR
December 10, 2016 at 1:11 AM delete

Sir can i use this method for bulk insertion of preloaded data (like 1000 data) ? if not can you recommend me some other sample app or any related article for bulk insertion ...

Thanks in advance ...........

Reply
avatar
vivek
AUTHOR
December 15, 2016 at 9:57 PM delete

FAILURE: Build failed with an exception.

* What went wrong:
A problem occurred configuring project ':app'.
> Could not resolve all dependencies for configuration ':app:_debugCompile'.
> Could not find any matches for com.example.sqliteasset:sqliteassethelper:+ as no versions of com.example.sqliteasset:sqliteassethelper are available.
Searched in the following locations:
https://jcenter.bintray.com/com/example/sqliteasset/sqliteassethelper/maven-metadata.xml
https://jcenter.bintray.com/com/example/sqliteasset/sqliteassethelper/
file:/C:/Users/mahadev/AppData/Local/Android/sdk/extras/android/m2repository/com/example/sqliteasset/sqliteassethelper/maven-metadata.xml
file:/C:/Users/mahadev/AppData/Local/Android/sdk/extras/android/m2repository/com/example/sqliteasset/sqliteassethelper/
file:/C:/Users/mahadev/AppData/Local/Android/sdk/extras/google/m2repository/com/example/sqliteasset/sqliteassethelper/maven-metadata.xml
file:/C:/Users/mahadev/AppData/Local/Android/sdk/extras/google/m2repository/com/example/sqliteasset/sqliteassethelper/
Required by:
ExtrnalDemo:app:unspecified

* Try:

Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output.
BUILD FAILED

dependencies is not set
how to set

Reply
avatar
vivek
AUTHOR
December 15, 2016 at 10:17 PM delete

12-15 22:14:03.690 3596-3596/? I/art﹕ Not late-enabling -Xcheck:jni (already on)
12-15 22:14:04.007 3596-3596/com.example.mahadev.extrnaldemo E/SQLiteLog﹕ (1) no such table: quotes
12-15 22:14:04.007 3596-3596/com.example.mahadev.extrnaldemo D/AndroidRuntime﹕ Shutting down VM
--------- beginning of crash
12-15 22:14:04.008 3596-3596/com.example.mahadev.extrnaldemo E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.example.mahadev.extrnaldemo, PID: 3596
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.mahadev.extrnaldemo/com.example.mahadev.extrnaldemo.MainActivity}: android.database.sqlite.SQLiteException: no such table: quotes (code 1): , while compiling: SELECT * FROM quotes
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2325)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2390)
at android.app.ActivityThread.access$800(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5257)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
Caused by: android.database.sqlite.SQLiteException: no such table: quotes (code 1): , while compiling: SELECT * FROM quotes
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
at com.example.mahadev.extrnaldemo.DatabaseAccess.getQuotes(DatabaseAccess.java:58)
at com.example.mahadev.extrnaldemo.MainActivity.onCreate(MainActivity.java:19)
at android.app.Activity.performCreate(Activity.java:5990)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1106)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2278)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2390)
at android.app.ActivityThread.access$800(ActivityThread.java:151)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:135)
at android.app.ActivityThread.main(ActivityThread.java:5257)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)

Reply
avatar
Gobinath
AUTHOR
December 18, 2016 at 8:59 PM delete

The error says "no such table: quotes". Please make sure that you have the quotes table in the database. If it is already there, uninstall the application from your Android device/emulator manually and then install the app again to delete the existing database from the device (Overwriting the app by reinstalling does not overwrite the database. That's why you need to uninstall the app first).

Reply
avatar
Gobinath
AUTHOR
December 18, 2016 at 9:00 PM delete

Sorry for the late response.
Yes you can but make sure that the apk size does not exceed the limited size.

Reply
avatar
Gobinath
AUTHOR
December 18, 2016 at 9:04 PM delete

Hi,
The dependency is com.readystatesoftware.sqliteasset:sqliteassethelper:+ not com.example.sqliteasset:sqliteassethelper:+.

Reply
avatar
Misam
AUTHOR
December 31, 2016 at 2:48 AM delete

Hi
Thank you a lot.

Reply
avatar
AwQ
AUTHOR
January 2, 2017 at 7:33 AM delete

Sir, nice tutorial, its works nice! But, how can i change ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, quotes);
this.listView.setAdapter(adapter); to return an image?

Reply
avatar
Gobinath
AUTHOR
January 5, 2017 at 5:53 AM delete

There is a separate article on how to import images using an external database. Have a look at http://www.javahelps.com/2016/04/import-database-with-images-in-android.html

Reply
avatar
Gobinath
AUTHOR
January 5, 2017 at 6:00 AM delete

Hi,
I think there may be issues with the database file in assets folder. Make sure that you have .zip file in the assets/databases folder (See the screenshot in step 7). If it is not the problem, manually uninstall the existing application from your emulator or device and then install the application again.

Reply
avatar
ahuramazda
AUTHOR
January 5, 2017 at 8:58 PM delete

thanks for answer. i find the problem. it's version issue. i used a table that has version number 5. in new app i think it will begin from 1 but it does not. so databasehandler goes to onUpgrade method and drop the table.

well, where can i see the version of database or manipulate it? Or how it is possible to upgrade a new database and giving a new version number.

Reply
avatar
geethadevi
AUTHOR
January 6, 2017 at 12:05 PM delete

I fixed the solutions use import and external database in android..Android Training in Chennai

Reply
avatar
hadi elton
AUTHOR
January 11, 2017 at 2:46 PM delete

hi sir
this codes work for me perfectly . very thank you for this learning..
but i need that my list view show more than one field(column) in my project . ( for example quotes,author & ... )
i see ur "customize list view" learning in "url:http://www.javahelps.com/2015/02/android-customized-listview.html".. thank u for that . but i cant use this example for an external database project like this example ( Import and Use External Database in Android ) .
if u can , tell me that where of this example (Import and Use External Database in Android) must changed for a customizelistview( 3 field listview) please...
thank you so much

Reply
avatar
Gobinath
AUTHOR
January 14, 2017 at 6:52 AM delete

Sorry for the late response.
You can combine both projects. Create a custom list view as given in customized list view tutorial. Then follow this tutorial, import the database and retrieve the data. Feed the retrieved data to the custom adapter.

Reply
avatar
hadi elton
AUTHOR
January 15, 2017 at 2:07 AM delete

thank u bro .. i solved the problem ..
do you have any learning or idea for a multi table database program ..
i have a project with 59 button & 59 table external database .
by clicking every button i want to show one of tables in list view .
whats the most sample & best way for this ?u dont have any tutorial ?

Reply
avatar
hadi elton
AUTHOR
January 16, 2017 at 12:06 PM delete

solved the problem . thank again

Reply
avatar
Mudit Wadhwa
AUTHOR
January 22, 2017 at 1:44 AM delete

Hi thanks for the tutorial. I want to know that can I use 2 tables in my database and use one table in one method of DatabaseAccess and another table in another method in DatabaseAccess. If so then a little help with the code.

Reply
avatar
Gobinath
AUTHOR
January 23, 2017 at 3:53 PM delete

I do not have tutorial for this specific requirement. However you can import database with any number of tables in this way. You can generalize the data loading if all the tables are in the same structure.

Reply
avatar
Gobinath
AUTHOR
January 23, 2017 at 3:56 PM delete

You can import a database with any number of tables. Just adding another read method is enough. Notice that "SELECT * FROM quotes" is the only place, I refer the table name.

Reply
avatar
Omer
AUTHOR
February 9, 2017 at 6:02 PM delete

Hi sir , thanks for the tutorial you realy help me a lot. But this codes work for me perfectly only when i paste the quotes.db file inside the (assets)databases folder (step 7) but if paste the quotes.db.zip inside the assets i got error after installing apk it says:" Unfortunately External Database Demo has stoped. do you can tell me what to do in order to solve this or why this is happens? thanks for yours tutorials

Reply
avatar
Omer
AUTHOR
February 9, 2017 at 9:52 PM delete

hi, does not matter I understand my mistake. thank you a lot!

Reply
avatar
swetha
AUTHOR
February 10, 2017 at 12:46 PM delete

Hi Sir,
While building the app, I am getting error "Failed to resolve: com.readystatesoftware.sqliteasset:sqliteassethelper:+"
Could u please help me?

Reply
avatar
Hemant Verma
AUTHOR
February 10, 2017 at 2:37 PM delete

thnx for helping

Reply
avatar
Gobinath
AUTHOR
February 11, 2017 at 8:39 PM delete

Hi,
Could you please check whether you have proper internet connection (If you are behind proxy, need to configure it in Android Studio.). If it does not resolve your problem, please comment below your gradle file code.

Reply
avatar
vivek
AUTHOR
February 16, 2017 at 7:24 PM delete

i will try this exmple.
click in lisview then next page display textview.
drag and drop to button next and privous.
how to this code complete in sqlite database in extrnal database

Reply
avatar
ashu kakde
AUTHOR
March 18, 2017 at 9:25 AM delete

hi, i tried this code on an intent activity but it says following error:
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.hppc.mood/com.example.hppc.mood.Eatry}: com.readystatesoftware.sqliteasset.SQLiteAssetHelper$SQLiteAssetException: Missing databases/quotes.db file (or .zip, .gz archive) in assets, or target folder not writable

Reply
avatar
Gobinath
AUTHOR
March 20, 2017 at 4:58 AM delete

Can you make sure that you have "assets/databases/quotes.db.zip"?

Reply
avatar
Cindy Michalowski
AUTHOR
March 21, 2017 at 12:47 AM delete

Thanks so much for this tutorial! Exactly what I needed. I noticed that I get the "No such table" error if I try to add the .zip file to the assets/databases folder. Not a big deal for me to just add mydatabase.db, as the database that I was to launch my app with only contains about 40 items.

Thanks again!

Reply
avatar
Gobinath
AUTHOR
March 21, 2017 at 8:58 PM delete

Hi,
I think you have modified your table (renamed, added a new table or removed an existing one) since the first installation. Try to manually uninstall the application manually from your testing device and reinstall the app again.

Reply
avatar
Rachid Sghir
AUTHOR
March 25, 2017 at 11:01 PM delete

Hi.
I've got this problem when i try open my app:
W/SQLiteAssetHelper: could not open database quotes.db - unkown error (code 14): Could not open databse.

Reply
avatar
Rachid Sghir
AUTHOR
March 26, 2017 at 8:39 PM delete

Hi
Thank you for this tutorial is very helpful.
I just have a question:
I created two tables (quotes[id, quote] and favoris[id, quote_id]) .

and I created a View " quoteFavoris" with this request:
Create view quoteFavoris as
Select id, quote from quotes
inner join favoris
On quotes.id = favoris.quote_id

So how can we read data from a view on the database.
I have tried to but I have got an erreur that no such table quotrFavoris.

Reply
avatar
Gobinath
AUTHOR
March 28, 2017 at 5:58 AM delete

Hi,
I haven't played with Views in SQLite but it should work with sqlite-asste-helper if it is working with regular Android applications. Make sure that you have uninstalled the app manually from the device and instal it again after each and every modifications you made in the database. If it does not help, try to create the view from the Android app itself.

Reply
avatar
Ewo
AUTHOR
April 23, 2017 at 6:33 PM delete

Hi, what if I have 2 or more columns in the database? How should I put it in the ArrayList and display it? Thanks

Reply
avatar
Gobinath
AUTHOR
April 25, 2017 at 7:13 PM delete

Hi,
How to create a customized list is explained in this tutorial: http://www.javahelps.com/2015/02/android-customized-listview.html

Reply
avatar
Ewo
AUTHOR
April 27, 2017 at 8:03 AM delete

Thank you very much. In my understanding, SQLiteAssetHelper copies the database from the asset folder to a private folder inside the app. I believe it just does it once, when the app is first launch. So your code here: Cursor cursor = database.rawQuery("SELECT * FROM quotes", null); access the copied database, right? So is it possible if I have 2 type of query methods, one for reading all data and one for reading a specific group of data?

Reply
avatar
Gobinath
AUTHOR
April 27, 2017 at 3:56 PM delete

Exactly. You are right. Once the database is copied, it is nothing other than an ordinary database. You can have any number of queries and any type of queries.

Reply
avatar
eF Bee
AUTHOR
May 10, 2017 at 10:34 PM delete

Hi Gobinath,
Really appreciate this post, unfortunately I found it after searching for just short of 5 hours.
My issue was the use of SQLiteOpenHelper as opposed to what I needed which was SQLiteAssetHelper.
Nice post once again.

Reply
avatar
hemn
AUTHOR
June 1, 2017 at 4:22 AM delete

HI Gobinath Thank u for your tutorial, how to your project can searchable with EditTextand and search button?

Reply
avatar
Ahmed Alkibsi
AUTHOR
June 19, 2017 at 4:10 AM delete

Hello, I Created DB . How I can search in DB External SQlit .
My DB SQlite Contain texts that showed in many fragments, , For that I need to make search for any word in my all data base and view all result in list view, all fragments have this word.
Please Help me.

Reply
avatar
JHHHH
AUTHOR
July 5, 2017 at 12:21 AM delete

sorry but i want to know if i will not use this getQuotes function from your code than also data will be copy to internal data base and what i have to do if i want to add some data and read as well form external database table which is not being created in my app only after running your code that is being accessible to me at app please help

Reply
avatar
KSC
AUTHOR
July 5, 2017 at 12:36 PM delete

sorry but i want to know if i will not use this getQuotes function from your code than also data will be copy to internal data base and what i have to do if i want to add some data and read as well form external database table which is not being created in my app only after running your code that is being accessible to me at app please help

Reply
avatar
Omer
AUTHOR
July 10, 2017 at 11:40 AM delete

Hi, thanks for the tutorial you realy help me a lot!
But I have a question:
I created a database in your way and everyrhing works prefectly.
but now I want to update the database, in the update i want to add a new table to the database.
(the new table contain column of pictures so i need to import the table like you explain)
I tried first to create another db with this table (in your way) and then copy the table to the first db but it isn't work.
And in the second time i increased the version number but then the changes I do to the the "old" database were gone.
so my question is if there is a simple way to add a new table to existing database in update?
I would be very happy if you could help me with this.

Reply
avatar
Mark Rossmiller
AUTHOR
July 21, 2017 at 11:26 PM delete

https://uploads.disquscdn.com/images/364bfcd243139dce0724b427c5512082a0103c6e773850116ddf9fa60a32c09d.jpg
Hello, Thanks for the External Database tutorial, I followed instructions exactly, except I chose to use a different database of 1,600 colleges selected by state residence and use a ScrollActivity app so that I could scroll through all list view college data rows.

My problem is that I can only view only the first college data row in the scroll view activity (see attached image). Why are not all list view data rows showing in the scroll view layout - Can you advise?

The relevant section of code int he ScrollActivity.java file is as follows:

public class ScrollingActivity extends AppCompatActivity {
private ListView listView;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_scrolling);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
setSupportActionBar(toolbar);

FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
.setAction("Action", null).show();
}
});
// database access code from .MainActivity example used in .ScrollActivity

this.listView = (ListView) findViewById(R.id.listView);
DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
databaseAccess.open();
List colleges = databaseAccess.getColleges();
databaseAccess.close();
ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, colleges);
this.listView.setAdapter(adapter);
}

The method in the DatabaseAccess.java file is as follows:

public List getColleges() {
List list = new ArrayList<>();
Cursor cursor = database.rawQuery("SELECT institution_name FROM titleix WHERE state_cd = \"CA\" " , null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
list.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
return list;
}

Reply
avatar
Mark Rossmiller
AUTHOR
July 22, 2017 at 1:13 AM delete

I have tried to declare a global variable in the OnCreate() method so that I can assign it the value of the a local variable declared in the for loop scope; but Android error notice states the variable must be declared final, then if I do that it cannot be assigned the local for loop variable value. How do I get this to work? https://uploads.disquscdn.com/images/c214d004aaf034784f282914581df6542137bb5f1b7e0d07ac8f37f4bd89a84f.jpg

Reply
avatar
Carlos Ivan Olvidaos
AUTHOR
July 22, 2017 at 9:15 PM delete

Thanks friend. Regards from México.

Reply
avatar
Mark Rossmiller
AUTHOR
July 23, 2017 at 12:09 AM delete

Thanks for the tutorial, but I have an issue: Why does my scrollview app only display one list item from the database?

I only modified the database to be retrieved into the listview, and using a scrollview activity.
_________________________________________________________________________________________

public class ScrollingActivity extends AppCompatActivity {
private ListView listView;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_scrolling);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
setSupportActionBar(toolbar);

FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
.setAction("Action", null).show();
}
});

this.listView = (ListView) findViewById(R.id.listView);
DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
databaseAccess.open();
List colleges = databaseAccess.getColleges();
databaseAccess.close();
ArrayAdapter adapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, colleges);
this.listView.setAdapter(adapter);
}

https://uploads.disquscdn.com/images/364bfcd243139dce0724b427c5512082a0103c6e773850116ddf9fa60a32c09d.jpg

Reply
avatar
Omer
AUTHOR
July 25, 2017 at 12:47 AM delete

Hi, thanks for the tutorial you realy help me a lot!
I just have one question:
I created two database in your way and everyrhing works prefectly.
but now I want to update Database1, in the update i want to copy all the values from one table in Database 1 to another table in Database 2.
I would be very happy if you could help me with this and explain me where i need to write the code (in DatabaseAccess1/DatabaseAccess2/ in Activity?)

i think i can do something like this:
INSERT INTO * db2.tbl
SELECT * FROM db1.tbl

Reply
avatar
Gobinath
AUTHOR
July 25, 2017 at 6:07 AM delete

Hi,
Please check the database and the getColleges method. The code you have shared seems to be good.

Reply
avatar
Gobinath
AUTHOR
July 25, 2017 at 6:15 AM delete

Hi,
The easy way to do this is reading the DB1 into memory and then write them into DB2. It is not efficient but should resolve your problem. An efficient way is attaching the DB1 into DB2 (Do a Google search on 'ATTACH SQLite')

Reply
avatar

Contact Form

Name

Email *

Message *