<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="7. Data Access Interfaces" />
<meta name="dc.subject" content="7. Data Access Interfaces" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="accessinterfaces.html" title="Chapter Contents" />
<link rel="prev" href="virtclientref.html" title="ADO.Net Data Provider" />
<link rel="next" href="odbcimplementation.html" title="Virtuoso Driver for ODBC" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>7. Data Access Interfaces</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="isql" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>7. Data Access Interfaces</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="accessinterfaces.html">Chapter Contents</a> | <a class="link" href="virtclientref.html" title="ADO.Net Data Provider">Prev</a> | <a class="link" href="odbcimplementation.html" title="Virtuoso Driver for ODBC">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="accessinterfaces.html">Data Access Interfaces</a>
</div>
<br />
<div>
<a href="virtclientref.html">ADO.Net Data Provider</a>
</div>
<div class="selected">
<a href="isql.html">Interactive SQL Utility</a>
<div>
<a href="#invokingisql" title="Invoking ISQL">Invoking ISQL</a>
<a href="#isqlcommands" title="ISQL Commands">ISQL Commands</a>
<a href="#isqlmacrodef" title="ISQL Macro Substitution">ISQL Macro Substitution</a>
<a href="#isqlvariables" title="ISQL Variables">ISQL Variables</a>
<a href="#isqlgentesttool" title="Using isql as a General Purpose Test Driver">Using isql as a General Purpose Test Driver</a>
</div>
</div>
<div>
<a href="odbcimplementation.html">Virtuoso Driver for ODBC</a>
</div>
<div>
<a href="VirtuosoDriverJDBC.html">Virtuoso Driver for JDBC</a>
</div>
<div>
<a href="virtoledb.html">OLE DB Provider for Virtuoso</a>
</div>
<div>
<a href="inprocess.html">Virtuoso In-Process Client</a>
</div>
<div>
<a href="accintudsockets.html">Unix Domain Socket Connections</a>
</div>
<div>
<a href="dataccessclientsconfailandbalance.html">Virtuoso Data Access Clients Connection Fail over and Load Balancing Support</a>
</div>
<br />
</div>
<div id="text">
<a name="isql" />
<h2>7.2. Interactive SQL Utility</h2>
<p>ISQL/ISQLO/ISQLU/isql-iodbc/isql-udbc</p>
<p>
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.
</p>
<p>
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.
</p>
<p>
ISQL also has a form of script language by itself.
</p>
<a name="invokingisql" />
<h3>7.2.1. Invoking ISQL</h3>
<p>
ISQL is invoked from the command line of the operating system. It is a text-mode application with support
of readline library (where available).
</p>
<p>
Specify the --help or the -? option to view the usage:
</p>
<div>
<pre class="programlisting">
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.
If no PK is specified, then anonymous connection is established.
-C - Specifies that password will be sent in cleartext
-X pkcs12_file - Specifies that encryption & X509 certificates will
be used i.e the client PK <pksc12 file>
-T server_cert - Specifies the CA certificate(s) file to be used i.e. the list
of trusted issuers: a PEM (base64) file of all the X509 certificates
of the Certification Authorities (CA) which the client will use for
verifying the server's certificate. This is used on client side to
verify the server certificate.
-b size - Specifies the size of the 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 ...
</pre>
</div>
<p>
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.
</p>
<p>
Otherwise ISQL treats the "non-option" parameters (identified by their position) as follows :
</p>
<ul>
<li>Position 1 - <Host> or <port> or <Host:port> (native), DSN (ISQLO/ISQLU/isql-iodbc/isql-udbc)</li>
<li>Position 2 - <user_id></li>
<li>Position 3 - <password></li>
<li>Position 4 and later - script file name to load</li>
</ul>
<p>
Let us consider the following command line:
</p>
<div>
<pre class="programlisting">
isql 1111 dba dba VERBOSE=OFF 'EXEC=status()' test.sql test2.sql -i arg1 arg2
</pre>
</div>
<p>
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').
</p>
<p>
The first 'dba' is the second "non-option", so it is used as User ID.
</p>
<p>
The second 'dba' is the third "non-option", so it is used as Password.
</p>
<p>
'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.
</p>
<p>
'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.
</p>
<p>
'test.sql' is the fourth "non-option" and it specifies a file name to load a script from and execute.
</p>
<p>
'test2.sql' is the fifth "non-option" and it again specifies a file name to load a script from and execute.
</p>
<p>
'-i' specifies that the script argument list starts. So every option after the -i is filled in the ISQL $ARGV[] array.
</p>
<p>
The order of execution is:
</p>
<p>
<ol>
<li>Connect to 'localhost:1111' using UserID/Password dba/dba.</li>
<li>Set the VERBOSE ISQL flag to OFF.</li>
<li>Set the $ARGV[0]=arg1 and $ARGV[1]=arg2.</li>
<li>Execute status() and display the results.</li>
<li>Execute the script in test.sql and display the results.</li>
<li>Execute the script in test2.sql and display the results.</li>
<li>Disconnect and exit the ISQL</li>
</ol>
</p>
<br />
<a name="isqlcommands" />
<h3>7.2.2. ISQL Commands</h3>
<div>
<pre class="programlisting">
<SQL_command> &
</pre>
</div>
<p>
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
</p>
<div>
<pre class="programlisting">
! <command> (;|&)
</pre>
</div>
<p>
Executes an OS command
</p>
<div>
<pre class="programlisting">
SET <ISQL_OPTION>( |=)<VALUE>
</pre>
</div>
<p>
sets the ISQL variable or associative array U value, if it is recognized as
such. Otherwise passed to the server.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples</div>
<div>
<pre class="programlisting">
SET U{test} 5; sets the associative array U's 'test' to have value of 5
</pre>
</div>
</div>
<div>
<pre class="programlisting">
SHOW [<ISQL_OPTION>]
</pre>
</div>
<p>
shows the value of an ISQL option, if it is recognized as such. Otherwise passed to the server.
</p>
<div>
<pre class="programlisting">
NOP
</pre>
</div>
<p>
no-operation. Useful sometimes in an $IF command.
</p>
<div>
<pre class="programlisting">
(ECHO|ECHOLN) [STDOUT|STDERR|ERROR_STREAM|BOTH] string1 string2 ...
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
WAIT_FOR_CHILDREN
</pre>
</div>
<p>
Waits for background ISQL subcommands spawned by specifying an & instead of ; at the end of the statement.
</p>
<div>
<pre class="programlisting">
LOAD <file_name>
</pre>
</div>
<p>
Executes each statement of the specified file. Statements end in a
semicolon or at the closing curly brace for procedure or trigger definitions.
</p>
<div>
<pre class="programlisting">
HELP [<isql-command>]
</pre>
</div>
<p>
Prints general or command specific help texts.
</p>
<div>
<pre class="programlisting">
(EXIT|QUIT) [NOT]
</pre>
</div>
<p>
Exits ISQL
</p>
<p>
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.
</p>
<div>
<pre class="programlisting">
SLEEP [<seconds>]
</pre>
</div>
<p>
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).
</p>
<div>
<pre class="programlisting">
CONNECT [<conn_str>]
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
TableQualifiers
</pre>
</div>
<p>Returns all defined table qualifiers.
</p>
<div>
<pre class="programlisting">
TableOwners
</pre>
</div>
<p>
Returns existing table owners list.
</p>
<div>
<pre class="programlisting">
TableTypes
</pre>
</div>
<p>
Returns list of current table types.
</p>
<div>
<pre class="programlisting">
RECONNECT [<User_ID>]
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
FOREACH [LINE|INTEGER|BLOB|TIMESTAMP|DATE|TIME]
[FOLLOWING|IN (-|-b|<file_name>)|BETWEEN <n1> <n2>]
<statement_with_params> <statement_with_params> ... [END]
</pre>
</div>
<p>
This command executes the <statement_with_params> in a loop, after binding the parameters specified in it to
values specified in the command.
</p>
<p>
Parameters are specified as follows :
</p>
<ul>
<li>? - an ordinary parameter bound to the foreach value</li>
<li>?C - the count of successful operations</li>
</ul>
<ul>
<li>LINE processes the input line by line</li>
<li>INTEGER converts the input into integer and then processes it.</li>
<li>BLOB reads the entire input and sets it as an blob parameter using SQLPutData</li>
<li>TIMESTAMP is the same as LINE, bit binds to a TIMESTAMP SQL Type</li>
<li>DATE is the same as LINE, but binds to a DATE SQL Type</li>
<li>TIME is the same as LINE, bit binds to a TIME SQL Type</li>
</ul>
<ul>
<li>IN specifies a set of items terminated by an END</li>
<li>BETWEEN specifies integer set starting from n1 and ending on n2</li>
<li>FOLLOWING as as IN but skips the input till the END keyword</li>
</ul>
<a name="" />
<div class="example">
<div class="exampletitle">Examples:</div>
<div>
<pre class="programlisting">
foreach integer between 1 10 insert into num_tb values (?);
</pre>
</div>
<p>Inserts 10 rows from 1 to 10 into the num_tb</p>
<div>
<pre class="programlisting">
foreach line in my_text_file.txt insert into str_tb (line_no, data) values (?C, ?);
</pre>
</div>
<p>
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
</p>
<div>
<pre class="programlisting">
foreach blob in my_text_file.txt insert into file_tb values (?);
</pre>
</div>
<p>
inserts a row into the file_tb with the contents of the my_text_file.txt
as a single blob value.
</p>
</div>
<div>
<pre class="programlisting">
SHUTDOWN
</pre>
</div>
<p>
Passes this through to the server as a command
</p>
<div>
<pre class="programlisting">
CALL <SQL statement>
</pre>
</div>
<p>
Tries to bind & print the SQL_RETURN_VALUE and display multiple resultsets (if any) after executing the statement.
</p>
<div>
<pre class="programlisting">
COLUMNS [<table_mask>][/<column_mask>]
</pre>
</div>
<p>
Calls SQLColumns ODBC
</p>
<div>
<pre class="programlisting">
TABLES [<table_mask>]
</pre>
</div>
<p>
Calls SQLTables ODBC
</p>
<div>
<pre class="programlisting">
PRIMARYKEYS [<table_mask>]
</pre>
</div>
<p>
Calls SQLPrimaryKeys ODBC
</p>
<div>
<pre class="programlisting">
COLUMNPRIVILEGES [<table_mask>]/[<procedure_column_mask>]
</pre>
</div>
<p>
Calls SQLColumnPrivileges ODBC
</p>
<div>
<pre class="programlisting">
PROCEDURES [<procedure_mask>]
</pre>
</div>
<p>
Calls SQLProcedures ODBC
</p>
<div>
<pre class="programlisting">
PROCEDURECOLUMNS [<procedure_mask>]/[<procedure_column_mask>]
</pre>
</div>
<p>
Calls SQLProcedureColumns ODBC
</p>
<div>
<pre class="programlisting">
TABLEPRIVILEGES [<table_mask>]
</pre>
</div>
<p>
Calls SQLTablePrivileges ODBC
</p>
<div>
<pre class="programlisting">
GETTYPEINFO
</pre>
</div>
<p>
Calls SQLGetTypeInfo (SQL_ALL_TYPES) ODBC
</p>
<div>
<pre class="programlisting">
STATISTICS [<table_mask>] [/U]
</pre>
</div>
<p>
Calls SQLStatistics ODBC. U means show only unique indexes otherwise all
</p>
<div>
<pre class="programlisting">
SPECIALCOLUMNS [<table_mask>] [/(B?S?T?N?)]
</pre>
</div>
<p>
Calls SQLSpecialColumns ODBC.
</p>
<ul>
<li>B means BEST_ROWID, otherwise ROWVER.</li>
<li>S means Session scope, T means transaction scope, otherwise current row.</li>
<li>N means No Nulls, otherwise Nullable.</li>
</ul>
<br />
<a name="isqlmacrodef" />
<h3>7.2.3. ISQL Macro Substitution</h3>
<p>
Before executing commands ISQL expands macros in statement text.
The following macros and macro related commands are available:
</p>
<ul>
<li>$LOADEXPR - the current script startup command</li>
<li>$LINENO - the current line number</li>
<li>$YYYYMMDD - the current date in the same format</li>
<li>$HHMMSS - the current time in the same format</li>
</ul>
<div>
<pre class="programlisting">
$IF <cond> <THEN_RESULT> [<ELSE_RESULT>]
</pre>
</div>
<p>
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>.
</p>
<div>
<pre class="programlisting">
$(+|-) <arg1> <arg2>
</pre>
</div>
<p>
result is the addition or substitution of the arg1 and arg2
</p>
<div>
<pre class="programlisting">
$LAST[<n>]
</pre>
</div>
<p>
an array of the last resultset row
</p>
<div>
<pre class="programlisting">
$ARGV[<n>]
</pre>
</div>
<p>
an array of the command line arguments
</p>
<div>
<pre class="programlisting">
$U[<name>]
</pre>
</div>
<p>
prints the user associative array U value for the name <name>
</p>
<div>
<pre class="programlisting">
$<ISQL_Variable>
</pre>
</div>
<p>
returns the value of that variable
</p>
<div>
<pre class="programlisting">
$LT <v1> <v2>
</pre>
</div>
<p>
returns 1 if the <v1> is lower that <v2>, empty otherwise
</p>
<div>
<pre class="programlisting">
$GT <v1> <v2>
</pre>
</div>
<p>
returns 1 if the <v1> is greater than <v2>, empty otherwise
</p>
<div>
<pre class="programlisting">
$GTE <v1> <v2>
</pre>
</div>
<p>
returns 1 if the <v1> is greater or equal than <v2>, empty otherwise
</p>
<div>
<pre class="programlisting">
$LTE <v1> <v2>
</pre>
</div>
<p>
returns 1 if the <v1> is lower or equal than <v2>, empty otherwise
</p>
<div>
<pre class="programlisting">
$EQU <v1> <v2>
</pre>
</div>
<p>
returns 1 if the <v1> is equal to <v2>, empty otherwise
</p>
<div>
<pre class="programlisting">
$NEQ <v1> <v2>
</pre>
</div>
<p>
returns 1 if the <v1> is not equal to <v2>, empty otherwise
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Examples:</div>
<div>
<pre class="programlisting">
ECHO $IF $EQU 1 2 "True" "False";
</pre>
</div>
<p>prints "False" to the standard output</p>
<div>
<pre class="programlisting">
EXIT $IF $NEQ 1 2 NOT;
</pre>
</div>
<p>never exits</p>
<div>
<pre class="programlisting">
ECHO $IF $EQU $ARGV[1] 1 "One" $IF $EQU $ARGV[1] "Two" "Unknown;
</pre>
</div>
<p>prints the "one" on 1 as arg 1, "two" on 2 as arg 1 otherwise "Unknown"</p>
</div>
<br />
<a name="isqlvariables" />
<h3>7.2.4. ISQL Variables</h3>
<p>
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.
</p>
<p>
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.
</p>
<a name="" />
<div class="example">
<div class="exampletitle">Example:</div>
<div>
<pre class="programlisting">
SQL> SET AUTOCOMMIT ON;
SQL> echo $autocommit;
ON
SQL> set U{var} foo;
SQL> echo $u{VAR};
foo
</pre>
</div>
<p>
Note that the $u{<var>} notation is a general purpose text substitution macro
without arguments. The expansion takes place regardless of SQL syntactic context.
</p>
</div>
<a name="" />
<div class="example">
<div class="exampletitle">Example:</div>
<div>
<pre class="programlisting">
set u{table} SYS_KEYS;
select count (*) from $u{table};
</pre>
</div>
</div>
<a name="varref" />
<h4>7.2.4.1. Variable Reference</h4>
<div>
<pre class="programlisting">
RETVAL
</pre>
</div>
<p>
Function called return value
</p>
<div>
<pre class="programlisting">
RETVALLEN
</pre>
</div>
<p>
Function called return value length
</p>
<div>
<pre class="programlisting">
ROWCNT
</pre>
</div>
<p>
Number of rows in the last result set. If the statement was an insert, update or delete this is the number of rows affected.
</p>
<div>
<pre class="programlisting">
COLCNT
</pre>
</div>
<p>
Number of columns in the last resultset
</p>
<div>
<pre class="programlisting">
ARGC
</pre>
</div>
<p>
Number of ISQL command line arguments
</p>
<div>
<pre class="programlisting">
I
</pre>
</div>
<p>
Number of script arguments (after -i)
</p>
<div>
<pre class="programlisting">
LIF
</pre>
</div>
<p>
Result of the last $IF
</p>
<div>
<pre class="programlisting">
INPUTLINE
</pre>
</div>
<p>
Current executed line
</p>
<div>
<pre class="programlisting">
STATE
</pre>
</div>
<p>
SQL State (defaults to OK)
</p>
<div>
<pre class="programlisting">
SQLSTATE
</pre>
</div>
<p>
Same as state.
</p>
<div>
<pre class="programlisting">
MESSAGE
</pre>
</div>
<p>
the SQL error message from the last operation.
</p>
<div>
<pre class="programlisting">
DRIVER
</pre>
</div>
<p>
The Driver name
</p>
<div>
<pre class="programlisting">
LWE
</pre>
</div>
<p>
Last ECHO output
</p>
<div>
<pre class="programlisting">
DSN
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
UID
</pre>
</div>
<p>
The User ID used/to be used in connecting to the server
</p>
<div>
<pre class="programlisting">
PWD
</pre>
</div>
<p>
The Password used/to be used in connecting to the server
</p>
<div>
<pre class="programlisting">
ERRORS
</pre>
</div>
<p>
The current stream for errors (STDIN, STDOUT, STDERR or a file name)
</p>
<div>
<pre class="programlisting">
PROMPT
</pre>
</div>
<p>
Prints the PROMPT or not
</p>
<div>
<pre class="programlisting">
EMPTY
</pre>
</div>
<p>
The current empty string value
</p>
<div>
<pre class="programlisting">
VERSION
</pre>
</div>
<p>
ISQL version
</p>
<div>
<pre class="programlisting">
BLOBS
</pre>
</div>
<p>
When ON binds prints the BLOB resultset values when printing the resultset. Otherwise prints 'BLOB x chars'
</p>
<div>
<pre class="programlisting">
ECHO
</pre>
</div>
<p>
When ON prints the commands to the standard output before executing
</p>
<div>
<pre class="programlisting">
BANNER
</pre>
</div>
<p>
When ON prints the column names & types banner when printing the resultset
</p>
<div>
<pre class="programlisting">
TYPES
</pre>
</div>
<p>
When ON prints the Type information in the resultset's banner
</p>
<div>
<pre class="programlisting">
VERBOSE
</pre>
</div>
<p>
When ON Prints the Timing row after the resultset output
</p>
<div>
<pre class="programlisting">
TIMESTOSTRINGS
</pre>
</div>
<p>
When ON binds the SQL_DATE, SQL_TIME & SQL_TIMESTAMP columns to strings
</p>
<div>
<pre class="programlisting">
TRAILING_NEWLINES
</pre>
</div>
<p>
When ON prints new line after the row's end
</p>
<div>
<pre class="programlisting">
DEADLOCK_RETRIES
</pre>
</div>
<p>
How many times to retry the statement if deadlock occurred
</p>
<div>
<pre class="programlisting">
MACRO_SUBSTITUTION
</pre>
</div>
<p>
When ON ISQL does understand & process $ macros
</p>
<div>
<pre class="programlisting">
IGNORE_PARAMS
</pre>
</div>
<p>
When ON passes the ? through to the server instead of returning an error for unbound ?
</p>
<div>
<pre class="programlisting">
BIND_RETURN_VALUES
</pre>
</div>
<p>
When ON binds a buffer to SQL_RETURN_VALUE
</p>
<div>
<pre class="programlisting">
AUTOCOMMIT
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
ACCESSMODE
</pre>
</div>
<p>
Sets the ODBC access mode (RW, RO)
</p>
<div>
<pre class="programlisting">
TIMEOUT
</pre>
</div>
<p>
ODBC Query timeout
</p>
<div>
<pre class="programlisting">
MAXROWS
</pre>
</div>
<p>
Print only the first so many rows. If 0 - unlimited.
</p>
<div>
<pre class="programlisting">
CURRENT_QUALIFIER
</pre>
</div>
<p>
Prints the current ODBC SQLGetInfo client Qualifier
</p>
<div>
<pre class="programlisting">
INFO_DATABASE_NAME
</pre>
</div>
<p>
Prints the current ODBC SQLGetInfo Database name
</p>
<div>
<pre class="programlisting">
INFO_USER_NAME
</pre>
</div>
<p>
Prints the current ODBC SQLGetInfo user name
</p>
<div>
<pre class="programlisting">
INFO_GETDATA_EXTENSTIONS
</pre>
</div>
<p>
Prints the current ODBC SQLGetInfo SQLGetData extensions flag
</p>
<div>
<pre class="programlisting">
COMMAND_TEXT_ON_ERROR
</pre>
</div>
<p>
When OFF ISQL does not print the text of the command in the error messages
</p>
<br />
<br />
<a name="isqlgentesttool" />
<h3>7.2.5. Using isql as a General Purpose Test Driver</h3>
<p>
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.
</p>
<p>
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.
</p>
<p>
Consider the script:
</p>
<div>
<pre class="programlisting">
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";
</pre>
</div>
<p>
Now suppose the above text were in the file test.sql.
The command
</p>
<div>
<pre class="programlisting">
isql 1111 errors=stdout <test.sql >test.out
</pre>
</div>
<p>
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:
</p>
<div>
<pre class="programlisting">
PASSED Inserted 40000 rows
PASSED Inserted 10000 distinct ctr values
</pre>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="virtclientref.html" title="ADO.Net Data Provider">Previous</a>
<br />ADO.Net Data Provider</td>
<td align="center" width="34%">
<a href="accessinterfaces.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="odbcimplementation.html" title="Virtuoso Driver for ODBC">Next</a>
<br />Virtuoso Driver for ODBC</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
789
