Strange Behaviour on RecordSet Update


It seems that i get weird things on mysql while running an update on some fields.
So i have 17 Records in total that i have to go trough and update data , well, it goes to the first record and then it stops saying that it reached to the end of the file but actually there are 16 more to go , any idea why ?

I`m using Xojo 2016R4.1

The code is the following :

		query = "SELECT * FROM photos"
		rs = app.sqlBase.SQLQuery(query)
		If rs <> Nil Then
				dim rc As Integer
				rc = rs.RecordCount
				While Not rs.EOF
						If Not App.sqlBase.Error Then
								pid = rs.Field("patient_id").StringValue
								pname = rs.Field("file").StringValue
								dim thumbnailAI As FolderItem = app.patientFolderItem.Child("C"+ pid).Child("photos").Child(pname)
								pictureBuffer = Picture.Open(thumbnailAI)
								pictureBuffer.Save(thumbnailAI, Picture.SaveAsJPEG)
								thumb = pictureBuffer.resize(128,128)
								rs.Field("pict").PictureValue = thumb
								if App.sqlBase.error then
										MsgBox("There was an error" + EndOfLine + app.sqlBase.ErrorMessage)
								end if
								MsgBox("There was an error" + EndOfLine + app.sqlBase.ErrorMessage)
						End If
		End If  

Thanks in advance .

Perhaps it’s related to this section on the RecordSet.Edit page of the docs:

[quote]Editing with MySQL
MySQL cannot guarantee the contents of a RecordSet after issuing an Update call (after having previously called Edit). This means you should not try to modify the contents of a RecordSet in a loop. Instead select just the single record you wish to modify.[/quote]

Well the app that im working on is switching between mysql and sqlite and i did some tests as well on sqlite and i get same issue, i remember having a lot of code doing that without issues in the past, and now creating an issue, is there some way to fix this ? i have a lot of data to loop trough and its hard to doit by hand.


Your problem is that you must do rs.edit before any changes. You have your rs.edit at the wrong place, entirely outside the loop.

like this:

rs.Field("pict").PictureValue = thumb

I find it is much more efficient to use prepared statements or SQL to do updates.

dim ps as MySQLPreparedStatement
ps = app.sqlBase.Prepare("UPDATE photos SET pict = ? WHERE patient_id = ?")