Trying to update recordset in mysql, only effects 1st record

Databases
1

data = db.SQLSelect(sql)

for a as integer = 1 to 10
  data.Edit
  data.Field("sid").StringValue = "TESTY"
  data.Update
  data.MoveNext
next a

data.Close
db.Close

for some reason this will only update the 1st record in the set.
any advice?

2

Use SQL Update instead.

Update <table> Set sid='TESTY' where <some condition>"
3

the recordset seems to be forgotten each time you do an rs.update
you can make the sqlsearch each time before you edit
or make a clone of the recordset (I dont know how however !)
or use the better method Dave just gave you.

4

Thanks guys.

Sometimes xojo pisses me off. Why can’t a record set be iterated, updated and then written back to the DB?

5

sometimes you need to read the Language Ref

This is most likely why it doesn’t “work”… and XOJO did tell you this right up front…

6

Well, it might in the documentation but I’ve never seen this before. We’ve used MySQL on a bunch of project. Probably the only things that’s saved us is using ActiveRecord which doesn’t use Edit (uses an update SQL statement with Prepared Statements).

Is this the only db that has this particular ‘feature’? If so, I would seriously consider having a database error be set upon the second Edit in the loop to keep unsuspecting developers from seeing this by accident. Worthy of a Feedback request IMO.

7

What I quoted above was directly from the Language Ref…
I have never encountered this issue, as I use RecordSets strictly from data extraction, and use direct SQL for any operations that alter the actual data itself.

8

I’ve got that problem also with sqlite if I remember good, in a biiiiigg loop on the records, the recordset was lost somewhere
so now I never trust the life of a recordset for a long time
I generally select all the rowids in an array then do the work in a separate loop searching for each record
it has the advantage to work with all databases
or I use an update sql statement when it is possible.

9

Part of the issue may be related to how some database engines work.
depending on what and how you update a record, sometimes it actually writes a NEW record and marks the old one as invalid.
Since the recordset maintains pointers to the physical location of the records retrieved, this would invalidate the values the recordset has… hence the recoomendate to update only one at a time.