SQLScope Help Index

Conceptual information

Creating and Running SQL Statements

Customizing SQLScope

Exiting SQLScope

Getting Help

Importing and Exporting Data

Logging in to Databases

Managing RI

Reference information

Command Menus

Operation Modes

Keyboard Shortcuts

Windows/Screen Areas


Creating and Running SQL Statements

If you have successfully completed a database login, you can create and run one or more of the statements shown in the SQL Text box.

Creating statements

You can create a statement in one of two ways:

•	Enter the statement in the SQL Text box.

•	Use the Lists Area to assist you in creating the statement.

The SQL Text box can contain one or more statements, with each statement separated by the specified Statement Separator. Each statement can also have comments.

Running statements

To run SQL statements, position the text cursor in the SQL Text box on any character of the statement you want to run. Then, click the Current button or choose the Current command on the Run menu.

When executing statements, SQLScope hides the Lists Area, enters Run mode, executes the statement, and displays the results as follows:

•	For a SELECT statement, SQLScope displays the result table. You can scroll forward or backward through this data. Also, you can adjust the width of any column by dragging a column border.

•	For any statement other than SELECT, SQLScope displays the status while the statement is executing.

If you have specified multiple statements in the SQL Text box, you can use the Run buttons or commands on the Run menu to execute additional statements. You can exit Run mode by clicking the Stop button or choosing the Stop command on the Run menu.


Customizing SQLScope

You can customize settings in SQLScope that control the following:

•	Default login values

•	Environment settings, such as default values in dialogs, screen layout, and options for saving the settings

To control the default login values for SQLScope, choose the Login Defaults command on the Settings menu.

To control the environment settings, choose the Environment command on the Settings menu.


Getting Help

To access the context-sensitive Help system at any time while running SQLScope, do one of the following:

Press F1	This key produces Help information about the highlighted menu command or current dialog.

Press SHIFT+F1	This key combination produces the Help cursor, a question mark with an arrow. Use the Help cursor to select a screen item. If a word or phrase appears in green with a dotted underscore, you can access the word's pop-up box by clicking on the word. Try it with the green words in this paragraph.

Use the Help Menu	This menu contains several commands for producing Help information.


Importing and Exporting Data

SQLScope can import data into a Scalable SQL database or export data from a Scalable SQL database. The data SQLScope imports or exports can be in UNF, SDF, or ASCII formats.

If you are importing data, you first create a SQL statement using an INSERT, UPDATE, or DELETE statement. Each column in the import file must correspond to a substitution value in the SQL statement.

If you are exporting data, first create a SQL statement using the SELECT command.

Related topics

Import command

Export command


Logging in to Databases

You must log in to a named or unnamed database before you can perform most tasks in SQLScope. To log in to a database, use the Login to Database dialog.

Note: If security is not enabled on your database, the username is not required. If no password is defined for your username, the password is not required.

You can be logged in to more than one database at a time. The Database list in the SQLScope main window contains the current databases. To choose a database, select it from the pull-down list.

To log out of a database, make that database the current one and choose the Logout command on the Database menu.


Managing Referential Integrity

If your database is on the local machine, is named, has referential constraints defined (whether or not RI is enabled), and you log into the database using its database name, you can use SQLScope to do the following:

•	Generate a report on the referential constraints defined for the database.

•	Verify or repair RI on the database. You should verify RI when you add referential constraints to tables or restore a partial backup of a database.

Note:

To check RI of a database on a netware server use riutil.nlm. To check RI on an NT server use SQLScope with the Use Thunk setting turned on for both the MicroKernel Router and Scalable SQL. Use the 16-bit Setup (w16sset.exe) to set Use Thunk on in the Access Control catagory for both components.

Related topics

List Referential Integrity dialog

Check Referential Integrity dialog


Keyboard Shortcuts

Key or Key Combination	Description

ALT+F4	Exits SQLScope.

F1	Provides help for a highlighted menu command or dialog.

SHIFT+F1	Enters Help mode.

F5	Updates database information in the Tables or Columns list.

CTRL+Z	Undoes last operation.

CTRL+X	Cuts highlighted text.

CTRL+C	Copies highlighted text.

CTRL+V	Pastes text from clipboard.

CTRL+S	Activates SQL Text box.

CTRL+D	Activates Database list.

CTRL+T	Activates Tables list.

CTRL+M	Activates Columns list.

CTRL+P	Activates Templates list.

CTRL+R	Activates Results Area.

CTRL+SHIFT+F	Moves text cursor to first statement.

CTRL+SHIFT+P	Moves text cursor to previous statement.

CTRL+SHIFT+N	Moves text cursor to next statement.

CTRL+SHIFT+L	Moves text cursor to last statement.

CTRL+F	Executes first statement.

CTRL+U	Executes current statement.

CTRL+A	Executes all statements.

CTRL+[keypad +]	Executes next statement.

CTRL+[keypad -]	Executes previous statement.

CTRL+O	Stops executing statements.


Command Menus

SQLScope provides the following menus:

File	Manage SQLScope files

Edit	Edit text

View	Display or hide elements of the SQLScope main window

Settings	Customize SQLScope

Database	Manage database logins and RI and refresh the Lists Area

Run	Run one or more SQL statements

Window	Refresh the list of tables and designate the currently active window

