Common SQL Object tasks apply to working with triggers, stored procedures, user-defined functions, and views.
“To create a common SQL object” on page 6-26
“To modify a common SQL object” on page 6-27
“To delete a common SQL object” on page 6-27
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” on page 3-3.)
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 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” on page 6-14.
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.
³ 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” on page 3-3.)
2 Expand the Engines and Databases nodes in Pervasive 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” on page 3-3.)
2 Perform one of the following actions:
a. 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.
b. 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.
³ 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”
on page 6-13.
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.
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” on page 6-14.
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”
on page 6-20 and “To run SQL statements in Outline view” on page 6-20.
³ To select an SQL statement separator
1 On the PCC Window menu, click Preferences. Expand the Pervasive 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:
a. Click Edit Undo (or press Ctrl+Z) to cancel typing actions.
b. 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.