Library > Products > Pervasive PSQL > Pervasive PSQL v9 SP2 (9.5) > User's Guide > SQL Editor > SQL Editor Tasks

PreviousUser's Guide (v9 SP2 (9.5) revision 1) Next

SQL Editor Tasks

Chapter contents

This section explains the tasks that you perform with SQL Editor. The tasks are divided into the following categories:

Category
Description
General Tasks
Orient you to the overall use of SQL Editor
Execution Tasks
Apply to the running of SQL statements
Grid Tasks
Apply to using the Grid
Text View Tasks
Apply to using the Text window
Outline View Tasks
Apply to using the Outline window
Common SQL Object Tasks
Apply to triggers, stored procedures, user-defined functions, and views

Note
If you use SQL Editor to change the structure of table with SQL statements, refresh the Pervasive PSQL Explorer to see the change. Right-click on the Tables node in Pervasive PSQL Explorer then click Refresh.

General Tasks

General tasks orient you to the overall use of SQL Editor.

Execution Tasks

Execution tasks apply to the running of SQL statements.

Grid Tasks

Grid tasks apply to working with the Grid window.

Text View Tasks

Text view tasks apply to working with the Text window.

Outline View Tasks

Outline view tasks apply to working with the Outline window.

Common SQL Object Tasks

Common SQL Object tasks apply to working with triggers, stored procedures, user-defined functions, and views.

General Tasks

To start SQL Editor for a new SQL query

  1. Start PCC if it is not already running. (See Starting PCC .)
  2. Click File 4 New 4 SQL Document or click .

    3. The Select Database dialog appears.

  3. Click the database in the list for which you want the SQL document to apply (click the expand icon to expand the tree nodes), or ensure that the option None is check marked if the SQL document does not apply to a specific database.
    4. Note
    The option None is selected by default if an object other than a database, or any of the nodes subordinate to a database, is selected in Pervasive PSQL Explorer.

    Note that the commands to execute SQL statements are disabled if None is specified as the context. See To set database context for an SQL query .
  4. Click OK.

    5. SQL Editor appears as a new window view in PCC. By default, PCC names the new document SQLDocn, where n is an integer that starts with 1 and increments by 1. The document name appears in the tab for SQL Editor.

To start SQL Editor by displaying all records in a table

  1. Start PCC if it is not already running. (See Starting PCC .)
  2. Expand the Engines and Databases nodes in Pervasive PSQL Explorer (click the expand icon to the left of each node).
  3. For the desired database, expand the Tables node (click the expand icon to the left of the node).
  4. Double-click the table for which you want to see all records (or right-click the table then click Open).

    5. By default, PCC open SQL Editor and executes a SELECT * FROM statement for the table. Note that the SELECT statement can fail depending on user and column-level permissions.

To set database context for an SQL query

Note that the commands to execute SQL statements are disabled until a database is specified as the context to which the SQL statement applies.

  1. Start PCC if it is not already running. (See Starting PCC .)
  2. Perform one of the following actions:
    1. For a new SQL statement, click File 4 New 4 SQL Document or click .
The Select Database dialog appears. Click the database in the list for which you want the SQL document to apply (click the expand icon to the left of each node to expand the nodes).
    1. If SQL Editor already contains SQL statements not associated with a database, click .
The Select Database dialog appears. Click the database in the list for which you want the SQL document to apply (click the expand icon to the left of each node to expand the nodes).
  1. Click OK.

To identify editor settings for SQL Editor

  1. Ensure that the cursor is positioned in SQL Editor.
  2. Observe the information blocks along the bottom of the PCC window.



    3. Block
    Meaning
    1
    Identifies whether SQL Editor accepts character input (Writable).
    2
    Indicates whether the editor is in insert mode or overwrite mode for character input. The Insert key toggles the mode. Note that the cursor changes shape for each mode.
    3
    Indicates the row and column at which the cursor is positioned. The first value represents the row, the second the column. In the image above, the cursor is positioned on the first row at the first character position.
    4
    Identifies the computer and the database to which the SQL document applies. The computer name is listed first followed by the database name. In the image above, the computer is "tment" and the database is "DEMODATA."
    Note: If None is specified as the database context, then the text "No Database Selected" appears instead of the name of the computer and database. The commands to execute SQL statements are disabled if None is specified. See To set database context for an SQL query .