Help	Obtain on-line Help


Operation Modes

SQLScope has three modes of operation: Run mode, Script mode, and Help mode. SQLScope enters Run mode when executing statements and returns to Script mode for most other operations. SQLScope enters Help mode when you press SHIFT+F1.

When SQLScope enters Run mode, it hides the Lists Area, enlarges the Results Area, and does not allow you to edit SQL statements. When SQLScope enters Help mode, the cursor changes to an arrow with an attached question mark.


Run Mode

SQLScope enters Run mode when it is executing SQL statements. To begin Run mode, execute the First or Current command. To end Run mode, execute the Stop command.

When you begin Run mode, SQLScope removes the Lists Area (if visible) from the screen and enlarges the Results Area. When you end Run mode, the Lists Area reappears on the screen (if set to be visible).

During Run mode, you cannot perform the following SQLScope operations:

•	Open or save script files

•	Create or edit SQL statements

•	Import or export data

•	Manage RI

•	Customize SQLScope

To perform these operations, you must be in Script mode.


Script Mode

When you start SQLScope, it opens in Script mode (the standard operating mode). In Script mode, you can perform most operations, including the following:

•	Open or save script files

•	Create or edit SQL statements

•	Import or export data

•	Manage RI

•	Customize SQLScope


Help Mode

You can activate Help mode by pressing SHIFT+F1. In Help mode, the cursor changes to an arrow with a question mark attached to it. When you select a screen item, SQLScope displays Help for the relevant topic.

To exit Help mode, press ESC.


Windows and Screen Areas

The SQLScope main window consists of three areas and a status bar. In addition, there is a menu bar and a title bar at the top of the screen.

For more information, choose one of the following topics:

Lists Area

Results Area

Statement Area

Status Bar


File Menu

The File menu allows you to manage SQLScope files associated with the current SQL statements. The following commands are available on the File menu:

New	Creates a new file

Open	Opens an existing file

Save	Saves the current SQL statements

Save As	Saves the current SQL statements in a different file

Import	Loads data from an external file

Export	Saves data to an external file

Exit	Exits SQLScope


Edit Menu

The Edit menu provides commands for modifying the text in the SQL Text box. The following commands are available on the Edit menu:

Undo	Reverses the last edit operation you performed.

Cut	Removes selected text and places it on the clipboard.

Copy	Copies selected text and places it on the clipboard.

Paste	Pastes text from the clipboard to the current cursor location.


View Menu

The View menu enables you to adjust the appearance of the SQLScope main window. You can hide or show areas using the following commands:

Lists	Shows or hides the Lists Area

Status Bar	Shows or hides the status bar

Movement Buttons	Shows or hides the Movement buttons

Run Buttons	Shows or hides the Run buttons


Settings Menu

The Settings menu allows you to customize SQLScope. The following commands are available:

Column Widths	Set widths for columns in the Results Area

Login Defaults	Set default values for the Login to Database dialog

Environment	Set default values for certain dialogs and customize the screen layout

Save	Save the current SQLScope settings to the initialization file for use in subsequent SQLScope sessions


Database Menu

The Database menu enables you to manage database logins and RI and refresh the Lists Area. The following commands are available:

Login	Log in to a database

Select	Select a current database

Logout	Log out of the current database

Refresh Lists	Update the Lists Area

Update Field List	Update the Columns list

Table Statistics	Display information about the data file related to the current table

List Constraints	Generate a report that lists all foreign key definitions and related information for a named database

Check Constraints	Check for orphan rows in a named database or verify the consistency of a data file's RI data with the data dictionary's RI data


Run Menu

The Run menu allows you to execute statements in the SQL Text box. SQLScope displays the currently executing statement in the SQL Text box.

If the statement is a SELECT statement, SQLScope displays the first set of rows so that you can browse the data (forward or backward). If the statement is part of a stored procedure, Scalable SQL loads the procedure and executes the first statement within the procedure. You can then step through each statement of the stored procedure.

For more information, choose one of the following topics:

First command

Current command

All command

Previous command

Next command

Stop command

Stored SQL submenu

Run buttons


Window Menu

The Window menu allows you to move among the SQLScope areas. The checked selection on this menu is the active area. The following commands are available:

SQL Text

Database

Tables

Columns

Templates

Results


Help Menu

The Help menu allows you to obtain help for several SQLScope topics. The following commands are available:

Using Help	Provides information about using Windows Help

Index	Displays an index of SQLScope Help topics

Active Window	Displays Help for the currently active window

Keyboard	Lists keyboard shortcuts

About SQLScope	Displays the version number and copyright information for SQLScope

Related topics

SQLScope Help index

Keyboard Shortcuts


New Command

To create a new script file, choose the New command on the File menu. If your current script file has changed since you opened or created it, SQLScope allows you to save it before opening the new script file.

Related topics

Open command

Save command

Save As command


Open Command

To open an existing script file, choose the Open command on the File menu and either select a file from the dialog, or enter a filename in the text box provided. If you have an active script file that has changed since you opened it, SQLScope allows you to save it before opening the next script file.

Related topics

New command

Save command

Save As command


Save Command

To save the current script file, choose the Save command on the File menu. The first time you save statements to a script file, SQLScope displays a dialog so you can specify the path and name of the file. When you save the script file, SQLScope saves the current SQL statements under their existing path and filename.

