Android Question Storage for sqlite database

helmut25

Member
I am trying to write an app retrieving data from an sqlite database.
I stored the database in the ..bin/extra/assets folder (is this the right place?).
The first attempt looked quite good and then I decided to rename a database field.
I was rather astonished remarking that the db file had disappeared from the assets folder
and could not be found in the entire project folder.
so I recreated the datase and stored it again in the assets folder.
When the source code was compiled I found an error message saying the the renamed
field does not exist. Obviously the compiler accessed the previous version?!
My questions: where inside the project folder do I have to store the database?
what has to be done when the database design is being changed?
thanx for your help.
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
I stored the database in the ..bin/extra/assets folder (is this the right place?).
No.

You should add it to the Files tab and then copy it to XUI.DefaultFolder.
B4X:
Dim VersionedDB As String = "db-v13.db"
If File.Exists(XUI.DefaultFolder,VersionedDB ) = False Then
 File.Copy(File.DirAssets, "db.db", XUI.DefaultFolder, VersionedDB)
End If
sql.Initialize(XUI.DefaultFolder, VersionedDB, False)
 
Upvote 0

helmut25

Member
No.

You should add it to the Files tab and then copy it to XUI.DefaultFolder.
B4X:
Dim VersionedDB As String = "db-v13.db"
If File.Exists(XUI.DefaultFolder,VersionedDB ) = False Then
 File.Copy(File.DirAssets, "db.db", XUI.DefaultFolder, VersionedDB)
End If
sql.Initialize(XUI.DefaultFolder, VersionedDB, False)
thanx for your quick answer.
storage is ok now but after sql.initialize......I have the command
B4X:
cursor = SQL1.ExecQuery("SELECT url,comment FROM Links")
And in this line I get an error:
Error occurred on line: 56 (B4XMainPage)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:1055)
---
Before the storage change the sql query worked fine.
 
Upvote 0

helmut25

Member
Post the full error message from the logs.
SQL1 init ok
Error occurred on line: 56 (B4XMainPage)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:1055)
at b4a.example.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:1060)
at b4a.example.b4xpagesmanager._showpage(b4xpagesmanager.java:417)
at b4a.example.b4xpagesmanager._addpage(b4xpagesmanager.java:245)
at b4a.example.b4xpagesmanager._addpageandcreate(b4xpagesmanager.java:259)
at b4a.example.b4xpagesmanager._initialize(b4xpagesmanager.java:165)
at b4a.example.main._activity_create(main.java:415)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
at b4a.example.main.afterFirstLayout(main.java:105)
at b4a.example.main.access$000(main.java:17)
at b4a.example.main$WaitForLayout.run(main.java:83)
at android.os.Handler.handleCallback(Handler.java:873)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:214)
at android.app.ActivityThread.main(ActivityThread.java:7078)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:494)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:975)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:336)
at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:285)
... 24 more
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:318)
... 25 more
Caused by: android.database.sqlite.SQLiteException: no such column: comment (code 1 SQLITE_ERROR[1]): , while compiling: SELECT url,comment FROM Links
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1229)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:703)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1865)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1804)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.example.b4xmainpage._b4xpage_created(b4xmainpage.java:77)
... 27 more
** Activity (main) Resume **
 
Upvote 0

helmut25

Member
SQL1 init ok
Error occurred on line: 56 (B4XMainPage)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:1055)
at b4a.example.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:1060)
at b4a.example.b4xpagesmanager._showpage(b4xpagesmanager.java:417)
at b4a.example.b4xpagesmanager._addpage(b4xpagesmanager.java:245)
at b4a.example.b4xpagesmanager._addpageandcreate(b4xpagesmanager.java:259)
at b4a.example.b4xpagesmanager._initialize(b4xpagesmanager.java:165)
at b4a.example.main._activity_create(main.java:415)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
at b4a.example.main.afterFirstLayout(main.java:105)
at b4a.example.main.access$000(main.java:17)
at b4a.example.main$WaitForLayout.run(main.java:83)
at android.os.Handler.handleCallback(Handler.java:873)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:214)
at android.app.ActivityThread.main(ActivityThread.java:7078)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:494)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:975)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:336)
at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:285)
... 24 more
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:318)
... 25 more
Caused by: android.database.sqlite.SQLiteException: no such column: comment (code 1 SQLITE_ERROR[1]): , while compiling: SELECT url,comment FROM Links
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1229)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:703)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1865)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1804)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.example.b4xmainpage._b4xpage_created(b4xmainpage.java:77)
... 27 more
** Activity (main) Resume **
The error is "no such column: comment (code 1 SQLITE_ERROR[1]): , while compiling: SELECT url,comment FROM Links".
I checked with DB-SQLite-Browser that the SQL statement is correct. As I already said, I renamed the field "comment",
which before had another name. I suppose, that the previous name is somewhere cached.
I removed the db file from the files tab and added it again, but to no avail. "Clean project" didn't help either.
 
Upvote 0

helmut25

Member
The error is "no such column: comment (code 1 SQLITE_ERROR[1]): , while compiling: SELECT url,comment FROM Links".
I checked with DB-SQLite-Browser that the SQL statement is correct. As I already said, I renamed the field "comment",
which before had another name. I suppose, that the previous name is somewhere cached.
I removed the db file from the files tab and added it again, but to no avail. "Clean project" didn't help either.
I have found the cause of the problem: as a database already existed in File.DirAssets, the new version has not been copied.
 
Upvote 0
Top