Just FYI that I've never seen that error message before. I never even knew there was a "systmp_views" table!
Karen -----Original Message----- From: 'Lawrence Lustig' via RBASE-L <[email protected]> To: rbase-l <[email protected]> Sent: Mon, Jan 16, 2017 1:22 pm Subject: Re: [RBASE-L] - Strange temporary views make form slow to close. Just found another example, a much simpler form (but still some temporary views and a few list views) that shows this message 42 times when closing, and takes over 20 seconds to return control to R:Base! -- Larry On Monday, January 16, 2017 11:16 AM, 'Lawrence Lustig' via RBASE-L <[email protected]> wrote: I have a fairly complex form. The form creates some temporary views and uses them to supply a variety of list views. When I close the form, I see the message 1 row(s) have been deleted from SYSTMP_VIEWS repeated 24 times. It takes about ten seconds for all these messages to display during which time R:Base is not accessible. These messages are not coming from the views I create in code (I don't drop those views when I close the form) but rather some strange views that seem to be internally created by R:Base. I note that the time of about ten seconds is consistent if I do EDIT USING MyForm from the R> prompt. But if I do EDIT USING MyForm from inside a PRG file and run the PRG file, the messages display much faster (maybe 4 seconds). But even 4 seconds is a delay I don't want my users to suffer through. I can find out what views are being dropped by R:Base by including the command sele LISTOF(sys_table_name) from sys_tables where sys_table_id in (select sys_table_id from systmp_views) in my ON CLOSE EEP and then repeating the same command after my form has closed. These views exist before the form is closed: #1, #11, #15, #16, tvwPOHdrEx, tvwPOItems, tvwPOReqDtlDates, #17, tvwPOReqHdrDisp, tvwPOAttachments, #22, tvwItemDisplay, #25, #26, #27, #28, #29, #30, #31, #32, #33, #34, #35, #36, #37, #38, #39, #40, #41, #42, #43, #44, #45, #46, #47, tvwPOReceiveHdrEx, #48, #49, #50 The views with names like "tvw. . ." are the ones I create, and I expect those to exist. It's the views with the number signs that I can't account for. And these are all that's left after the form has finished: #1, #11, #15, #16, tvwPOHdrEx, tvwPOItems, tvwPOReqDtlDates, #17, tvwPOReqHdrDisp, tvwPOAttachments, #22, tvwItemDisplay, tvwPOReceiveHdrEx. My questions are: Where are these views coming from and how can I avoid creating and dropping them? Or, at least, not paying the performance penalty I'm seeing. If R:Base is making them, how come it's not cleaning up all of them? Note that six of the numbered views remain after the form is closed. Additional info: Database is running with STATICDB ON. -- Larry On Wednesday, November 2, 2016 3:24 PM, Jim Belisle <[email protected]> wrote: Thanks Javier. I have had a user asking for some time to have me change an important column. I have done it manually in the past so have put it off. This will help I am sure. James Belisle Making Information Systems People Friendly Since 1990 From: [email protected] [mailto:[email protected]] On Behalf Of Javier Valencia Sent: Wednesday, November 02, 2016 2:08 PM To: [email protected] Subject: RE: [RBASE-L] - Column size change Here is the code I wrote; I created it because I had a data base with lots of text columns that needed to be resized and I figured in the long term it would save time over doing it manually. As you can see it is quite old and I believe I had a newer version where I checked for the system view needed and if not available it created it; you can easily add the code to do this or simply create the view ahead of time. I have not used this code in a long time or with the latest versions so I am not sure if it still works as originally designed. You might to make a few changes but the majority of the code should work correctly; I am sure you can make changes as needed. As with all utilities, please be sure to back up your data base before running the utility just in case something gets corrupted you can get the original version back. I make no promises that the code will work as originally designed and you can use at your own risk. Hopefully it will be of use to you or anyone else that needs it. *( Change_txt_col_lgt.rmd - Change the length of a text column Written by: Javier Valencia on 01/15/2004 Last Modified by: Javier Valencia on 01/15/2004 This utility will change the length of a text column where the column is defined in more than one table. The code uses a View V_table_column which is a combination of the: SYS_TABLES and SYS_COLUMNS linked by the SYS_TABLE_ID common column The view definition is as follows: SYS_COLUMNS.SYS_DATA_TYPE, SYS_COLUMNS.SYS_LENGTH FROM SYS_TABLES sys_tables, SYS_COLUMNS WHERE sys_tables.SYS_TABLE_ID = SYS_COLUMNS.SYS_TABLE_ID AND sys_tables.SYS_TABLE_TYPE = 'TABLE' ORDER BY sys_tables.SYS_TABLE_NAME ASC, SYS_COLUMNS.SYS_COLUMN_NAME ASC The RENAME and ALTER commands do not work when there is an open CURSOR so the CURSOR needs to be opened and droped several times ) CLEAR VAR vcolname,vrows,vnewlgt,vendkey SET VAR vcaption TEXT = ' STEP 1 - Select Column' SET VAR vcolname TEXT = NULL SET VAR voldlgt INTEGER = NULL LABEL start -- select TEXT columns from tables (not views or system tables) -- The SYS_TABLE_TYPE = 'TABLE' is redundant as it is in view definition -- You can include SYS_DATA_TYPE = 3 (text column) in the view definition and -- do away with the WHERE clause all together. CHOOSE vcolname FROM #VALUES FOR DISTINCT sys_column_name + FROM v_table_column + WHERE sys_table_type = 'TABLE' AND sys_data_type IN (3,-3) + CAPTION .vcaption LINES 10 IF vcolname = '[Esc]' THEN GOTO done ENDIF -- Get the current text length SET VAR voldlgt = sys_length IN sys_columns WHERE sys_column_name = .vcolname -- Get the new text length SET VAR vcaption TEXT = ' STEP 2 - Select New Length' SET VAR vmsg TEXT = ('Current Length is: ' + CTXT(.voldlgt) + + (CHAR(013))+ 'Enter New Lenght:' ) SET VAR vtnewlgt TEXT = NULL --SET VAR viNewLgt INTEGER = 0 SET VAR vrows INTEGER = NULL DIALOG .vmsg vtnewlgt=4 vendkey 1 + CAPTION vcaption ICON APP + OPTION TITLE_FONT_COLOR BLACK + |TITLE_BACK_COLOR WHITE + |TRANSPARENCY 255 + |WINDOW_BACK_COLOR WHITE IF vendkey = '[Esc]' OR vtnewlgt IS NULL THEN GOTO done ENDIF -- check to see how many tables have this column, if only one, use DB Designer -- if not ask for confirmation SELECT COUNT(*) INTO vrows INDICATOR ivrows + FROM v_table_column WHERE sys_column_name = .vcolname IF vrows = 1 THEN PAUSE 2 USING + 'Only 1 column with this name, use DBDESIGN to change lenght!' + CAPTION .vcaption ICON warning + OPTION BACK_COLOR WHITE + |MESSAGE_COLOR RED + |MESSAGE_FONT_COLOR WHITE GOTO done ELSE CLS SET VAR vmsg = + ('Column:'+(CHAR(009))+(CHAR(009)) & .vcolname + (CHAR(013))+ + 'Current Length:' + (CHAR(009))& CTXT(.voldlgt) + (CHAR(013))+ + 'New Length:' + (CHAR(009)) & .vtnewlgt + (CHAR(013))+ + 'Tables:' + (CHAR(009)) & CTXT(.vrows) + (CHAR(013))) CLS DIALOG .vmsg vyesno vendkey yes CAPTION ' Step 3 - Confirm Everything' ICON APP + OPTION TITLE_FONT_COLOR BLACK + |TITLE_BACK_COLOR WHITE + |TRANSPARENCY 255 + |WINDOW_BACK_COLOR WHITE + |BUTTON_YES_CAPTION &Yes + |BUTTON_NO_CAPTION &No + |BUTTON_YES_COLOR GREEN + |BUTTON_NO_COLOR RED + |BUTTON_YES_FONT_COLOR WHITE + |BUTTON_NO_FONT_COLOR WHITE IF vendkey = '[Esc]' OR vyesno = 'No' THEN GOTO done ENDIF SET ERROR MESSAGES 705 OFF DROP CURSOR ptr2 SET ERROR MESSAGES 705 ON SET VAR vtemp1colname = (.vcolname + '_T1') SET VAR vtemp2colname = (.vcolname + '_T2') -- Rename the columns to a temporary name RENAME COLUMN &vcolname TO &vtemp1colname NOCHECK -- Find the first table with this column DECLARE ptr2 CURSOR FOR SELECT sys_table_name FROM v_table_column + WHERE sys_column_name = .vtemp1colname OPEN ptr2 SET VAR SQLCODE = 0 FETCH ptr2 INTO vtablename DROP CURSOR ptr2 -- Rename the temporary column name to the oruginal column name RENAME COLUMN &vtemp1colname TO &vcolname IN &vtablename NOCHECK -- Change the TEXT Length to the new length SET VAR vcomm = ('ALTER TABLE ' + .vtablename + ' ALTER COLUMN ' + + .vcolname + ' TO ' + .vcolname + ' TEXT ' + .vtnewlgt) &vcomm -- The RENAME and ALTER command will not work with an open cursor -- so the procedure will need to be repeated until there are no -- more tables to be updated LABEL do_again DECLARE ptr2 CURSOR FOR SELECT sys_table_name FROM v_table_column + WHERE sys_column_name = .vtemp1colname OPEN ptr2 SET VAR SQLCODE = 0 FETCH ptr2 INTO vtablename IF SQLCODE = 100 THEN DROP CURSOR ptr2 GOTO end_do_again ENDIF DROP CURSOR ptr2 -- Rename the next occurrence to a second temporary name, -- change it TEXT size and rename it back to the original name RENAME COLUMN &vtemp1colname TO &vtemp2colname IN &vtablename NOCHECK SET VAR vcomm = ('ALTER TABLE ' + .vtablename + ' ALTER COLUMN ' + + .vtemp2colname + ' TO ' + .vtemp2colname + ' TEXT ' + .vtnewlgt) &vcomm RENAME COLUMN &vtemp2colname TO &vcolname IN &vtablename NOCHECK SET VAR SQLCODE = 0 GOTO do_again LABEL end_do_again PAUSE 2 USING 'Columns Updated!' + CAPTION .vcaption ICON warning + OPTION BACK_COLOR WHITE + |MESSAGE_COLOR RED + |MESSAGE_FONT_COLOR WHITE GOTO done ENDIF LABEL done CLEAR VAR vcaption,vcolname,vcomm,vendkey,vinewlgt,vmsg,voldlgt,vrows,vtempcolname,vyesno,vtablename,vtnewlgt,vmsg RETURN Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From:[email protected] [mailto:[email protected]]On Behalf Of Javier Valencia Sent: Wednesday, November 02, 2016 1:02 PM To: [email protected] Subject: RE: [RBASE-L] - Column size change I keep the last 2 or 3 version on my computer but the older ones are archived and obviously this utility is several years old so I did not find among the files currently in my computer. I will do a search of the server backups for the older versions and hopefully I will find it there. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From:[email protected] [mailto:[email protected]]On Behalf Of [email protected] Sent: Wednesday, November 02, 2016 9:17 AM To: [email protected] Subject: RE: [RBASE-L] - Column size change Thank You Tony and Javier for the responses. As Jim said, if someone does find the program please post or send. Cheers Anne -------- Original Message -------- Subject: RE: [RBASE-L] - Column size change From: "Javier Valencia" <[email protected]> Date: Tue, November 01, 2016 1:01 pm To: <[email protected]> Anne, First you have to rename the Foreign keys say ColumnA to ColumnAx, then resize the Primary key and then change the name back of the Foreign keys to the new name and the columns will take on the new size. I wrote a program that does it all for you, asks you which TEXT column you want to re-seize and what size you want it to be and then it goes through the process above, identifying the Primary and Foreign keys and resizing accordingly and in the proper sequence. I have not used it in a long time and I will see if I can find it. I now I sent it to several member before and maybe someone has it handy. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From:[email protected] [mailto:[email protected]]On Behalf Of [email protected] Sent: Tuesday, November 01, 2016 11:30 AM To: [email protected] Subject: [RBASE-L] - Column size change Hello! Is there a command that would increase the size of a column that exists in multiple tables, or is the manual way the only way? Thank You Anne -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
