www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Data Access Interfaces

Virtuoso .NET Data Provider
Interactive SQL Utility
Invoking ISQL ISQL Commands ISQL Macro Substitution ISQL Variables Using isql as a General Purpose Test Driver
Virtuoso Driver for ODBC
Virtuoso Driver for JDBC
OLE DB Provider for Virtuoso
Virtuoso In-Process Client
Unix Domain Socket Connections

7.2. Interactive SQL Utility

ISQL/ISQLO/ISQLU/isql-iodbc/isql-udbc

The Virtuoso ISQL utility allows users to execute queries & scripts against the Virtuoso or other SQL servers (depending on the suffix). It also supports a large number of options and it's own commands.

ISQL parses it's input for CREATE (PROCEDURE|TRIGGER|FUNCTION), '{', '}', ';', double and single quotes and comments to detect where the current command starts and finishes. If it's in a PROCEDURE|TRIGGER|FUNCTION declaration it changes it's command line prompt to '<procedure_name>(<curly_brace_nesting_level>)' to help the user enter the correct command. Otherwise it considers semicolon (;) as the statement end and executes the statement.

ISQL also has a form of script language by itself.

7.2.1. Invoking ISQL

ISQL is invoked from the command line of the operating system. It is a text-mode application with support of readline library (where available).

Specify the --help or the -? option to view the usage:

bash$ ./bin/isql --help
OpenLink Interactive SQL (Virtuoso), version 0.9849b.

   Usage :
isql <HOST>[:<PORT>] <UID> <PWD> file1 file2 ...

isql -H <server_IP> [-S <server_port>] [-U <UID>] [-P <PWD>]
     [-E] [-X <pkcs12_file>] [-K]
     [-u <name>=<val>]* [-i <param1> <param2>]

     isql -?
Connection options:

  -?                  - This help message
  -U username         - Specifies the login user ID
  -P password         - Specifies the login password
  -H server_addr      - Specifies the Server address (IP)
  -S server port      - Specifies the TCP port to connect to
  -E                  - Specifies that encryption will be used
  -C                  - Specifies that password will be sent in cleartext
  -X pkcs12_file      - Specifies that encryption & X509 certificates will
                        be used
  -T server_cert      - Specifies that CA certificate file to be used
  -b size             - Specifies that large command buffer to be used
                        (in KBytes)
  -K                  - Shuts down the virtuoso on connecting to it

Parameter passing options:

  -u name1=val1... - Everything after -u is stored to associative array U,
                        until -i is encountered. If no equal sign then value
                        is NULL
  -i                  - Ignore everything after the -i option, after which
                        comes arbitrary input parameter(s) for isql procedure,
                        which can be referenced with $ARGV[$I] by the
                        ISQL-commands.
  <OPT>=<value>       - Sets the ISQL options

  Note that if none of the above matches then the non-options go as
  <HOST>[:<PORT>] <UID> <PWD> file1 file2 ...

If the command line option is not one of the above it is considered a "non-option" parameter. If the non-option parameter contains the equal sign ('=') then it's considered an ISQL option value assignment. For example 'MAXROWS=10' limits the number of rows returned for a resultset to 10 instead of the default 0.

Otherwise ISQL treats the "non-option" parameters (identified by their position) as follows :

Let us consider the following command line:

isql 1111 dba dba VERBOSE=OFF 'EXEC=status()' test.sql test2.sql -i arg1 arg2

The '1111' is the first "non-option", so it represents the connection host:port (This is all digits so the virtuoso CLI prepends this with 'localhost', i.e. this is an shortcut for 'localhost:1111').

The first 'dba' is the second "non-option", so it is used as User ID.

The second 'dba' is the third "non-option", so it is used as Password.

'VERBOSE=OFF' is a "non-option", but it has an equal sign in it, so this becomes an ISQL option SET statement. In this particular case this disables the output of "Done xxx msec" messages after each command.

'EXEC=status()' is a "non-option", but again has again an equal sign in it. EXEC is a special option. Setting it to a value means executing that value as an SQL command. The result of this is that status() gets executed and it's results shown.

'test.sql' is the fourth "non-option" and it specifies a file name to load a script from and execute.