Related topics

New command

Open command

Save As command


Save As Command

To save the current SQL statements to a new script file, choose the Save As command on the File menu. In the dialog that appears, specify the new path and name of the file.

Related topics

New command

Open command

Save command


Import Command

This command allows you to load data into a Scalable SQL database. The data file must have one of the following formats:

•	MicroKernel unformatted (UNF)

•	Standard data format (SDF)

•	ASCII (ASC)

The current SQL statement must be a valid INSERT, UPDATE, or DELETE statement. In addition, for each row being imported, a one-to-one correspondence must exist between the substitution variables in the SQL statement and the column values in the import file.

To import data, choose the Import command on the File menu, and enter the path and filename into the dialog.

Related topic

Export command


Export Command

This command allows you to export data from a Scalable SQL database. The data file can have one of the following formats:

•	MicroKernel unformatted (UNF)

•	Standard data format (SDF)

•	ASCII (ASC)

The current SQL statement must be a valid SELECT statement. To export data, choose the Export command on the File menu, and enter the required information into the dialog.

Related topic

Import command


Exit Command

To exit SQLScope, choose the Exit command on the File menu. If your current script file has changed, you can save the changes when prompted.

If you decide not to exit SQLScope, click the Cancel button in the Exit SQLScope dialog.


Undo Command

To cancel your last operation in the SQL Text box, choose the Undo command on the Edit menu. The Undo command is only available when your last operation can be canceled.

Related topics

Cut command

Copy command

Paste command


Cut Command

To remove (cut) text from the SQL Text box and place it on the clipboard, highlight the text you want to remove and choose the Cut command on the Edit menu. You can use the Paste command to insert text from the clipboard into the SQL Text box.

Related topics

Undo command

Copy command

Paste command


Copy Command

To copy text from the SQL Text box to the clipboard, highlight the text you want to copy and choose the Copy command from the Edit menu. You can use the Paste command to insert the copied text into the SQL Text box.

Related topics

Undo command

Cut command

Paste command


Paste Command

To paste text from the clipboard into the SQL Text box, place the text cursor at the desired location and then choose the Paste command on the Edit menu. If text is currently highlighted in the SQL Text box, SQLScope replaces it with the text from the clipboard.

Related topics

Undo command

Cut command

Copy command


Lists Command

This command shows or hides the Lists Area. The Lists Area contains the following items:

•	Database drop-down list

•	Tables list

•	Fields list

•	Templates list

When the Lists Area is visible, you can copy the current selection from any of the lists into the SQL Text box.


Status Bar Command

This command shows or hides the status bar at the bottom of the SQLScope main window. The status bar shows progress information about SQL statement execution and provides a short description for highlighted menu commands.


Movement Buttons Command

This command shows or hides the Movement buttons on the left in the Statement Area. The Movement buttons allow you to move the text cursor among SQL statements in the SQL Text box. The Previous and Next selections are based on cursor position.

The Movement buttons consist of the following:

 Moves the text cursor to the beginning of the first statement.

 Moves the text cursor to the beginning of the last statement.

 Moves the text cursor to the beginning of the previous statement.

 Moves the text cursor to the beginning of the next statement.


Run Buttons Command

This command shows or hides the Run buttons on the right in the Statement Area. The Run buttons allow you to run SQL statements in the SQL Text box. The previous and next selections are based on cursor position.

The Run buttons consist of the following:

 Executes the SQL statement at the current cursor position.

 Executes the first SQL statement.

 Executes the previous SQL statement.

 Executes the next SQL statement.

 Stops executing SQL statements and exits Run mode.


Column Widths Command

This command allows you to adjust the width of columns displayed in the Results Area. Adjusting the column widths affects their display only for the current statement execution. That is, if you run another statement, the columns revert to their default display widths.

When you choose the Column Widths command on the Settings menu, SQLScope displays the Column Display Width dialog. Use the Previous and Next buttons to go to the column whose width you want to adjust and then use the Width text box to specify a new width for the column.


Login Defaults Command

This command allows you to set default values for the Login to Database dialog. You can set defaults for the current session only, or you can save the defaults you set for use in subsequent SQLScope sessions.


Environment Command

This command allows you to customize the SQLScope main window environment. It also provides commands to set default values for the following dialogs:

•	Run All

•	List Referential Integrity

•	Check Referential Integrity

•	Import SQL Data

•	Export SQL Data

You can customize the SQLScope window and set defaults for the current session only, or you can save the environment settings for use in subsequent SQLScope sessions.


Save Command

This command saves your current SQLScope settings to the initialization file for use in subsequent SQLScope sessions.


Login Command

This command allows you to log in to a Scalable SQL database. You can use this command multiple times if you want to be logged in to multiple databases simultaneously. When you choose this command, SQLScope displays the Login to Database dialog.


Select Command

This command allows you to select a current database.


Logout Command

This command logs you out of the current database.


Refresh Lists Command

This command refreshes the Tables and Columns lists. Before you can choose this command, you must log in to a database and ensure that the Lists Area is visible.

This command is useful after you execute a series of SQL statements that may have changed the tables or columns in the current data dictionary.

Related topics

Columns list

Login command

Tables list


