Some of these temporary views result from views that contain various
combinations of INNER JOINSand OUTER JOINS. Apparently RBase materializes
intermediate temporary views while it processes complexviews.
Be careful when you develop reports, labels, and forms especially if you
continually toggle between
coding the item and doing a preview of the item. Clean up of such temporary
views do not occur untilyou exit the report/label/form designer. Each time you
preview a new set of temp views may be created.
Jim Bentley, American Celiac Society 1-504-305-2968
From: 'Lawrence Lustig' via RBASE-L <[email protected]>
To: "[email protected]" <[email protected]>
Sent: Monday, January 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:
#yiv3031684618 -- filtered {panose-1:2 4 5 3 5 4 6 3 2 4;}#yiv3031684618
filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv3031684618
filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv3031684618
filtered {font-family:Verdana;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv3031684618
p.yiv3031684618MsoNormal, #yiv3031684618 li.yiv3031684618MsoNormal,
#yiv3031684618 div.yiv3031684618MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv3031684618 a:link,
#yiv3031684618 span.yiv3031684618MsoHyperlink
{color:black;text-decoration:underline;}#yiv3031684618 a:visited,
#yiv3031684618 span.yiv3031684618MsoHyperlinkFollowed
{color:black;text-decoration:underline;}#yiv3031684618 p
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv3031684618
p.yiv3031684618MsoAcetate, #yiv3031684618 li.yiv3031684618MsoAcetate,
#yiv3031684618 div.yiv3031684618MsoAcetate
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv3031684618
span.yiv3031684618BalloonTextChar {}#yiv3031684618
p.yiv3031684618msochpdefault, #yiv3031684618 li.yiv3031684618msochpdefault,
#yiv3031684618 div.yiv3031684618msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv3031684618
span.yiv3031684618emailstyle18 {}#yiv3031684618 span.yiv3031684618EmailStyle22
{color:#1F497D;}#yiv3031684618 span.yiv3031684618EmailStyle23
{color:#1F497D;}#yiv3031684618 span.yiv3031684618EmailStyle24
{color:#1F497D;}#yiv3031684618 span.yiv3031684618EmailStyle25
{color:#1F497D;}#yiv3031684618 span.yiv3031684618EmailStyle26
{color:black;}#yiv3031684618 .yiv3031684618MsoChpDefault
{font-size:10.0pt;}#yiv3031684618 filtered {margin:1.0in 1.0in 1.0in
1.0in;}#yiv3031684618 div.yiv3031684618WordSection1 {}#yiv3031684618 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.
