5. SQL Terminal

5.1 Overview

SQL Terminal allows users to perform data definition and data manipulation operations (direct SQL). Additionally, one can use it for uploading SQL scripts and for Internet queries in MS Excel. To operate SQL Terminal, a minimum knowledge of SQL is required (see Appendix B).

  
5.2 Operating SQL Terminal

SQL Terminal has an HTML-based interface, so you can access it through any Web browser. Points from where to reach SQL Terminal are:

• the link SQL Terminal of the database index screen (see section 4.7)
• the link SQL Terminal from Roadmap or Administrator Console.

Refer to section 8.4 about Dynamic Internal Resources  for more technical information.

Note: For better results it is recommended that you use the Ovrimos navigation options (back, forth, etc.) instead of your browser's.

User authentication  is required to operate SQL Terminal. User Name  and Password  for first time users are admin and pegasus   respectively. User admin can change this password through the UPDATE USER statement (see Appendix B) either on SQL Terminal or SQL Query Tool (see Chapter 9).

The appearance of results on your browser's window can be controlled by Rowset size . Rowset size defines the number of rows retrieved and displayed as a result of the execution of an SQL transaction or of an SQL script's uploading. By default the Rowset size is 20, but users can modify it. If the Fetch all box is checked, Rowset size is ignored and all rows are retrieved and displayed.

  

Figure 5.1: SQL Terminal

In case SQL Terminal has remained idle for a period of time, which is specified by $HTTPSESSIONTIMEOUT, (see Appendix A.2.2), the user is disconnected automatically from the database.

WARNING! DO NOT ATTEMPT TO UPDATE SYSTEM TABLES.

The following sections explain the Internet queries feature and how you can operate SQL Terminal to :

• commit or rollback SQL transactions
• upload SQL scripts 

    
5.2.1 SQL transactions

A transaction is a logical unit of work; i.e. it is a sequence of data manipulation operations. A transaction can be either executed interactively or be uploaded as an SQL script.

In case of interactive execution, you type each SQL statement on SQL Terminal's input area and then you click the Execute button.

Example:
Interactive execution of SQL statements:
- Type the SQL statement in the input area.

            SELECT S.SNO, S.SNAME
            FROM S
            WHERE SP.SNO='S1'

- Click the Execute button.
There is no need to add a semicolon (;) at the end of the statement.

Each transaction can either terminate successfully by clicking the Commit button or be aborted by clicking the Rollback button. Termination with Commit causes all changes made by the transaction to become permanent to the database. On the contrary, termination with Rollback cancels all changes made by the transaction.

Isolation Level  and Access Mode  can be specified through the drop-down lists shown in figure 5.1. Alternatively, the SET TRANSACTION statement can be used to define the above characteristics of the next SQL transaction to be initiated. This statement can be executed only when no SQL transaction is in progress.

In case SQL transactions have not been committed within $HTTPSESSIONTIMEOUT and SQL Terminal has been idle during this time, (see section A.2.2), the user is disconnected automatically from the database, as has already been explained above, and the transactions are rolled back automatically. However, the statements CREATE/DROP INDEX, CREATE/DROP USER, CREATE/DROP DOMAIN, CREATE/DROP VIEW, DROP TABLE, ALTER TABLE, GRANT and REVOKE, which are not part of a transaction, are not rolled back. They are committed automatically.

In case of SQL statements entered interactively, it is suggested that you commit or rollback them, after you have completed the transaction. This applies even in the case of SELECT statement.

Figure 5.2 is an example of the output produced when executing an SQL statement. Notice that the executed statement appears on the window area.

  

Figure 5.2: Output displayed on SQL Terminal

Statements entered interactively are added to History. They may be retrieved from History drop-down list for re-run or editing.

    
5.2.2 Uploading SQL Scripts

Uploaded SQL scripts run as a separate transaction and are executed by SQL Query Tool. The script's results can be formatted as explained in SQL Query Tool chapter (see section 9.6)

• Click Browse to select the file with the SQL script
• Click the Upload button to upload the file
• The script's results appear on your browser's window.

Statements belonging to SQL scripts are not recorded in History.

  
5.2.3 Internet queries in MS Excel

Internet Queries is a feature of MS Excel supported natively by Ovrimos. Internet Queries in MS Excel are contained in files ending in .iqy. One can construct Internet Queries (iqy's) that request data from Ovrimos using plain SQL. Excel places the data in tabular form in the sheet.

The data will be requested from SQL Terminal, using a standard HTTP GET query. The query will have to be authenticated, so Excel will present you with a dialog box to enter a valid username and password for that database. On subsequent queries to the same database, Excel remembers the password and does not present the dialog box.

The form data sent are:

• sql : The SQL statement. Excel will url-encode it before sending it, so it can contain spaces, but any ``equals" sign must be encoded as %3D.
• sz : The rowset size. No more than ``sz" rows will be returned to Excel.
• all : Whether the rowset size ``sz" will be honored, or if all data are requested. In the latter case, one must include ``all=on" in his iqy. The value of ``sz" is then ignored.
• close : Essential for the iqy. It instructs SQL Terminal to close the connection used to retrieve the data and discard the HTTP session. Be sure to include ``close=on" in your iqy.

Example:
Contents of a sample .iqy file which contains an Internet Query
http://194.219.34.131:8084/sqlterminal?sql=select table_name from sys.tables&sz=&all=on&close=on

      
5.3 Disconnecting and Reconnecting to the database

When you click Disconnect, you are disconnected from the database, but the SQL Terminal window remains open. If Disconnect is dimmed, this means that you are not connected to a database.
You can reconnect to the database (without authentication) by selecting one of the recently typed SQL statements from the History list and executing it. In case you haven't committed your SQL statements when you click Disconnect, a rollback will take place automatically (see also section 5.2.1).