SQLAPP4 is an alternative client program, using a command line screen (i.e. DOS or Unix console) instead of a browser.
The SQL client program philosophy is based on two concepts, the connection and the statement. The sqlapp4 executable program allows one connection, within which a number of statements are executed. Statements may be nested if necessary.

The client program can be used in two modes, either as a simple SQL client or as a CGI client. It may also be used for BLOB insertion.

SQL client

The SQL client program, with simple SQL terminal interface, is invoked from the command line with the following parameters:

(Win32 call)
sqlapp4 <server>|localhost <database>|#<portnum> <userid> <passwd> [<script>]
(Unix call)
sqlapp4 <server>|localhost <portnum> <userid> <passwd> [<script>]

where:
<server> is the name of the computer where Ovrimos is running. "localhost" can be used when the database is running on the same machine.
<database> is the database name, as created by the Database Manager. Using the database name is meaningful only in environments where the Registry is visible (i.e. from an NT computer to an NT database server). Otherwise, the database name may not be visible, therefore it may not be known to the server.
<portnum> is the database SQLPORT. Instead of a database name or when the client is not on the same LAN with the server (e.g. across the Internet), a port number can be used, prefixed by "#".
Unix environment connections always require port instead of database name.
Note! In Unix versions, no # is used before the port number.
<userid> is the user's id. When the database is created there is only one user, admin.
<passwd> is the user's password. User's admin initial password is pegasus.

The SQL script file is optional. It is a file where SQL commands are typed one after another. If a script file is given in the command line, then the SQL client runs as a batch process, i.e. it is started, the script is run and the process terminates with commit as a default, regardless of the number of errors that may occur.

If no script file is typed in the command line, then sqlapp4 is run interactively. In this case, a window, like the one in picture client-1, appears.

Note! The frames below show the possible output on a non-graphics terminal. For example, if the host is venus and the user has logged in as admin, the result may be as follows:
 

SQLAPP4  Session opened 1998-10-10 20:04:42 local time for admin on venus  Type ':help;' for help on directives  SQL(1)

The SQL client program opens one connection to the database. The prompt SQL (1) indicates that the first (outermost) statement will be executed.
Script files may also be invoked from within the SQL terminal application. For example, if a script called test1.sql is to be executed, the command line will be:
SQL(1) @test1.sql;

In this case the script is executed but, of course, neither commit nor termination of the application takes place.

Normal use of the SQL command line allows execution of the most common SQL statements, i.e. creation of tables and indices, select commands that fetch a number of rows, update, insert and delete, as well as commit and rollback. All SQL commands supported by Ovrimos may be executed by the SQL command line.

select name, serial from planets where serial <= 4 order by serial;

In the default execution mode, the results appear in a table form. Picture client-2 shows a sample SQL terminal screen after execution of the above select statement.
 

SQL(1) select name, descr, serial from planets where serial <= 4;  Column 0: 'NAME' varchar(15) not nullable  Column 1: 'DESCR' varchar (50) nullable  Column 2: 'SERIAL' integer nullable  NAME               |DESCR                             | SERIAL  -----------------------------------------------------------------------  'Mercury'          |'The hot planet'                        |1   'Venus'            |'The greenhouse planet'                 |2  'Earth'            |'Mostly harmless'                       |3  'Mars'             |'The red planet'                        |4   SQL(1)

There is a number of directives, provided as simple options, which give users more control over their SQL terminal. They also offer means for a nesting of statements. The directives are typed at the SQL prompt, prefixed by the : symbol to differentiate them from other SQL statements.

By typing the directive : help; (as shown in picture client-3) users may have a list of the available directives. 
 

SQL(1):help;  Directives are  :cursor <name>;  :isolation <number 1-4>;  :navigation on|off;  :pause [on|off];  :info on|off;  :cgi [bar|table|list|select]; (in cgi scripts)   and also :<directive>?;  @somefile; reads from file 'somefile'  SQL(1)

Use of the directives:

:cursor <name>; It allows the user to name a cursor. This may be used later for updating the current row of a cursor.

:isolation number<1-4>; It modifies the isolation level for concurrent transactions.

1. Read uncommitted
2. Read committed
3. Repeatable read
4. Serializable

By default the isolation level is 1 (Read uncommitted).

:navigation on|off; The program allows navigation on select cursors. This means that when a select statement is executed and navigation is on, the results do not appear all together in a table form, but one by one. The user is able to 'navigate' through the cursor.
By default, navigation is off. See Cursors and navigation for a description of the cursor environment and an example of use of this feature.

