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 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, the selected columns are first, middle, and last:
{sql}select * from employee.names{/sql}
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 contains 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).