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.
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:
<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.
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 :
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.
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
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"
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.
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.
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
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
Previous
Virtuoso .NET Data Provider |
Chapter Contents |
Next
Virtuoso Driver for ODBC |