Strange Behaviour on RecordSet Update

Databases
1

Greetings,

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
				
				rs.Edit
				
				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
								
								rs.Update
								
								if App.sqlBase.error then
										MsgBox("There was an error" + EndOfLine + app.sqlBase.ErrorMessage)
								end if
								
								rs.MoveNext
						Else
								MsgBox("There was an error" + EndOfLine + app.sqlBase.ErrorMessage)
						End If
						
				Wend
				
				rs.Close
		End If

Thanks in advance .

2

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]

3

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.

THanks

4

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.edit
rs.Field("pict").PictureValue = thumb
rs.Update

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 = ?")
ps.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_BLOB)
ps.Bind(0,thumb)
ps.BindType(1,MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1,pid)
ps.SQLExecute