All geek questions in one place

ORMLite Problem with inserting tables from one database into another database on Android

I download the SQLite database file from the server to the user's Android device. After loading, I insert or replace some tables in the local database using the loaded database. For this I use ORMLite 4.47.

First, I attach the database files to the DatabaseConnection:

DatabaseConnection con = null; con = conSrc.getReadWriteConnection(); con.executeStatement("attach database '" + localDatabase.getAbsolutePath() + "' as '" + localDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS); con.executeStatement("attach database '" + downloadedDatabase.getAbsolutePath() + "' as '" + remoteDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS);

After attaching the database, I create the following query to copy from the loaded database (remoteDb) to the local database (localDb):

 INSERT OR REPLACE INTO localDb.table_items (createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6) SELECT createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6 FROM remoteDb.table_items

Then I execute the following function from ORMLite:

 con.executeStatement(query, flags);

The function works fine on Android version 4.1 (with SQLite 3.7.11) and higher. But I get the following error on Android versions 4.0 and 4.0.3 (with SQLite 3.7.4):

 09-13 15:51:19.852: E/com.example.controller(1028): java.sql.SQLException: Problems executing INSERT OR REPLACE INTO localDb.table_items (createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6) SELECT createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6 FROM remoteDb.table_items Android statement: INSERT OR REPLACE INTO localDb.table_items (createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6) SELECT createdAt_device, updatedAt_device, _id, column_1, column_2, column_3, column_4, column_5, column_6 FROM remoteDb.table_items 09-13 15:51:19.852: E/com.example.controller(1028): at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22) 09-13 15:51:19.852: E/com.example.controller(1028): at com.j256.ormlite.android.AndroidCompiledStatement.execSql(AndroidCompiledStatement.java:185) 09-13 15:51:19.852: E/com.example.controller(1028): at com.j256.ormlite.android.AndroidDatabaseConnection.executeStatement(AndroidDatabaseConnection.java:134) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller$DatabaseTransferHandler.executeDatabaseTransfers(contoller.java:763) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller$DatabaseTransferHandler.access$0(contoller.java:740) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller.transferTables(contoller.java:494) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller.addElement(contoller.java:132) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller.addOrRemoveElements(contoller.java:109) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller.addElements(contoller.java:147) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller.download(contoller.java:254) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller.access$3(controller.java:204) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller$2.doInBackground(contoller.java:468) 09-13 15:51:19.852: E/com.example.controller(1028): at com.example.controller$2.doInBackground(contoller.java:1) 09-13 15:51:19.852: E/com.example.controller(1028): at android.os.AsyncTask$2.call(AsyncTask.java:264) 09-13 15:51:19.852: E/com.example.controller(1028): at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305) 09-13 15:51:19.852: E/com.example.controller(1028): at java.util.concurrent.FutureTask.run(FutureTask.java:137) 09-13 15:51:19.852: E/com.example.controller(1028): at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:208) 09-13 15:51:19.852: E/com.example.controller(1028): at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1076) 09-13 15:51:19.852: E/com.example.controller(1028): at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:569) 09-13 15:51:19.852: E/com.example.controller(1028): at java.lang.Thread.run(Thread.java:856) 09-13 15:51:19.852: E/com.example.controller(1028): Caused by: android.database.sqlite.SQLiteDatabaseLockedException: database is locked 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteStatement.native_executeSql(Native Method) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:90) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1839) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:661) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteDatabase.beginTransactionNonExclusive(SQLiteDatabase.java:576) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:247) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:84) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899) 09-13 15:51:19.852: E/com.example.controller(1028): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1889) 09-13 15:51:19.852: E/com.example.controller(1028): at com.j256.ormlite.android.AndroidCompiledStatement.execSql(AndroidCompiledStatement.java:183) 09-13 15:51:19.852: E/com.example.controller(1028): ... 18 more

How can I solve this problem? Perhaps there is a better way to download a large amount of information to a device?

+4
source share
1 answer

I am an employee of the author of this question, and I managed to find a way to avoid this problem. I decided that I would post it here if someone else stumbles on the same issue.

Android devices with this particular OS version seem to have a problem with this Ormlite command sequence:

 DatabaseConnection con = null; con = conSrc.getReadWriteConnection(); con.executeStatement("attach database '" + localDatabase.getAbsolutePath() + "' as '" + localDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS); con.executeStatement("attach database '" + downloadedDatabase.getAbsolutePath() + "' as '" + remoteDb + "'", DatabaseConnection.DEFAULT_RESULT_FLAGS);

I don’t know if this is caused by the version of SQLite3 that is pre-installed on these devices or something else.

Now we could avoid this because we could (fortunately) avoid using Ormlite in this situation at all. The SQLite "ATTACH DATABASE" statement must not be executed on the DatabaseConnection instance — it can be executed in the local database file. Therefore, instead of the previous block, only this statement is required:

 db.execSQL("ATTACH DATABASE '" + tempDb.getAbsolutePath() + "' AS '" + remoteDb + "'");

where db is an instance of SQLiteDatabase local database. The SQLite documentation states that:

"The ATTACH DATABASE statement adds another database file to the current database connection ."

therefore there is no instance of DatabaseConnection .

+5
source

Source: https://habr.com/ru/post/1502087/

More articles:


All Articles