Unable to use external database in Android app

Question

I want to use external datbase in MySQL . I have an archive database databse.db.zip and copied to the assets folder of my Java Android project. Finally, I created a class to open the database:

 import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteQueryBuilder; import com.readystatesoftware.sqliteasset.SQLiteAssetHelper; public class databaseOpen extends SQLiteAssetHelper { private static final String DATABASE_NAME = "parole.db"; private static final int DATABASE_VERSION = 1; public databaseOpen(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } public String risposta(String parolain ){ SQLiteDatabase db = getReadableDatabase(); SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); String[] selectionArgs = {"ciao"}; Cursor c =db.query("prima", null, null,selectionArgs, null, null, null); c.moveToFirst(); return c.getString(0); } } 

And I returned it to my main :

 private databaseOpen db; @Override protected void onCreate(Bundle savedInstanceState) { db = new databaseOpen(this); Toast toast=Toast.makeText(this, db.risposta("ciao"), Toast.LENGTH_LONG); } 

but that will not work. Here is the result I get:

 09-26 15:20:23.761 1099-1099/? I/SQLiteAssetHelper successfully opened database parole.db 09-26 15:20:23.764 1099-1099/? E/SQLiteLog (1) no such table: prima 09-26 15:20:23.783 1099-1099/? D/AndroidRuntime Shutting down VM 09-26 15:20:23.801 1099-1099/? E/AndroidRuntime FATAL EXCEPTION: main Process: com.example.roby.jarvis, PID: 1099 java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.roby.jarvis/com.example.roby.jarvis.Home}: android.database.sqlite.SQLiteException: no such table: prima (code 1): , while compiling: SELECT * FROM prima 

Answer 1

I want to use external datbase in mysql, I have zipping this (databse.db.zip) and copy this in the permissions folder in java project

For the record, either you use the wrong terminology or are confused. Android and the code in your post works with SQLite. It cannot work with MySQL.

When something does not work, read the error message, which usually explains the problem, for example:

09-26 15: 20: 23,764 1099-1099 /? E / SQLiteLog: (1) there is no such table: prima

A table named prima does not exist in the file.

The next step for debugging is to check the contents of the file on your PC using the sqlite3 command line sqlite3 or similar, and see what tables it has ( .schema command).

Answer 2

First: You do not copy the Database file from your resource file to your device, which will obviously result in no such table found .

Try this code, its work for me. (I also commented on different sections for your understanding)

 import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import android.os.Build; import android.util.Log; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; /** * Created by HP-HP on 23-07-2015. */ public class DataBaseHelper extends SQLiteOpenHelper { //The Android default system path of your application database. private static String DB_PATH ; private static String DB_NAME = "profanityv2"; private SQLiteDatabase myDataBase; private final Context myContext; /** * Constructor * Takes and keeps a reference of the passed context in order to access to the application assets and resources. * @param context */ public DataBaseHelper(Context context) { super(context, DB_NAME, null, 1); if(android.os.Build.VERSION.SDK_INT >= 4.2){ DB_PATH = context.getApplicationInfo().dataDir + "/databases/"; } else { DB_PATH = "/data/data/" + context.getPackageName() + "/databases/"; } this.myContext = context; } /** * Creates a empty database on the system and rewrites it with your own database. * */ public void createDataBase() throws IOException { boolean dbExist = checkDataBase(); if(dbExist){ //do nothing - database already exist }else{ //By calling this method and empty database will be created into the default system path //of your application so we are gonna be able to overwrite that database with our database. this.getReadableDatabase(); try { copyDataBase(); } catch (IOException e) { throw new Error("Error copying database"); } } } /** * Check if the database already exist to avoid re-copying the file each time you open the application. * @return true if it exists, false if it doesn't */ private boolean checkDataBase(){ SQLiteDatabase checkDB = null; try{ String myPath = DB_PATH + DB_NAME; checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY); }catch(SQLiteException e){ //database does't exist yet. try { myContext.deleteDatabase("profanity"); } catch (Exception e1) { e1.printStackTrace(); } } if(checkDB != null){ checkDB.close(); } return checkDB != null; } /** * Copies your database from your local assets-folder to the just created empty database in the * system folder, from where it can be accessed and handled. * This is done by transfering bytestream. * */ private void copyDataBase() throws IOException{ //Open your local db as the input stream InputStream myInput = myContext.getAssets().open(DB_NAME); // Path to the just created empty db String outFileName = DB_PATH + DB_NAME; //Open the empty db as the output stream OutputStream myOutput = new FileOutputStream(outFileName); //transfer bytes from the inputfile to the outputfile byte[] buffer = new byte[1024]; int length; while ((length = myInput.read(buffer))>0){ myOutput.write(buffer, 0, length); } //Close the streams myOutput.flush(); myOutput.close(); myInput.close(); } public void openDataBase() throws SQLException { //Open the database String myPath = DB_PATH + DB_NAME; myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS|SQLiteDatabase.CREATE_IF_NECESSARY ); } @Override public synchronized void close() { if(myDataBase != null) myDataBase.close(); super.close(); } public Cursor getData() { Cursor c = null; try { c = myDataBase.rawQuery("SELECT * FROM profanity_table", null); } catch (Exception e) { e.printStackTrace(); } // Note: Master is the one table in External db. Here we trying to access the records of table from external db. return c; } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } // Add your public helper methods to access and get content from the database. // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy // to you to create adapters for your views. } 

using

 DataBaseHelper dataBaseHelper = new DataBaseHelper(this); try { dataBaseHelper.createDataBase(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { dataBaseHelper.openDataBase(); }catch(SQLException sqle){ throw sqle; } Cursor c = dataBaseHelper.getData(); if(c!=null) { /* your cursor code */ c.close(); } dataBaseHelper.close(); 

Note: Some devices may not be able to copy the database to the device. (Known devices: OnePlus)

Remember to add this permission.

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

in your manifest.

Edit 1: OnePlus can copy the database to /data/data/<package-name>/databases/<filename>.db , but it does not allow access to this data, and I have no idea about it.