9. SQL Query Tool

9.1 Overview

SQL Query Tool is a command line, client program, which accepts SQL statements and processes them either interactively or in batch mode (that is, you can execute an SQL script with parameters, if any). It can also be used for producing dynamic Web content.

Reference: see Appendix B about the SQL statements supported by Ovrimos.

9.2 Operating SQL Query Tool

To invoke SQL Query Tool you should start a database on a server and then enter the following line:

<Ovrimos root dir>/bin/sqlapp4 <host-computer>|localhost <database name>|<SQL port number> <user> <password> <[script]>

or, alternatively

<Ovrimos root dir>/bin/sqlapp4
[-server <server>|localhost]
[-db <db>|<portnum>]
[-user <userid>]
[-passwd <passwd>]
[-script <input>]
[-ciphersuites <ciphersuites>]
[-cert <certfile>]
[-key <keyfile>]
[-trust <casfile>]

<host-computer> : either the name of the computer with the installed product or its IP address. It may also be localhost if Ovrimos is running on the user's computer.
<SQL port number> : as an alternative to the database name you can enter the SQL port number corresponding to the database.
<[script]>: this is optional. In case all the SQL statements you want to execute are included in a script, you enter its path and name.
<ciphersuites> : the security level. May be something of the form HIGH:MEDIUM:LOW:EXP
<cert> : The file where the certificate resides. For example cert.pem
<key> : The file where the encryption key resides.
<trust>: The file where the trusted CAs resides. For example cas.pem.

At first time installation, the only available user is admin with User Name  admin and password  pegasus  .

It is not obligatory to have SQL Query Tool under <Ovrimos root dir>/bin directory. Since it doesn't need any client-side installation, it can be copied into any directory and run from any host computer. When running SQL Query Tool, a file named session.log is generated automatically in the current directory.

SQLAPP4
Session opened 1999-11-04 12:54:17 local time for admin
on server10
Type ':help;' for help on directives
SQL(1)

Output on user's screen after invoking SQL Query Tool. It is assumed that user admin has logged in 'server10'

To exit SQL Query Tool you should type quit;.

To control SQL Query Tool's operation, a list of available directives is provided by typing :help; as shown below.

:<directive>?; shows the current value of each directive. 
:info on; provides detailed information about how Ovrimos executes each SQL statement. If the directive info is on, an execution plan is displayed for every SQL statement you execute. To turn off this option, type :info off;.

The directive :bloblength <length>; instructs SQL Query Tool to display the contents of all LONG columns (LONG VARBINARY and LONG VARCHAR) as if they were ordinary CHAR or BINARY data of the length specified. The default length is 0 (zero) which means that no data are displayed.       

SQL(1) :help;

Directives are
:cursor <name>;
:navigation on|off; (permits scrolling through the 
cursor or normal tabular output)
:pause; (waits for keypress)
:pause on|off; (waits between rows in tabular output)
:bloblength <length>; (length to retrieve for 
BLOBS - default=0)
:info on|off; (shows execution plan)
:cgi [bare|table|list|select]; (output format 
in cgi scripts)
 and also ":<directive>?;" to display current value
 @somefile; reads from file 'somefile'

Available directives for controlling SQL Query Tool's operation.

   
9.3 Executing SQL statements interactively

Interactive execution of SQL statements means that SQL statements are typed one after another and you are able to terminate successfully these SQL operations at any point, by typing commit;. In case you don't want to make these changes permanent to the database, you can cancel them by typing rollback;. To terminate an SQL statement type a semicolon (;) and press Enter.

SQL(1) select CNAME, CITY from customers where CID<=2006;

Column 0: 'CNAME' varchar(30) nullable
Column 1: 'CITY' varchar(20) nullable
CNAME                        |CITY
-----------------------------+-------------
'Hanson'                     |'Helsinki'
'Gerard'                     |'Athens'
'Longman'                    |'Rome'
'Gable'                      |'London'
'Conrad'                     |'Helsinki'

SQL(1)

Example of interactive operation: a SELECT statement has been executed and the results are displayed on the screen.

When you exit SQL Query Tool, the last uncommitted transaction will be committed.
The directive !<new statement> opens a new statement handle.

  
9.4 Executing SQL scripts

To execute an SQL script, type the symbol @ and then the path of the specified script. A semicolon should also be added at the end of the line. For example, if a script called test.sql is to be executed, the command line will be:
@test.sql;

The directive :pause; suspends processing of an SQL script. To continue the script's execution, press Enter.

When you exit SQL Query Tool, the last uncommitted transaction will be committed.

Below is an example of sql script execution.

SQL(1) @e: $\backslash$ovrimos $\backslash$test.sql;

select CID, CNAME, CITY from customers;
Column 0: 'CID' integer nullable
Column 1: 'CNAME' varchar(30) nullable
Column 2: 'CITY' varchar(20) nullable
CID      |  CNAME                         | CITY
---------+--------------------------------+---------
2001     |'Hanson'                        |'Helsinki'
2002     |'Gerard'                        |'Athens'
2003     |'Longman'                       |'Rome'
2004     |'Gable'                         |'London'
2006     |'Conrad'                        |'Helsinki'
2007     |'Carlton'                       |'Paris'
2008     |'Peterson'                      |'New York'
insert into customers (CID, CNAME, CITY, RATING, SID)
values (2009, 'Holmes', 'Berlin', 300, 1005);
1 row inserted
update customers set CITY='Zurich' where CID=2001;
1 row updated
select CID, CNAME, CITY from customers where CITY='Berlin';
Column 0: 'CID' integer nullable
Column 1: 'CNAME' varchar(30) nullable
Column 2: 'CITY' varchar(20) nullable
CID       |CNAME                        |CITY
----------+-----------------------------+-----------
2009      |'Holmes'                     |'Berlin'

