Karen,
Another possibility is to build in a counter. Make the counter visible and then you might be able to see how many rows are handled by the program before it stops. The next step could be to incorporate the statement 'set trace on' when the counter has reached a certain value. This procedure could provide the possibility to have a look at all variables and other relevant thing. If the routine always stops at the same point you might be able to find out what causes the problem. Just an idea Tony From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: zondag 12 augustus 2012 17:06 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Cursor ideas? Jim: Thanks for the feedback! I'll try to answer your questions. As I mentioned, we're still on 32-bit because we need to connect to it with the DOS version until we get this (and a couple other things) converted. They're on 7.6. We haven't upgraded yet because we need to go 64-bit but are holding off til we know we can get off DOS completely. If worse comes to worse, we can maintain one copy of DOS and write a routine that would unload data for all the tables involved in this program, upload to DOS, run the routine, then send the data back to a 64-bit database. I've never heard of "#nnn" temp tables before. Since they're on 7.6 could this be an issue? Are you suggesting that I do something like cursor thru sys_tables to grab #nnn names and do a drop table if any, perhaps in the startup program or at the top of this program? Yes this is extremely old code, and tons of it. We do run with whileopt off (I put it at the top of the program even though the startup specifies it too). I did catch some places where they defined variables with datatypes within the cursor, and moved them outside so I think I'm clean there. As far as data types changing... I didn't check for that, and I've seen many places before where the previous programmer did that, so I'll check for that! Yes, that's something I would never personally do! There's 3 cursors if I remember right. I'll check if they're all pre-defined before the first while loop. As far as computed columns, do you mean if they are part of the "declare cursor ... for select" command? I'll check that too. Thanks for your suggestions! Haven't decided yet if I'll try the "goto first" or clean up the code. With 3 levels of cursors, using "goto" could get pretty confusing! Karen In a message dated 8/11/2012 11:10:19 AM Central Daylight Time, <mailto:[email protected]> [email protected] writes: What versions are you using? Earlier versions have a problem that automatically created "#nnn" temporary tables to support some views and cursor definitions. The temporary tables were not removed after use and affected the stability of the system. This affected DOS and Windows versions (32bit &64Bit). A fix was issued for DOS v9.1 and Windows v9.x, to the best of my memory, some time in 2011. To check this SELECT COUNT(*) FROM SYS_TABLES WHERE (SGET(SYS_TABLE_NAME,1,1))='#' after system fails and before you disconnect as disconnecting will remove all temporary tables. You mention that you tried it in Windows. Was that the 32bit version of the 64bit version. In the Windows 32 bit version you may be encountering one or more limits. See <http://rbase.com/rbg95/compare.php> http://rbase.com/rbg95/compare.php which spells out size limitations. Normally the number of rows in the table should not affect the cursor except if the code creates temporary tables as outlined above. I was the identifier of the If this is extremely old code or code that has been modified over time you might want to review the code to make sure that: * ALL variables used have been defined outside of loops (preferable in a block close to the beginning of the program). * Make sure that the data type for a variable is not changed once it is defined. * Make sure that no variables are deleted with loops * Make sure that all CURSOR definitions occur outside the loops * If you have nested CURSORs this could affect memory requirements * Are their any computed columns in the table being processed by the cursor. If so they could be the source of the "#nnn" temporary tables I know the ideal is to have all parts of the system in the 64 bit version RBase for Windows. Since the commission calculation is being made on a copy of the database with importation of the final results back into the live database have you considered developing a STORED PROCEDURE to unload any required tables to a file and RELOAD them into a 32bit for DOS database and run the calculation, have a STORED PROCEDURE to export the results and a SOTRED PROCEDURE to re-import into the 64bit Windows version. Jim Bentley, American Celiac Society 1-504-737-3293
