SQL


SQL		Menus

SirSQL User Interface

SirSQL has a simple and easy to use graphical user interface. The main window contains a menu bar, a toolbar, output and input windows and a status bar.

The input window is the place to type SirSQL commands. You can load a file with SQL commands into this window using the menus or by dropping a file on the main window. You can save the contents of the input area into a text file. You can also store and retrieve sets of SQL commands as procedures (members). To execute the SQL commands from the input window, use the File/Run menu or the toolbar or the Ctrl-R key.

Results and messages are displayed in the output window. This is a relatively small window and is automatically rebuilt when necessary. This means that earlier output is discarded and lost unless it logged to the SirSQL.slg file. Logging depends on the LogOutput setting in the initialisation file.

The output window is as wide as necessary to display a complete line of the output. It is treated as a single page with headings at the beginning of a display of a table.

You can select part of the output to save, print or copy to the clipboard. You can use the appropriate INCLUDE/EXCLUDE commands to alter the output display of a table.

The status line displays the information about execution of the last command. If a command was unsuccessful, the status line indicates this and the command is retained in the input window to be edited and re-submitted.

SirSQL provides a set of menus and dialog boxes to perform many common operations. Some popular menu commands are also available on the toolbar.

The main menu consists of the following:

File

Open
Save As
Run
History
Clear Output
Save Output
Print Output
Exit

Database

Connect
Database
List
Members

Tabfile

Connect
Disconnect
List
Create

SQL

Interactive Select

Utilities

Verify
Export
Import
Backup
Restore

Options

Settings
Auto Display
Save workspace

Help

File Menu

Open loads a text file (presumably containing SQL commands) into the input window.

Save As saves the contents of the input area into a text file.

Run executes the SQL commands from the input area.

History displays any previously entered commands and allows you to re-run them or load them into the input area for editing.

Clear Output clears the output area.

Save Output saves the output area as a named text file.

Print Output sends the output area to the current system printer.

Exit finishes the SQL session and exits the system. This also saves the workspace.

Connect database

Use this dialog to connect a database. Specify the database by its name. A database name is up to 8 characters long and not case sensitive. If the database is not located in the current working directory, prefix the database name with the appropriate path.

Specify the main password and read/write security passwords. A database can be protected by write and read passwords, only a write password or not have passwords at all;. it cannot have a read password without a write password. If you specify the read password without a write password, the system uses the same password for both read and write security.

The most recently connected database is selected as the default database.

See the CONNECT DATABASE command.

List of connected databases

Use this dialog to perform basic operations on databases. You can use it to change the default database. Select a database from the list and use the OK button to close the dialog. If you close the dialog using the Cancel button, the previous default is not changed (if this database is still connected).

You can add databases to the list using the Connect button. Other buttons act on the database selected in the list. You can disconnect databases using the Disconnect button. Use the Structure button to get the information about the tables and fields defined in the database. Use the Members button to get the list of families and members (stored procedures) associated with the selected database.

Database structure

Use this dialog to inspect the database structure. Select a table to get the list of its fields. Use the Field Info and Table Info buttons to get properties of individual tables and fields.

Create tabfile

Use this dialog to create a new tabfile. Specify the tabfile name. A tabfile name is up to 32 characters long and not case sensitive.

Specify the filename explicitly to place the physical file into a directory other than your current working directory or to have the file name other than the name of the tabfile suffixed by the .tbf extension.

Specify the journal file name if you want to have a journal for the tabfile. Specify group and user passwords if you need to control access to this tabfile.

Only specify block size when necessary.

The tabfile is created, automatically connected and selected as the default.

See the CREATE TABFILE command.

Connect tabfile

Use this dialog to connect a tabfile. Specify the tabfile by name which is up to 32 characters long and not case sensitive unless enclosed in quotes. Specify the filename if the tabfile is not in the current working directory, or where the filename is not the tabfile name with a .tbf suffix. You can use the browse button to locate the file.

Specify group and user IDs and passwords if the tabfile is password protected.

The latest tabfile connected is set to be the default.

See CONNECT TABFILE command.

List of connected tabfiles

Use this dialog to perform basic operations on tabfiles.

You can change the default tabfile. Select a tabfile from the list and use the OK button to close the dialog. If you close the dialog using the Cancel button, the previous default will not be changed (if this tabfile is still connected).

You can add tabfiles to the list using the Create and Connect buttons. Other buttons act on the tabfile selected in the list. You can disconnect tabfiles using the Disconnect button. The Drop button disconnects and removes the tabfile.

Use the Structure button to get the information about the tables, fields and indexes defined in the tabfile. This also allows you to Create indexes for the table.

Tabfile structure

Use this dialog to inspect the tabfile structure. Select a table to get the list of its fields. Use the Field Info and Table Info buttons to get properties of individual tables and fields.

Use the Drop Table button to delete the table from the tabfile.

Use the Indexes button to inspect/add/remove the table's indexes.

Indexes

Select an index to get the list of its key fields. Use the Add index and Drop Index buttons to add/remove indexes.

Create index

To create a new index select the desired fields of the table and add them to the list of key fields. Define a name for the new index. Check the Unique checkbox if you create a unique index.

See the CREATE INDEX command.

Select

This dialog helps you to construct simple SELECT queries interactively. It does not provide all functionality of the SirSQL SELECT statement, but you can load the resulting statement into the input window and use it as the starting point for writing a more complex query.

The query under construction is displayed in the lower part of the dialog.

Start construction of the query by selecting some tables for the FROM clause on the From tab of the dialog. You can then activate other tabs. You can always return to the From tab later to modify the list of tables, but you need to have at least one table in this list to enable other tabs.

If you select the fields from a table and later delete this table from the table list, this results in an invalid query as references to fields are not deleted from the query.

Use these links for the information on the individual tabs:

See the SELECT command.

From

Use this tab to define the list of tables you want to run the query on. SirSQL allows you to run query on tables from multiple databases and tabfiles. You need to define aliases (use the Add As button) if you have the same table name on two databases/tabfiles or need to do a self-joint.

You need to specify at least one table to enable the other tabs. You can always return to this tab and modify the list of tables.

See the FROM clause of the SQL SELECT statement.

What

Use this tab to define the list of variables you're interested in. You can select the variables from any table in the FROM clause.

You can use Add As button and type any SQL expression (like aggregate function or arithmetic expression) if what you want to get is not just the value of the field.

Where

Use this tab to write the WHERE clause of your SELECT statement. Basically you just type it in, but you can select the variables from any table in the FROM clause and insert them into the text of your conditions.

See the WHERE clause of the SQL SELECT statement.

Order by

Use this tab to build the list of key fields used to sort the results of the SELECT statement. You can select the variables from any table in the FROM clause and use them as keys for ascending or descending sort.

See the ORDER BY clause of the SQL SELECT statement.

Group by

This tab is useful only when you do select on aggregate functions (you can do it using the Add As button on the What tab). Use the Group by tab to build the list of key fields used to group the results of the SELECT statement. Additionally you can write the conditions for the HAVING clause very much like for the WHERE clause. You can select the variables from any table in the FROM clause and insert them into the text of your conditions.

See the GROUP BY and HAVING clauses of the SQL SELECT statement.