Contents SAS/IntrNet 9.1: htmSQL Previous Next

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 2 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 nth column name in the results set where n>=1.
&sys.colname[m..n] references all column names from the mth value to the nth 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 NameDescriptionExample 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).


Contents SAS/IntrNet 9.1: htmSQL Previous Next