MySQL Edit - Update yields unexpected result [SOLVED]

Databases
1

As a newbie to Xojo, I might be doing this all wrong, but…

I am attempting to walk through a recordset and updating some fields in each record until EOF. The code similar to that shown below works fine in Access and ooBase.
When run in Xojo the result is this:
The FIRST record in the recordset gets its field contents updated with data that was intended for the last record. All other records remain untouched (ie not updated).

Am I missing something? Is this the wrong approach?
As an aside, I have also noticed that if I use a DataControl object to perform this operation, the end result is exactly the same as above. Is this a problem with MySQL or is it me?

Any advice gratefully received!

[code] Dim varStake As Double
Dim varPercent As Double
Dim db as new MySQLCommunityServer
db.DatabaseName = “CurrencyClub”
db.UserName = “root”
db.Password = “test”

if db.Connect then
  Dim rsMembers As RecordSet
  rsMembers = db.SQLSelect("SELECT * FROM members")
  if rsMembers <> nil then
      if(rsMembers.RecordCount <> 0) then
      rsMembers.MoveFirst
      While not rsMembers.EOF
        varStake = val(rsMembers.Field("MemStakeValue").StringValue)
        varPercent = 100*(varStake / varFund)
        'msgBox "Member Stake: "& varStake & "Fund Tot: " & varFund & "Member Percent: " & varPercent
        rsMembers.edit
        rsMembers.Field("MemStakePercent").StringValue = Format(varPercent, "##.0000")
        rsMembers.Field("MemTel1").StringValue = Cstr(rsMembers.Field("ID"))
        rsMembers.update
        rsMembers.MoveNext
      WEND
      rsMembers.Close
    End If
  else
    //problem
    MsgBox("DB error? " + db.ErrorMessage)
  end if
Else
  [/code]
2

Why are you doing this would be my question? This should be done by the engine. Why are you storing numbers as strings in MySQL?

So the Table members has a field called MemTel1 which you want to have the exact id as the field ID … why ?

I don’t get the table, you should always include that when asking a question.

What is varFund?

This should just be 1 simple update statement that will update the entire table, no loops no procedures.

If you insist on doing it this way, how can you check for an error after you’ve completed your transaction?

3

All other questions aside, your code is correct. I believe this is a bug that has been reported.

4

Under Xojo, MoveFisrt() is not supported for MySQL. I don’t know what happens when you call it.

5

It is a bit if a guess but I wonder if some value that you set in the first update causes it to go wrong? How do you identify the first record and last record to know what has happened? Is ID the primary key (and therefore unique)? Jym’s comments are sensible observations especially about performing the update, you could achieve the whole thing in a single SQL UPDATE statement but it looks like your code should work.

6

Thanks for the observations guys. Firstly, Jim, the table “members” has a primary key called “ID”. The only reason I was storing this ID value in to the field “MemTel1” was so that I could confirm to myself that the update was always writing to Record No.1 of the recordset. (ie record 1’s MemTel1 field would always be changed to the ID number of the latest record in the the set) - this was just for debug purposes.

I would love to accomplish this loop with a single SQL UPDATE command, but have not yet worked out how to build the command given the maths involved require a comparison to be made between a value in the member’s record (MemStakeValue) and an external value held in a local variable (varFund), and for the result of that comparison to then be written to the member’s record (MemStakePercent).

nevertheless, my code has worked fine in the past on other platforms…

Anyway, I would greatly appreciate any SQL UPDATE command examples that I could learn from!
Thanks again!

7

“UPDATE members SET MemStakePercent = 100* (MemStakeValue /” + string(varFund) + “)”

But what do you need it stored for? Probably be easier on the engine to store varfund somewhere and do the math when pulling it out

SELECT memStakeValue, 100*(memStakeValue/column_varFund), varFund FROM members

8

Have you debugged? You could set a breakpoint:

[code] While not rsMembers.EOF

  •          dim checkCurrentID as string = Cstr(rsMembers.Field("ID")) // break here and check this value
           varStake = val(rsMembers.Field("MemStakeValue").StringValue) ...[/code]

I am curious about what’s wrong.

Then, remove the MoveFirst() as it does not work and have unpredictable results (should raise an exception for me).

9

This behavior has been reported. <https://xojo.com/issue/26701>

One suggested workaround was to move the Update call outside the loop. I don’t know if it works, but it might be worth a try.

10

Well, if it presents an unreliable behavior, I would avoid using it. As suggested before, I would try the direct SQLExecute Update. :wink:

11

Hi guys,
Many thanks for your suggestions. DOH!! the direct SQL call is so obvious, now that Jim has shown it to me. I feel like Homer… Thanks Jim, you’re a Champ!

ps. Rick, yes I did remove the MoveFirst line, and yes I have stepped through the code with break and all variables and DB field values check out correctly at run time, until the .update command when the “save” takes place on record #1 - always!

Anyway - problem solved!

12

Problem worked around. :wink: The bug #26701 still in the queue to be solved. :wink: