Hi, Gertjan!
Many thanks for your complex answer!
Well, I would say the %ScrollableResultSet ist not very well documented.
The confusion I made with MaxRows it's pretty usual, I suppose. (Or am I too stupid to realize what MaxRows are for?)
The CurrRowSet it's a method of %ScrollableResultSet, so it should work an I think it's better this way, than directly set the member of the class.
I change may little example and now it works. It's quicker than the %ResultSet, but the question still remains:
should we show the last stand of the database to the user, or just provide a speedy navigation.
But that's everybody's option, though I read in some post that one can make %ScrollResultSet react to database changes.
I'll try that by myself... :o)
Again, many thanks, Gertjan!
Marius
On Fri, 23 Jul 2004 20:58:20 +0200, Gertjan Klein <[EMAIL PROTECTED]> wrote:
Marius Gabor wrote:
Set resultSet = ##class(%ScrollableResultSet).%New( "%DynamicQuery:SQL" )
Set resultSet.MaxRows = 100
Here you ask for a maximum of 100 rows to be returned.
Do resultSet.Prepare( query ) Do resultSet.Execute()
It is always a good idea to check the return value of these methods, in case something went wrong.
Set resultSet.CurrRowSet = 200000
Strange syntax, which, to my surprise, actually works, I have no idea why. You probably ment
Set resultSet.CurrRow = 200000
Anyway, you request the current row number to be set to 200000, but the maximum row number is 100. Therefore, your request is ignored. You can check this by reading the value of CurrRow after the assignment; in this case, it stays empty.
1) It finds me 131 records, not 100 as I specified. This is buggy, but with some "for i=1...100" it can be solved.
Yes, I have just noticed that MaxRows appears to be ignored, or results in a seemingly arbitrary resultcount. :( (As 131 is less than 200000, the attempt to set CurrRow to the latter value still fails, though.) As a workaround, you may be able to use TOP 100 in the SQL query; this will probably be faster than setting MaxRows anyway, beacuse when setting MaxRows, the full result set is generated first, and then only the first MaxRows rows are copied. Using the SQL TOP nnn, only the first nnn rows are generated.
2) The %Id() method doesn't work in any way, I alway become an empty string back.
An object only has an ID *after* you save it; this is no different for the resultset. Saving a test resultset results in %Id() returning 1 here. (Note that if you want to save a resultset and re-open it, you then *have* to save it again if you want to keep it, or the data will be deleted behind your back when the object closes.)
3) The CurrRow begins 1st postion, not from position 200000, as I said before.
You don't *have* a position 200000, so this makes sense.
I did another example, with the ResultSet class which works correctly but
it's too slow.
If I'm not mistaken, the %ScrollableResultSet class inherits the %ResultSet class's code to actually perform the query and return the results, so if anything, I'd expect the former to be *slower* (marginally, except perhaps on large resultsets) than the latter, as it has to copy the result data.
Until now, the samples from the Intersystems presentation were pretty good, but still to slow even on a local machine.
Speeding up SQL retrieval code probably requires the proper definition of indices and limiting the number of returned rows.
HTH, Gertjan.
