New topic: Database / Recordset Scope Question
<http://forums.realsoftware.com/viewtopic.php?t=47002> Page 1 of 1 [ 7 posts ] Previous topic | Next topic Author Message xbww Post subject: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 8:15 pm Joined: Wed Oct 12, 2005 5:30 pm Posts: 105 Location: Bryan, Texas Let's say I have a method like this... Function GetRecordset() as Recordset Dim db as database Dim rst as recordset // get a recordset return rst Assuming that we are reading data only, when we call the function and get a recordset, will there be any issues since the database is no longer in scope? If the recordset is 1,000,000 records, are all of the records returned to the caller? Or, does the recordset still need a connection to the database while we moveNext, moveFirst, etc? I know this wont work if you are writing because editing a record locks the record, which cannot be done without a continuous connection to the db. Top HMARROQUINC Post subject: Re: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 8:50 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 442 Location: Guatemala, Central America Once you have queried the database the recordset contains all the records you request and can disconnect from the database. Now... try to only fetch as many records as required unless there is a valid, and there usually isn't, reason to get all the contents from a table. Once disconnected you can .movefirst , .movenext , .movelast as needed. _________________ Future RS guru. Ride the world! Top timhare Post subject: Re: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 11:14 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12071 Location: Portland, OR USA xbww wrote:I know this wont work if you are writing because editing a record locks the record, which cannot be done without a continuous connection to the db. What makes you think the connection is closed? I would expect the connection to live as long as the recordset does. Top HMARROQUINC Post subject: Re: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 11:37 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 442 Location: Guatemala, Central America Tim, Now you got me thinking. Coming from many years of VB where there are disconnected recordsets I thought that something like this would actually disconnect and still keep a recordset. dim rs as RecordSet = db.SQLSelect("SELECT * FROM testtable") if db.error then MsgBox "Error attempting to read " + EndOfLine + CStr(db.ErrorCode) + " - " + db.ErrorMessage exit end if db.Close // closing the db for testing purposes listbox1.DeleteAllRows while rs.eof = false listbox1.AddRow rs.Field("field1").StringValue, rs.Field("field2").StringValue rs.MoveNext wend Now, is there a way I can test if the db is connected or disconnected? Is keeping the recordset in scope forcing the db to stay connected? The code I posted works and I was assuming it was a disconnected recordset, but now you got me thinking that my assumption could be wrong, which happens a lot when one starts assuming things. Testing against Realsqldatabase by the way. _________________ Future RS guru. Ride the world! Top HMARROQUINC Post subject: Re: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 11:41 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 442 Location: Guatemala, Central America Actually there is a very crude way to test if the db is still connected... I added a .sqlexecute after closing the db and as expected the db was closed so the recordset is still alive after being disconnected. So it seems that for a change my assumption was correct dim rs as RecordSet = db.SQLSelect("SELECT * FROM testtable") if db.error then MsgBox "Error attempting to read " + EndOfLine + CStr(db.ErrorCode) + " - " + db.ErrorMessage exit end if db.Close listbox1.DeleteAllRows while rs.eof = false listbox1.AddRow rs.Field("field1").StringValue, rs.Field("field2").StringValue rs.MoveNext wend db.SQLExecute(s) // Fails here db.commit if db.error then MsgBox "error " + CStr(db.ErrorCode) + " - " + db.ErrorMessage exit end if Bear with me, I'm only on page 64 of the Using SQLite book LOL! _________________ Future RS guru. Ride the world! Top timhare Post subject: Re: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 11:48 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 12071 Location: Portland, OR USA Explicitly calling Close is different than letting it go out of scope. I would expect the recordset to keep a reference to the database, which would prevent it from closing. If have not tested that, it's an assumption on my end. What you are seeing is probably due to the fact that the REALSQLdatabase loads all the data into the recordset (a client-side cursor) rather than fetching it as needed (server-side cursor). In the past, I believe all the database classes used client-side cursors. That has changed, but REALSQLdatabase isn't a client/server connection. Top HMARROQUINC Post subject: Re: Database / Recordset Scope QuestionPosted: Tue Feb 19, 2013 11:58 pm Joined: Sun Jan 25, 2009 5:11 pm Posts: 442 Location: Guatemala, Central America I see your point. Since I'm taking a break I might just as well test it. And yes, I thought realdatabase was a special case regarding the cursor location, that's why I mentioned it. _________________ Future RS guru. Ride the world! Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 7 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]
