SQL Editor Tasks
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 PSQL Explorer to see the change. Right-click the Tables node in PSQL Explorer then click Refresh.
General Tasks
General tasks orient you to the overall use of SQL Editor.
•
To start SQL Editor for a new SQL query
•
To start SQL Editor by displaying all records in a table
•
To set database context for an SQL query
•
To identify editor settings for SQL Editor
•
To create an SQL query or script
•
To open an SQL script
•
To type comments into SQL Editor
•
To cancel (undo) or restore (redo) typing actions in SQL Editor
•
To find text or replace text in SQL Editor
•
To select text in SQL Editor
•
To perform basic editing functions in SQL Editor
Execution Tasks
Execution tasks apply to the running of SQL statements.
•
To enable the execution commands and icons for SQL statements
•
To run a single SQL statement in SQL Editor
•
To run selected SQL statements in SQL Editor
•
To run all SQL statements in SQL Editor
•
To run SQL statements in Outline view
Grid Tasks
Grid tasks apply to working with the Grid window.
•
To change data within the Grid
•
To add rows of data to the Grid
•
To delete row(s) of data from the Grid
•
To enter a date, time, or timestamp data type in the Grid using scalar functions
•
To refresh data in the Grid
•
To copy data from the Grid
Text View Tasks
Text view tasks apply to working with the Text window.
•
To clear results from Text view
•
To select and copy text from Text view
Outline View Tasks
Outline view tasks apply to working with the Outline window.
•
To minimize, maximize, or restore Outline view size
Common SQL Object Tasks
Common SQL Object tasks apply to working with triggers, stored procedures, user-defined functions, and views.
•
To create a common SQL object
•
To modify a common SQL object
•
To delete a common SQL object
General Tasks
►
To start SQL Editor for a new SQL query
1
Start PCC if it is not already running. (See Starting PCC on Windows.)
2
Click File > New > SQL Document or click .
The Select Database dialog box appears.
3
Click the database in the list for which you want the SQL document to apply, or ensure that the option None is check marked if the SQL document does not apply to a specific database.
*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 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.

Select the Set selected database as default for this session option to use the selected database whenever you open a new SQL Editor tab. If you leave this option unselected, you will be prompted to select a database each time you open a new SQL Editor tab.
4
Click OK.
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.
5
To start SQL Editor by displaying all records in a table
1
Start PCC if it is not already running. (See Starting PCC on Windows.)
2
Expand the Engines and Databases nodes in PSQL Explorer.
3
For the desired database, expand the Tables node.
4
Double-click the table for which you want to see all records (or right-click the table then click Open).
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
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 on Windows.)
2
Perform one of the following actions:
•
For a new SQL statement, click File > New > SQL Document or click .
The Select Database dialog appears. Click the database in the list for which you want the SQL document to apply and click OK.
•
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.
3
Click OK.
*Note: Select the Set selected database as default for this session option to use the selected database whenever you open a new SQL Editor tab. If you leave this option unselected, you will be prompted to select a database each time you open a new SQL Editor tab.

To unselect the default database:
Access the Select Database dialog by clicking , and unselect the Set selected database as default for this session option.
or
Click Windows > Preferences then click the General node. Unselect Do not prompt for new database each time a SQL document is opened.

The selected database is not maintained across PCC sessions. If you close and reopen PCC, you will have to select a new default database context.
►
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.
 
 
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.
Separate SQL statements with a delimiter. You can use the pound sign (#) or the semicolon (;).
3
Optionally, click File > 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 > Open.
2
Navigate to the location of the text file, select the file, then click Open.
By default, the Open dialog looks for files in the PVSW\bin directory with a file name extension of “SQL.”
3
Execute the file. See To run all SQL statements in SQL Editor and To run SQL statements in Outline view.
►
To select an SQL statement separator
1
On the PCC Window menu, click Preferences. Expand the PSQL node if it is not already expanded.
2
Click SQL Editor.
3
Select the desired choices for SQL Statement Separator.
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.
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:
•
Click Edit > Undo (or press Ctrl+Z) to cancel typing actions.
•
Click Edit > Redo (or press Ctrl+Y) to restore typing actions.
►
To find text or replace text in SQL Editor
1
Click Edit > Find/Replace (or press Ctrl+F).
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:
•
Click Edit > Select All to select all of the contents of SQL Editor.
•
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:
•
Click SQL > Execute in Grid or SQL > Execute in Text.
•
Press F9 or Shift+F9.
•
Click or .
*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.
You may select one or more statements.
2
Perform one of the following actions:
•
Click SQL > Execute in Text or SQL > Execute All SQL Statements.
•
Press Shift+F9.
•
Click or .
*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 > Execute All SQL Statements, press F10, or click .
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 the root node then click Execute All Statements.
To execute one or more statements, click the desired statements.
Note that you can select multiple statements by using Ctrl+click. The statements do not have to be contiguous.
2
If multiple statements are selected, right-click one of the selected statements, then click Execute Selected Statements.
3
If a single statement is selected, perform one of the following actions:
•
Right-click the statement, then click Execute in Grid or Execute in Text.
•
Press F9 or Shift+F9.
•
Click or .
*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.
2
Change the data in the cell.
3
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.
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.
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 a Grid cell then right-click. Click Copy. Click 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.
3
Click Add.
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.
4
Optionally, click Refresh Grid on Exit if you want the table data refreshed .
When you close the Add Rows dialog, a refresh re-executes the statement last executed in SQL Editor.
5
Click Close.
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.
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 > 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.
*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.
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:
•
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.
•
To select an entire row, right-click 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 PSQL 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:
•
Press and hold down the left mouse button and drag the cursor across the text you want to select.
•
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 button:
Button
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 PSQL Explorer, expand the Engines node and the registered server nodes to display the available databases.
2
Right-click the database for which you want the common SQL object to apply (or right-click 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.
5
Click File > Save or .
►
To modify a common SQL object
1
In PCC PSQL Explorer, click the database in the list for which you want to modify the common SQL object.
2
Expand the node for the object (function, stored procedures, user-defined functions, or triggers) that you want to modify.
3
Double-click the object that you want to modify or right-click the object then click Edit.
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 PSQL Explorer, click the database in the list for which you want to delete the common SQL object.
2
Expand the node for the object (function, stored procedures, user-defined functions, or triggers) that you want to delete.
3
Click the object that you want to delete, then perform one of the following actions:
•
Right-click, then click Delete.
•
Press Delete.
•
Click on PSQL Explorer.
Note that you can select multiple objects for deletion by using Ctrl+click or Shift+click.