=====================================================================
Working with Variables
=====================================================================
Product: R:BASE Version: 3.1x, 4.0, 4.5
=====================================================================
Area: Logic and Manipulation Catalog: Programming in R:BASE
=====================================================================
Level
Products:
Variables hold temporary values which cease to exist when you exit
from R:BASE. Variables are known across applications unless they are
explicitly removed. Variables hold column values, constants, results
of calculations and parts of R:BASE commands. The value of the
variable determines how it is used. A variable that contains part of
an R:BASE command must be evaluated differently than a variable that
contains values. There are four different types of R:BASE variables:
system, dotted, ampersand and percent.
Many R:BASE commands create variables and put values into the
variable. Some of the most commonly used are: SET VAR, COMPUTE,
SELECT, FILLIN, DIALOG and CHOOSE. Commands whose syntax diagram
includes varname indicates a variable can be used in the command.
System Variables
System variables are those variables defined by R:BASE itself. Their
names begin with a "#" character (except for SQLCODE). In general,
you don't define or change the value of system variables. You usually
use them in expressions to calculate other values, or for comparisons.
Not all system variables are displayed with a SHOW VAR command, some
remain hidden from view.
R:BASE System Variables
#DATE
Always defined. #DATE is set to the system date of your computer. It
cannot be changed except by exiting R:BASE and using the DATE command
at your operating system prompt.
#TIME
Always defined. #TIME is set to the system time of your computer. It
cannot be edited except by exiting R:BASE and using the TIME command
at your operating system prompt.
#PI
Always defined. It is set to the value of PI to 14 decimal places, the
maximum precision available with a DOUBLE data type.
SQLCODE
Always defined. SQLCODE is the ANSI SQL error variable. It is the only
R:BASE system variable whose name does not begin with "#". Its value
is set and changed as SQL commands execute. When a command is
successful its value is 0.
#printervar
Printer variables are defined when a printer (.PRD file) is set. These
variables do not display with a SHOW VAR command; however, SHOW VAR
#printervar displays the printer code. For example, SHOW VAR #BOLD.
Printer variables display in Reports and Label Create/modify under the
Print styles menu option. You can't change the value of a printer
variable using the SET VAR command.
#BROWDATA
Holds a value captured using the Shift-F10 key while browsing or
editing data with the Info/views menu.
#PAGE
The page numbering variable used by Reports. You can set #PAGE using
the SET VAR command. You set it to enable your report to start
printing at a number other than 1.
#RETURN
Used by variable forms only. #RETURN holds the keystroke that was used
to exit the form.
Dotted Variables
Dotted variables are by far the most common R:BASE variables. Most
variables are used to hold values. When you use a variable in a
calculation or as a comparison value, you "dot" the variable, i.e.
preface its name with a period, ".". The period looks like a dot,
hence the name "dotted variable." The dot tells R:BASE to use the
value contained in the variable.
A rule of thumb for when to "dot" a variable is to always "dot" the
variable when it is on the right side of the operator. Dotting a
variable basically turns it into a constant value. R:BASE looks only
at the value of the variable when it is dotted. R:BASE doesn't look
at the datatype, just at the value the variable contains. That's why
you can have a TEXT datatype variable (result of a DIALOG command, for
example) containing a value that looks like a DATE and use that
variable to compare to a DATE datatype column or variable.
Note that in expressions, R:BASE checks the datatype of a dotted
variable. An expression is anything enclosed in parentheses. R:BASE
verifies the datatype in expressions to make sure the expression is
valid. You can't add an INTEGER to a TEXT for example. Dotted
variables are commonly used in WHERE clauses and in calculations
with other variables. For example,
SET VAR vdate DATE = 5/30/93
SELECT * FROM transmaster +
WHERE transdate <= .vdate
selects all the records from the table Transmaster where the value
in the column transdate is less than or equal to the value contained
in the variable vdate.
SELECT netamount, shipmethod +
INTO vamount ind1, vship ind2 +
FROM orders WHERE ordernum = .vorder
IF vship = 'AIR' THEN
SET VAR vfreight = $11.00
ELSE
SET VAR vfreight = $5.00
ENDIF
SET VAR vamount = +
(.vamount + .vfreight)
SET VAR vtax = +
taxrate IN states WHERE stateabr = .vstate
SET VAR vamount = +
(.vamount = (.vamount * .vtax))
On the right side of the equals sign (the operator), the variable is
dotted. On the left side of the operator, in the IF and the SET VAR
commands, the variable is referenced by its name only, it is not
dotted.
When using the syntax diagrams and on-line HELP, varname means use
the variable name only, .varname indicates you use a dotted variable
in that position. A dotted variable can also be used any place you see
value in a syntax diagram.
Dotted variables are used in Form and Report expressions as they are
in the SET VAR command. On the right side of the operator, dot the
variable.
Ampersand Variables
You can't dot a variable when it contains part of a command - a table
or column name, or an ORDER BY or WHERE clause. When a variable
contains part of a command, its name is prefaced with an ampersand,
"&", and it is called an ampersand variable. The "&" in front of the
variable name tells R:BASE that the variable contains part of the
command, not a value, and the contents of the variable are used when
parsing the command. Don't confuse the ampersand that prefaces a
variable name with the ampersand that is used to concatenate TEXT
values.
Ampersand variables are most often used to hold table and column names
and WHERE and ORDER BY clauses. By using ampersand variables to hold
column and table names, you can use the same command to select data
from different tables. The CHOOSE command displays menus of available
tables and columns. For example,
CHOOSE vtable FROM #TABLES AT CENTER,CENTER
CHOOSE vcollist FROM #COLUMNS IN &vtable +
AT CENTER,CENTER CHKBOX
Each time, a different table and different columns can be selected.
The CHOOSE...FROM #TABLES automatically displays a menu of all user
defined tables in the database. Use #TBLVIEWS to include view names
in the menu. The CHOOSE...FROM #COLUMNS automatically displays a menu
of all the columns in the specified table or view. Including the
CHKBOX option lets the user select the columns to view. The values
selected from the menus are placed into variables. The variables might
look like this:
R>SHOW VAR
Variable = Value Type
------------------ ------------------------------ --------
#DATE = 07/22/93 DATE
#TIME = 8:42:38 TIME
#PI = 3.14159265358979 DOUBLE
SQLCODE = 0 INTEGER
vtable = employee TEXT
vcollist = empid,emplname,empphone,empext TEXT
The variables are then used in any command that uses a table name or
column list. In syntax diagrams where you see colname or tblview you
can substitute an ampersand variable that contains the column, table
or view name. The variables must be used as ampersand variables to
tell R:BASE they contain part of the command. For example,
BROWSE &vcollist FROM &vtable
To prompt for an ORDER BY clause, use the CHKSORT option instead of
CHKBOX on the CHOOSE...FROM #COLUMNS.
CHOOSE vorderby FROM #COLUMNS IN &vtable +
AT CENTER,CENTER CHKSORT
The CHKSORT option prompts for Ascending or Descending just like the
R:BASE sort menus. The variable contains ASC or DESC as well as the
column names. It might look like this:
vorderby = emplname ASC,empfname ASC TEXT
In the command, follow the keywords ORDER BY with the ampersand
variable containing the columns to order by.
BROWSE &vcollist FROM &vtable +
ORDER BY &vorderby
The easiest way to let users enter their own WHERE clause is to use
the CHOOSE...FROM #WHERE. Like the CHOOSE...FROM #COLUMNS, #WHERE
requires a table or view name reference. It presents a selection box
that looks just like the one for Quick select off the R:BASE main
menu. The variable might look like this:
vwhere = empstate = 'WA' TEXT
It is used in the command as an ampersand variable after the keyword
WHERE:
BROWSE &vcollist FROM &vtable +
WHERE &vwhere ORDER BY &vorderby
Here's a complete program that includes simple error checking. You
need to check that both a table and a column list are selected. This
program simply exits if either a table or a column list is not
selected. You could write a message to the user and ask if they want
to exit or start over. The program determines whether or not an ORDER
BY clause and a WHERE clause were selected. You need to execute
different commands if one of them was not selected.
CHOOSE vtable FROM #TABLES +
AT CENTER,CENTER
IF vtable = '[Esc]' THEN
RETURN
ENDIF
CHOOSE vcollist FROM #COLUMNS +
IN &vtable CASCADE CHKBOX
IF vcollist = '[Esc]' THEN
CLS
RETURN
ENDIF
CLS
WRITE ' Table:', .vtable AT 3,5
WRITE ' Columns:', .vcollist AT 4,5
WRITE ' Choose columns to sort by...' +
AT 6,5
CHOOSE vorderby FROM #COLUMNS +
IN &vtable AT 8,12 CHKSORT
CLS FROM 6 TO 25
IF vorderby <> '[Esc]' THEN
WRITE 'Order by:', .vorderby AT 5,5
ELSE
WRITE 'No sort' AT 5,5
ENDIF
CHOOSE vwhere FROM #WHERE IN &vtable
IF vwhere <> '[Esc]' AND +
vorderby <> '[Esc]' THEN
BROWSE &vcollist FROM &vtable +
WHERE &vwhere ORDER BY &vorderby
ELSE
IF vwhere = '[Esc]' AND +
vorderby <> '[Esc]' THEN
BROWSE &vcollist FROM &vtable +
ORDER BY &vorderby
ELSE
IF vwhere <> '[Esc]' AND +
vorderby = '[Esc]' THEN
BROWSE &vcollist FROM &vtable +
WHERE &vwhere
ELSE
BROWSE &vcollist FROM &vtable
ENDIF
ENDIF
ENDIF
Because an ampersand variable is part of a command, it can't be used
inside parentheses. Parentheses indicate expressions, expressions are
parsed separately from the rest of the command. You need to include
the parentheses as part of the variable value. Sub-selects and IN
lists are enclosed in parentheses and you can't use an ampersand
variable inside them, you need to include the entire sub-select or IN
list, including parentheses, as the variable value.
For example, you have a vwhere variable,
vwhere = custstate = 'CA' TEXT
To use it in a sub-select to view transaction information for
customers from the state of California you might try:
BROWSE * FROM transmaster +
WHERE custid IN +
(SELECT custid FROM customer +
WHERE &vwhere)
This command generates an error message. The ampersand variable is
inside parentheses and isn't evaluated correctly. You need to put the
entire sub-select into the variable.
SET VAR vwhere2 = +
('(SELECT * FROM customer WHERE' +
& .vwhere + ')')
Everything inside the quotes is a text string. The variable
concatenates a text string that is part of a command to the vwhere
variable value. Notice that vwhere is used here as a dotted variable.
The resulting variable, vwhere2, looks like this:
vwhere2 = (SELECT * FROM customer WHERE TEXT
custstate= 'CA')
The command becomes:
BROWSE * FROM transmaster +
WHERE custid IN &vwhere2
The same is true when you select a list of values from a checkbox menu
to use in an IN list. The WHERE clause, ...WHERE colname IN
(&varname), returns an error. You need to use ...WHERE colname IN
&varname. The variable must include the parentheses. See the article
"Use SSUB on Variables that Hold Multiple Checkbox Picks" from the
January/February 1992 Exchange for an example of a program that makes
an IN list from checkbox menu selections (FAX server document #377).
Note that ampersand variables can't be used in Form and Report
expressions.
Another way to use an ampersand variable is to do ad-hoc commands
from an application. This uses what is often called a "fake r>
prompt". It appears as if you are typing in commands at the R>
prompt, but you are really typing the command into a variable and
then executing the command using an ampersand variable. Here's code
that does this.
SET VAR vcmd TEXT = 'a'
WHILE vcmd IS NOT NULL THEN
FILLIN vcmd=78,10,600 USING 'r>'
&vcmd
ENDWH
Using a FILLIN box lets you enter commands longer than 79 characters.
You don't enter the + for continuation, and won't see the "+>" prompt
when the line continues. Just type in the command letting the words
wrap. You can even use the F3 key to view and select column and table
names.
Percent Variables
A percent variable is created when a value is passed to a command file
from the R> prompt or another command file. Percent variables are
created and named by R:BASE. A percent variable is named %n, where n
is a number from 1 to 9. You can pass up to nine parameters. The first
parameter passed is named %1, the second %2 and so on. Percent
variables are commonly used with library routines, a single routine
that is used by many different application.
Percent variables are commonly referenced as just %n, but the actual
name is %n-m, where m is the run level, for example %1-0. From the R>
prompt, m is 0, from the first level, m is 1 and so on. Referring to a
percent variable as %1 only, automatically refers to the first
parameter passed to that particular command file. If you are passing
parameters at multiple levels, refer to the variable explicitly, e.g.
%1-2. You may have variables, %1-0, %1-2 and %1-3, there is no %1-1
variable if no USING parameter was used at run level 1.
Look at this example with multiple levels of passing parameters.
R>RUN test.cmd USING 'one', 'two'
SHOW VAR
Variable = Value Type
------------------ ------------------------------ --------
#DATE = 07/22/93 DATE
#TIME = 10:29:27 TIME
#PI = 3.14159265358979 DOUBLE
SQLCODE = 0 INTEGER
%1-0 = one TEXT
%2-0 = two TEXT
-- two parameters were passed, they are names %1-0 and %1-2
-- from within this file, RUN another file and pass parameters
RUN test1.cmd USING 'three','four'
-- run level 1, the first nested RUN
SET VAR vlevel1 = .%1
SHOW VAR
Variable = Value Type
------------------ ------------------------------ --------
#DATE = 07/22/93 DATE
#TIME = 10:29:28 TIME
#PI = 3.14159265358979 DOUBLE
SQLCODE = 0 INTEGER
%1-0 = one TEXT
%2-0 = two TEXT
%1-1 = three TEXT
%2-1 = four TEXT
vlevel1 = three TEXT
-- the parameters passed to file test1 are named with a "-1"
-- indicating
-- the run level. Notice that setting a variable to just %1
-- sets it to the first parameter passed at this run level.
-- From within this file, RUN another file, but don't pass parameters.
RUN test2.cmd
-- run level 2, the second nested RUN
SET VAR vlevel2_1 =.%1
SET VAR vlevel2_2 = .%1-1
SHOW VAR
Variable = Value Type
------------------ ------------------------------ --------
#DATE = 07/22/93 DATE
#TIME = 10:29:30 TIME
#PI = 3.14159265358979 DOUBLE
SQLCODE = 0 INTEGER
%1-0 = one TEXT
%2-0 = two TEXT
%1-1 = three TEXT
%2-1 = four TEXT
vlevel1 = three TEXT
vlevel2_1 = .%1 TEXT
vlevel2_2 = three TEXT
-- no parameters are passed at this level so there are no percent
-- variables created. Setting a variable to just %1 finds no value
-- because no parameter was passed at this level. The variable takes
-- on the literal text value ".%1".
-- RUN another file and pass parameters.
RUN test3.cmd using 'five','six'
-- run level 3, the third nested RUN
SHOW VAR
Variable = Value Type
------------------ ------------------------------ --------
#DATE = 07/22/93 DATE
#TIME = 10:29:32 TIME
#PI = 3.14159265358979 DOUBLE
SQLCODE = 0 INTEGER
%1-0 = one TEXT
%2-0 = two TEXT
%1-1 = three TEXT
%2-1 = four TEXT
vlevel1 = three TEXT
vlevel2_1 = .%1 TEXT
vlevel2_2 = three TEXT
%1-3 = five TEXT
%2-3 = six TEXT
-- Now there are percent variables named "-3", for run level 3.
-- We are actually four RUNs deep, but this is the third nested RUN
-- command. The first run, from the R> prompt, is named "-0" and
-- is not nested.
Parameters pass values, they do not pass data types. You cannot
pre-type a percent variable. It is recommended to set a percent
variable to a defined variable of the appropriate datatype in the
command file and then use that variable in subsequent commands rather
than using the percent variable itself.
Application developer Dennis McGrath uses a command file with passed
parameters to manipulate printer variables. Sometimes you want to send
printer control codes to the printer directly from an application, not
from within a report.
You can't define a printer control code at the R> prompt or in an
application using the "<" and ">" to enclose the decimal codes. That
format is specific to reports, only the report knows to interpret and
translate the codes. At the R> prompt or in an application, printer
control codes are defined using the CHAR function. For example, to
define Landscape mode for an HP Laser Jet printer, define this
variable,
SET VAR vland = +
(CHAR(27) + CHAR(38) + CHAR(108) +
+ CHAR(49) + CHAR(79))
The SHOW VAR command shows variable vland and contains the ASCII
character equivalents of the decimal numbers, vland = <-&l1O. The
control code is sent to the printer by using the SHOW VAR vland or
WRITE .vland command. You may want to do this to set the printer for
printing labels in condensed print, for example. Labels don't have
report header and footer lines to set the codes just once, you need
to set it every line. It is easier to send the code to the printer,
then print the labels, then reset the printer. Use these commands,
OUTPUT PRINTER
WRITE .vland
LBLPRINT emplabel
WRITE .vreset
OUTPUT SCREEN
The problem with defining printer control variables in an application
is that you need to know the decimal codes for the printer, and the
values are hard coded into the variable definition. They don't change
if the printer changes. Using Dennis' program, you can set variables
from the codes defined in the specified printer file, printer.PRD. The
codes are not hard coded for a particular printer, they change as the
printer changes.
Two parameters are passed to the program, the name of the printer
control variable to be converted (as a dotted variable) and the name
of the resulting variable (as a TEXT string). The program reads the
decimal codes from the system printer control variable and converts
them using the CHAR function. The resulting variable can be used to
send the specified printer control code to the printer from an
application. There must a printer set to use this program
successfully.
-- PRINT40.CMD -- use this file with R:BASE 3.x-4.0
-- It takes the data from a system printer variable and places the
-- printer control characters into a variable of your choice
-- for use where you wish to send printer control characters from
-- a command file without the use of a report
-- SYNTAX: RUN print40.cmd USING .#printvar 'varname'
-- SAMPLE: RUN print40.cmd USING .#Bold 'vboldon'
-- The contents of #Bold are converted and placed into vboldon
-- initialize variables
SET VAR vtempint INT, vcount INT, vtemp TEXT, vcode_conv TEXT = NULL
-- set percent variables to program variables
SET VAR vcode TEXT = .%1, vprntvar TEXT = .%2
-- get the length of the printer control code and
-- strip leading < and trailing >
SET VAR vtempint = (SLOC(.vcode,'>') - 2)
SET VAR vcode = (SGET(.vcode,.vtempint,2))
-- ssub function uses space delimiter with negative number to
-- separate items by blank instead of comma
SET VAR vcount = -1
WHILE #PI > 0.0 THEN
-- get one code string from vcode, the following commands are
-- repeated until no more codes are found, vtemp IS NULL
SET VAR vtemp = (SSUB(.vcode,.vcount))
IF vtemp IS NULL THEN
BREAK
ENDIF
-- convert the number from TEXT to INTEGER for the CHAR function
-- and add the character to the code string
SET VAR vtempint = (INT(.vtemp))
SET VAR vcode_conv = (.vcode_conv + CHAR(.vtempint))
-- decrement the count to get the next code
SET VAR vcount = (.vcount - 1)
ENDWH
-- place the completed printer control code into the result variable
-- an ampersand variable is used because vprntvar contains a variable
-- name
SET VAR &vprntvar = .vcode_conv
RETURN
-- PRINT45.CMD -- use this file with R:BASE 4.5
-- less code is needed because TEXT functions can be nested.
-- It takes the data from a system printer variable and places the
-- printer control characters into a variable of your choice
-- for use where you wish to send printer control characters from
-- a command file without the use of a report
-- SYNTAX: RUN print45.cmd USING .#printvar 'varname'
-- SAMPLE: RUN print45.cmd USING .#Bold 'vboldon'
-- The contents of #Bold are converted and placed into vboldon
-- initialize variables
SET VAR vcount INT, vtemp INT, vcode_conv = NULL
-- set percent variables to program variables
SET VAR vcode TEXT = .%1, vprntvar TEXT = .%2
-- get the length of the printer control code and
-- strip leading < and trailing >
SET VAR vcode = (SGET(.vcode,(SLOC(.vcode,'>') - 2),2))
-- ssub function uses space delimiter with negative number to
-- separate items by blank instead of comma
SET VAR vcount = -1
WHILE #PI > 0.0 THEN
-- get one code string from vcode, the following commands are
-- repeated until no more codes are found, vtemp IS NULL
SET VAR vtemp = (INT (SSUB(.vcode,.vcount)) )
IF vtemp IS NULL THEN
BREAK
ENDIF
-- convert the number from TEXT to INTEGER for the CHAR function
-- and add the character to the code string
SET VAR vcode_conv = (.vcode_conv + CHAR(.vtemp))
-- decrement the count to get the next code
SET VAR vcount = (.vcount - 1)
ENDWH
-- place the completed printer control code into the result variable
-- an ampersand variable is used because vprntvar contains a variable
-- name
SET VAR &vprntvar = .vcode_conv
RETURN
Clearing Variables
Variables are cleared by using the CLEAR VARIABLE command. Individual
variables can be cleared or all variables can be cleared. The CLEAR ALL
VAR command clears all user defined variables, but only clears the
system variable #PAGE. All other system variables are still there,
including printer variables.
To completely clear out the memory allocated for variables, use the
command, CLEAR ALL VAR NOW. A fourth argument to the CLEAR ALL VAR
command clears all the variable storage blocks, including all printer
control code variables and other non-permanent system variables, and
reallocates the blocks with just the permanent system variables,
#DATE, #TIME, #PI and SQLCODE.