mySQL looping thru Records

JohnV · **Joined:** Sun Jan 28, 2007 2:38 pm **Posts:** 231

I am trying to loop thru a record set and make changes to each record. I successfully get three records.

I can only get the code to commit the changes to the first record. What am I failing to do?

rs=DB.sqlSelect(stmt) //Got 3 records

while not rs.eof
  rs.edit
  
  rs.field("Foo").StringValue="Bar"
  rs.field("note").StringValue="Append top of note"+endofline+rs.field("note").StringValue
  rs.update
  rs.MoveNext
wend

I have tried moving the rs.update all over the place

I have also tried calling db.commit all over the place.
I have stripped the error handling out for simplicity in this post.

HMARROQUINC · **Posted:** Fri Mar 15, 2013 6:00 pm

Call .commit after the loop.

Pseudo code

dim rs as recordset
rs = db.sqlselect("select col_a, col_b, col_c FROM yoursuperdupertable")
while rs.eof = false
  rs.edit
  rs.field("col_a").stringvalue = "Hello!"
  rs.update
  rs.movenext
wend
db.commit

This is off the top of my head and typed in here directly, but I believe the code is correct.

JohnV · **Joined:** Sun Jan 28, 2007 2:38 pm **Posts:** 231

Calling db.commit after the fact also fails.

I think there is problem in the mySQL plugin: Bug 14857 in Feedback suggests the same behavior.

HMARROQUINC · **Posted:** Fri Mar 15, 2013 6:37 pm

Then perhaps you will need to call an update sql statement.

capaho · **Joined:** Fri Jul 21, 2006 12:28 am **Posts:** 509

JohnV wrote:

Calling db.commit after the fact also fails.

I think there is problem in the mySQL plugin: Bug 14857 in Feedback suggests the same behavior.

I have just run into this problem myself. Only the first record updates, then nothing happens after that even though the loop runs all the way through. It definitely looks like a bug in the mySQL plugin.

J.Hansen · **Joined:** Wed Dec 14, 2011 4:20 am **Posts:** 84

JohnV wrote:

I am trying to loop thru a record set and make changes to each record. I successfully get three records.

I can only get the code to commit the changes to the first record. What am I failing to do?

rs=DB.sqlSelect(stmt) //Got 3 records

while not rs.eof
  rs.edit
  
  rs.field("Foo").StringValue="Bar"
  rs.field("note").StringValue="Append top of note"+endofline+rs.field("note").StringValue
  rs.update
  rs.MoveNext
wend

I have tried moving the rs.update all over the place

I have also tried calling db.commit all over the place.
I have stripped the error handling out for simplicity in this post.

According to RS documentation link: http://docs.realsoftware.com/index.php/RecordSet.Edit

It says: "Be sure the SQL incudes the primary key column (or columns) so that you can update the table after making changes."

Have you check that. ?

Also which version of RS are you using, as in the past there was some issues with the MySql plugin.

capaho · **Joined:** Fri Jul 21, 2006 12:28 am **Posts:** 509

J.Hansen wrote:

According to RS documentation link: http://docs.realsoftware.com/index.php/RecordSet.Edit

It says: "Be sure the SQL incudes the primary key column (or columns) so that you can update the table after making changes."

Have you check that. ?

Also which version of RS are you using, as in the past there was some issues with the MySql plugin.

RecordSet.Edit/RecordSet.Update appears to be broken in the MySQL plugin. I tried everything I could think of but never could get more than the first record to update. It looks to me like RecordSet.MoveNext doesn't function with RecordSet.Edit. The only way I can update multiple records in MySQL is to use IndexSchema or TableSchema to find an index column and then use the index with the SQL update command to update the necessary records.

J.Hansen · **Joined:** Wed Dec 14, 2011 4:20 am **Posts:** 84

capaho wrote:

J.Hansen wrote:

According to RS documentation link: http://docs.realsoftware.com/index.php/RecordSet.Edit