'test2.sql' is the fifth "non-option" and it again specifies a file name to load a script from and execute.

'-i' specifies that the script argument list starts. So every option after the -i is filled in the ISQL $ARGV[] array.

The order of execution is:

  1. Connect to 'localhost:1111' using UserID/Password dba/dba.
  2. Set the VERBOSE ISQL flag to OFF.
  3. Set the $ARGV[0]=arg1 and $ARGV[1]=arg2.
  4. Execute status() and display the results.
  5. Execute the script in test.sql and display the results.
  6. Execute the script in test2.sql and display the results.
  7. Disconnect and exit the ISQL


7.2.2. ISQL Commands

<SQL_command> &

Spawns a new copy of ISQL as a background OS process to execute the command. The new copy will make its own connection and will terminate after the command completes. The isql instance which received the command prompts for the next command without waiting for the spawned isql instance

! <command> (;|&)

Executes an OS command

SET <ISQL_OPTION>( |=)<VALUE>

sets the ISQL variable or associative array U value, if it is recognized as such. Otherwise passed to the server.

Examples
SET U{test} 5; sets the associative array U's 'test' to have value of 5
SHOW [<ISQL_OPTION>]

shows the value of an ISQL option, if it is recognized as such. Otherwise passed to the server.

NOP

no-operation. Useful sometimes in an $IF command.

(ECHO|ECHOLN) [STDOUT|STDERR|ERROR_STREAM|BOTH] string1 string2 ...

echoes it's arguments into the specifies output stream (defaults to STDOUT if not specified) If ECHOLN is used it appends a new line after the last character printed.

WAIT_FOR_CHILDREN

Waits for background ISQL subcommands spawned by specifying an & instead of ; at the end of the statement.

LOAD <file_name>

Executes each statement of the specified file. Statements end in a semicolon or at the closing curly brace for procedure or trigger definitions.

HELP [<isql-command>]

Prints general or command specific help texts.

(EXIT|QUIT) [NOT]

Exits ISQL

EXIT NOT is a NO-OP, allowing statements like: EXIT $IF $EQU $ARGV[0] 10 $ARGV[0] NOT; which exits with exit code 10 if $ARGV[0] is ten (presumably keeping a some-kind of failure counter) but otherwise does nothing special, and continues from the next statement.

SLEEP [<seconds>]

sleeps for the specified number of seconds or until an OS signal arrives. If seconds is not specified it's considered 0 and the behavior is OS dependent (the OS sleep function on Unixes, Sleep() in the Windows API).

CONNECT [<conn_str>]

Without arguments forces the connection to be made with an ordinary way with SQLConnect and so-far specified connection details. With an argument uses SQLDriverConnect, giving that argument as a connection string (e.g. "DSN=Virtuoso;UID=DBA,PWD=DBA") If we are already connected, then is just NO-OP which is silently ignored.

RECONNECT [<User_ID>]

If the ISQL is already connected then disconnects. After that it tries connecting to the same data source, but with using its argument as a user name. If none specified it gets the user name/password from the UID and PWD ISQL variables.

FOREACH [LINE|INTEGER|BLOB|TIMESTAMP|DATE|TIME]
	[FOLLOWING|IN (-|-b|<file_name>)|BETWEEN <n1> <n2>]
	<statement_with_params> <statement_with_params> ... [END]

This command executes the <statement_with_params> in a loop, after binding the parameters specified in it to values specified in the command.

Parameters are specified as follows :

Examples:
foreach integer between 1 10 insert into num_tb values (?);

Inserts 10 rows from 1 to 10 into the num_tb

foreach line in my_text_file.txt insert into str_tb (line_no, data) values (?C, ?);

Inserts each line of the text file my_text_file.txt into str_tb's data column and puts line number in line_no column

foreach blob in my_text_file.txt insert into file_tb values (?);

inserts a row into the file_tb with the contents of the my_text_file.txt as a single blob value.

SHUTDOWN

Passes this through to the server as a command

CALL <SQL statement>

Tries to bind & print the SQL_RETURN_VALUE and display multiple resultsets (if any) after executing the statement.

COLUMNS [<table_mask>][/<column_mask>]

Calls SQLColumns ODBC