Update Column List Command

This command fills the Columns list with the column names from the table highlighted in the Tables list. The command clears the existing column names, if necessary, before updating the list. Before you can choose this command, you must log  in to a database, ensure that the Lists Area is visible, and highlight a table name in the Tables list.

To update the Columns list, either choose the Update Columns List command on the Database menu, or click the >> (update columns) button, shown below:


Table Statistics Command

This command allows you to display MicroKernel-level information about data files. Before you can use this command, you must log in to a database.


List Constraints Command

This command allows you to generate a report that lists all foreign key definitions and related information for a named database. Before you can use this command, you must log in to a named database.


Check Constraints Command

This command allows you to check for orphan rows in a named database or verify the consistency of a data file's RI data with the data dictionary's RI data. Before you can use this command, you must log in to a named database.


SQL Text Command

This command specifies the SQL Text box as the active area for keyboard input.

The SQL Text box contains the text for the SQL statements you want to execute. When SQLScope is in Run mode, you can browse the currently executing statement using the SQL Text box, but you cannot edit the statement.

The SQL Text box can contain more than one statement. Each statement can have comments at the end of each line.

Related topics

Database command

Tables command

Columns command

Templates command

Results command

Statement Separator


Database Command

This command specifies the Database pull-down list as the active area for keyboard input.

The Database pull-down list box contains the current list of databases. You can change the active database by selecting a database from this list.

Related topics

SQL Text command

Tables command

Columns command

Templates command

Results command


Tables Command

This command specifies the Tables list box as the active area for keyboard input.

The Tables list box contains the list of tables in the current database. You can use this list box to select a table name for insertion into an SQL statement. In addition, when a table name is highlighted, you can update the Columns list with the column names defined for that table.

Related topics

SQL Text command

Database command

Columns command

Templates command

Results command


Columns Command

This command specifies the Columns list as the active area for keyboard input.

The Columns list contains the columns defined for the table highlighted in the Tables list. You can use this list to select a column name for insertion into an SQL statement. In addition, when a table name is highlighted in the Tables list, you can update the Columns list with the column names defined for that table.

Related topics

SQL Text command

Database command

Tables command

Templates command

Results command


Templates Command

This command specifies the Templates list as the active area for keyboard input.

The Templates list contains predefined templates of valid SQL syntax. You can use this list to select a template for insertion into an SQL statement.

Template names begin with a dollar sign ($). Template text may contain references to other templates. If you need help with a statement element, you can insert the referenced template for that element. For example, if you need help defining a WHERE clause, double click on $Where_Clause to highlight that text. Then, double-click on the Where_Clause template in the Templates text box.

Related topics

SQL Text command

Database command

Tables command

Columns command

Results command


Results Command

This command specifies the Results Area as the active area for keyboard input. The Results Area displays the results of the statement you just executed.

Related topics

SQL Text command

Database command

Tables command

Columns command

Templates command


First Command

This command runs the first SQL statement in the SQL Text box, regardless of where the text cursor is positioned. The results appear in the Results Area.

Related topics

All command

Current command

Previous command

Next command

Stop command

Stored SQL cascading menu

Run buttons


Current Command

This command runs the current SQL statement (that is, the statement on which the text cursor is currently positioned in the SQL Text box) and displays the results in the Results Area.

Related topics

First command

All command

Previous command

Next command

Stop command

Stored SQL cascading menu

Run buttons


All Command

This command runs all SQL statements in the SQL Text box and displays the results in the Results Area.

Related topics

First command

Current command

Previous command

Next command

Stop command

Stored SQL cascading menu

Run buttons


Previous Command

This command runs the previous SQL statement (that is, the statement that precedes the current statement in the SQL Text box) and displays the results in the Results Area.

Related topics

First command

Current command

All command

Next command

Stop command

Stored SQL cascading menu

Run buttons


# the Tables list.

To update the Columns list, either choose the UCommand

