This just happened to a program that had been running fine for yearts:

program sort 2 files using 2 separate cursors; all of a sudden, the 2 cursors return the same key causing the program to not work correctly.

The only thing changed, as far as I know, is user changing to a new computer running windows 2000.

Anybody has similar experience?

Frank,

For some unknown reason I seem to have problems with Reduce. I didn't think it had anything to do with the Windows version. But if you too use Reduce you might try switching to Push.Select and Pop.Select and see if it helps. (I think you'll find postings where Don Bakke (and Sprezzatura?) helped me with Push.Select and Pop.Select.)

- Oystein -

We are using Reduce. Thanks for the help. Will try the other selects if I can find the documentation for it.

Frank,

Push.Select saves info about a current list. Pop.Select restores it. Inbetween you can ro a second select. I do that second select with Rlist.

Push.Select and Pop.Select have four parameters. The first one is Cursor - the cursor number. The other three are for what else you need to save - system variables @dict, @record, etc.

Since I often have to save more than three system variables I stopped using the last three parameters, and save the system variables in my own variables instead.

(They are really stupid those three parameters.)

Here's an example of my code:

/* save away active list */

saveAtDict =@dict saveAtRecord =@record saveAtID =@id saveAtRecCount =@recCount saveAtRnCounter=@rn.Counter /* (say it's cursor 0 you want to save away and use for a secondary selct) */ Cursor=0 Push.Select( Cursor, Save1, Save2, Save3 ) /* secondary select statement here */ RList_Statement=SELECT " : TableName : " ( WITH ……. Set_Status(0) RList( RList_Statement, TARGET_ACTIVELIST$, "", "", "" ) Dummy=Get_Status( Status_Text ) Set_Status(0) if Status_Text "" then …error handling… end /* loop through secondary list */ Done=false$ loop while not(Done) readnext Key using Cursor by AT then read Row from TableVar, Key else …etc… …etc… repeat /* restore active list */ Pop.Select( Cursor, Save1, Save2, Save3 ) transfer saveAtDict to @dict transfer saveAtRecord to @record transfer saveAtID to @id transfer saveAtRecCount to @recCount @rn.Counter=saveAtRnCounter

Hope I didn't forget anything.

- Oystein -

Frank,

I found that when using multiple cursors, and the size of the "select lists" for the cursors are so large that the "fragment" into the "SYSLISTS" table… they will overwrite eachother and cause the exact condition you are explaining.

I found this problem at the first of this year. I have verified that the flaw exists. I have talked to Revelation directly on this topic. I have not seen any resolve to this date.

I am very interested that you have found this flaw too. Maybe this will inspire Revelation to fix it.

Now AREV handles this problem just fine. The select list fragments are saved with a unique row key. OI needs to do the same. I strongly suggest using the cursor number to unique the select list fragments.

If you want to read more here is the address of one of the posts I have made over a period of a couple months about this problem…

http://www.revelation.com/THEWORKS/OIdiscuss.nsf/4539f515a2139aa98525652c004759c0/5e06f6042d27dfa585256ca40066757d?OpenDocument&Highlight=0,fragment

I have verified that PUSH.SELECT and POP.SELECT do not help in this situation. I have verified this with Don Bakke. Although I am not sure Revelation understands this. I never got an acknoledgement on this.

Remember that what is happening is that the select list is so big that it fragments to the "SYSLISTS" table. And when two select lists fragments the second list (or cursor) will overwrite the first one. Once the secondlist overwrites the first one, the first one is lost.

What needs to be done is to have the select list fragments have unique row ids in the "SYSLISTS" table.

By adding the cursor number to the row id it will assure the uniqueness of the row ids in the "SYSLISTS" table.

Richard,

If this is correct (and I have no reason to doubt you) some statement from Revelation (acknowledgement, guidelines for workarounds) would be welcome, not to mention a patch or some other solution.

My secondary selects have been small, I think. I might need larger ones now.

- Oystein -

Oystein,

Try this test…

DECLARE SUBROUTINE REDUCE

FILENAME=SYSOBJ'

REDUCE_SCRIPT='

IDS1='

IDS2='

IDS3='

IDS4='

*

* Test to see how select one should be. REDUCE(REDUCE_SCRIPT,'@ID',1,FILENAME,CURSOR1,FLAG) SELECT FILENAME BY '@ID' USING CURSOR1 ELSE END DONE=0 LOOP READNEXT ID USING CURSOR1 BY AT ELSE DONE=1 END UNTIL DONE DO IDS1=ID REPEAT *

* Test to see how select two should be.

REDUCE(REDUCE_SCRIPT,'#@ID',1,FILENAME,CURSOR2,FLAG)

SELECT FILENAME BY '#@ID' USING CURSOR2 ELSE

END

DONE=0

LOOP

READNEXT ID USING CURSOR2 BY AT ELSE

DONE=1

END

UNTIL DONE DO

IDS2=ID

REPEAT

*

* Select one again. REDUCE(REDUCE_SCRIPT,'@ID',1,FILENAME,CURSOR1,FLAG) SELECT FILENAME BY '@ID' USING CURSOR1 ELSE END *

* Select two again.

REDUCE(REDUCE_SCRIPT,'#@ID',1,FILENAME,CURSOR2,FLAG)

SELECT FILENAME BY '#@ID' USING CURSOR2 ELSE

END

*

* Read next from select one. DONE=0 LOOP READNEXT ID USING CURSOR1 BY AT ELSE DONE=1 END UNTIL DONE DO IDS3=ID REPEAT *

* Read next from select two.

DONE=0

LOOP

READNEXT ID USING CURSOR2 BY AT ELSE

DONE=1

END

UNTIL DONE DO

IDS4=ID

REPEAT

*

* The IDS1 variable should be a list of assending ids. * The IDS2 variable should be a list of descending ids. * The IDS3 variable should be a list of assending ids (like IDS1). * The IDS4 variable should be a list of descending ids (like IDS2). DEBUG RETURN 1 </QUOTE> —- === At 11 JUN 2003 05:30PM Oystein Reigem wrote: === <QUOTE>Richard, Try this test… I did. But I'm not sure I understand what's going on. Selects and cursors and SYSLISTS rows etc are really strange stuff. When I work with these things I can't keep from thinking they would be implemented in a much clearer and more user friendly (developer friendly) way in a modern tool. You say that if the program worked as expected the result would be the following: * The IDS1 variable should be a list of assending ids. * The IDS2 variable should be a list of descending ids. * The IDS3 variable should be a list of assending ids (like IDS1). * The IDS4 variable should be a list of descending ids (like IDS2). But the real result is IDS1 is ascending. IDS2 is descending. IDS3 is descending. IDS4 is descending. Agree? Here's your code. I've inserted some comments and questions. Sorry for the posting becoming quite long. (My comments and questions about which cursor is used at any time might be beside the subject, but it would be nice to know if I am right or not): DECLARE SUBROUTINE REDUCE FILENAME=SYSOBJ' REDUCE_SCRIPT=' IDS1=' IDS2=' IDS3=' IDS4=' * * Test to see how select one should be.[/i] All four REDUCE's will return the results in the next available cursor (argument no 3=1). Cursor 0 is sort of taken, so the next available cursor for this first REDUCE is 1: CURSOR1=1. Argument no 2 says the sorting should be ascending on key. REDUCE(REDUCE_SCRIPT,'@ID',1,FILENAME,CURSOR1,FLAG) SELECT FILENAME BY '@ID' USING CURSOR1 ELSE END DONE=0 LOOP READNEXT ID USING CURSOR1 BY AT ELSE DONE=1 END UNTIL DONE DO IDS1=ID REPEAT And IDS1 comes out right - as an ascending list of keys. * * Test to see how select two should be.[/i] The previous REDUCE used cursor 1, but because of the READNEXT loop that followed cursor 1 is free again. So the next available cursor is also 1: CURSOR2=1. I this correct? Argument no 2 says the sorting should be descending on key. REDUCE(REDUCE_SCRIPT,'#@ID',1,FILENAME,CURSOR2,FLAG) SELECT FILENAME BY '#@ID' USING CURSOR2 ELSE END DONE=0 LOOP READNEXT ID USING CURSOR2 BY AT ELSE DONE=1 END UNTIL DONE DO IDS2=ID REPEAT And IDS2 comes out right - as a descending list of keys. * * Select one again.[/i] For the same reason as for the second REDUCE (the READNEXT loop freeing cursor 1) the next available cursor for this third REDUCE is 1: CURSOR1=1. Argument no 2 says the sorting should be ascending on key. REDUCE(REDUCE_SCRIPT,'@ID',1,FILENAME,CURSOR1,FLAG) SELECT FILENAME BY '@ID' USING CURSOR1 ELSE END The result is supposed to be an ascending list of keys - stored as several SYSLISTS rows (fragmented, as you say). And presumably it is. But the result is not copied into the IDS3 variable yet, but left in SYSLISTS. I assume it's in the T (fragmented) rows (T*…*…*1, T*…*…*2, etc). * * Select two again.[/i] This final time there was no READNEXT loop, so cursor 1 is still occupied. Therefore the next available cursor is 2: CURSOR2=2. REDUCE(REDUCE_SCRIPT,'#@ID',1,FILENAME,CURSOR2,FLAG) SELECT FILENAME BY '#@ID' USING CURSOR2 ELSE END The result is supposed to be a descending list of keys - stored as several SYSLISTS rows. And you say it just overwrote the T rows, so the third result is lost. * * Read next from select one. DONE=0 LOOP READNEXT ID USING CURSOR1 BY AT ELSE DONE=1 END UNTIL DONE DO IDS3=ID REPEAT[/i] What got into IDS3 here is the content of the current fragmented T rows in SYSLISTS. Which contain not the third result, but the fourth. * * Read next from select two. DONE=0 LOOP READNEXT ID USING CURSOR2 BY AT ELSE DONE=1 END UNTIL DONE DO IDS4=ID REPEAT[/i] And what got into IDS4 here is also the content of the current fragmented T rows in SYSLISTS. Which accidentally contain the correct (fourth) result. Because no matter which cursor you used, long lists are stored in one and the same place - as fragmented T rows in SYSLISTS. * DEBUG RETURN 1[/i] Have I got it? - Oystein - </QUOTE> —- === At 11 JUN 2003 11:15PM Donald Bakke wrote: === <QUOTE>Oystein, I didn't read through your comments but I can say that I have previously confirmed Richard's problem. dbakke@srpcs.com SRP Computer Solutions, Inc. </QUOTE> —- === At 12 JUN 2003 05:56AM Oystein Reigem wrote: === <QUOTE>Don (et al), How do you deal with this problem with long results overwriting an earlier result? (1) In OI32 one possible solution might be to save the first result in a variable, since variables in OI32 can contain more than 64K of data. (2) A different solution might be based on named lists. The first result could be saved in named rows in SYSLISTS, so it would not be overwritten. Now Richard's example is about two SELECTs in a row, with different cursors. (Even though they are done with different cursors the second result overwrites the first one.) My current scenario is a bit different, with two nested SELECT/READNEXT loops. For each key in my first SELECT I do a secondary SELECT and loop through all of that secondary SELECT's keys. (So there are not two fixed lists that are cross-combined. The secondary list depends on the current key (row) from the primary (first) list.) Let's say I go for solution (2). But then I need to do more than just save and reactivate the primary list. Every time I reactivate the list I must pick up the thread at the correct key. In the Kth pass of the outer loop, when I need to get to key no K, I must either (2a) READNEXT K times, or I must (2b) have some scheme for picking the keys directly from the SYSLISTS rows, i.e, keep track of current row and byte position in SYSLISTS. (There might also be possible variations on (2) if the secondary list only occasionally is large enough to overwrite the first one. Then the program might do its business in the old, naive way until a large secondary list is encountered.) - Oystein - </QUOTE> —- === At 12 JUN 2003 09:53AM Bob Carten - Revtech Support wrote: === <QUOTE> I spent a little time looking for this problem. Did not get all the way through it yet. I can see that the Reduce routine has some special flags in it for long selects, and that we made some minor changes with the 32-bit upgrade, so we have reason to be suspicious. I will post back here when I find something, probably not for a few days. Changes to Reduce can produce unwanted side effects – Spindex anyone? – so if we do make a change it will need to go through a beta cycle. Bob </QUOTE> —- === At 12 JUN 2003 12:49PM Richard Hunt wrote: === <QUOTE>Bob, I believe that the only problem is that when the list fragments to the SYSLISTS table the row id is not "unique" enough for each cursor. If the cursor # was added to the row id in the SYSLISTS table (almost like AREV does), the problem will go away. Currently the row id for a fragment is… "T*":@STATION:'*':@USERNAME:'*':FRAGMENT_NUMBER If it was to be… 'T*':@STATION:'*':@USERNAME:'*':CURSOR_NUMBER:'*':FRAGMENT_NUMBER I think the "whole" problem will go away. </QUOTE> —- === At 12 JUN 2003 01:11PM Richard Hunt wrote: === <QUOTE>Frank, This will happen to all selects that fragment. The workaround for two selects would be as follows… 1) first select… do the select process then do a readnext loop to store the ids in a variable. With OI32 this can be done (not with OI16). 2) Do second select (last select) and process it in the normal way. Kinda like this… …select one… IDS=' DONE=0 LOOP READNEXT ID USING CURSOR1 BY AT ELSE DONE=1 END UNTIL DONE DO IDS := @FM:ID REPEAT IDS=IDS12,LEN(IDS1) now you can "readnext" from select list one by using the following statements… POS=0 MORE=(IDS NE ) LOOP WHILE MORE REMOVE ID FROM IDS AT POS SETTING MORE … … … REPEAT This will work although it is a bit slow. </QUOTE> —- === At 12 JUN 2003 05:12PM Bob Carten wrote: === <QUOTE> Thanks Richard. Bob </QUOTE> —- === At 24 JUN 2003 10:29PM Frank Lee wrote: === <QUOTE>I read thru all the responses but still unable to find the answer to the problem. I like the allegation that the problem is due to fragmentation to SYSLISTS, but do not understand about adding the cursor number to the row id. Is that an option somewhere or what? Frank </QUOTE> —- === At 24 JUN 2003 10:33PM Frank Lee wrote: === <QUOTE>How do you "By adding the cursor number to the row id it will assure the uniqueness of the row ids in the "SYSLISTS" table."? Frank </QUOTE> —- === At 25 JUN 2003 12:49AM The Sprezzatura Group wrote: === <QUOTE>Frank You can't - this suggests a bug in OI itself that can only be resolved by Revelation. The Sprezzatura Group World Leaders in all things RevSoft </QUOTE> —- === At 25 JUN 2003 04:19AM Oystein Reigem wrote: === <QUOTE>Frank, Richard must have meant that Revelation should change the name of the T rows to include the cursor number, so that there can be one current long list per cursor. I don't think we developers can do that. I can think of many workarounds, but surely Revelation ought to fix the bug. We can't have lists overwriting each other. My own current case is a program that processes data from a set of tables. The program has a main loop where it readnexts through a primary list from one of the tables, and for each key does a sequence of secondary selects in related tables. The primary list is often large. On rare occasions there is a large secondary list. When both lists are large the primary list is overwritten. I will solve the problem in one of the two following ways: - do the primary select - somehow copy the list of keys to a variable (*) () - let the main loop walk that variable - do the primary select and save to a named list - let the main loop be a double loop: an outer loop where the content of each fragment (SYSLISTS row) is read to a variable (), and an inner loop that walks that variable - Oystein - (*) Either with a readnext loop that appends each key to the variable, or with a loop that reads one whole fragment (SYSLIST row) at a time and concatenates them in a variable. In the latter case I'll have to use a named list, because short lists don't get a T row (???). (**) Note that the first fragment (SYSLISTS row) of a named row starts with a header: header:@FM:key:@FM:key:@FM:key:@FM:key:@FM:key:… (The header always starts with a @TM. I always look for that @TM if I want to know if there's a header.) </QUOTE> —- === At 25 JUN 2003 08:53PM Richard Hunt wrote: === <QUOTE>Frank, Like Oystein said, I was trying to explain what Revelation should do to solve the problem (adding the cursor # to the key). There is no way you can do it. Trust me Frank I sure know how frustrating it is for you. I have had this problem since the first of the year. Kinda a long time to wait for a fix on a problem that is quite disasterous. Your original post said that you had a problem with 2 cursors and one of the overwriting the other. I would then do a loop of readnext's and save the whole cursor (select list) 1 into a variable kinda like this… IDS=' DONE=0 LOOP READNEXT ID ELSE DONE=1 END UNTIL DONE DO IDS=ID REPEAT Then do the following rather than doing the actual "READNEXT" for cursor (select list) 1. POS=0 MORE=(IDS NE ) LOOP WHILE MORE REMOVE ID FROM IDS AT POS SETTING MORE … … … REPEAT See this will eliminate using multiple cursors (select lists). I know of no other way to work around the multiple cursor flaw. If this does not work for you let me know. I surely will give it a try to help you with this and share more on how I worked around it. </QUOTE> —- === At 26 JUN 2003 04:41AM Oystein Reigem wrote: === <QUOTE>Richard, Frank, I know of no other way to work around the multiple cursor flaw. In my postings I've hinted at a different way. I'll flesh it out for you, but take no responsibility for errors in the code: Save the first list to named SYSLISTS rows, e.g, like this: <code> $insert Rlist_Equates </code> <code> declare function Get_Status </code> <code> declare subroutine Set_Status </code> <code> </code> <code> Query=…your SELECT statement here… </code> <code> </code> <code> /* make a suitable name. make sure the name is unique */ </code> <code> TargetName=PRIMARYLIST*" : @Station : "*" : @Username </code> <code> </code> <code> /* select and save list to named row(s) */ </code> <code> Set_Status(0) </code> <code> Rlist( Query, TARGET_SAVELIST$, TargetName, "", "" ) </code> <code> Dummy=Get_Status( Status_Text ) </code> <code> Set_Status(0) </code> <code> if Status_Text then </code> <code> … error handling … </code> <code> end </code> After this code is run your list will be stored in one or more SYSLISTS rows. I think even an empty list (i.e, if the query doesn't select any rows) will be stored. Say your station is called "XYZ-123" and your username is "WIZ". Then the SYSLISTS rows containing the list will have names PRIMARYLIST*XYZ-123*WIZ PRIMARYLIST*XYZ-123*WIZ*1 PRIMARYLIST*XYZ-123*WIZ*2 PRIMARYLIST*XYZ-123*WIZ*3 … etc … Then let your main loop be a nested loop with the outer loop walking the SYSLISTS rows (fragments), and the inner loop walking the keys in each row: <code> open "SYSLISTS" to SysListVar else </code> <code> … error handling … </code> <code> end </code> <code> </code> <code> RowNo=0 </code> <code> MoreRows=true$ </code> <code> repeat </code> <code> RowName=TargetName </code> <code> if RowNo ] 0 then RowName := "*" : RowNo </code> <code> read Row from SysListVar, RowName then </code> <code> if RowNo=0 then </code> <code> /* remove header */ </code> <code> if Row1, 1=@TM then </code> <code> Row=delete( Row, 1, 0, 0 ) </code> <code> end else </code> <code> … no header!?!? error handling … </code> <code> end </code> <code> end </code> <code> Pos=0 </code> <code> More=true$ </code> <code> loop </code> <code> while More </code> <code> remove Key from Row at Pos setting More </code> <code> … … … </code> <code> next I </code> <code> end else </code> <code> MoreRows=false$ </code> <code> end </code> <code> until not(MoreRows) </code> This method might be better than the variable method on very long lists, because you don't need the whole list in memory. (Somebody who knows better might tell you that's not important; I'm not an expert on this.) If for some reason you want smaller chunks in memory than whole SYSLISTS rows you can do that too. (I do that myself some places.) The programming gets a bit more complicated, but not much. - Oystein - </QUOTE> —- === At 26 JUN 2003 04:55AM Oystein Reigem wrote: === <QUOTE>Richard, Frank, I know of no other way to work around the multiple cursor flaw. In my postings I've hinted at a different way. I'll flesh it out for you, but take no responsibility for errors in the code: Save the first list to named SYSLISTS rows, e.g, like this: <code> $insert Rlist_Equates </code> <code> declare function Get_Status </code> <code> declare subroutine Set_Status </code> <code> </code> <code> Query=…your SELECT statement here… </code> <code> </code> <code> /* make a suitable name. make sure the name is unique */ </code> <code> TargetName=PRIMARYLIST*" : @Station : "*" : @Username </code> <code> </code> <code> /* select and save list to named row(s) */ </code> <code> Set_Status(0) </code> <code> Rlist( Query, TARGET_SAVELIST$, TargetName, "", "" ) </code> <code> Dummy=Get_Status( Status_Text ) </code> <code> Set_Status(0) </code> <code> if Status_Text then </code> <code> … error handling … </code> <code> end </code> After this code is run your list will be stored in one or more SYSLISTS rows. I think even an empty list (i.e, if the query doesn't select any rows) will be stored. Say your station is called "XYZ-123" and your username is "WIZ". Then the SYSLISTS rows containing the list will have names PRIMARYLIST*XYZ-123*WIZ PRIMARYLIST*XYZ-123*WIZ*1 PRIMARYLIST*XYZ-123*WIZ*2 PRIMARYLIST*XYZ-123*WIZ*3 … etc … Then let your main loop be a nested loop with the outer loop walking the SYSLISTS rows (fragments), and the inner loop walking the keys in each row: <code> open "SYSLISTS" to SysListVar else </code> <code> … error handling … </code> <code> end </code> <code> </code> <code> RowNo=0 </code> <code> MoreRows=true$ </code> <code> repeat </code> <code> RowName=TargetName </code> <code> if RowNo ] 0 then RowName := "*" : RowNo </code> <code> read Row from SysListVar, RowName then </code> <code> if RowNo=0 then </code> <code> /* remove header */ </code> <code> if Row1, 1=@TM then </code> <code> Row=delete( Row, 1, 0, 0 ) </code> <code> end else </code> <code> … no header!?!? error handling … </code> <code> end </code> <code> end </code> <code> Pos=0 </code> <code> More=true$ </code> <code> loop </code> <code> while More </code> <code> remove Key from Row at Pos setting More </code> <code> … … … </code> <code> next I </code> <code> end else </code> <code> MoreRows=false$ </code> <code> end </code> <code> until not(MoreRows) </code> This method might be better than the variable method on very long lists, because you don't need the whole list in memory. (Somebody who knows better might tell you that's not important; I'm not an expert on this.) If for some reason you want smaller chunks in memory than whole SYSLISTS rows you can do that too. (I do that myself some places.) The programming gets a bit more complicated, but not much. - Oystein - </QUOTE> View this thread on the forum...