TABLES [<table_mask>]

Calls SQLTables ODBC

PRIMARYKEYS [<table_mask>]

Calls SQLPrimaryKeys ODBC

COLUMNPRIVILEGES [<table_mask>]/[<procedure_column_mask>]

Calls SQLColumnPrivileges ODBC

PROCEDURES [<procedure_mask>]

Calls SQLProcedures ODBC

PROCEDURECOLUMNS [<procedure_mask>]/[<procedure_column_mask>]

Calls SQLProcedureColumns ODBC

TABLEPRIVILEGES [<table_mask>]

Calls SQLTablePrivileges ODBC

GETTYPEINFO

Calls SQLGetTypeInfo (SQL_ALL_TYPES) ODBC

STATISTICS [<table_mask>] [/U]

Calls SQLStatistics ODBC. U means show only unique indexes otherwise all

SPECIALCOLUMNS [<table_mask>] [/(B?S?T?N?)]

Calls SQLSpecialColumns ODBC.


7.2.3. ISQL Macro Substitution

Before executing commands ISQL expands macros in statement text. The following macros and macro related commands are available:

$IF <cond> <THEN_RESULT> [<ELSE_RESULT>]

If the condition evaluates to non-empty and is not the literal 0 the value of the $IF is the <THEN_RESULT> else the <ELSE_RESULT>.

$(+|-) <arg1> <arg2>

result is the addition or substitution of the arg1 and arg2

$LAST[<n>]

an array of the last resultset row

$ARGV[<n>]

an array of the command line arguments

$U[<name>]

prints the user associative array U value for the name <name>

$<ISQL_Variable>

returns the value of that variable

$LT <v1> <v2>

returns 1 if the <v1> is lower that <v2>, empty otherwise

$GT <v1> <v2>

returns 1 if the <v1> is greater than <v2>, empty otherwise

$GTE <v1> <v2>

returns 1 if the <v1> is greater or equal than <v2>, empty otherwise

$LTE <v1> <v2>

returns 1 if the <v1> is lower or equal than <v2>, empty otherwise

$EQU <v1> <v2>

returns 1 if the <v1> is equal to <v2>, empty otherwise

$NEQ <v1> <v2>

returns 1 if the <v1> is not equal to <v2>, empty otherwise

Examples:
ECHO $IF $EQU 1 2 "True" "False";

prints "False" to the standard output

EXIT $IF $NEQ 1 2 NOT;

never exits

ECHO $IF $EQU $ARGV[1] 1 "One" $IF $EQU $ARGV[1] "Two" "Unknown;

prints the "one" on 1 as arg 1, "two" on 2 as arg 1 otherwise "Unknown"


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:
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:
set u{table} SYS_KEYS;
select count (*) from $u{table};

7.2.4.1. 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



7.2.5. Using isql as a General Purpose Test Driver

isql can be used to automatically run SQL scripts which execute statements, perform simple tests on the results and generate a report. This can be used to automate tests of stored procedures or to benchmark them.

Also note the use of & at the end of a command to spawn multiple isql instances on the background. This is useful for automatically creating concurrency situations for testing.

Consider the script:

drop table tt;
create table tt (id int identity not null primary key, ctr int);
create procedure tt_fill (in n int)
{
  declare ctr int;
  ctr := 0;
  while (ctr < n){
    insert into tt (ctr) values (ctr);
    ctr := ctr + 1;
  }
}

tt_fill (10000) &
tt_fill (10000) &
tt_fill (10000) &
tt_fill (10000) &

wait_for_children;
select count (*), count (distinct ctr)  from tt;

echo both $if $equ $last[1] 40000 "PASSED" "***FAILED";
echo both " Inserted " $last[1] " rows\n";
echo both $if $equ $last[2] 10000 "PASSED" "***FAILED";
echo both " Inserted " $last[2] " distinct ctr values\n";

Now suppose the above text were in the file test.sql. The command

isql 1111 errors=stdout <test.sql >test.out

would print the diagnostics to the standard error and the full trace to test.out. Note the errors=stdout would direct the error message for no table in the initial drop table to the text.out file, so the console would just print:

PASSED Inserted 40000 rows
PASSED Inserted 10000 distinct ctr values