Android Question Storage for sqlite database

H

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.
 
Sort by date Sort by votes
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
helmut25 said:
I stored the database in the ..bin/extra/assets folder (is this the right place?).
Click to expand...
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
H

helmut25

Member
Erel said:
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)
Click to expand...
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
H

helmut25

Member
Erel said:
Post the full error message from the logs.
Click to expand...
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
H

helmut25

Member
helmut25 said:
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 **
Click to expand...
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
H

helmut25

Member
helmut25 said:
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.
Click to expand...
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
KMatle

KMatle

Expert
Licensed User
Longtime User
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.
 
Upvote 0
Jmu5667

Jmu5667

Well-Known Member
Licensed User
Longtime User
I know this is an old thread. This is what I do for new columns:

B4X: 
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
 
Upvote 0
You must log in or register to reply here.

Similar Threads

W
  • Question
Android Question OSMDroid doesn't show map
Replies
1
Views
1K
Wosl
W
FrostCodes
  • Question
Android Question How to detect if IME soft keyboard is closed?
Replies
4
Views
2K
FrostCodes
FrostCodes
A
  • Question
Android Question Notifications with sound, but not for service?
Replies
2
Views
584
App Dude
A
G
  • Solved
Android Question Unparseable date
Replies
2
Views
3K
gacar
G
A
  • Question
Android Question file dialog
Replies
3
Views
2K
aeropic
A
Top