Real Software Forums
http://forums.realsoftware.com/

mySQL looping thru Records
http://forums.realsoftware.com/viewtopic.php?f=3&t=47299
Page 1 of 1

Author:  JohnV [ Fri Mar 15, 2013 4:50 pm ]
Post subject:  mySQL looping thru Records

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.

Author:  HMARROQUINC [ Fri Mar 15, 2013 6:00 pm ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  JohnV [ Fri Mar 15, 2013 6:02 pm ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  HMARROQUINC [ Fri Mar 15, 2013 6:37 pm ]
Post subject:  Re: mySQL looping thru Records

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

Author:  capaho [ Wed May 15, 2013 8:51 pm ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  J.Hansen [ Thu May 16, 2013 12:59 am ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  capaho [ Thu May 16, 2013 7:34 pm ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  J.Hansen [ Fri May 17, 2013 10:39 am ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  silverpie [ Fri May 17, 2013 12:35 pm ]
Post subject:  Re: mySQL looping thru Records

"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.

Author:  timhare [ Fri May 17, 2013 1:40 pm ]
Post subject:  Re: mySQL looping thru Records

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.

Author:  JohnV [ Tue May 21, 2013 9:18 am ]
Post subject:  Re: mySQL looping thru Records

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!

Author:  J.Hansen [ Wed May 22, 2013 1:07 am ]
Post subject:  Re: mySQL looping thru Records

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

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/