It says: "Be sure the SQL incudes the primary key column (or columns) so that you can update the table after making changes."

Have you check that. ?

Also which version of RS are you using, as in the past there was some issues with the MySql plugin.

RecordSet.Edit/RecordSet.Update appears to be broken in the MySQL plugin. I tried everything I could think of but never could get more than the first record to update. It looks to me like RecordSet.MoveNext doesn't function with RecordSet.Edit. The only way I can update multiple records in MySQL is to use IndexSchema or TableSchema to find an index column and then use the index with the SQL update command to update the necessary records.

I have tried following with RS version: RS2012R2,1, RS2012R2 and RS2011R4.3 and all of them gives same problem.

Created a MySql database:

db.SQLExecute("CREATE table Items ( id INT NOT NULL AUTO_INCREMENT, name TEXT,  number INTEGER, PRIMARY KEY(id) )")

if db.Error Then
  MsgBox("DB Error: " + db.ErrorMessage)
Else
  
  db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Car', 20)")
  db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'House', 21)")
  db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Notebook', 22)")
  db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Realsoftware', 23)")
  db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Realsoftware', 24)")
  db.SQLExecute("INSERT INTO Items(name, number) VALUES ( 'Realsoftware', 25)")
  db.SQLExecute( "COMMIT" )
End If

Tried to update name='Realsoftware' with Code:

If Not db1.Connect Then
  Msgbox "Could not connect to database"
Else
  rs = db1.SQLSelect("SELECT * FROM Items WHERE Name = 'Realsoftware' ")
  
  If rs <> Nil Then
    Listbox1.AddRow "Update Recordset  "+"Number of Records: " + str(rs.RecordCount)
    While Not rs.EOF
      rs.Edit
      if Not db1.Error Then
        rs.Field("number").IntegerValue = 30
        rs.Update
        Listbox1.AddRow ""
        ListBox1.Cell(Listbox1.LastIndex,0) =  rs.Field("id").StringValue
        ListBox1.Cell(Listbox1.LastIndex,1) =  rs.Field("name").StringValue
        ListBox1.Cell(Listbox1.LastIndex,2) =  rs.Field("number").StringValue
        rs.MoveNext
      End if
    Wend
    db1.Commit
    
  Else
    If db1.Error Then MsgBox(db1.ErrorMessage)
  End If
  db1.Close
End If

And I only manage to update the first record.

In the MySql log file shows the Primary key is the same for all three records:

Quote:

29840 Query SELECT * FROM Items WHERE Name = 'Realsoftware'
29840 Query SHOW COLUMNS FROM Items
29840 Query UPDATE Items SET number='30' WHERE id='4'
29840 Query UPDATE Items SET number='30' WHERE id='4'
29840 Query UPDATE Items SET number='30' WHERE id='4'
29840 Query commit

I will report it to RS.

silverpie · **Joined:** Sat Oct 01, 2005 9:55 am **Posts:** 527

"In the MySql log file shows the Primary key is the same for all three records:"

At that point, something has already gone horribly wrong. The very definition of a primary key is that it's different for each record.

timhare · **Posted:** Fri May 17, 2013 1:40 pm

Silverpie, I think you misunderstood his statement. RS is using the same primary key value (the one from the first record) for all the updates, instead of using the primary key value from the current record for each update. I think this has already been reported.

JohnV · **Joined:** Sun Jan 28, 2007 2:38 pm **Posts:** 231

Tried to update name='Realsoftware' with Code:

If Not db1.Connect Then
  Msgbox "Could not connect to database"