To create an SQL query or script

By default, when you start SQL Editor, you may type in SQL statements. A script is one or more SQL statements saved as a text file.

  1. Perform the steps for To start SQL Editor for a new SQL query .
  2. Type the SQL statements into SQL Editor.

    3. Separate SQL statements with a delimiter. You can use the pound sign (#) or the semicolon (;).

  3. Optionally, click File 4 Save As to save the SQL statements as a text file.

To open an SQL script

A script is one or more SQL statements saved as a text file. You can execute the statements in SQL Editor after you open a script in the editor.

  1. Click File 4 Open.
  2. Navigate to the location of the text file, select the file, then click Open.

    3. By default, the Open dialog looks for files in the PVSW\bin directory with a file name extension of "SQL."

To select an SQL statement separator

  1. On the Window menu in PCC menu bar, click Preferences. Expand the "Pervasive" node if it is not already expanded (click the icon to the left of the node).
  2. Click SQL Editor.
  3. Click the desired choices for "SQL Statement Separator."

    4. Note
    Based on the separator option you select, PCC looks for the selected character(s) and identifies each as the end of a statement. It sends each identified statement to the database engine and displays results of that statement before sending the next statement.

    If you use # as a separator in a script but do not select the # (Pound) option, you will receive an error message when you run the script.

    If you do not select ; (Semicolon) as a separator, but use a semicolon as a separator in a script anyway, you will not receive an error message if the statements are properly parsed. This is because the database engine recognizes semicolons as separators. However, PCC will not display the results for all the statements. It will only display results for one statement (probably the first statement). As far as PCC is concerned, if you don't select a semicolon as a separator, statements separated by a semicolon are a single statement.

To type comments into SQL Editor

Single-line comments are indicated by double dashes (--) or double slashes (//). Each comment must be on a separate new line or after the statement separator on an existing line.

SQL Editor also supports the use of a start/end comment block that can span multiple lines (/* */).

  1. Click at the beginning of the line where you want a comment.
  2. Type "--" or "//" followed by your comment text.

    3. The following example shows valid comments.

    SELECT * FROM t1#

    -- This is a valid comment

    // and so is this

    SELECT * FROM t2# -- This is valid after the # sign

    The following example shows multi-line comments.

    SELECT * FROM t1# -- single line comment

    /* This is a comment block that spans two lines.

    Statements inside this block are ignored */

    SELECT * FROM t2#

To cancel (undo) or restore (redo) typing actions in SQL Editor

  1. Perform one of the following actions:
    1. Click Edit 4 Undo (or press Ctrl+Z) to cancel typing actions.
    2. Click Edit 4 Redo (or press Ctrl+Y) to restore typing actions.

To find text or replace text in SQL Editor

  1. Click Edit 4 Find/Replace (or press Ctrl+F).

    2. A dialog appears on which you specify a text string to find or replace.

To select text in SQL Editor

  1. Perform one of the following actions:
    1. Click Edit 4 Select All to select all of the contents of SQL Editor.
    2. Press and hold down the left mouse button and drag the cursor across the text you want to select.

To perform basic editing functions in SQL Editor

  1. Click Edit then click the function you want: cut, copy, paste, and so forth.

Statement Execution Tasks

To enable the execution commands and icons for SQL statements

  1. Follow the steps for To set database context for an SQL query .

To run a single SQL statement in SQL Editor

  1. Position the cursor on the statement or select the statement.
  2. Perform one of the following actions:
    1. Click SQL 4 Execute in Grid or SQL 4 Execute in Text.
    2. Press F9 or Shift+F9.
    3. Click or .
      4. Note
      SQL Editor automatically uses the Text window view for the results of SQL statements that are not SELECT statements. Only SELECT statements use the Grid window view.

To run selected SQL statements in SQL Editor

  1. Press and hold down the left mouse button and drag the cursor across the statement that you want to run.

    2. You may select one or more statements.

  2. Perform one of the following actions:
    1. Click SQL 4 Execute in Text or SQL 4 Execute All SQL Statements.
    2. Press Shift+F9.
    3. Click or .
      4. Note
      SQL Editor automatically uses the Text window view for the results of SQL statements that are not SELECT statements. Only SELECT statements use the Grid window view.

To run all SQL statements in SQL Editor

  1. Click SQL 4 Execute All SQL Statements, press F10, or click .

    2. Ensure that either no statements are selected or that all statements are selected. If you select a portion of the statements in SQL Editor, only the selected portion executes.

To run SQL statements in Outline view

  1. To execute all statements in Outline view, right-click on the root node then click Execute All Statements.

    2. To execute one or more statements, click on the desired statement(s).

    Note that you can select multiple statement by using Ctrl+click. The statements do not have to be contiguous.

  2. If multiple statements are selected, right-click on one of the selected statements, then click Execute Selected Statements.
  3. If a single statement is selected, perform one of the following actions:
    1. Right-click on the statement, then click Execute in Grid or Execute in Text.
    2. Press F9 or Shift+F9.
    3. Click or .
      4. Note
      SQL Editor automatically uses the Text view for the results of SQL statements that are not SELECT statements. Only SELECT statements use the Grid.

Grid Tasks

To change data within the Grid

  1. Click the Grid cell that contains the value you want to change.
Tip
By default, the entire contents becomes selected when you click the cell. Press Delete or Backspace to delete the entire contents of the cell.
  1. Change the data in the cell.
  2. Move the cursor outside of the cell (for instance, press Tab or click outside of the cell).
Caution
Moving the cursor from the cell automatically saves the data changes to physical storage. You cannot explicitly save the changes made to the cell.

To add rows of data to the Grid

  1. Click on the Grid.

    2. The Add Rows dialog appears. For example, the following image shows the dialog for the "Billing" table that is part of the sample database DEMODATA.



  2. Click in the Value cell for each Column Name and type the desired value.

    3. The value must be a data type valid for that column.



Tip
You can copy data from Grid cells and paste it into the Value cells. Click on a Grid cell then right-click. Click Copy. Click on a Value cell on the Add Rows dialog then right-click. Click Paste. Also note that Ctrl+C and Ctrl+V provide the copy and paste actions, respectively.
  1. Click Add.

    2. The record is added to the table. Also note that the option Refresh Grid on Exit becomes enabled.

    If you want to add multiple records, you can change values for specific value cells then click Add. If you want to clear all of the value cells, click Reset.

  2. Optionally, click Refresh Grid on Exit if you want the table data refreshed .



    3. When you close the Add Rows dialog, a refresh re-executes the statement last executed in SQL Editor.

  3. Click Close.

    4. If Refresh Grid on Exit is enabled, the Grid displays the record(s) that you just added (assuming that the last statement executed in SQL Editor was SELECT * FROM Billing).



To delete row(s) of data from the Grid

Caution
Deleting a row from the Grid removes that record from physical storage. No undo feature is available to reclaim the deleted record.
  1. Click any cell within the row (the record) that you want to delete.

    2. You may also select and delete multiple rows. To select multiple rows, press and hold down the Shift or Ctrl key, then click a cell in each desired row.

  2. Click Edit 4 Delete or click .
  3. Click OK to confirm the deletion.

To enter a date, time, or timestamp data type in the Grid using scalar functions

  1. As a convenience, you can type the following scalar functions for date, time, and timestamp in Grid cells:
    Data Type
    Scalar Function1 As Typed in Grid Cell
    Date
    • now()
    • curdate()
    See also NOW ( ) and CURDATE ( ) , both in SQL Engine Reference.
    Time
    • now()
    • curtime()
    See also NOW ( ) and CURTIME ( ) in SQL Engine Reference.
    Timestamp
    • now()
    See also NOW ( ) in SQL Engine Reference.
    1 The names are case insensitive. NOW() and now() are equivalent. The parentheses are required. That is, NOW is invalid but NOW() is valid.
    2. Note
    You can also omit the seconds for a time data type provided that you include "AM" or "PM." For example, 10:30 AM is a valid entry. Time defaults to "AM" if you omit "AM" or "PM." For example, 10:30:00 is entered as 10:30:00 AM.

To refresh data in the Grid

  1. Click on the Grid.

    2. A refresh re-executes the statement last executed in SQL Editor and sends the results to the Grid.

To copy data from the Grid

  1. Perform on of the following actions:
    1. To select the data for an individual cell, click in the cell, then click (or right-click and click Copy).
By default, the entire content of the cell is selected.
    1. To select an entire row, right-click on any cell then click Copy Text or click .
You may also select multiple rows. To select multiple rows, press and hold down the Shift or Ctrl key, then click a cell in each desired row.
Note
When you copy an entire row or multiple rows, the rows are pasted in the same layout as they appear in the Text window view. You can specify the number of characters between the pasted columns. Click Window then expand the Pervasive node in the Preferences tree. Click Text Output in the tree and set the desired value for Number of spaces between columns.

Text Window Tasks

To clear results from Text view

  1. Click on the Text view.

To select and copy text from Text view

  1. Perform one of the following actions:
    1. Press and hold down the left mouse button and drag the cursor across the text you want to select.
    2. Right-click within the Text view then click Select All.
  2. Right-click then click Copy.

Outline View Tasks

See also To run SQL statements in Outline view .

To minimize, maximize, or restore Outline view size

  1. Click the desired sizing icon:
    Icon
    Sizing Action

    Minimizes the Outline window

    Maximizes the Outline window

    Restores the Outline window to its size before it was minimized

Common SQL Objects Tasks

Common SQL objects include triggers, stored procedures, user-defined functions, and views.

To create a common SQL object

  1. In PCC Pervasive PSQL Explorer, expand the Engines node and the registered server nodes so that you can see the available databases (click the expand icon to the left of each node to expand the nodes).
  2. Right-click on the database for which you want the common SQL object to apply (or right-click on any of the database's subordinate nodes).
  3. Click New then one of the following depending on the object that you want to create:
    • Function
    • Stored procedure
    • Trigger
    • View

      • A new SQL Editor is opened that contains a default name for the object on a tab. The name is in the form object_n, where object is the name of the object and n is an integer that starts with one and increments by one. For example, if you create a new view, a new SQL Editor contains a tab named "View_1." After you save the object with a name of your choice, the tab reflects the saved name.

  4. Modify the skeletal SQL statement for the common object.
Tip
Hover the mouse cursor on the statement to obtain a tool tip on the syntax, which also includes an example.
  1. Click File 4 Save or .

To modify a common SQL object

  1. In PCC Pervasive PSQL Explorer, click the database in the list for which you want to modify the common SQL object (click the expand icon to the left of each node to expand the nodes).
  2. Expand the node for the object that you want to modify (click the expand icon to expand the node for function, stored procedures, user-defined functions, or triggers).
  3. Double-click the object that you want to modify or right-click the object then click Edit.

    4. A new SQL Editor is opened that contains a tab. The tab name reflects the name by which you saved the object.

To delete a common SQL object

  1. In PCC Pervasive PSQL Explorer, click the database in the list for which you want to delete the common SQL object (click the expand icon to the left of each node to expand the node).
  2. Expand the node for the object that you want to delete (click the expand icon to expand the node for function, stored procedures, user-defined functions, or triggers).
  3. Click the object that you want to delete, then perform one of the following actions:
    1. Right-click, then click Delete.
    2. Press Delete.
    3. Click on Pervasive PSQL Explorer.

      4. Note that you can select multiple objects for deletion by using Ctrl+click or Shift+click.

Chapter contents
Book contents

Prev topic: SQL Editor Used in SQL View Tab of Table Editor
Next topic: Pervasive System Analyzer (PSA)