# 7. Data Access Interfaces

Chapter Contents | Prev | Next
Keyword Search:
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 :

- Position 1 - <Host> or <port> or <Host:port> (native), DSN (ISQLO/ISQLU/isql-iodbc/isql-udbc)
- Position 2 - <user_id>
- Position 3 - <password>
- Position 4 and later - script file name to load

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 :

- ? - an ordinary parameter bound to the foreach value
- ?C - the count of successful operations

- LINE processes the input line by line
- INTEGER converts the input into integer and then processes it.
- BLOB reads the entire input and sets it as an blob parameter using SQLPutData
- TIMESTAMP is the same as LINE, bit binds to a TIMESTAMP SQL Type
- DATE is the same as LINE, but binds to a DATE SQL Type
- TIME is the same as LINE, bit binds to a TIME SQL Type

- IN specifies a set of items terminated by an END
- BETWEEN specifies integer set starting from n1 and ending on n2
- FOLLOWING as as IN but skips the input till the END keyword

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.

- B means BEST_ROWID, otherwise ROWVER.
- S means Session scope, T means transaction scope, otherwise current row.
- N means No Nulls, otherwise Nullable.

### 7.2.3. ISQL Macro Substitution

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

- $LOADEXPR - the current script startup command
- $LINENO - the current line number
- $YYYYMMDD - the current date in the same format
- $HHMMSS - the current time in the same format

    $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

Previous Virtuoso .NET Data Provider | Chapter Contents | Next Virtuoso Driver for ODBC