Android Question Storage for sqlite database

[helmut25]

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]

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.

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)

 

[helmut25]

No.

You should add it to the Files tab and then copy it to XUI.DefaultFolder.

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

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.

 

[Erel]

Post the full error message from the logs.

 

[helmut25]

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 **

 

[helmut25]

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.

 

[helmut25]

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.

 

[KMatle]

I create my db's/tables in my app by code. Benefit: If you have an update you can check for single tables/colums/values to add, etc.

 

[Jmu5667]

I know this is an old thread. This is what I do for new columns:

Sub check_field(pSQL As SQL,f As String, t As String, tb As String)

    Dim addColumn As Boolean = False
    
    ' // if we cant select the column, the add it
    Try
        pSQL.ExecQuerySingleResult($"SELECT ${f} FROM ${tb} LIMIT 1"$)
    Catch
        addColumn = True        
    End Try

    If addColumn Then
        Try 
            pSQL.ExecNonQuery($"ALTER TABLE ${tb} ADD COLUMN ${f} ${t}"$)            
        Catch
           log($"cls_sms_store::check_field:: - error - ${LastException.Message}"$)
        End Try        
    End If


End Sub