Android Question SQL Question

[Sergio Haurat]

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?

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]

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?

 

[LucaMs]

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.

 

[Sergio Haurat]

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

Local SQLite version

 

[Sergio Haurat]

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.

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.