MySQL Weird behavior

  1. 3 months ago

    Aurelian N

    May 6 Pre-Release Testers, Xojo Pro

    Hello,

    So I have an app that connects to a MySQL server , and I have the following code :

    rs = App.sqlBase.SQLQuery(sql)
    
    System.DebugLog("App.EnableInt -> sql : " + sql)
    
    If rs <> Nil Then
      System.DebugLog("App.EnableInt -> rs recordCount : " + rs.RecordCount.ToText)
      
      If rs.RecordCount = 1 Then
        rs.Edit
        
        System.DebugLog("App.EnableInt -> rs.Field('state') = : " + rs.Field("state").StringValue)
        
        If rs.Field("state").IntegerValue = 0 Then
          
          rs.Field("integrator").StringValue = Integrator
          rs.Field("status").StringValue = SetStatus(State)
          rs.Field("state").IntegerValue = State
          'Check integrations status
          rs.Field("isEnabled").StringValue = State.ToText
          
          rs.Update
          
          If App.sqlBase.Error Then
            System.DebugLog("App.EnableInt -> App.sqlBase.Error : " + App.sqlBase.ErrorMessage)
          End If
          
        End If
        
      End If
      
      rs.Close
    End If

    So if I run same code with breakpoint on the code it updates the database, but if I turn the breakpoints off and run the code it does not update the table. Same code, that is super weird, the DB does not throw any error but in the same time it does not update the record as well.

    Any ideas ?

    OSX 10.14.4 and 10.13.6

    Compiled and tested on XOJO 2019R1

  2. Aurelian N

    May 6 Pre-Release Testers, Xojo Pro

    Well the weird part is that by recompiling the project again the code start to work, no idea if I have a corrupted project or a but in XOJO , or something related to MySQL plugin.

  3. Jean-Yves P

    May 6 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 months ago

    try to replace your rs.edit ... rs.update with an sql execute "insert into.... where rowid=xxx"
    I have a very limited trust in rs.update methods, often leading to strange errors.
    you have better control with insert into queries.

  4. Rick A

    May 6 (Brazil. GMT-3:00)

    @Jean-YvesPochez try to replace your rs.edit ... rs.update with an sql execute "insert into....

    The way he wrote, shows he updating a retrieved row, so, the SQL involved would be UPDATE table...

  5. Jean-Yves P

    May 6 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 months ago

    @Rick A : good catch !
    the idea is to avoid the edit and update methods. use the corresponding sql queries.

  6. Aurelian N

    May 8 Pre-Release Testers, Xojo Pro

    Thanks guys, well indeed I discovered that now but in the end , what is the use of edit and update if they are not working properly ? or not working at all ? I guess they need review or to be removed if not usable, the best way .

    thanks again.

  7. Maximilian T

    May 9 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    I think you may have to throw a db.commit in there. I personally never use recordsets to update data (I rather use sqlexecute or use prepared statements depending on wether the data contains user entered data) as this way is more debugger-friendly.

or Sign Up to reply!