Android upgrading sql database using android-sqlite-asset-helper

Question

I am using android-sqlite-asset-helper. I have a database file which I am using only to read some entries. I have a new database file which I want to upgrade and I am following the instructions as per the documentation here. As per the description, I have to generate SQL commands text file with certain name format. I used SQLite Compare Utility to generate commands. As the tables differ by only an entry in data element, I am getting a simple SQL command file which is:

    -- Generated by SQLite Compare utility

-- The script can be used to migrate database
-- C:\database_06102015_experiment\olddatabase\vibhakti_pratyaya_san.sqlite schema
-- to the schema of database
-- C:\database_06102015_experiment\vibhakti_pratyaya_san.sqlite

BEGIN TRANSACTION;


COMMIT TRANSACTION;

When I copy the SQL command file and database ZIP file (which contains the *-v2.db file only) and run I get these errors:

06-11 13:30:55.140: I/SQLiteAssetHelper(1093): successfully opened database vibhakti_pratyaya_san
06-11 13:30:55.140: W/SQLiteAssetHelper(1093): Upgrading database vibhakti_pratyaya_san from version 1 to 2...
06-11 13:30:55.150: W/SQLiteAssetHelper(1093): processing upgrade: databases/vibhakti_pratyaya_san_upgrade_1-2.sql
06-11 13:30:55.160: E/SQLiteLog(1093): (1) statement aborts at 2: [-- Generated by SQLite Compare utility

06-11 13:30:55.160: E/SQLiteLog(1093): 

06-11 13:30:55.160: E/SQLiteLog(1093): -- The script can be used to migrate database

06-11 13:30:55.160: E/SQLiteLog(1093): -- C:\database_06102015_experiment\olddatabase\vibhak
06-11 13:30:55.220: E/SQLiteAssetHelper(1093): Couldn't open vibhakti_pratyaya_san for writing (will try read-only):
06-11 13:30:55.220: E/SQLiteAssetHelper(1093): android.database.sqlite.SQLiteException: cannot start a transaction within a transaction (code 1)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:734)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1603)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.readystatesoftware.sqliteasset.SQLiteAssetHelper.onUpgrade(SQLiteAssetHelper.java:326)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.readystatesoftware.sqliteasset.SQLiteAssetHelper.getWritableDatabase(SQLiteAssetHelper.java:199)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.readystatesoftware.sqliteasset.SQLiteAssetHelper.getReadableDatabase(SQLiteAssetHelper.java:257)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.mamillasoft.vipra_sanskrit.MyDatabase.getAllLikeList(MyDatabase.java:345)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.mamillasoft.vipra_sanskrit.MainActivity.onCreate(MainActivity.java:334)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.Activity.performCreate(Activity.java:5133)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2175)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2261)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.ActivityThread.access$600(ActivityThread.java:141)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1256)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.os.Handler.dispatchMessage(Handler.java:99)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.os.Looper.loop(Looper.java:137)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at android.app.ActivityThread.main(ActivityThread.java:5103)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at java.lang.reflect.Method.invokeNative(Native Method)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at java.lang.reflect.Method.invoke(Method.java:525)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:737)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
06-11 13:30:55.220: E/SQLiteAssetHelper(1093):  at dalvik.system.NativeStart.main(Native Method)
06-11 13:30:55.241: D/AndroidRuntime(1093): Shutting down VM

Questions:

1. Why is it not able to open the database to write?

2. What is the meaning of the exception:

cannot start a transaction within a transaction

3. Why are the comments in SQL command file treated as code lines?

Answer 1

The entire upgrade process is already wrapped in a transaction by SQLiteAssetHelper, so you must not use transactions in the script.

That SQLite Compare utility compares only the schema, not the tables' contents, so it is not suitable for this job.

The "Couldn't open ... for writing" message is just a consequence of the failed upgrade.

The log shows only some comments because the entire command (that ends at the BEGIN TRANSACTION;) was truncated; the error message shows that it was read completey.