Sqlite DB Compatibility issues

[Celliware]

Hi To All

Looking for answer, there must be a difference between the way Android 2.3 and 2.3.3 addresses the db. As i have created an application which runs perfectly on 2.3, but when I install on a 2.3.3 the application displays mal formed db. Looking for some expert help. :BangHead:

 

[Erel]

How did you create the database? How are you copying it to the device?

 

[Celliware]

Hi There The db was created on the desktop using Sqlite DB Browser I copied the db via usb cable to the devices You can take the db from the tablet using 2.3 which works and install on either a 2.3.1 or 2.3.6 and the results are the same . :BangHead::BangHead:

 

[Erel]

Can you post the exact error message from the logs?

I guess that the desktop tool is creating the database in some non-standard way.

 

[Celliware]

00:19:B5:04:0C:4E
MPT58
00:02:5B:B3:29:EA
main_getagents_details (B4A line: 781)
Cursor1 = SQL1.ExecQuery(txt)
android.database.sqlite.SQLiteDatabaseCorruptException: database disk image is malformed: , while compiling: SELECT * FROM Agent
at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:53)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1438)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1406)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:166)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:154)
at mobilemedia.client.main._getagents_details(main.java:2211)
at mobilemedia.client.main._btnlogin_click(main.java:895)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:167)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:155)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:151)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:55)
at android.view.View.performClick(View.java:2532)
at android.view.View$PerformClick.run(View.java:9277)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:143)
at android.app.ActivityThread.main(ActivityThread.java:4196)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:839)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:597)


Code extract.


' setup table and query string to get a token
DBTableName = "Agent" ' select stock table

txt = "SELECT * FROM " & DBTableName ' get Agents details from table
Cursor1 = SQL1.ExecQuery(txt)
Log(Cursor1)
' need to get record values from table based on identified columns

 

[Erel]

Can you upload the database file?

 

[Celliware]

db file

Hi There i will send you the db file. Thanks

 

[mc73]

Hi There The db was created on the desktop using Sqlite DB Browser I copied the db via usb cable to the devices You can take the db from the tablet using 2.3 which works and install on either a 2.3.1 or 2.3.6 and the results are the same . :BangHead::BangHead:

Please, keep in mind, that transferring a db from one device to the other, should happen only if and when your db is not locked, thus db-journal file does not exist any more. Otherwise it is very luckily that you'll get a locked or malformed db at your new device.

 

[mangojack]

mc73 .. Can you supply any links for a readup re locked db's, db-journals ect.
I was about to query the purpose/reason for db-journal's

Cheers mj

 

[mc73]

mc73 .. Can you supply any links for a readup re locked db's, db-journals ect.
I was about to query the purpose/reason for db-journal's

Cheers mj

The journal file is holding temporary data of ongoing transactions, in order to rollback if something went wrong. When we startTransaction and until we end it, this file is there, thus very useful. Now, if one tries to copy the db, while journal is there, usually it cannot be used, it will report errors. Before such operations, we should close the sql object and of course endTransaction if we have it open. Here's a relative link by googling.

 

[Mahares]

1. Does having the journal file in the database folder on the device mean that the database was not exited properly by not closing the SQL object?.
2. What does it exactly mean if there is a journal file and its size is 0 B
3. What does it exactly mean if there is a journal file and its size is say 500 KB
4. If you have one common database used by several applications that you migrate from one to the other not through startactivity, but through intent frequently, is there any need to close the SQL object every time you exit one and enter the other?
5. Do you think the best approach to avoiding a lock database situation is import data into tables from text files when doing an upgrade as opposed to copying an entire database over an existing one, although the latter is more practical.
6. Could you please answer each individually
thanks

 

[mc73]

1. Does having the journal file in the database folder on the device mean that the database was not exited properly by not closing the SQL object?.
2. What does it exactly mean if there is a journal file and its size is 0 B
3. What does it exactly mean if there is a journal file and its size is say 500 KB
4. If you have one common database used by several applications that you migrate from one to the other not through startactivity, but through intent frequently, is there any need to close the SQL object every time you exit one and enter the other?
5. Do you think the best approach to avoiding a lock database situation is import data into tables from text files when doing an upgrade as opposed to copying an entire database over an existing one, although the latter is more practical.
6. Could you please answer each individually
thanks

1. Means that either a cursor remained open, or a transaction is pending, in general. However,

1 & 2 & 3, This can also happen when we have the database opened by other applications. You can think of it, as many users enter and interact with the same database.

4. You should always close the sql object. Occasionally there can be a db-journal with 0bytes, still it's a good practice to get rid of it. sql.close will do the job, if no pending transactions are there.

5. In general, it is a good approach to create an sql file (be it txt or whatever) forming the necessary update/insert/delete queries and even recreate tables. This is the approach used in import/export in mysql most of the time, instead of a total replacement of the db. After all, there may always be data in our db which we don't wish to alter.

In two of my apps, with many devices acting as clients (in the one of them, I have now 20+ androids running on a android based E.R.P. I made), I found all the above to work well. Please note that before, I had problems mainly due to the causes I described above. So, no matter if it is theoretical correct or not, they worked for me I am really no expert in the sqlite structure, hope other users here can give more info/knowledge.

 

[Celliware]

Hi There Thanks for the recommendations, I now have it connecting to the db on the 3 version of android. Its was the figure problem based on a condition switch when to initial the db. Still not sure why it worked on the one only My next challenge which failed last night was to include the db in the install hence me copy with cable.