Else
  rs = db1.SQLSelect("SELECT * FROM Items WHERE Name = 'Realsoftware' ")
  
  If rs <> Nil Then
    Listbox1.AddRow "Update Recordset  "+"Number of Records: " + str(rs.RecordCount)
    While Not rs.EOF
      rs.Edit    ///<<-------MOVE THIS OUTSIDE THE LOOP!
      if Not db1.Error Then
        rs.Field("number").IntegerValue = 30
        rs.Update
        Listbox1.AddRow ""
        ListBox1.Cell(Listbox1.LastIndex,0) =  rs.Field("id").StringValue
        ListBox1.Cell(Listbox1.LastIndex,1) =  rs.Field("name").StringValue
        ListBox1.Cell(Listbox1.LastIndex,2) =  rs.Field("number").StringValue
        rs.MoveNext
      End if
    Wend
    db1.Commit
    
  Else
    If db1.Error Then MsgBox(db1.ErrorMessage)
  End If
  db1.Close
End If

And I only manage to update the first record.

I had this very same issue and it is now resolved by moving the rs.edit outside the While loop

Only call rs.edit once!

J.Hansen · **Joined:** Wed Dec 14, 2011 4:20 am **Posts:** 84

JohnV wrote:

Tried to update name='Realsoftware' with Code:

If Not db1.Connect Then
  Msgbox "Could not connect to database"
Else
  rs = db1.SQLSelect("SELECT * FROM Items WHERE Name = 'Realsoftware' ")
  
  If rs <> Nil Then
    Listbox1.AddRow "Update Recordset  "+"Number of Records: " + str(rs.RecordCount)
    While Not rs.EOF
      rs.Edit    ///<<-------MOVE THIS OUTSIDE THE LOOP!
      if Not db1.Error Then
        rs.Field("number").IntegerValue = 30
        rs.Update
        Listbox1.AddRow ""
        ListBox1.Cell(Listbox1.LastIndex,0) =  rs.Field("id").StringValue
        ListBox1.Cell(Listbox1.LastIndex,1) =  rs.Field("name").StringValue
        ListBox1.Cell(Listbox1.LastIndex,2) =  rs.Field("number").StringValue
        rs.MoveNext
      End if
    Wend
    db1.Commit
    
  Else
    If db1.Error Then MsgBox(db1.ErrorMessage)
  End If
  db1.Close
End If

And I only manage to update the first record.

I had this very same issue and it is now resolved by moving the rs.edit outside the While loop

Only call rs.edit once!

That is strange.
If I do that as mentioned above, in Windows 7 Pro (64bit) with RS2012R2.1 or RS2011R4.3 I only manage to update the first record.

What platform are you running RS on and which version of RS ?

This is the code when rs is outside the loop:

If Not db.Connect Then
  Msgbox "Could not connect to database"
Else
  rs = db.SQLSelect("SELECT * FROM Items WHERE Name = 'Realsoftware' ")
  
  If rs <> Nil Then
    Listbox1.AddRow "Update Recordset  "+"Number of Records: " + str(rs.RecordCount)
    rs.Edit   // OUTSIDE THE LOOP ONLY UPDATE THE FIRST RECORD
    While Not rs.EOF
      //rs.Edit     ///<<-------MOVE THIS OUTSIDE THE LOOP!
      if Not db.Error Then
        rs.Field("number").IntegerValue = 30
        rs.Update
        Listbox1.AddRow ""
        ListBox1.Cell(Listbox1.LastIndex,0) =  rs.Field("id").StringValue
        ListBox1.Cell(Listbox1.LastIndex,1) =  rs.Field("name").StringValue
        ListBox1.Cell(Listbox1.LastIndex,2) =  rs.Field("number").StringValue
        rs.MoveNext
      Else
        MsgBox(db.ErrorMessage)
        exit
      End if
    Wend
    db.Commit
    
  Else
    If db.Error Then MsgBox(db.ErrorMessage)
  End If
  db.Close
  rs.Close
End If

This is the log from mysql:

Quote:

31741 Query SELECT * FROM Items WHERE Name = 'Realsoftware'
31741 Query SHOW COLUMNS FROM Items
31741 Query UPDATE Items SET number='30' WHERE id='4'
31741 Query commit

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

mySQL looping thru Records