These variables are sometimes set as a side effect of executing statements, e.g. rowcnt and sometimes should be explicitly set by the user to control the operation of isql, e.g. maxrows.

The variable names are case insensitive. Like in UNIX shells, the variable name in an expression must be prefixed by a $ to return its value. Variables do not have an explicit data type. Like Unix shell variables they have string values which are sometimes interpreted as numbers and sometimes as booleans. Generally an on/off choice is represented by the values ON/OFF. The ON/OFF values are case insensitive.

Example 7.4. Example:

SQL> SET AUTOCOMMIT ON;
SQL> echo $autocommit;
ON
SQL> set U{var} foo;
SQL> echo $u{VAR};
foo

Note that the $u{<var>} notation is a general purpose text substitution macro without arguments. The expansion takes place regardless of SQL syntactic context.


Example 7.5. Example:

set u{table} SYS_KEYS;
select count (*) from $u{table};

RETVAL

Function called return value

RETVALLEN

Function called return value length

ROWCNT

Number of rows in the last result set. If the statement was an insert, update or delete this is the number of rows affected.

COLCNT

Number of columns in the last resultset

ARGC

Number of ISQL command line arguments

I

Number of script arguments (after -i)

LIF

Result of the last $IF

INPUTLINE

Current executed line

STATE

SQL State (defaults to OK)

SQLSTATE

Same as state.

MESSAGE

the SQL error message from the last operation.

DRIVER

The Driver name

LWE

Last ECHO output

DSN

The DSN or host address used/to be used in connecting to the server. Use this with uid and pwd before the connect or reconnect command to change the connected server. This is useful for scripts which access multiple servers.

UID

The User ID used/to be used in connecting to the server

PWD

The Password used/to be used in connecting to the server

ERRORS

The current stream for errors (STDIN, STDOUT, STDERR or a file name)

PROMPT

Prints the PROMPT or not

EMPTY

The current empty string value

VERSION

ISQL version

BLOBS

When ON binds prints the BLOB resultset values when printing the resultset. Otherwise prints 'BLOB x chars'

ECHO

When ON prints the commands to the standard output before executing

BANNER

When ON prints the column names & types banner when printing the resultset

TYPES

When ON prints the Type information in the resultset's banner

VERBOSE

When ON Prints the Timing row after the resultset output

TIMESTOSTRINGS

When ON binds the SQL_DATE, SQL_TIME & SQL_TIMESTAMP columns to strings

TRAILING_NEWLINES

When ON prints new line after the row's end

DEADLOCK_RETRIES

How many times to retry the statement if deadlock occurred

MACRO_SUBSTITUTION

When ON ISQL does understand & process $ macros

IGNORE_PARAMS

When ON passes the ? through to the server instead of returning an error for unbound ?

BIND_RETURN_VALUES

When ON binds a buffer to SQL_RETURN_VALUE

AUTOCOMMIT

When ON ISQL executes statements in autocommit mode. By default isql executes statements in manual commit mode, following each execution with a SQLTransact call to commit. The difference is not visible most of the time.

ACCESSMODE

Sets the ODBC access mode (RW, RO)

TIMEOUT

ODBC Query timeout

MAXROWS

Print only the first so many rows. If 0 - unlimited.

CURRENT_QUALIFIER

Prints the current ODBC SQLGetInfo client Qualifier

INFO_DATABASE_NAME

Prints the current ODBC SQLGetInfo Database name

INFO_USER_NAME

Prints the current ODBC SQLGetInfo user name

INFO_GETDATA_EXTENSTIONS

Prints the current ODBC SQLGetInfo SQLGetData extensions flag

COMMAND_TEXT_ON_ERROR

When OFF ISQL does not print the text of the command in the error messages