MySQL Weird behavior

Hello,

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

[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[/code]

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

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.

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.

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

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

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.

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.