Automatically Creating Bind Variables for Retrieved Columns

When you set autovariables on in ttIsql, TimesTen creates an automatic bind variable named after each column fetched. As the rows are fetched, the fetched values are copied into the variables.

The following example selects all rows from the employees table. Since all columns are retrieved, automatic variables are created and named for each column. The bind variable contains the last value retrieved for each column.

Command> SET AUTOVARIABLES ON;
Command> SELECT * FROM employees;
...
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000,
 <NULL>, 101, 70 >
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR, 
12000, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT, 
8300, <NULL>, 205, 110 >

Command> PRINT;
EMPLOYEE_ID          : 206
FIRST_NAME           : William
LAST_NAME            : Gietz
EMAIL                : WGIETZ
PHONE_NUMBER         : 515.123.8181
HIRE_DATE            : 1994-06-07 00:00:00
JOB_ID               : AC_ACCOUNT
SALARY               : 8300
COMMISSION_PCT       : <NULL>
MANAGER_ID           : 205
DEPARTMENT_ID        : 110

As each result set is fetched, each column value is placed into a variable named after the column. If more than one row is fetched, the last value of each column overrides the value of the variable. To prevent the variables from being overwritten, turn off the setting.

If you provide an alias for a column name, the automatic bind variable name uses the alias, rather than the column name.

Command> SET AUTOVARIABLES ON;
Command> SELECT employee_id ID, First_name SURNAME, last_name LASTNAME 
 FROM employees;
 
ID, SURNAME, LASTNAME
...
< 204, Hermann, Baer >
< 205, Shelley, Higgins >
< 206, William, Gietz >
107 rows found.
Command> PRINT;
ID                   : 206
SURNAME              : William
LASTNAME             : Gietz

You can also use the describe command to show the column names. The following example uses the describe command to display the column names for the ttConfiguration built-in procedure.

Command> DESCRIBE TTCONFIGURATION;
 
Procedure TTCONFIGURATION:
  Parameters:
    PARAMNAME                       TT_VARCHAR (30)
  Columns:
    PARAMNAME                       TT_VARCHAR (30) NOT NULL
    PARAMVALUE                      TT_VARCHAR (1024)
 
1 procedure found.

For any query that fetches data without a known named column, set columnlabels on to show the column names. The following example shows that the columns returns from ttConfiguration built-in procedure are paramname and paramvalue.

Command> SET AUTOVARIABLES ON;
Command> SET COLUMNLABELS ON;

Command> call TTCONFIGURATION('LockLevel');

PARAMNAME, PARAMVALUE
< LockLevel, 0 >
1 row found.
Command> print paramname;
PARAMNAME : LockLevel