:pause [on|off]; The function of pause is dual. When pause is on and navigation is off, it stops after each row when cursors are run. :pause; may also be typed between statements to allow a synchronization between scripts.

:info on|off; By default the info is off when the SQL client is started. When on, it provides the user information about the execution plan of each SQL statement.

:cgi [bare|table|list|select]; (in cgi scripts) An option for the output form of the returned results.

• Bare is plain text, where columns and lines are merely separated by comma (,).
• Table is the normal table form.
• List is in the form of a list.
• Select is in the form of a menu.

Finally, :<Directive>?; allows the user to examine the status of each option and @somefile; reads from file 'somefile' indicates the way to use a script.

Cursors and navigation

Cursors are a mechanism to traverse data obtained by a select statement. In the Ovrimos environment, navigation of cursors is allowed for better control of the result rows. When navigation is on and a select statement is executed, the results do not appear in the classic table form. Instead, the user is inside a cursor and the possible actions at this point are the following:

1. f(irst)  Fetches the first row of the selection.
2. default (Enter) Fetches the next row.
3. n(ext)  Fetches the next row of the selection.
4. l(ast) Fetches the last row of the selection.
5. p(revious) Fetches the previous row of the selection.
6. !(new stmt) Starts a new SQL statement, without exiting from the current.
7. q(uit) Quits the cursor.

select ... for update of;

with navigation on, the rows appear one after the other. A new statement may be opened with the ! and the command:

update where current of <cursorname>;

updates the current position of the cursor.

Picture client-4 shows an example of nested transactions. First, navigation is set of on. Then the cursor for the following select is named alpha. When the select for update statement is made, the rows start coming one after another. Users may call each one and when the update is required, then they use the ! to nest a new SQL statement inside the running one. Thus, the SQL(2) prompt appears, showing that it is another statement, nested within the first. Update is now made on the current of alpha.
 
 

SQL(1):cursor alpha;   -> OK, cursor name set to alpha   SQL(1) select name from planets where serial > 2 for update of;  Column 0: 'NAME' varchar(15) nullable  Cursor navigation mode:  Legal cmds:f(irst),default->n(ext),l(ast),p(revious), !(new stmt)  *   0 NAME:'Earth'  *!  SQL(2) update planets set descr = 'The beautiful planet' where current of alpha;  1 row updated  SQL(2)

Database administration commands

A few database administration commands, which follow the basic SQL syntax but do not belong to the SQL Standard are described below.

• Finding out what tables, columns and data types are stored in the database:

select table_name, column_name, type_name
from sys.tables a, sys.columns b, sys.data_types c
where a.table_id = b.table_id and b.data_type = c.data_type;
• In the beginning there is only one user, admin, with a known password, pegasus.

There is also the user public, without password. This user may not connect to the database yet.
The administrator may change the password, using the following command:
UPDATE USER admin WITH 'new-password';
e.g. update user admin with 'nebula';
As always, the password is case sensitive.
• Other users may be created using the following command:

CREATE USER name FOR 'user name' WITH 'user password';
e.g. create user mary for 'Mary' with 'xyz123';
• Users may be removed, using the following command:

DROP USER user-name;
e.g. drop user mary;

Warning! DO NOT attempt to update system tables from SQL. System tables are visible, but they are not to be updated from SQL. Users should not alter the system table data. In case of updating a user (name, privileges, password etc.) administrators should use the update user command, which is specifically designed for this purpose. See Appendix SQL: update user.

The tables of the database belong to the user who has originally created them. Privileges may be granted and revoked through the usual SQL statements. If a user wishes to access another user's table, then the owner's name is required as a qualification, e.g. if table books is created by user mary, then other users must use a full name, e.g. mary.books, to access it.

The sqlapp4 executable program may be used from a machine other than the SQL server host computer. It may be copied and run from any path or any machine connected to the machine where the server is.

After running the sqlapp4 program, a file named session.log  is generated.
 

Bulk Updates

Sometimes, it is necessary to have a large number of updates, i.e. insertions, deletions, modifications. Since in the case of such updates, the user does not require any response from the database, it is possible to speed up the execution by declaring that a number of such updates follows. All updates are kept in an internal buffer and sent for massive execution at the end.
Bulk updates make program execution very fast when written in a script and then submitted to sqlapp4.
 

Note: The user must make sure that no more than 64K of data is given for bulk update, since this is an internal limit of the application.
All bulk updates are atomic, i.e. they are all committed or rolled back together.

The syntax for a bulk update is:

SQL (1) begin;
Bulk SQL >

....

Bulk SQL > end;
SQL (1)

Begin and end bracket the bulk update.
 

BLOB support

