Android Question SQLite Cannot Save Changes

[BitsAndBytes]

I am adding a simple row on my database by pressing a button but when i close my app the database reverts back to its original state without had save the last row

Sub Activity_Create(FirstTime As Boolean)
Activity.LoadLayout("main")
If FirstTime = True Then     
     If File.Exists(File.DirInternal, "db.db") = False Then
        File.Copy(File.DirAssets, "db.db",File.DirInternal,"db.db")
        sql.Initialize(File.DirInternal, "db.db",  True)
     Else
        sql.Initialize(File.DirInternal, "db.db",  True)
     End If
End If
End Sub


Sub buttonSaveContact_Click
Dim m As Map
Dim list As List
    m.Initialize
    list.initialise
    m.Put("DESCRIPTION", "description1")
    m.Put("LOCATION", "location1")
    m.Put("NOTE", "notes1")
    list.Add(M)
    DBUtils.InsertMaps(sql, "TABLE_CONTACTS", list)
End sub

I am testing the application it on a Android 6.0.1 Xiaomi Prime 3s Mobile Phone.
This is wierd because my database works fine.

 

[imbault]

Please put your code inside :

dim..

Then, send the whole code with declarations of sql etc, in order to help your

 

[DonManfred]

Move the sql init code otside of the FIRSTTIME = true
If your app is started again (firsttime may be not true) you database does not get initialized...

 

[Erel]

If your app is started again (firsttime may be not true) you database does not get initialized...

This is not 100% correct. Assuming that SQL is a process global variable then it should only be initialized when FirstTime is true.
As a general rule it is better to move it to the starter service and initialize the SQL object in Service_Create.

Is sql a process global variable?

 

[BitsAndBytes]

Yes the sql is a variable on Process_Globals

 

[Erel]

The code you posted looks correct. The problem is somewhere else.

 

[mc73]

list.initialise

Is this a typo or wrong paste?

 

[BitsAndBytes]

I have created the db.db with DB Browser for SQLite with 3 tables.
I checked also the manifest permisions.

 

[BitsAndBytes]

list.initialise

Is this a typo or wrong paste?

this is a list and i initialise it on same code block

 

[fixit30]

Can you post your code that you use to read the information out of the database?

 

[BitsAndBytes]

Sub loadContactsList()
Dim i As Int
Dim Rows As Int
Dim Cursor As Cursor
   contactsList.Initialize
   Cursor = sql.ExecQuery("SELECT * FROM TABLE_CONTACTS")
   sql.BeginTransaction
   Rows = Cursor.RowCount
   If Cursor.RowCount > 0 Then
     For i = 0 To Cursor.RowCount - 1
            Dim tempContact As ContactObject
            Cursor.Position = i
            tempContact.contact_key = Cursor.GetInt("CONTACT_KEY")
            tempContact.contact_description = Cursor.GetString("DESCRIPTION")
            tempContact.contact_location = Cursor.GetString("LOCATION")
            tempContact.contact_note = Cursor.GetString("NOTE")
            contactsList.Add(tempContact)
     Next
   End If
   Cursor.Close
End Sub

 

[Computersmith64]

this is a list and i initialise it on same code block

Must be a typo - presumably it would generate a compile error if it was actually spelled like that.

So BitsAndBytes - have you stepped through the code to see which part of your If statement is being executed in your Activity_Create? If it's executing the first part, then it's obviously copying the database every time, so you need to look at why the File.Exists statement isn't recognizing the database name. Maybe there's some weird issue with calling it "db.db" - perhaps try using a different name (like "database.db" or something) just to be sure.

- Colin.

 

[Computersmith64]

Sub loadContactsList()
Dim i As Int
Dim Rows As Int
Dim Cursor As Cursor
   contactsList.Initialize
   Cursor = sql.ExecQuery("SELECT * FROM TABLE_CONTACTS")
   sql.BeginTransaction
   Rows = Cursor.RowCount
   If Cursor.RowCount > 0 Then
     For i = 0 To Cursor.RowCount - 1
            Dim tempContact As ContactObject
            Cursor.Position = i
            tempContact.contact_key = Cursor.GetInt("CONTACT_KEY")
            tempContact.contact_description = Cursor.GetString("DESCRIPTION")
            tempContact.contact_location = Cursor.GetString("LOCATION")
            tempContact.contact_note = Cursor.GetString("NOTE")
            contactsList.Add(tempContact)
     Next
   End If
   Cursor.Close
End Sub

Oh - on second thought, I don't think you can get away with naming a variable the same name as an object - ie: I don't think you can use:

Dim Cursor as Cursor

Instead try:

Private cur as Cursor

- Colin.

 

[BitsAndBytes]

Must be a typo - presumably it would generate a compile error if it was actually spelled like that.

So BitsAndBytes - have you stepped through the code to see which part of your If statement is being executed in your Activity_Create? If it's executing the first part, then it's obviously copying the database every time, so you need to look at why the File.Exists statement isn't recognizing the database name. Maybe there's some weird issue with calling it "db.db" - perhaps try using a different name (like "database.db" or something) just to be sure.

- Colin.

i also check it out. The application doesnt write the database.db file again. It just cannot commit changes done every time you start the app

 

[Computersmith64]

Erel can confirm whether this is correct or not, but I know I've had issues in the past when I've used object / type names as variable names. You can get away with it in languages like C, Java, etc... by using different case (eg: var int: Int), but I don't think you can do it in B4A because I don't think it's case sensitive when it comes to variable names.

- Colin.

 

[BitsAndBytes]

Oh - on second thought, I don't think you can get away with naming a variable the same name as an object - ie: I don't think you can use:

Dim Cursor as Cursor

Instead try:

Private cur as Cursor

- Colin.

i fixed this now . Thank you

 

[Computersmith64]

i fixed this now . Thank you

So did that solve your problem?

 

[BitsAndBytes]

So did that solve your problem?

No. The problem is not fixed yet. Thanks for the correction

 

[fixit30]

Are you sure that DBUtils did not error?

What happens if you try the following instead of DBUtils.InsertMaps?

sql.ExecNonQuery2("INSERT INTO TABLE_CONTACTS (DESCRIPTION, LOCATION, NOTE) VALUES (?, ?, ?)", Array As Object("description1", "location1", "notes1"))

 

[BitsAndBytes]

Are you sure that DBUtils did not error?

What happens if you try the following instead of DBUtils.InsertMaps?

sql.ExecNonQuery2("INSERT INTO TABLE_CONTACTS (DESCRIPTION, LOCATION, NOTE) VALUES (?, ?, ?)", Array As Object("description1", "location1", "notes1"))

All thought this way is so clear. I tested it before a few minutes and still the problem persists.