SQL(1)

Output on user's screen after the SQL script test.sql has been executed

   
9.5 Cursors and Navigation

Users can control the appearance of a SELECT statement's result set, through the directives :cursor;, :navigation; and :pause on|off;.

If you have specified :navigation off;  (default status), all the retrieved rows are displayed.
If you have specified :navigation off; and :pause on;, then, one row at a time is displayed. By pressing Enter, you can fetch the next retrieved row.

When the directive navigation on; has been specified, the user has the following options: fetch the first row of the result set (type f), move to the next retrieved row (press Enter or type n), move to the previous row (type p), fetch the last row of the result set (type l). In any of these options, only one row is displayed at a time.
To name a cursor, use the directive :cursor<name>;. To quit the cursor, type q.

  
9.6 HTML output from SQL Query Tool

The output of an SQL script (executed by SQL Query Tool) can be supplied by the HTTP server which is active on the specific database. Ovrimos enables users to produce HTML output as explained below.

The script's results which appear on the browser's window can be formatted by using the :cgi directive with one of the optional parameters bare | table | list | select. These optional parameters determine the HTML formatting to be used. Default option is table.

The following sample SQL scripts demonstrate the use of the :cgi; directive. The output of each script on user's browser is shown in the corresponding figure.

Alternatively, you can view their results yourself (e.g. the output of planet1.sql):

1.

Start up the server of testbase  and connect to it.

2.

Populate testbase, following the instructions provided in section 2.

3.

Copy the planet*.sql from the directory demos to <Ovrimos root dir>/testbase/docs

4.

Direct your favorite browser to the first script's URL:
http://<host's IP address>|localhost:8082/planet1.sql

Reference: Chapter 8 provides additional information about the HTTP server and the dynamically produced Web content.

planet1.sql - Displaying results in table, bare (plain text) or list format.

--!<h1>Planets and Satellites</h1>
--!<h3>Table of the planets</h3>
:cgi table;
select name, serial, descr from planets
order by serial;
--!<p>

:cgi bare;
select descr from planets where name='Saturn';
--! is the 
select serial from planets where name='Saturn';
--!th planet from the sun.

--!<p>Here are some of the planet's satellites:
--!<UL>
:cgi list;
select name from satellites where planet='Saturn';
--!</UL>

Figure 9.1 shows the results of executing the above script.

planet1.sql - Comments on the script's listing

• SQL statements are not prefixed by any symbol and terminate with a semicolon ;
• HTML elements are prefixed by the symbol --!, providing an easy way to insert verbatim HTML in the SQL script.
• Bulleted or Numbered list of items is created using the :cgi list directive with the corresponding HTML tag (i.e. <UL> or <OL>). It's the user's responsibility to place these HTML tags, as required.

  

Figure 9.1: Output of planet1.sql on a browser's window

Similarly, you may execute a script that displays the results in menu form.

planet2.sql - Displaying results as a list of choices

--!<h1>Planets of the solar system</h1>
--!<p><h3>Select the planet you want to find out more 
--!information about</h3>

-- This demonstrates output in menu form.

--!<form method=get action="planet3.sql">
:cgi select;

--!<select name="planet">
select name from planets order by 1;
--!</select>

--!<input type=submit value="Fetch more data">
--!</form>

Figure 9.2 shows the results of executing the above script.

  

Figure 9.2: HTML page with selection menu

When selecting the menu item (planet) for which to fetch more data, one more script, planet3.sql will be executed.

planet3.sql - Fetched data on a selected planet, after planet2.sql is executed

--! <h3>Data on {planet}</h3>
-- This demonstrates bare output. 

:cgi bare;

--! The planet {planet}, &quot;
select descr from planets where name='{planet}';
--! &quot;, the #
select serial from planets where name='{planet}';
--! planet from the sun orbits
select distance_mmiles from planets where name='{planet}';
--! million miles away.<hr>
select image(big_pic) 
from planets where name='{planet}';

Figure 9.3 shows the results of executing the above script.

planet2.sql and planet3.sql - Comments on scripts listing

• SQL comments begin by the symbol -- and are therefore ignored.
• You can specify an SQL script as the action attribute of a <form> HTML tag.
• You can use the :cgi select directive with the <select> HTML tag to display results as a scrolling list of choices.
• The IMAGE Ovrimos function is used with a SELECT statement instead of the <img> HTML tag to display the BLOB.

  

Figure 9.3: Plain text and Image output

9.6.1 How to produce HTML output from SQL Query Tool

• Through SQL Terminal: scripts uploaded through SQL Terminal (see section 5.2.2) are executed by SQL Query Tool and their results appear on your browser's window in a table format. However, you can control the appearance of the output by including any HTML element or the :cgi directive with any of its optional parameters in your script. This way, you take advantage of HTML's strength for describing Web content which will be rendered in its final form by your browser.

• Placing your script in the Ovrimos URL-space: You can view the results of an SQL script on your browser's window if you place this file in the $HTTPROOTDIR or $HTTPSHAREDDIR (refer to Chapter A about Ovrimos parameters) where Ovrimos Web resources   are placed.