Database Tab

You can perform the following operations from the Database tab:

For information on supported SQL-J statements, see the SQL-J Grammar Index section of the Cloudscape Reference Manual.

SQL Window

The SQL window displays in the right panel when a database node is highlighted in the left panel.

Executing Statements from the SQL Window

To execute SQL-J statements against a database:

  1. Highlight the database in the left panel. Statements are sent only to the selected database.
  2. Clear the SQL window if necessary. To do this, click the Clear icon .
  3. Type an SQL-J statement or statements in the top window of the right panel. You can type any supported SQL-J command, including DDL statements. Separate multiple statements with semicolons.

    The SQL-J Grammar Index section of the Cloudscape Reference Manual lists all SQL-J statements supported by Cloudscape products.

  4. Click the Execute icon, or press <Ctrl-e>.

    Any results appear in the bottom pane.

Results are displayed as each statement is executed. The currently executed statement is highlighted. If you click the Stop button while the statements are running, partial results are displayed for the statement running at the time execution is halted. If you do not stop execution, only the results from the final statement are displayed after execution completes.

If you execute an SQL-J statement that modifies the database or data, all changes are automatically saved. Cview always executes in auto-commit mode.

NOTE: It can be useful to open two Cview windows, one set to the SQL window in Details Only mode. This makes it easy to browse the database without having to navigate back to the SQL window each time you want to execute an SQL-J statement.

Executing from a Script File

To execute SQL-J statements from a file:

  1. Click the Script icon above the SQL window, then click Open.
  2. Choose a file using the file browser, then click Open.

    The contents of the file appear in the SQL window.

  3. Optionally, edit the SQL-J statements.
  4. Click the Execute icon.

    If any queries are executed, their results appear in the bottom window.

Saving SQL-J Statements to a File

To save the SQL-J batch that appears in the SQL window to a new file:

  1. Click the Script icon, then click Save.
  2. Use the file browser to navigate to the desired directory.
  3. Enter the name of the file in the File Name field.
  4. Click Save.

To save the batch to the same file, click the Script icon, then Save. The file you specify is overwritten each time you save the SQL-J batch. To save the batch to another file, choose Save As from the Script icon and specify another file name.

Using Auto Text

Auto text provides an easy way to look up table, view, or column names and include them in your SQL-J statements.

To use the auto text feature:

  1. From the SQL window, click the Auto Text icon .
  2. Choose Tables or Views from the pop-up menu.

    The menu expands to display the names of all objects of the chosen type in the current database.

  3. To see a list of column names in a particular object, choose the name of the table or view from the menu.
  4. Click the name you want to use. It automatically appears in the SQL window at the current cursor position.

Statistics

Runtime statistics provide information about how a statement is processed within Cloudscape.

To generate statistics:

  1. Check the Use Statistics box on the Databases tab, displayed when a database node is highlighted in the left pane.
  2. To see the statistics, choose the Statistics tab after executing an SQL-J statement.

For information on using runtime statistics within Cview, see the Tuning Cloudscape guide.

Stopping a Statement

To stop a statement while it is executing, click the Stop button beneath the bottom window. The statement is stopped just before the next row is fetched. The status bar indicates how many rows have been fetched in the result so far, as well as the compilation and execution time for the terminated statement.

Seeing Previous Statements

To see statements that were run earlier on the current database, use the forward and back arrows above the SQL window.

If you issue multiple statements in a batch and then use the arrows, the entire batch appears in the window.

To execute a statement that was previously run on another database:

  1. Choose the database on which you originally ran the statement.
  2. Highlight the statement and copy it to the clipboard. Use your operating system's procedures to highlight and copy.
  3. Choose the database on which to run the statement.
  4. Paste the statement into the SQL window. Use your operating system's procedures to paste.
  5. Click the Execute icon.

Case Sensitivity in the SQL Window

By default, database items created in Cloudscape are case-insensitive and are stored internally as all uppercase. To preserve the case of an item, use double quotes (") around the item in the SQL window.

For example, the following statement in the SQL window creates a table whose name is case insensitive, and is stored and displayed as MYTABLE:

CREATE TABLE MyTable (counter INT)

However, the following statement creates a table whose name is case-sensitive and which is stored and displayed as MyTable:

CREATE TABLE "MyTable" (counter INT)

The following statement retrieves data from MYTABLE but not from MyTable, since case insensitivity is assumed:

SELECT * FROM MyTable

See the Cloudscape Reference Manual for more information on case sensitivity in SQL-J.

Case sensitivity is handled differently if you create items in the SQL window than if you create the items directly using Cview tools. See Case Sensitivity in Table and Column Names for details.