This command runs the previous SQL statement (that is, the statement that precedes thes16K run;execute Next Command

This command runs the next SQL statement (that is, the statement that follows the current statement in the SQL Text box) and displays the results in the Results Area.

Related topics

First command

Current command

All command

Previous command

Stop command

Stored SQL cascading menu

Run buttons


Stop Command

This command ends statement execution for the current script, discards the results, and returns SQLScope to script mode. You can then edit the SQL statements.

Related topics

First command

Current command

All command

Previous command

Next command

Stored SQL cascading menu

Run buttons


Stored SQL Submenu

The Stored SQL submenu allows you to execute SQL statements within a stored procedure as individual statements, instead of as a complete unit. For more information, choose one of the following commands:

Previous command

Next command

Run All Statements command


Run Previous Stored Procedure Command

In a stored procedure, this command runs the statement that precedes the current statement.

Related topics

Next command

Run All Statements command


Run Next Stored Procedure Command

In a stored procedure, this command runs the statement that follows the current statement.

Related topics

Previous command

Run All Statements command


Run All Statements

This command runs all statements in a stored procedure in the order in which they are listed.

Related topics

Previous command

Next command


Menu Bar

The menu bar appears at the top of the SQLScope main window, under the title bar. It lists the SQLScope menus.


Lists Area

The Lists Area contains the >> (update columns) button and the following lists:

Database	Shows the current databases.

Tables	Lists the tables in the current database.

Columns	Lists the columns in the current table.

Templates	Contains predefined templates of SQL statements.

You can hide or show the Lists Area using the Lists command on the View menu. You can also set the height of the Lists Area either by using the Environment command on the Settings menu, or by dragging the Lists Area bottom margin to the desired position.


Lists Area Height

You can set the height of the Lists Area to display between 4 and 8 lines in each list. Doing so allows you to see more or less of the Lists Area and, consequently, make more or less space available for the Statement Area.

To set the height of the Lists Area, do one of the following:

•	Move the cursor to the bottom of the Lists Area until the cursor changes to a horizontal bar with arrows pointing up and down. Then, click and drag the Lists Area margin to the desired position.

•	Select the Environment command on the Settings menu. Then, in the Environment Settings dialog, set the height for the Lists Area.


Statement area

The Statement Area has three parts, as follows:

Movement buttons	Moves the text cursor in the SQL Text box.

Run buttons	Runs the statements in the SQL Text box.

SQL Text box	Contains the SQL statements you want to run.

You can show or hide the Movement and Run buttons using the appropriate command from the View menu. You can also set the height of the Statement Area, either by using the Environment command on the Settings menu, or by dragging the Statement Area top or bottom margin to the desired position.


Statement area Height

You can set the height of the Statement Area to display between 5 and 11 lines in the SQL Text box. Doing so allows you to see more or less of the Statement Area and, consequently, make more or less space available for the Lists Area and Results Area.

To set the height of the Statement Area, do one of the following:

•	Move the cursor to the top or bottom of the Statement Area until the cursor changes to a horizontal bar with arrows pointing up and down. Then, click and drag the Statement Area margin to the desired position.

•	Select the Environment command on the Settings menu. Then, in the Environment Settings dialog, set the height for the Statement Area.


Status Bar

The status bar shows progress reports about SQL statement execution and provides a short description of the currently highlighted menu command.


Results Area

You can use this area only when SQLScope is in Run mode. In Run mode, this area contains the results of the last SQL statement you ran.

If all the data for a column is not displayed, you can widen the column. Move the cursor to the right edge of the title for the column, until the cursor changes to a vertical bar with arrows pointing right and left. Then, click and drag the column margin to the desired position.


Login to Database Dialog

The Login to Database dialog allows you to log in to a Scalable SQL database. You can use it multiple times if you want to be logged in to multiple databases simultaneously. When you first start SQLScope, this dialog appears. You can also access this dialog by choosing the Login command on the Database menu.

In this dialog, you must either select the Use Database Names check box and specify a database name, or specify the directory path of the data dictionary.

If security is enabled, you must also enter a username. If a password is defined for your username, you must also enter a password.

If you are logging into a database that was created with Scalable SQL v3.01 and you want it to remain compatible with v3.01 applications, select the Version 3.01 Compatible check box .

When you have specified the required information, press ENTER or click the Login button to log in. If the login succeeds, you can run SQL statements and perform other tasks using the database.

If the login fails, SQLScope displays a status code and allows you to try logging in again. The status codes you may encounter if the login fails appear in the Scalable SQL Status Codes and Messages manual.

Related topic

Logging in to Databases


Use Database Names Check Box

Selecting this check box enables the Database Name pull-down list. Clearing this check box enables the Database Directory text box, which allows you to specify the physical location of the data dictionary.


Database Name Drop-down List

Use this pull-down list to specify the name of the desired database. This pull-down list displays a list of the available named databases. If this list box is disabled, select the Use Database Names check box.

Related topic

Database Directory text box


Database Directory Text Box

Use this text box to specify the location of your database's data dictionary. The location must be a directory that already exists.

Related topics

Database Name drop-down list

Use Database Names check box


Username Text Box

Use this text box to specify your username for the database. Usernames are case-sensitive and cannot exceed 30 characters. A username is required only if the database has security enabled. If your username has a password defined, you must also specify the password.


Password Text Box

Use this text box to specify your password. Passwords are case-sensitive and cannot exceed eight characters. For security reasons, SQLScope displays an asterisk (*) for each character you enter.


Welcome to SQLScope

Welcome to SQLScope. This application is an interactive SQL script file editor for Scalable SQL. It allows you to create SQL statements, run them, and view the results. In addition to creating and running SQL statements, you can perform the following tasks:

•	Log in to and out of databases

•	Save and recall script files

•	Edit SQL statements

•	Manage RI for named databases

•	Import and export data in UNF (MicroKernel unformatted), ASCII, and SDF (comma delimited) formats

The current dialog allows you to exit SQLScope if you do not want to use it now. If you want to exit, press ESC or click the Esc button. If you want to continue with SQLScope, either press ENTER or click the Enter button.


About SQLScope

This dialog displays the icon that identifies SQLScope, the version number, and the copyright notice. When you have finished viewing the information, click the OK button.


Open Statement Script File Dialog

This dialog allows you to specify a script file to open. Script files contain one or more SQL statements. Either select a filename and directory from the list, or enter a full pathname in the File Name text box, then click the Open button.


Save Statement Script File Dialog

This dialog allows you to specify the name of a file in which to save the SQL statements currently contained in the SQL Text box. You can either select a filename and directory from the list, or enter a full pathname in the File Name text box. Then, click the Save button.


Export Data Dialog

This dialog allows you to save the results of the current statement to a file that uses the UNF, ASCII, or SDF format. To export data from your database, the current SQL statement must be a valid SELECT statement. SQLScope exports each column in the list to the external file according to the file format you choose.

For more information, see Importing and Exporting Data.


Import Data Dialog

This dialog allows you to load data into a Scalable SQL database. The data can be in UNF, ASCII, or SDF format. To import data into your database, the current SQL statement must be a valid INSERT, UPDATE, or DELETE statement. For each record being imported, there must be a one-to-one correspondence between the substitution variables in the SQL statement and the columns in the import file.

For more information, see Importing and Exporting Data.


Login Defaults Dialog

This dialog allows you to customize SQLScope to automatically include a database name or location and a username when you are logging in to a database. In this dialog, you must either select the Use Database Names check box and specify a database name, or specify the directory path of the data dictionary. If security is enabled, you must also enter a username (and, if applicable, a password).

Once you have customized the dialog, you can save the settings for subsequent SQLScope sessions by using the Save command on the Settings menu.

Related topics

Logging in to databases

Login to Database dialog


Environment Settings Dialog

This dialog allows you to customize SQLScope to automatically include certain values in dialogs, alter screen layout, and save the settings for subsequent SQLScope sessions. To customize dialog default values, click the button for the relevant dialog and enter the values.

Run All	To specify default values for the Run All dialog, click this button.

List Constraints	To specify default values for the List Referential Integrity dialog, click this button.

Check Constraints	To specify default values for the Check Referential Integrity dialog, click this button.

Import/Export	To specify default values for importing and exporting data, click this button.

Status Bar	To hide the status bar, clear this check box.

Lists Area	To hide the Lists Area, clear this check box.

Height	To adjust the height of the Lists Area, enter a number of lines in this text box. The Lists Area can be 4 to 11 lines high.

Movement Buttons	To hide the Movement buttons in the Statement Area, clear this check box.

Run Buttons	To hide the Run buttons in the Statement Area, clear this check box.

Statement Area Height	To adjust the height of the Statement Area, enter a number of lines in this text box. The Statement Area can be 5 to 12 lines high.

Maximize at Startup	If you want the SQLScope main window to fill the screen upon startup, select this check box.

Statement Separator	If you want to use a different statement separator use this pull-down list.

Login	To save the default values you specified in the Login Defaults dialog, select this check box. SQLScope saves the values to the initialization file and uses them in subsequent SQLScope sessions.

Environment	To save the default values you specified in this dialog, select this check box. SQLScope saves the values to the initialization file and uses them in subsequent SQLScope sessions.


Save Settings Dialog

This dialog allows you to save the current login defaults or environment settings for use in subsequent SQLScope sessions. The options in this dialog are as follows:

Login	To save the default values you specified in the Login Defaults dialog, select this check box.

Environment	To save the default values you specified in the Environment Settings dialog, select this check box.

If you save these settings, SQLScope writes them to the initialization file.


Table Statistics Dialog

Using this dialog, you can view MicroKernel-level statistics for a data file. However, you cannot change these statistics. If you want to display the statistics for another table, use the Table pull-down list to select that table. This dialog displays the following statistics:

Column	Lists the columns defined for the current table.

Type	Shows the data type defined for each column.

Size	Shows the size in bytes of each column.

File	Shows the data file associated with the current table.

Page Size	Shows the page size (in bytes) of the data file.

Number of Records	Shows the number of records the data file contains.

Unused Pages	Shows the number of preallocated pages available. If preallocation is enabled, the MicroKernel preallocates a specified number of pages when it creates the data file.

Compressed	Shows whether data compression is enabled. If it is, the MicroKernel compresses each record it inserts into the data file. When you use a CREATE TABLE statement, you can enable compression.

Variable Records	Shows whether the data file contains variable-length records.

Truncate Blanks	Shows whether blank truncation is enabled. If it is, the MicroKernel truncates the blanks in variable-length records. Blank truncation is applicable only if the Variable Records statistic is Yes.

Free Space Threshold	Displays a percentage (5%, 10%, 20%, or 30%) if the data file has a free space threshold. The MicroKernel stores the variable-length portions of records on their own pages (called variable pages), which are separate from the fixed-length portions stored on data pages. The threshold is applicable only if the Compressed or Variable Records statistic is Yes.

Key #	Lists the MicroKernel key numbers for the current table. The MicroKernel stores Scalable SQL indexes as keys.

Name	Displays the name of the index, if applicable.

Values	Displays the number of column values stored for the index.

Segment	Displays the column or columns on which the index is defined.

Attributes	Displays the attributes defined for the index.


List Referential Integrity Dialog

Using this dialog, you can produce a report that lists all foreign key definitions and related information for any named database. The options in this dialog are as follows:

Output File	Enter a full pathname for the report file. If the file does not exist, SQLScope creates it.

Append Output	If the output file you specify exists and you want SQLScope to overwrite the existing file contents, clear this check box.

Trace Information	If you want the report to include additional trace information (such as header information about the options you specified, full pathnames, and detailed information about each table in the report), select this check box.

Foreign Key Name	(Default) If you want to sort the output by the foreign key names defined in the database, select this check box.

Parent Table	If you want to sort the output by the parent table names defined in the database, select this check box.

Dependent Table	If you want to sort the output by the dependent table names defined in the database, select this check box.

Background	If you want to generate the report in the background so you can continue working in SQLScope, select this check box.

Note: You cannot exit SQLScope while a background process is running.


Check Referential Integrity Dialog

Using this dialog, you can check for orphan rows in a named database, or you can verify the consistency of a data file's RI data with the data dictionary's RI data. The options in this dialog are as follows:

Output File	Enter a full pathname for the report file. If the file does not exist, SQLScope creates it.

Append Output	If the output file you specify exists and you want SQLScope to overwrite the existing file contents, clear this check box.

Trace Information	If you want the report to include additional trace information (such as header information about the options you specified, full pathnames, and detailed information about each table in the report), select this check box.

All	(Default) If you want SQLScope to check all tables in the database, select this check box.

Specific	If you want SQLScope to check only one table, use this pull-down list to specify the table.

Orphan Rows	If you do not want SQLScope to check for orphan rows, clear this check box.

Write to Exception Table	If you do not want SQLScope to create an exception table, clear this check box.

Delete	If you do not want SQLScope to delete the orphan rows it finds, clear this check box.

Inconsistencies	If you do not want SQLScope to check for inconsistencies, clear this check box.

Repair	If you do not want SQLScope to repair inconsistencies, clear this check box.

Background	If you want to generate the report in the background so you can continue working in SQLScope, select this check box.

Note: You cannot exit SQLScope while a background process is running.


Exception Tables

By default, SQLScope generates an exception table for each table in which it finds orphan rows. The exception table becomes part of the database, with the same location and filename as the original data file, but with a .EXC extension. For example, if SQLScope generates an exception table for the Person table, the exception table is named EXC_PERSON and is stored in a data file named PERSON.EXC.

The first column in the exception table is an index and contains the parent table name. The remainder of each row contains the same column values as the original orphan row, and each row can contain up to 4090 bytes; SQLScope truncates rows larger this. You can use SQLScope to review the exception table by issuing SQL statements, just as you would to review any other table.


Database Inconsistencies

By default, SQLScope checks for inconsistencies between the information in the data dictionary and that in the individual data files. For example, you may create an inconsistency if you move a data file from one database to another, because the old database name stored in the data file does not match the new database name stored in the data dictionary.

SQLScope checks for the following inconsistencies:

database names	Checks the database name stored in the data file against the database name stored in the data dictionary.

primary key	Checks the number of referencing foreign keys stored in the data file against the referential constraints stored in the data dictionary.

foreign keys	Checks the number of foreign keys defined in the data file against the referential constraints stored in the data dictionary.

To repair inconsistencies, SQLScope updates the information stored in the individual data files to match that in the data dictionary.


Run All Dialog

This dialog allows you to redirect the results of SQL statements to an external file. The options in this dialog are as follows:

Stop on Error	If you want SQLScope to continue executing statements even if it encounters an error, clear this check box.

Include Statement Text	If you do not want SQLScope to include the text of the SQL statements in the external file, clear this check box.

Append Output	If the file you specified already exists and you want SQLScope to overwrite the contents of the file, clear this check box.

Background	If you want to run the statements in the background so you can continue working in SQLScope, select this check box.

Note: You cannot exit SQLScope while a background process is running.


Column Display Width Dialog

This dialog allows you to specify the display width for any column that an active SELECT statement accesses.

To change the width of a column, ensure that the correct column heading is displayed in the Column text box. (Use the Previous and Next buttons to see columns to the right and left of the current column.) When the correct column is displayed, specify a new column width.

The new width remains in effect even if you move to another column. However, SQLScope does not update the display until you exit the dialog.

Note: The size you specify may not accommodate certain columns even though the column values have the specified character width, because each character has proportional (not fixed) width. The width you specify determines the average number of characters to be displayed.


Exit SQLScope Dialog

This dialog verifies that you want to exit SQLScope.

If you want to exit, click the Exit button. If you do not want to exit, click the Cancel button.


Database Names Dialog

You can be logged in to more than one database at a time. Either enter the name of a database and click the OK button, or double-click on the name if it is listed in the Database Names list.

Related topic

Database Names


Select Database Dialog

You can be logged in to more than one database at a time. This dialog allows you to change the current database by highlighting the database you prefer in the Database list. In addition, you can log out from a database by highlighting it and clicking the Logout button. You can also log in to a database by clicking the Login button.

Related Topic

Login to Database dialog


Database Names Memory Error

SQLScope cannot obtain the memory necessary for storing the database name. You must free some memory resources to process database names.


Unsuccessful Database Login

SQLScope cannot log in to the database you requested. The message box displays the status of the login attempt. Refer to the Scalable SQL Status Codes and Messages manual for an explanation of the status code and a suggested remedy.

Some common solutions to an unsuccessful login attempt are as follows:

•	Make sure you specified a valid username and password.

Note: Usernames and passwords are case-sensitive.

•	Make sure your workstation or the server storing the database has Scalable SQL loaded.

•	Make sure you specified the correct location for the database. If you are logging in to a named database, make sure the database is defined on the workstation or server. To see a list of defined database names, use the Setup utility.


Save Script File Changes

The contents of the script file (shown in the SQL Text box) have changed since you opened the file or started a new file. You can now save the changed script file.

If you click the Save button, SQLScope displays the Save SQL Script File dialog. In that dialog, specify the file where the changes should be saved.


Version 3.01 Compatible Check Box

The compatibility mode check box allows you to log into v3.01 Scalable SQL databases and operate in v3.01 mode.

Select this check box if you want a v3.01 database to continue to be compatible with applications written with v3.01. If you log into a v3.01 database but do not select this check box, you must use v4.0 SQL syntax; if you then use new v4.0 features, you run the risk of making the database incompatible with v3.01 statements and applications.

If you select this check box and log into a v4.0 database, you will not be able to use v4.0 syntax and data types in the database.

Related Topic

      Login to Database Dialog

      Logging in to Databases


Statement Separator

You can specify the character you wish to use to separate SQL statements. The

default separator is a semicolon (;). However, since a stored procedure

declaration is considered to be a single SQL statement and the syntax of a stored

procedure declaration may include semicolons, you can specify a different

character to separate your SQL statements.

To change the statement separator character, select a new character from the

Statement Separator pull-down list. Your choices are the pound sign (#),

and the semicolon (;).


select

Position the cursor on an item and click the left mouse button.


click and drag

Click and hold down the left mouse button while moving the cursor to a new position.


database name

The name for a particular database or set of dictionary files.


pop-up box

A box that contains the definition of a word. The box pops up when you point the cursor at the associated word and hold down the left mouse button.


double-click

Position the cursor on an item and click the left mouse button twice.


Btrieve Unformatted Data
UNF

MicroKernel unformatted. This is a data file format SQLScope recognizes for importing and exporting data. Numeric columns remain in binary form. SQLScope does not convert them to printable ASCII characters. Each row is preceded by its length in ASCII digits and a comma delimiter. A carriage return/line feed terminates each row.

Note: When you use a UNF file to import data, be sure that each row of the file is structured correctly according to the data dictionary definition of the Scalable SQL table into which you are importing. Each column in the row must be of the correct data type and at the proper offset in the row. Otherwise, the data in the file will not agree with the data dictionary definition.


Standard Data Format
SDF

Standard data format. This is a data file format SQLScope recognizes for importing and exporting data. The data is represented as standard ASCII characters, commas separate the columns, and double quotation marks enclose string columns. A carriage return/line feed terminates each row. You can use this format in dBASE II or III programs and in most BASIC programs.


ASCII Data Format
ASCII

ASCII data format. This is a data file format SQLScope recognizes for importing and exporting data. Each row in the input file must be preceded by the length of the row and a delimiter (either a comma or a space). The data follows the delimiter and should be the exact length specified. Each column in the row must be the length of the display size of the column. A carriage return/line feed terminates each row.


comment

An optional part of a SQL statement that contains notes from the statement's author. A comment begins with two dashes (--) and ends with a carriage return/line feed. Place comments at the end of the SQL statement; SQLScope ignores all text between the dashes and the end of the line.


foreign key

A column or group of columns that references a primary key. The foreign key can be in the same table as the primary key or in a different table. A foreign key cannot contain null column values, and the columns it includes must match the primary key columns in type, length, and order.


primary key

A column or group of columns that uniquely identifies each row in a table. Primary keys are implemented as unique, non-null indexes.


parent table

A table that contains a primary key referenced by a foreign key.


dependent table

A table that contains at least one foreign key.


orphan row

In a dependent table, a row with a foreign key value that does not exist in the index corresponding to the parent table's primary key. That is, the dependent key value does not have a corresponding parent key value.


stored procedure

One or more SQL statements that you precompile and save in the dictionary. These statements can be either standard SQL statements or previously stored SQL statements. You give each stored procedure a name, which you can use later to execute the statement.


Ppage Size
page size

The smallest unit of storage that the MicroKernel moves between main memory and disk. The page size of a file determines the maximum number of index segments you can define in the table, as follows:

Page Size (in Bytes)	Maximum Number of Segments

512	8

1024	23

2048	54

3072	54

4096	119


preallocation

A method the MicroKernel uses for allocating a specific amount of disk space for a file when you create the file. This space is reserved for future expansion of the file when the MicroKernel needs it.


free space threshold

A mechanism that the MicroKernel uses to determine whether to add data to an existing variable page or to create a new one. A higher free space threshold reduces fragmentation of variable-length records across several pages, but uses more disk space.

When you use a CREATE TABLE statement, you can specify a free space threshold.


index name
named index

An index that you name by using the CREATE INDEX statement. You can delete a named index, whereas you cannot delete an unnamed index (an index you create by using a WITH INDEX clause in a CREATE TABLE statement).


attribute;index
index attribute

A characteristic assigned to an index and stored in the data dictionary. The keywords that specify index attributes are as follows:

CASE	Identifies an index as case-insensitive.

MOD	Indicates that you can modify index column values after Scalable SQL stores the corresponding row.

NULL	Indicates that Scalable SQL does not include columns that contain the predefined null value in the index structure.

DESC	Specifies the sort order for the index as from largest to smallest.

UNIQUE	Indicates that Scalable SQL does not allow multiple rows to have the same column value for this index.