7.2.4. ISQL Variables
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};
Variable Reference
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