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

mySQL rs.movenext failure?
http://forums.realsoftware.com/viewtopic.php?f=3&t=47672
Page 1 of 1

Author:  JohnV [ Mon Apr 22, 2013 3:01 pm ]
Post subject:  mySQL rs.movenext failure?

I want to add a simple number to each record. 1, 2 ,3 4, 5 etc
(actually Im doing something very complex: So a SQL statement will not get me there. But we will use it here to keep my problem easy to define)

All updates are bing applied to the first record.

while not rs.eof
rs.Edit
rs.Field("SimpleNumber").IntegerValue=i
i=i+1
rs.update
rs.MoveNext
wend


Nothing is being applied at all if I do this (move the rs.update):


while not rs.eof
rs.Edit
rs.Field("SimpleNumber").IntegerValue=i
i=i+1
rs.MoveNext
wend
rs.update


I have to do this for 48 million records So Arrays are out of the question.


Is there something in mySQL that does not mesh with RealStudio? Or is there a bug in the the mySql plugin? (or am I daft?)

Author:  timhare [ Mon Apr 22, 2013 3:18 pm ]
Post subject:  Re: mySQL rs.movenext failure?

Does the recordset contain a unique key for each record?

Author:  JohnV [ Mon Apr 22, 2013 3:32 pm ]
Post subject:  Re: mySQL rs.movenext failure?

The record set DOES contain a unique ID.

ID int(11) PK AI

Author:  Paul Lefebvre [ Thu Apr 25, 2013 10:26 am ]
Post subject:  Re: mySQL rs.movenext failure?

Your second syntax just won't work. RecordSet.Update only updates the current row in the RecordSet.

For best performance, wrap the loop in a transaction and Commit it at the end (or periodically depending on your needs).

But still, 48 million records is going to take a while.

It might be a better idea to use a stored procedure for something like this to eliminate communication between the client app and the DB server.

Author:  JohnV [ Thu Apr 25, 2013 11:01 am ]
Post subject:  Re: mySQL rs.movenext failure?

Thanks Paul.

So you are saying that rs.movenext does not actually move "the current record"?


My question is why doesnt the first syntax work at all?

Author:  Paul Lefebvre [ Thu Apr 25, 2013 11:59 am ]
Post subject:  Re: mySQL rs.movenext failure?

RecordSet.MoveNext moves the current record. I would expect your first syntax to work fine. Be sure to check Database.Error, ErrorCode and ErrorMessage after each call to RecordSet.Update.

Author:  superjacent [ Thu Apr 25, 2013 7:32 pm ]
Post subject:  Re: mySQL rs.movenext failure?

JohnV wrote:
I have to do this for 48 million records So Arrays are out of the question.


Hi John,

Firstly, your first snippet of code should work. Secondly, and maybe of value, I'm not sure, but I came across something similar years ago. Consider working with smaller chunks of data by utilising the LIMIT keyword. See this thread, is where I came across the LIMIT keyword.

Now off-topic, what type of information/data is it that has grown to 48 million records? If you can divulge that.

Author:  timhare [ Thu Apr 25, 2013 8:04 pm ]
Post subject:  Re: mySQL rs.movenext failure?

I would recommend doing the updates in sql statements via SQLExecute. Build a string like "update tablexxx set ... where ID=...".

Author:  JohnV [ Fri Apr 26, 2013 10:05 am ]
Post subject:  Re: mySQL rs.movenext failure?

@timhare: This in fact works around the issue.

But Clearly there is a bug in the MySQLPlugin.

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