Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Thu Oct 19, 2017 12:57 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: mySQL rs.movenext failure?
PostPosted: Mon Apr 22, 2013 3:01 pm 
Offline

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


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Mon Apr 22, 2013 3:18 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Does the recordset contain a unique key for each record?


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Mon Apr 22, 2013 3:32 pm 
Offline

Joined: Sun Jan 28, 2007 2:38 pm
Posts: 231
The record set DOES contain a unique ID.

ID int(11) PK AI


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Thu Apr 25, 2013 10:26 am 
Offline
Site Admin
User avatar

Joined: Fri Sep 30, 2005 9:35 am
Posts: 987
Location: South Portland, Maine
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.

_________________
Paul Lefebvre
Developer Evangelist
Xojo, Inc.


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Thu Apr 25, 2013 11:01 am 
Offline

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?


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Thu Apr 25, 2013 11:59 am 
Offline
Site Admin
User avatar

Joined: Fri Sep 30, 2005 9:35 am
Posts: 987
Location: South Portland, Maine
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.

_________________
Paul Lefebvre
Developer Evangelist
Xojo, Inc.


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Thu Apr 25, 2013 7:32 pm 
Offline
User avatar

Joined: Sat Oct 01, 2005 4:47 am
Posts: 130
Location: Melbourne, Australia
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.

_________________
Steve
rs2012 r2.1 Windows 7.


Last edited by superjacent on Fri Apr 26, 2013 9:14 pm, edited 1 time in total.

Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Thu Apr 25, 2013 8:04 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
I would recommend doing the updates in sql statements via SQLExecute. Build a string like "update tablexxx set ... where ID=...".


Top
 Profile  
Reply with quote  
 Post subject: Re: mySQL rs.movenext failure?
PostPosted: Fri Apr 26, 2013 10:05 am 
Offline

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.


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

All times are UTC - 5 hours


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:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group