Databases
StackOverflow2013
Postsdbo
POIssues when using sqlite db copied from assets folder on Android
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIssues when using sqlite db copied from assets folder on Android
    text
    copied!<p>Although we are generally getting favorable reviews, the following errors are piling up and giving us grief:</p> <pre><code>java.lang.RuntimeException: Unable to start activity ComponentInfo{com.svs.missions.MissionsGalleryView}: android.database.sqlite.SQLiteException: no such table: missions: , while compiling: SELECT * FROM missions WHERE banner is not null order by title COLLATE NOCASE at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2787) at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2803) at android.app.ActivityThread.access$2300(ActivityThread.java:135) at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2136) at android.os.Handler.dispatchMessage(Handler.java:99) at android.os.Looper.loop(Looper.java:144) at android.app.ActivityThread.main(ActivityThread.java:4937) at java.lang.reflect.Method.invokeNative(Native Method) at java.lang.reflect.Method.invoke(Method.java:521) at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868) at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626) at dalvik.system.NativeStart.main(Native Method) Caused by: android.database.sqlite.SQLiteException: no such table: missions: , while compiling: SELECT * FROM missions WHERE banner is not null order by title COLLATE NOCASE at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method) at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91) at android.database.sqlite.SQLiteCompiledSql.&lt;init&gt;(SQLiteCompiledSql.java:64) at android.database.sqlite.SQLiteProgram.&lt;init&gt;(SQLiteProgram.java:80) at android.database.sqlite.SQLiteQuery.&lt;init&gt;(SQLiteQuery.java:46) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:53) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1409) at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1293) at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1248) at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1328) ... </code></pre> <p>It seems that not all the tables get created when copying the db from the assets folder. Here is one feedback message from a user in the market panel:</p> <pre><code>“Whenever I click on "missions" the app force closes. all other links seem ok.” </code></pre> <p>So it is really hit and miss. For some users, it appears none of the tables get copied.</p> <p>I’m tempted to just rip out the database copy altogether and create the db on launch, but then I still cannot guarantee the db will be created (and won't get any of the metadata that ships with the app, unless we download it on launch, lots of data btw). Seems a little silly to me though. So many different platforms to support. So disjointed.</p> <p>Funny that I call table creation code on every launch just to be sure the db tables exist, and it doesn’t fix it (snippet here):</p> <pre><code> this.openDB(); db.execSQL("CREATE TABLE if not exists missions (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, created TEXT, orgHTML TEXT, UNIQUE(id) ON CONFLICT IGNORE);"); ... this.closeDB(); </code></pre> <p>I found this: </p> <p><a href="http://www.anddev.org/networking-database-problems-f29/missing-table-in-sqlite-with-specific-version-of-desire-hd-t50364.html" rel="nofollow">http://www.anddev.org/networking-database-problems-f29/missing-table-in-sqlite-with-specific-version-of-desire-hd-t50364.html</a></p> <p>and am already applying the workaround, but still getting error reports.</p> <p>I don’t know for sure if it is only on HTC devices, just following the lead of that forum post above.</p> <p>Background:</p> <p>The db is in the assets folder. It is 2.8mb large, and I assign a .mp3 extension to avoid compression so that it will copy (fix an old issue that is well known).</p> <p>One thing I haven't added yet is a check for free space, but that will be next. I may just create the whole db empty if there is not enough free space I suppose. But if it runs out of free space when copying the assets folder db, the db should be corrupted I would think.</p> <p>BTW: Oncreate and Onupgrade are currently empty:</p> <pre><code>@Override public void onCreate(SQLiteDatabase sqLiteDatabase) { } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { } </code></pre> <p>This works on all of our test devices:</p> <p>HTC Aria, 2.1, small footprint, 16mb heap</p> <p>Original Droid, 2.2.1 </p> <p>Samsung Galaxy, 2.2 </p> <p>Asus tablet (don’t have model), 3.0 </p> <p>Xoom, 3.1</p> <p>The DB class extends sqliteopenhelper:</p> <pre><code>public class DBManager extends SQLiteOpenHelper { </code></pre> <p>Here is the code used (Since none of our users have contacted us yet, we don't know what the Log.e writes are, unfortunately):</p> <pre><code> ... private static final String DB_NAME = "db.mp3"; private static final String DB_PATH = "/data/data/com.svs/databases/"; ... public boolean createNewDatabaseFromAsset() { File file = new File(DB_PATH , DB_NAME); if (file.exists()) { return true; } SQLiteDatabase db_Read = null; db_Read=this.getReadableDatabase(); db_Read.close(); try { InputStream assetsDB = this.context.getAssets().open(DB_NAME); OutputStream dbOut = new FileOutputStream(DB_PATH + DB_NAME); byte[] buffer = new byte[1024]; int length; while ((length = assetsDB.read(buffer)) &gt; 0) { dbOut.write(buffer, 0, length); } dbOut.flush(); dbOut.close(); assetsDB.close(); this.buildTables(); Log.i(TAG, "New database created..."); return true; } catch (IOException e) { Log.e(TAG, "IO Error. Could not create new database..."); e.printStackTrace(); this.buildTables(); return false; } catch (SQLiteException e) { Log.e(TAG, "Database Locked. Could not create new database..."); e.printStackTrace(); this.buildTables(); return false; } } public DBManager openDB() { if (!createNewDatabaseFromAsset()) return null; if (db==null){ dbManager = new DBManager(context); db = dbManager.getWritableDatabase(); } return this; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload