mySQL rs.movenext failure?

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

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?)

timhare · **Posted:** Mon Apr 22, 2013 3:18 pm

Does the recordset contain a unique key for each record?

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

The record set DOES contain a unique ID.

ID int(11) PK AI

Paul Lefebvre · **Posted:** Thu Apr 25, 2013 10:26 am

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.

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

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?

Paul Lefebvre · **Posted:** Thu Apr 25, 2013 11:59 am

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.

superjacent · **Posted:** Thu Apr 25, 2013 7:32 pm

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.

timhare · **Posted:** Thu Apr 25, 2013 8:04 pm

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

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

@timhare: This in fact works around the issue.

But Clearly there is a bug in the MySQLPlugin.

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

Real Software Forums

mySQL rs.movenext failure?