Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue May 30, 2017 2:29 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: mySQL looping thru Records
PostPosted: Fri Mar 15, 2013 4:50 pm 
Offline

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.


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Fri Mar 15, 2013 6:00 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
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.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Fri Mar 15, 2013 6:02 pm 
Offline

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.


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Fri Mar 15, 2013 6:37 pm 
Offline
User avatar

Joined: Sun Jan 25, 2009 5:11 pm
Posts: 576
Location: Guatemala, Central America
Then perhaps you will need to call an update sql statement.

_________________
Future RS guru.
Ride the world!


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Wed May 15, 2013 8:51 pm 
Offline

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.

_________________
Regards,
Carl
Capaho Web
http://www.capaho.com


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Thu May 16, 2013 12:59 am 
Offline

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.


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Thu May 16, 2013 7:34 pm 
Offline

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.

_________________
Regards,
Carl
Capaho Web
http://www.capaho.com


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Fri May 17, 2013 10:39 am 
Offline

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.


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Fri May 17, 2013 12:35 pm 
Offline

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.


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Fri May 17, 2013 1:40 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
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.


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Tue May 21, 2013 9:18 am 
Offline

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!


Top
 Profile  
 
 Post subject: Re: mySQL looping thru Records
PostPosted: Wed May 22, 2013 1:07 am 
Offline

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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group