BLOBs (Binary Large Objects) are declared either as long varbinary or as long varchar whenever a table is created.
For example if the user wishes to create a table of artworks by code, title and picture, a sample table declaration could be as follows:

create table artwork (
code integer not null,
name varchar(40),
picture long varbinary
);

This command will create a table with three columns, the code, the name and a long varbinary (a BLOB) for the contents of the picture.

To insert into the table values for the BLOBs the idea is to use SQL parameters. (The ? symbol). Note that the sqlapp4 executable program does not accept parameters except for the case of declaring a BLOB.

For example, if the associated BLOB is a GIF file called circle.gif, then insertion of the row into the database would be done as follows:

SQL(1) insert into artwork values (1, 'A painting with circles', ?'image/gif');

Here ? is the parameter and 'image/gif' is the mime type image with file extension .gif. As soon as this command is typed the program responds with the prompt:

File to send for BLOB parameter 0?

Now, the user types the name of the .gif file, e.g.  c:\pictures\circle.gif.
The file name must be typed with the full or relative path name as the operating system requires for finding a file. No quotes are used for the file name.

The program responds with the message:

1 row inserted.

If the user types:

SQL(1) select * from artwork;

the program responds by printing the following information:

Column 0: 'CODE' integer not nullable
Column 1: 'NAME' varchar(40) nullable
Column 2: 'PICTURE' long varbinary nullable

Code    |  Name                           |   Picture
-----------------------------------------------------------------
1       |'A painting with pictures'       |(Not output)

The BLOB data cannot be printed on the SQL terminal screen.
 

BLOBS and the URI function

To allow handling of BLOBS, a special function called  uri (Uniform Resource Identifier) is written, which is capable of returning the file information type as address.

e.g., if the user types:

SQL(1) select code, uri(picture) from artwork;

the program responds with:

Column 0: 'CODE' integer not nullable
Column 2: 'PICTURE' long varbinary nullable

Code    |
-----------------------------------------------------------------
1       | http//:128.134.23.140:8182/blobxxxxxxxx

This last piece of information is the HTTP address of the BLOB. It may be used by the Web server to return the BLOB data.
 

SQLAPP4 as a CGI Client

All CGI files are executed by the HTTP Server. SQL script files are also executed by the HTTP Server if they are located under the database docs directory. The user may type the SQL script file name as a URL address in any browser and the script will be executed. The results will be displayed on the browser window.
Scripts may also ne executed as parameters to sqlapp4. To use the sqlapp4 program with CGI files, the CGI files must be in a CGI directory, under the database document root. The sqlapp4 program is called from the server. The first step is to have the executable sqlapp4 installed as a CGI program. The installation depends on the server, but often it is enough to copy the executable and the SQL script files in a directory called cgi or cgi-bin.
Depending on the operating system and the web server, the user may have to modify the file extension (from sqlapp4.exe to sqlapp4.cgi on Win32) or use no extension at all (on Unix).

How to run the SQL Client as a CGI client

To invoke the client program sqlapp4, it is necessary to make an HTML form. The ACTION calls the program without parameters. Since, however, parameters are required for connecting to the database, these are gathered in the HTML form. The method used is GET.

The following is an example of the HTML form data source and the resulting HTML form on screen. The parameters are visible in the form and a default value will appear on screen.

<HTML>
<HEAD>
 CGI Test
</HEAD>
<BODY>
<FORM METHOD=GET ACTION="/cgi-bin/sqlapp4.cgi">
<TABLE>
<TR><TD>Server</TD>
    <TD> <input name="SERVER" value="Some-host"></TD></TR>
<TR><TD>Database</TD>
   <TD> <input name="DB" value="Some-db"></TD></TR>
<TR><TD>User</TD>
   <TD><input type="text" name="userid" value="Some-user"></TD></TR>
<TR><TD>Password</TD>
   <TD><input type="password" name="passwd"></TD> </TR>
<TR><TD>File</TD>
   <TD><input type="text" name="file" value="example1.sql"></TD></TR>
</TABLE>
<input type="submit" value="Submit"><BR>
<input type="reset" value="Reset">
</FORM>
</BODY>
</HTML>

The above HTML form uses GET to collect the input for the parameters server, database, username, password and script file and passes them to the ACTION program sqlapp4.

Picture client-5 is the resulting HTML screen.

The user may change the default parameters.

The example1.sql file, on the other hand, contains SQL statements as well as comments that format the output in HTML form. Picture client-6 is the example1.sql script which is a very simple example of SQL, enriched with HTML statements.
 

