New topic: mySQL rs.movenext failure?
<http://forums.realsoftware.com/viewtopic.php?t=47672> Page 1 of 1 [ 9 posts ] Previous topic | Next topic Author Message JohnV Post subject: mySQL rs.movenext failure?Posted: Mon Apr 22, 2013 3:01 pm Joined: Sun Jan 28, 2007 2:38 pm Posts: 227 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 timhare Post subject: Re: mySQL rs.movenext failure?Posted: Mon Apr 22, 2013 3:18 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12262 Location: Portland, OR USA Does the recordset contain a unique key for each record? Top JohnV Post subject: Re: mySQL rs.movenext failure?Posted: Mon Apr 22, 2013 3:32 pm Joined: Sun Jan 28, 2007 2:38 pm Posts: 227 The record set DOES contain a unique ID. ID int(11) PK AI Top Paul Lefebvre Post subject: Re: mySQL rs.movenext failure?Posted: Thu Apr 25, 2013 10:26 am Site Admin Joined: Fri Sep 30, 2005 9:35 am Posts: 971 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 Real Software, Inc. The Real Studio Developer Conference is back: April 23rd-26th, 2013 in Orlando, FL. Top JohnV Post subject: Re: mySQL rs.movenext failure?Posted: Thu Apr 25, 2013 11:01 am Joined: Sun Jan 28, 2007 2:38 pm Posts: 227 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 Paul Lefebvre Post subject: Re: mySQL rs.movenext failure?Posted: Thu Apr 25, 2013 11:59 am Site Admin Joined: Fri Sep 30, 2005 9:35 am Posts: 971 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 Real Software, Inc. The Real Studio Developer Conference is back: April 23rd-26th, 2013 in Orlando, FL. Top superjacent Post subject: Re: mySQL rs.movenext failure?Posted: Thu Apr 25, 2013 7:32 pm Joined: Sat Oct 01, 2005 4:47 am Posts: 107 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 timhare Post subject: Re: mySQL rs.movenext failure?Posted: Thu Apr 25, 2013 8:04 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12262 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 JohnV Post subject: Re: mySQL rs.movenext failure?Posted: Fri Apr 26, 2013 10:05 am Joined: Sun Jan 28, 2007 2:38 pm Posts: 227 @timhare: This in fact works around the issue. But Clearly there is a bug in the MySQLPlugin. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 9 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
