Here is a program that creates variables from columns in a table.
Choose the table, it creates variables.
I use this when I want to make a form and base it on the FormTable
(1 column, 1 row.)
*(****************************
program name : fvColumn.eep
purpose of program: to create form variables from table columns
date written / by: 9 March 2002 by CR Peterson
called by: R:>prompt
Version Number: 1.0
NOTE: For Version 7.0 lose the AT in the Choose Command
*****************************)
SET MESSAGES ON
SET ERROR MESSAGES ON
CLS
CLEAR VAR vResponse
CLEAR VAR vEndkey
CLEAR VAR vLines
CLEAR VAR vWidth
CLEAR VAR vItemCount1
SET VAR vItemCount1 INTEGER = 0
-- Choose the name of the table you want columns from
-- modified 12/12/2003 for ease of use
CHOOSE vResponse FROM #Tables +
AT 15, 25 +
TITLE 'tables in database' +
CAPTION 'choose a table; press enter' +
IF vResponse IS NULL OR vResponse = '[ESC]' THEN
RETURN
ENDIF
*( original way of entering table name
DIALOG 'What table do you wish to create variables for?' +
vResponse=18=10 vEndkey 1 +
CAPTION 'type table name, press enter' +
AT 15, 25
IF vEndKey IS NULL OR vEndKey = '[ESC]' THEN
RETURN
ENDIF
*)
-- are any of the column names too long to become fvVariables
SELECT (LISTOF(sys_column_Name)) INTO vLongNameList IND vInd +
FROM sys_Columns +
WHERE (SYS_Table_ID = (SELECT sys_table_ID +
FROM sys_tables +
WHERE (sys_table_name = .vResponse) ) ) +
AND ( (SLEN(sys_column_name)) > 16 )
SET VAR vItemCount1 = ((ITEMCNT(.vLongNameList)) + 1)
IF (SLEN(vLongNameList)) > 0 THEN
CHOOSE vLongName FROM #LIST .vLongNameList +
AT 10,20 +
TITLE 'column names which are too long' +
CAPTION 'shorten these column or variable names' +
LINES .vItemCount1
ENDIF
-- format the output
SET HEADINGS OFF
SET VAR vLines = (CVAL('lines'))
SET LINES 0
SET VAR vWidth = (CVAL('width'))
SET WIDTH 80
OUTPUT fvColumn.txt
-- write the command to clear all variables
WRITE ' '
WRITE '-- clear all column variables'
WRITE ' '
SELECT ('CLEAR VAR fv' + Sys_Column_Name)=80 +
FROM SYS_Columns +
WHERE (SYS_Table_ID = (SELECT sys_table_ID +
FROM sys_tables +
WHERE (sys_table_name = .vResponse) ) )
-- write the command to set all variables
WRITE ' '
WRITE ' '
WRITE '-- set all column variables'
WRITE ' '
SELECT ('SET VAR fv' + Sys_Column_Name)=30, sys_Type_Name=8, '= NULL' +
FROM SYS_Columns +
WHERE (SYS_Table_ID = (SELECT sys_table_ID +
FROM sys_tables +
WHERE (sys_table_name = .vResponse) ) )
OUTPUT SCREEN
SET LINES .vLines
SET WIDTH .vWidth
CLEAR VAR vResponse
CLEAR VAR vEndKey
CLEAR VAR vLines
CLEAR VAR vWidth
--CLEAR VAR vLongNameList
CLEAR VAR vLongName
CLEAR VAR vind
--CLEAR VAR vItemCount1
RETURN
--------------------------
Running this code in ConComp for the table: Customer creates
fvColumn.txt:
-- clear all column variables
CLEAR VAR fvCustID
CLEAR VAR fvCompany
CLEAR VAR fvCustAddress
CLEAR VAR fvCustCity
CLEAR VAR fvCustState
CLEAR VAR fvCustZip
CLEAR VAR fvCustPhone
CLEAR VAR fvModLevel
CLEAR VAR fvLastUpdateDate
CLEAR VAR fvLastUpdateTime
CLEAR VAR fvLastOrderDate
CLEAR VAR fvCustURL
CLEAR VAR fvCustEMail
CLEAR VAR fvCustStatus
-- set all column variables
SET VAR fvCustID INTEGER = NULL
SET VAR fvCompany TEXT = NULL
SET VAR fvCustAddress TEXT = NULL
SET VAR fvCustCity TEXT = NULL
SET VAR fvCustState TEXT = NULL
SET VAR fvCustZip TEXT = NULL
SET VAR fvCustPhone TEXT = NULL
SET VAR fvModLevel INTEGER = NULL
SET VAR fvLastUpdateDate DATE = NULL
SET VAR fvLastUpdateTime TIME = NULL
SET VAR fvLastOrderDate DATE = NULL
SET VAR fvCustURL TEXT = NULL
SET VAR fvCustEMail TEXT = NULL
SET VAR fvCustStatus TEXT = NULL
--------------------
Happy Holidays.
Randy Peterson
Shane Handley wrote:
> The logic isn't very complicated if you could do this from the R:base side, rather
> than r:code. R:base could simply have a function that does a similar function to
> list, only it outputs it to a .dat file and truncates the lines accordingly to
> achieve
> the same result instantly. So it makes a variable for each column and puts it in a
> file.
>
> output [table].dat
> list [table] - without comments, or any other information,
>
> truncate lines an insert set var v,
>
> remove any additional information other than the type definition.
>
> ..and you have a ready made file that defines a variable for each column within
> your table.
>
> You could do a similar thing to format a "list' to create a cursor which includes
> every column/inserts a null variable into each of these columns.
>
> I thouht about knocking up an application that does this, but haven't had the time
> (qEdit must suffice for now).
>
> > Someone should talk to Razzak about including an R:Base data dictionary
> feature and the features Shane is talking about into a new version of R:Code.
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [EMAIL PROTECTED]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