--!<H1><B> Planets and Satellites </B></H1>  --!Table of the planets: <BR><BR>  :cgi table; --the default  select name, serial, descr from planets  order by serial;  --! <BR><BR><BR>  :cgi bare;  select descr from planets where name = 'Saturn';  --! is the   select serial from planets where name = 'Saturn';  --!th planet from the sun. <BR><BR>    --!Here are some of the planet's satellites:  --!<UL>  :cgi list;  select name from satellites where planet='Saturn';  --!</UL>

The SQL commands are the lines that are not prefixed by any symbol, such as:

select name, serial, descr from planets;

The lines prefixed by --! are ignored by SQL and used as HTML commands. The lines prefixed by -- are ignored by both SQL and HTTP server and are used as comments.

Finally, there is a :cgi directive besides SQL and HTML commands. The :cgi directive is used to format the results of the select statement.
The results of the select command may be presented on screen in four different forms:

1. In table form. It is the default form, producing HTML tables.

2. In list form. Every row appears as the line of a list. The program does not produce automatically a full list. The first and the last HTML line (in the beginning and end of list) must be typed into the script to open and close the list.

3. In menu form. The results appear as a menu.

4. In plain text form. The results are presented as plain text. Rows and columns appear one next to the other, with commas (,) separating one from another. This form is very useful if it is desirable to embody the results inside normal text.

Other SQL terminal directives may apply as well, but some of them, such as pause are meaningless for the cgi client, since it runs with scripts and there is no interaction on the command line.

The above SQL script consists of three queries. For demonstration reasons, the results of each select statement appear in different format. Picture client-7 shows the resulting HTML output.

HTML forms and SQL scripts may be as composite as necessary to cover each user's needs. Following is another example of HTML and SQL combination, where the initial HTML form has the database connection information hidden. This can be useful both for security reasons and for speed, in case a user always connects to the same database.

The use of a menu as HTML output is also exemplified.
 

<HTML>
<BODY>
<FORM METHOD=GET ACTION="/cgi-bin/sqlapp4.cgi">
<input type="hidden" name="server" value="some-host">
<input type="hidden" name="db" value="#3245">
<input type="hidden" name="userid" value="admin">
<input type="hidden" name="passwd" value="pegasus">
<BR>
<TABLE>
<TR><TD>File</TD>
<TD><input type="text" name="file" value="planets5.sql"</TD></TR>
</TABLE>
<input type="submit" value="Submit"> <BR>
<input type="reset" value="Reset">
</FORM>
</BODY>
</HTML>

Notice that this time all connection parameters except for the script file are hidden. There is also an example of using the #sqlport instead of the database name in connection.

The resulting HTML form is shown in picture client-9.

The respective planets5.sql script is one that exemplifies the use of menus, as well as the way of passing parameters from one form to another.
 
 

<HTML>   <BODY>   --!<H1> Planets of the solar system </H1><BR>   --!Select a planet to know more.<BR>   --This demonstrates output in menu form.   --!<FORM METHOD=GET ACTION="/cgi-bin/sqlapp4.cgi">   --!<input type=hidden name="server" value="{server}">   --!<input type=hidden name="db" value="{db}">   --!<input type=hidden name="userid" value="{userid}">   --!<input type=hidden name="passwd" value="{passwd}">   --!<input type=hidden name="file" value="planets6.sql">   :cgi select;   --!<select name="planet">   select name from planets order by 1;   --</select>   --!input type=submit value="Fetch more data">   --!</FORM>   </BODY>   </HTML>

Inside the SQL scripts it is possible to place a variable, enclosed in braces {}. This mechanism works for macro substitution. Variables may be anything, text, part of a statement, full statement etc. Of course, macro substitution is not recursive.

Picture client-11 is the resulting HTML page after execution of the planets5.sql script.

Now the selection from the menu and the pressing of the button Fetch more data will invoke the sqlapp4 program with the planets6.sql script which is as shown in picture client-12.
 

<HTML>   </BODY>   --!<H1> Data on {planet></H1>   -- This demostrates bare output. It allows for easy embedding   -- inside arbitrary text.   :cgi bare;   --! The planet {planet} could be characterized as &quot;   select descr from planets where name='{planet}';   --! &quot; She is the #   select serial from planets where name='{planet}';   --| planet from the sun orbiting   select distance from planets where name='{planet}';   --! million miles away.<hr>   select '<img src="' ||uri(small_pic)||'">'   from planets where name = '{planet}';   </BODY>   </HTML>

Note the way the planet name is passed from one form to the other as parameter.

One of the table columns is a BLOB, named small_pic. There is a special handling of BLOBs , which allows the output to appear on screen. See also the effects of the URI function.

After executing the planets6.sql script, the resulting screen is as follows.