SAS/IntrNet 9.2: htmSQL

Automatic Variables

htmSQL automatically defines a number of variables that contain htmSQL processing information. The following sections list the variables and examples of variable values and usage:

htmSQL also provides a sample input file named autovars.hsql that lists values for many of the automatic variables.

Date and Time Variables

The format of the date and time variable information is determined by the LC_TIME and LANG environment variables and according to the NLS installation on your Web server machine.

Variable Name	Description	Example Value
sys.ampm	the time of day before or after noon (AM or PM)	PM
sys.date	the current date in ddMmmyy or ddMmmyyyy format (depending on the value of the `YEARDIGITS` run-time configuration option)	04Apr2000
sys.datetime	the current date in ddMmmyy hh:mm:ss TZ or ddMmmyyyy hh:mm:ss TZ format (depending on the value of the `YEARDIGITS` run-time configuration option)	04Apr2000 16:30:52 EDT ^*
sys.fulldate	the current date, including the weekday and date	Friday, April 04, 1997
sys.fulldatetime	the current date, including the weekday, date, time, and time zone	Friday, April 04, 1997 04:30:52 PM EDT ^*
sys.month	the month of the year	September
sys.month3	the month of the year (abbreviated)	Sep
sys.monthday	the day of the month	05
sys.monthnum	the month of the year expressed numerically	09
sys.seconds	the second of the minute	47
sys.time	the time of day using a 12-hour clock	3:36
sys.time24	the time of day using a 24-hour clock	15:36
sys.tz	the time zone	EDT ^*
sys.weekday	the day of the week	Saturday
sys.weekday3	the day of the week (abbreviated)	Sat
sys.year	the calendar year	1997
sys.year2	the last two digits of the calendar year	97

^*For the Windows platforms, the value for the time zone is not abbreviated (for example, Eastern Daylight Time).

SQL-Related Variables

The following list contains variables for information that is related to an SQL statement:

sys.colname

The column names in a results set. Use the following syntax to reference a single specific value, a range of values, or all values:

`&sys.colname[n]`	references the `n`th column name in the results set where `n>=1`.
`&sys.colname[m..n]`	references all column names from the `m`th value to the `n`th value where `m<=1<n` and the results set contains two or more columns.
`&sys.colname[*]`	references all the column names in the results set.

For example, for the following SQL statement,

{sql}select * from employee.names{/sql}

the selected columns are first, middle, and last. The following are some example references and their resolved values:

{&sys.colname[1]}     ----->   first
{&sys.colname[2..3]}  ----->   middle last
{&sys.colname[*]}     ----->   first middle last

sys.qrow

The number of the current row in the results set. The following example shows how to include the SYS.QROW variable in an eachrow section:

{eachrow}
{&sys.qrow} {&lastname} {&firstname} {&middleinit}
{/eachrow}

Each row that is output would contain the number of the row and the three values that correspond to the other three variable references. The output might look something like the following:

1        Doe     John    D.
2        Doe     Jane    R.
3        Doe     Sam     E.

sys.query

The last SQL query that is processed. All the characters in the query are included except for

newline characters
leading blank spaces that are before the SELECT keyword.

sys.updrows sys.updcount

The number of rows that are inserted, deleted, or updated by the last INSERT, DELETE, or UPDATE statement.

sys.updrc

The return code from the last INSERT, DELETE, or UPDATE statement.

Miscellaneous Variables

Variable Name	Description	Example Value
sys.dirurl	the URL directory path (with no filename) for the top-level input file	/myfiles/
sys.filetime	the date and time of the last modification of the current input file	Monday, May 05, 1997 02:05:45 PM EDT ^*
sys.fileurl	the URL pathname for the top-level input file	/myfiles/myinput.hsql
sys.url	the URL for invoking htmSQL (not including the pathname of the input file)	/cgi-bin/htmSQL
sys.version	the version number for htmSQL	2.0

^*For the Windows platforms, the value for the time zone is not abbreviated (for example, Eastern Daylight Time).

Top of Page