G'day,
I started writing this up and in doing so solved my problem. So thanks
for being there! I still have a couple of questions remaining so if anyone
knows one or more of the answers I would be pleased to hear.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have developed a you-beaut SuperSifter (trade mark pending) module that
allows a user to select a table, then with a CHOOSE a column in that table
by which to select records then a Where builder type form (but with
explanations and samples) and the code creates a multi-table SQL SELECT
statement from the menu options.
Trouble is, (I assumed) because there are several views based on variables
that are not always set at start-up the routine crashes on Win95 (works fine
on NT).
As per Bill Downall's recommendation all views using var have the var
enclosed in parentheses.
"No problem!" I hear someone say, "Declare your var prior to running the
routine so there is no problem at the engine level."
Tried that. Setting the var required by the views causes the results of the
CHOOSE to take 14.5 seconds longer to display than with the var unset.
Anyone know why? This is the CHOOSE:
CHOOSE vColName +
FROM #VALUES +
FOR (LJS(sys_comment,50) & LJS(sys_column_name,20)),sys_column_name +
FROM SYS_COLUMNS +
WHERE sys_table_id = .vSysTableID +
ORDER BY sys_comment +
AT 12,10 +
TITLE 'Column Descriptions and Names' +
CAPTION 'Click a column to sift on or press [Esc] when done' +
LINES 20 +
FORMATTED
I have tried Dennis McGrath's suggestion of basing the CHOOSE on a table
projected from SYS_COLUMNS but the PROJECT takes 14.5 seconds too
with the var set! That is unacceptable even as part of a start-up routine.
One potential workaround I tried is to delete the 11 views based on var
prior to calling the SuperSifter then recreating those views after the
CHOOSE command has executed.
This cut the wait time down to 6 seconds but I still get the message:
-ERROR- Unrecognized global variable in expression. (2161)
with no views in existence based on var.
I do not know why I would get this error message after the following code:
SELECT SYS_TYPE_NAME INTO +
vDataType IND vi1 +
FROM sys_columns +
WHERE sys_table_id = .vSysTableID +
AND sys_column_name = .vColName
when both var have values.
On writing this I relooked at the code and noticed the tautological nature
of the SELECT. Because R:Base enforces referential integrity at the
engine level then for each column name there can only be one data type.
So I changed the above to:
SELECT SYS_TYPE_NAME INTO +
vDataType IND vi1 +
FROM sys_columns +
WHERE sys_column_name = .vColName +
AND LIMIT=1
and ceased getting the error message re vSysTableID AND CEASED
EXPERIENCING THE CRASHES!
I would be pleased to learn why I would get an error message number 2161
saying that there was an unrecognized global variable in the expression when
there wasn't.
CONCLUSION
Dennis McGrath's suggestion of PROJECTing system tables and using the
projected table in lieu of the system table would appear to resolve a bug in
using variables with the system tables.
Warmest regards,
Tom Grimshaw
coy: Just For You Software
tel: 61 (0)2 9552 3311
fax: 61 (0)2 9566 2164
email: [EMAIL PROTECTED]
mobile: 0414 675 903
street: 3/66 Wentworth Park Rd Glebe NSW 2037
post: PO Box 470 Glebe NSW 2037 Australia
