Karen, I didn't know about '#nnn' tables until I encountered them in V9.1. I don't know if they occur in v7.6 for DOS. As I stated I encountered them in v9.x I don't know if they occur in v7.6 For DOS. I am assuming you are encountering your problem only in the Windows V9.x versions. If you disconnect they disappear. At this point I am not suggesting you try to "delete" or handle them. First you need to determine if they are being created.
As for computed columns, check both in the Declare Cursor and in any Views that are used in this code. There have been significant changes into the RBase engine since v7.6. I think the introduction of the '#nnn' temporary tables are the result of changes in the engine to improve the speed in handling certain join syntax and computed column processing. Feel free to call me for further discussions. Jim Bentley, American Celiac Society 1-504-737-3293 ________________________________ From: "[email protected]" <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Sunday, August 12, 2012 10:06 AM 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, [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 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 >
