Android Question SQL Question

Sergio Haurat

Active Member
Licensed User
Longtime User
Basically I have a local database of some tables with some records that occasionally have modifications.

When I start the application I get the last update data from the remote MySQL database.

If the data differs from what is recorded in the local database, I query all records and update them. When finished, I update the date and time data that I have in the remote database so that they do not have differences again and thus load again and again. I don't know why the UPDATE statement doesn't work. It does not return any errors.

What could I be forgetting?

B4X:
Public Sub set_federations_map(mapRecords As Map, strDateTime As String)
    Dim sqlSelectUpd As SQL
    sqlSelectUpd.Initialize(xui.DefaultFolder, "strings.db", False)
    sqlSelectUpd.ExecNonQuery("DELETE FROM cmn_lst_federaciones")
    sqlSelectUpd.BeginTransaction
    For Each intID As Int In mapRecords.Keys
        Dim strDescShort As String = mapRecords.Get(intID).As (List).Get(0).As (String)
        Dim strDesc  As String = mapRecords.Get(intID).As (List).Get(1).As (String)
        sqlSelectUpd.ExecNonQuery2("INSERT INTO cmn_lst_federaciones VALUES (?, ?, ?)", Array As Object(intID, strDesc, strDescShort))
    Next
    sqlSelectUpd.TransactionSuccessful
    sqlSelectUpd.EndTransaction
    sqlSelectUpd.ExecNonQuery("UPDATE cfg_last_update SET updated = '" & strDateTime & "' WHERE table_id = 'cmn_lst_federaciones'")
    sqlSelectUpd.Close
End Sub
 

PaulMeuris

Active Member
Licensed User
sqlSelectUpd is initialized using "strings.db".
The update statement also uses this database.
Is that correct or do you want to do the update in the remote MySQL database?
 
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
sqlSelectUpd is initialized using "strings.db".
The update statement also uses this database.
Is that correct or do you want to do the update in the remote MySQL database?
I assume both but in that part of code only the local db, evidently updating the date on the server happens in another part of code.

I think it would be useful:

- use also for that update ExecNonQuery2 and maybe also BeginTransaction
- write everything in a Try block
- read the date (by code) after the update and log it.
 
Last edited:
Upvote 0

Sergio Haurat

Active Member
Licensed User
Longtime User
sqlSelectUpd is initialized using "strings.db".
The update statement also uses this database.
Is that correct or do you want to do the update in the remote MySQL database?
This is correct
I first check a MySQL table for the latest update of the records (in this case, the federations)
Upon completion, I update the local registry of the federations, with the data obtained from the remote database.


MySQL record
1710387848020.png

Local SQLite version
1710387893997.png
 
Upvote 0

Sergio Haurat

Active Member
Licensed User
Longtime User
I assume both but in that part of code only the local db, evidently updating the date on the server happens in another part of code.

I think it would be useful:

- use also for that update ExecNonQuery2 and maybe also BeginTransaction
- write everything in a Try block
- read the date (by code) after the update and log it.
Hello @LucaMs, the server is updated automatically by a trigger in MySQL.

1710388050558.png


Tomorrow I will follow your recommendations and tell you the result. On one occasion I thought it was because the table was in "shared folders" and was updated on the device in debug mode, but even sending it to the device, in release mode, it did not update the data either. I don't know if it is a permissions problem with the destination of the xui.DefalultFolder variable, but it seems not, the application does not stop or trigger an error when inserting or updating records.
 
Upvote 0
Top