SQL Editor Concepts

SQL Editor Concepts

This section contains the following topics:

•

SQL Editor Concepts

•

Working with Common SQL Objects

•

SQL Editor Used in SQL View Tab of Table Editor

•

SQL Editor Tasks

SQL Editor is one of the editors within PSQL Control Center (PCC). The editor allows you to run Structured Query Language (SQL) statements against a PSQL database. With SQL statements, you may retrieve, create, change, or delete data in a database provided you have the proper database permissions to perform these actions.

The SQL statements that you may use with SQL Editor are documented in SQL Engine Reference. See especially SQL Grammar in PSQL.

Figure 22 SQL Editor

Caution: Backup all your data definition files (DDFs) and data files before you perform functions through SQL Editor. This tool gives you the ability to modify your database table definitions and data. You could inadvertently change your files in an irreversible manner. Full recovery is possible if you have performed a backup.

Statement Separators

SQL Editor requires a way to differentiate where one statement ends and another begins. The way to differentiate statements is to place a statement separator at the end of each statement. SQL Editor accepts only the pound sign (#) and the semicolon (;) as statement separators. See To select an SQL statement separator.

If you are not using temporary tables, you may use either separator solely or mix their usage within a set of SQL statements. That is, some statements can end with a pound sign and others with a semicolon if you so choose.

Temporary tables begin with “#” or “##.” If you use temporary tables, the pound sign may not be used as a statement separator. Instead, set the statement separator to the semicolon.

The following actions and SQL statements are not supported in SQL Editor:

•

Creating a database

•

Use of dynamic parameters (indicated by a question mark)

•

COMMIT and START TRANSACTION

•

In PCC SQL Editor in v12, you cannot edit views, stored procedures, user-defined functions, or triggers that contain characters outside of the database code page. You must first drop them and then reenter them from an edited source file.

Displaying Statement Results

SQL Editor displays the results of running SQL statements in the following PCC window views:

•

•

In addition, by default, an Outline window view displays a list of the SQL statements in SQL Editor, typically with a shorter line length. For example, you may want the Outline view to show only the first 5 words of each SQL statement in SQL Editor. To set preferences for SQL Editor, click SQL Editor..

Grid Window View

PCC provides a command to execute an individual SQL statement in SQL Editor so that the results appear in the Grid view. The command is called Execute in Grid and can be invoked from the SQL menu, from a toolbar button, or from within the Outline view.

Note: The Grid is also used by Table Editor to show table data when you start Table Editor. That is, when you right-click a table then click Edit. See To view table data.

Identifying the Grid Window

The Grid window view, or Grid for short, shows the columns and data in a table. The Grid uses a matrix format, like a spreadsheet, to show the result of running SQL SELECT statements (statements that return data).

Figure 23 Grid Window View

Modifying Data and Adding Rows

The Grid allows you to directly change database data by changing the values in the grid cells. You can also add rows to your table with the Grid. You must have the proper table permissions to affect the database data.

See To set preferences for PCC Window Views, Grid Tasks, and Assigning Permissions Tasks.

Records Affected and Scrolling

The Grid caches results locally and initially retrieves 100 records. The Grid displays as many records as its vertical size permits.

As you scroll the vertical scroll bar, more records are retrieved and made available to the Grid. The number of records retrieved appears in the lower right corner of the main window.

Once you scroll to the bottom, the rows fetched indicator reports the total number of records returned by the SQL statement.

Text Window View

PCC provides a command to execute an individual SQL statement in SQL Editor so that the results appear in the Text view. The command is called Execute in Text and can be invoked from the SQL menu or from a toolbar button.

The Text window view is automatically used for the results of any SQL statement that is not a SELECT statement. For example, suppose you want to delete some records and have typed a DELETE statement into SQL Editor. If you select the menu command Execute in Grid, SQL Editor returns the results to the Text window view, not to the Grid window view.

A command also exists to execute all statements sequentially in the SQL Editor. The command is called Execute All SQL Statements and can be invoked from the SQL menu, from a toolbar button, or from within the Outline view. Results from this command always display in the Text view regardless of the statements in SQL Editor.

Identifying the Text Window

The Text window view shows in a text format the result of running SQL statements. You cannot change the data values in the database by changing the text, but you can copy the text.

You may use the Text window view for SELECT statements to show data returned. The data returned appears in a columnar format with each field represented as an underlined heading. The data appears as rows below the headings.

For how to change the font used by the Text window, see To set preferences for Text Output, click Text Output..

For PCC running on an operating system set to an English language locale, the system selects a default font. For non-English locales, PCC seeks to match the “default font” or “system font” if such can be found. Otherwise it will select reasonable font.

Figure 24 Text Window View

If execution stops because of an error, the Text window view lists the statement that was last run. Knowing the last statement run can help you troubleshoot problems.

Scrolling and Positioning

As a convenience, the Text window view automatically scrolls to the top line of the data returned by the last statement executed. For example, suppose that you execute the following two statements sequentially in SQL Editor, each time sending the results to the Text window view: SELECT * FROM Class and SELECT * FROM Billing.

The Text window view automatically scrolls to the top of the data returned by SELECT * FROM Billing, the last statement executed.

Outline Window View

The Outline window view allows you to view the SQL statements in a tree structure. The root node of the tree is the same name as the name of the SQL Editor session to which the outline corresponds.

Figure 25 Outline Window View

The number of words displayed in the Outline window view depends on your preferences setting. To set preferences for SQL Editor, click SQL Editor..

You can also execute statements from the Outline window view. The Outline view allows you to select multiple statements to execute (with Ctrl+click). For example, if your Outline view shows three statements as in the figure above, you may choose to execute statements 1 and 3 but not 2. See To run SQL statements in Outline view.