Pervasive logo

Prev User's Guide Next

SQL Data Manager Tasks

This section explains the tasks that you perform with SQL DM. The tasks are divided into the following categories:
Category
Description
General Tasks
Orient you to the overall use of SQL DM
Query Pane Tasks
Help you use Query Pane
Execution and Results Tasks
Apply to the running of SQL statements and working with the results
Query Builder Tasks
Orient you to the use of Query Builder Diagram and Query Builder Grid

What to do first? If you are new to SQL DM, begin with the general tasks. If you are new to SQL programming, begin with the tasks for Query Builder. If you are experienced with SQL programming, begin with the tasks for Query Pane.

General Tasks

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

Query Pane Tasks

Query Pane tasks help you use Query Pane.

Execution and Results Tasks

Execution and results tasks apply to the running of SQL statements and working with results on the Grid Results tab and the Text Results tab.

Query Builder Tasks

Query Builder Diagram tasks orient you to the use of Query Builder Diagram and Query Builder Grid.

General Tasks

To start SQL DM as a stand-alone application

  1. From the desktop, click Start4Programs4 Pervasive.SQL V84 SQL Data Manager.

    This step assumes that SQL DM was installed as part of a Pervasive.SQL default installation. A vendor application that uses an embedded database engine may require that you start SQL DM with different menu commands. (The GUI executable is named sqldmgr.exe.)

    SQL DM starts by providing a dialog in which you choose a database engine and database name to connect to. By default, the local database engine (on the same computer as SQL DM) is specified.

  2. Click the desired database engine and database to open.
  3. Click OK.

Note
SQL DM opens a separate instance for each database. This means that you can open multiple instances of SQL DM for the same database. Changes saved in one instance are reflected in another instance only if you run a query (or queries) in the other instance. For example, suppose you start SQL DM twice (called instance A and B) for database MyDBase. If you change data in instance A and save the changes, you need to run a SELECT statement in instance B to see those changes.

To start SQL DM from PCC

  1. Start PCC if it is not already running. (Click Start4Programs4Pervasive.SQL V84Control Center.)
  2. Expand the tree in the namespace (click the plus signs) until the Tables node appears for the database in which you are interested.
  3. In the PCC data pane, right-click on a table icon, then click Open. You may also double click the table icon.

SQL DM starts and runs a Select * From table_name statement, where table_name is the name of the table icon. The results are placed into the Grid Results tab.

Note
SQL DM opens a separate instance for each table. This means that you can open multiple instances of SQL DM from PCC for the same table. Changes saved in one instance are reflected in another instance only if you run a query (or queries) in the other instance. For example, suppose you open SQL DM twice (called instance A and B) for table MyTable. If you change data in the grid in instance A and save the changes, you need to run a SELECT statement in instance B to see those changes.

To identify machine and database currently open

  1. Observe the SQL DM title bar.

    The title bar shows the name of the open database and the name of the machine on which the database resides.

    For example, the following image shows that DEMODATA is the open database and DOCLAB2 is the machine on which DEMODATA resides.

    If the title bar does not show the name of a database and the name of a machine, then no database is open.

To open a database

  1. Click File4Open Database.

    The Login to database dialog appears.

    If the desired database has Pervasive.SQL security enabled, type the user name and password required for the database in the User and Password fields, respectively. By default, the user name is Master. If the database does not have security enabled, you may leave the User and Password fields blank.

  2. Click a desired server in the Server list.
  3. Click a desired database in the Database list.
  4. Click OK.

To create an SQL query or script

By default, when you start SQL DM, Query Pane is ready for you type in SQL statements. Statements that you build with Query Builder also appear in Query Pane. Note that Query Builder clears the contents of Query Pane, so you will lose any statements typed in unless you have saved them as a script. A script is one or more SQL statements saved as an ASCII file. See To save an SQL query as a script .

  1. Perform one of the following actions:
    1. Type the SQL query into Query Pane.

      Separate SQL statements with a delimiter. By default, SQL DM uses the pound sign (#). You may change this delimiter character to the semi-colon (;). See To set SQL statement separator .

      Note that you must use the pound sign as the statement delimiter in Query Pane if you create stored procedures. Stored procedures use the semicolon as a statement delimiter within the procedure itself.

    2. Click File4New SQL Query or click .

      If Query Pane contains statements that have not been saved, you are prompted to save them. Click Yes to save the existing statements, specify a file name and location, then click Save. Click No to discard the existing statements.

      Any SQL statements in Query Pane are cleared and the statement indicator is positioned at the top left of the pane. The indicator is the yellow arrow .

    3. Use Query Builder to build a statement. See Query Builder Tasks .

      If execution stops because of an error, the statement indicator positions to the statement that was being run when the interruption occurred. Also, the Text Results tab lists the statement that was last run. Knowing the last statement run can help you troubleshoot the problem. See To continue running SQL statements that were stopped .

To close a database

  1. Click File4Close Database.
  2. Click Yes to confirm that you want to close the database.

    Note that the title bar no longer shows the name of an open database or the name of a machine on which the database resides. In addition, the commands and icons for running a query become inactive (grayed out). You can run a query only if a database is open.

To change data with Grid Results grid

The Grid Results grid must be active before you can change values in cells. See To set Grid Results to allow changes to database data .

Note
If you have a table without primary keys that contains records with duplicate data for a column, you will be unable to update the duplicate data on the Grid Results tab. SQL DM cannot tell one record from another because of the duplicate data. This problem does not occur if your table contains primary keys because they prevent duplicate data. (You may use an UPDATE statement to change duplicate data.)

  1. Click the Grid Results tab.
  2. Populate the grid by running a SELECT statement in Query Pane. Click Tools4Query4Run Current SQL statement in grid or click .

    After the grid has been populated by a SELECT statement, you may directly update the table by making changes to the values in the active grid. You must have the proper table permissions to affect the database data.

  3. Change the data in one or more grid cells for a particular row.

    An icon that indicates a changed value appears in the leftmost column of the row:

  4. Perform an action in SQL DM that causes the changed data to be written to the database. The following actions result in the data being changed:
    • Clicking the leftmost column of the row containing the changed data (clicking the icon for the row)
    • Moving to a grid row or a cell different from the one containing the changed data (you may move with the Tab key, arrow keys, or by using the mouse)
    • Running a SELECT statement
    • Closing the database (File4Close Database)
    • Exiting SQL DM (File4Exit).

Note  You may see the row in which you change a value move to a different location within the grid. An example helps clarify why this occurs.

Suppose you have a table, t1, with a single integer column, col1, that contains the values 1 through 5 in ascending order. If you run a SELECT * statement against t1, SQL DM returns 5 records. Now assume you delete record 3 (DELETE FROM t1 WHERE col1=3). Your table now has a "hole" in it where record 3 existed. If you run a SELECT * statement again, SQL DM returns 4 records (1, 2, 4, and 5).

Now assume you add another value for col1 at the bottom of the Grid Results tab (you type in "6" at the bottom of the integer column and press the Tab key). The record containing the value "6" moves in the grid to the 3rd position from the top. In other words, the new record occupies the "hole" that resulted from the deletion of the other record.

This is the normal behavior of the Grid Results tab. The SRDE fills any "holes" in a table that have resulted from deleting records. SQL DM just queries the database and displays the natural ordering of the records. Once all of the "holes" are filled, records are added to the end of the existing records.

To save an SQL query as a script

  1. Perform one of the following actions:
    1. Click File4Save SQL Query or click .

      If you do not have an SQL script already open, a Save As dialog appears. SQL DM assigns a default name to the query. The default name is queryn.sql, where n is an integer that increments by one. Query1.sql is the first default name, query2.sql the second, and so forth.

      If you have an SQL script already open, a Save As dialog does not appear. SQL DM saves the SQL statements in Query Pane with the name of the open script, even if all lines from the open script have been cleared and new lines typed. For example, suppose you open MySQLScript.sql then click File4New SQL Query. Query Pane is cleared and the statement indicator is positioned to the top of the pane. You type an SQL statement then click File4Save SQL Query. The SQL statement is saved as file MySQLScript.sql.

    2. Click File4Save SQL Query As.

      A Save As dialog appears. If you have an SQL script open, this option lets you save the query with a different file name.

To open an SQL script

  1. Click File4Open SQL Query or click .

    If Query Pane contains statements that have not been saved, you are prompted to save them. Click Yes to save the existing statements, specify a file name and location, then click Save. Click No to discard the existing statements.

  2. Type in the file name of the query you want to open in the File name field, or navigate to the location of the file and click on the file.

    An SQL query file is an ASCII file of one or more SQL statements. By default, an SQL query files saved from SQL DM has a file extension of "sql."

  3. Click Open.

    SQL DM places the contents of the open script into Query Pane. Note that if you change the script then click File4Save SQL Query or click , the changes overwrite the script file. If you want to save the changes to a different location or as a different script name, click File4Save SQL Query As.

To display help

  1. Click Help, then the type of help you want:
    • How Do I? - Lists the tasks that you can perform with SQL DM..
    • Topics - Lists the documentation topics pertaining to SQL DM.
    • SQL Reference - Displays an alphabetical list of SQL statements and keywords that are links to the SQL Engine Reference. (Statements not supported by SQL DM are listed below the alphabetical list.)
    • Search Documentation - Searches for a string throughout the installed set of Pervasive.SQL documentation.
    • What's This? - Identifies objects in the SQL DM GUI.
    • Glossary - Defines terms used in the Pervasive.SQL documentation and products.
    • Online Manuals (Web) - Directs your browser to www.pervasive.com where you can reference the online manuals.
    • Knowledge Base (Web) - Directs your browser to www.pervasive.com where you can query the Pervasive.SQL Knowledge Base for additional information about Pervasive.SQL.
    • Developer Center (Web) - Directs your browser to www.pervasive.com where you can research information specifically for application developers using Pervasive.SQL.
    • About - Displays the name and version of SQL DM and the Pervasive.SQL copyright information.

To exit SQL DM

  1. Click File4Exit. (You may also click the "X" close button in the upper right corner of the GUI.)

    If Query Pane contains statements that have not been saved, you are prompted to save them. Click Yes to save the existing statements, specify a file name and location, then click Save. Click No to discard the existing statements.

Query Pane Tasks

To show or hide Query Pane

  1. Perform one of the following actions:
    1. Click View4Query Pane.
    2. Click .

      These actions function as a toggle. That is, if Query Pane is hidden, the action shows it, and vice versa. A database must be open to enable the command or icon.

To move the cursor within Query Pane without running any SQL statements

The statement indicator, the yellow arrow in the left margin of Query Pane, indicates the statement in which the cursor is located.A statement delimiter is used to separate statements. See To set SQL statement separator .

  1. Perform one of the following actions to move the cursor in Query Pane:
    1. Click on the statement you to which you want to move the cursor.
    2. Use the keyboard arrow keys to move the cursor to the desired statement.

To move the cursor within Query Pane and run the SQL statement to which it moves

The statement indicator, the yellow arrow in the left margin of Query Pane, indicates the statement in which the cursor is located.A statement delimiter is used to separate statements. See To set SQL statement separator .

  1. Perform one of the following actions to move the cursor in Query Pane:
    1. Click to move the cursor to the first statement and run that statement.

    1. Click to move the cursor to the previous statement and run that statement.

    1. Click to move the cursor to the next statement and run that statement.

    1. Click to move the cursor to the last statement and run that statement.

Note  SQL DM shows the appropriate results tab as you move through the statements. Only SELECT statements send results to the Grid Results tab (you may also send SELECT results to the Text Results tab). All other statements send results to the Text Results tab.

For example, suppose you have the following lines in Query Pane:

SELECT * FROM Person#
SELECT * FROM Faculty#
DELETE FROM Person WHERE Person.ID=104321686#
SELECT * FROM Class#

Assume that the cursor is located on the first line and you click to move to the next line, which contains a SELECT statement. The results show on the Grid Results tab. You click again to move to the next line, which contains a DELETE statement. The results show on the Text Results tab. You click again to move to the next line, which contains another SELECT statement. The results show on the Grid Results tab.

See also Results Tabs and To type comments into Query Pane .

To set SQL statement separator

Two or more SQL statements in Query Pane must be separated by a delimiter. By default, SQL DM sets the delimiter to the pound sign (#). You may change the delimiter to the semicolon (;) if you choose.

Note
You must use the pound sign as the statement delimiter in Query Pane if you create stored procedures. Stored procedures use the semicolon as a statement delimiter within the procedure itself.

  1. Click Tools4Properties.
  2. Click the semicolon (;) in the list for SQL statements separator.
  3. Click OK.

To set a query time-out period

The query time-out period specifies how many seconds to allow an SQL statement to run before the SRDE cancels the statement. The default is zero seconds, which means that no time-out period is specified. The value you specify is stored in the registry and will also apply the next time you start SQL DM.

The time-out period applies to SELECT, UPDATE, INSERT, and DELETE statements, but not to Data Definition Language (DDL) statements (such as CREATE INDEX, ALTER TABLE, DROP TABLE, and so forth). The SRDE stops processing a query after the specified period, returns SQL_ERROR, and sets SQLSTATE to "S1T00." Because SQL statements are atomic, no records are updated, inserted, or deleted if the SRDE cancels an UPDATE, INSERT, or DELETE statement, respectively.

  1. Click Tools4Properties.
  2. Type a value for Query time-out.

    The allowed range is 0 through 4,294,967,295. Partial seconds (for example, 30.5) and negative numbers are not allowed.

  3. Click OK.

To wrap text within Query Pane

By default, word wrap is turned off, which is indicated by lack of a checkmark beside the command.

  1. Perform one of the following actions:
    1. Right-click within Query Pane then click Word Wrap (or press Control+W).
    2. Click View4Word Wrap.

      Note that these actions function as a toggle (alternately activates and deactivates word wrap).

To type comments into Query Pane

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. See To set SQL statement separator .

SQL DM 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#

Note  If the cursor is positioned after the statement separator, the next statement is current. This is true even if the statement indicator, the yellow arrow , is still on the line containing the cursor.

An example helps clarify this. Suppose you have the following lines in Query Pane:

SELECT * FROM Person# -- This is a comment
SELECT * FROM Faculty#
SELECT * FROM Class#

You run the first statement, then position the cursor in front of the "t" in "This." The cursor is positioned after the statement separator (#) on that line. The statement indicator still points to the line SELECT * FROM Person, but the actual current line is SELECT * FROM Faculty. If you click to move to (and run) the next line, the statement indicator moves to (and runs) SELECT * FROM Class.

To cancel (undo) typing actions in Query Pane

  1. Perform one of the following actions:
    1. Click Edit4Undo (or press Control+Z) for each typing action that you wish to cancel.

      For example, if you have typed "Select * From Person" and you click Edit4Undo, the word "Person" is removed. If you click Edit4Undo again, the word "From" is removed, and so forth.

    2. Right-click within Query Pane, then click Undo (or press Control+Z).

To cancel an Undo action (redo) in Query Pane

The Redo action performs the opposite of the last Undo action. For example, if the last Undo action removed the word "Select" from a statement, Redo restores the word "Select" to the statement.

  1. Right-click within Query Pane, then click Redo (or press Control+Y) for each Undo action that you want to cancel.

To find text in Query Pane

  1. Click Edit4Find (or press Control+F).

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

  2. Type, or paste, a text string into the Find what field.
  3. Specify any options for the search, such as case sensitivity and direction.
  4. Click Find Next.
  5. To repeat the search for the same string, click Edit4Find Next (or press F3).

To replace text in Query Pane

  1. Click Edit4Replace (or press Control+H).

    A dialog appears in which you specify a text string to search for and a text string with which to replace it.

  2. Type, or paste, a text string into the Find what field.
  3. Type, or paste, a text string into the Replace with field.
  4. Specify any options for the search, such as case sensitivity and direction.
  5. Perform one of the following actions:
    1. Click Find Next to locate the next occurrence of the search string, then click Replace to change the search string to the replace string. Repeat these actions as desired.
    2. Click Replace All to change all occurrences of the search string to the replace string.
  6. Click Cancel to exit the dialog.

To select text in Query Pane

  1. Perform one of the following actions:
    1. Click Edit4Select All, right-click within Query Pane then click Select All, or press Control+A. Any of these actions selects all of the contents of Query Pane.
    2. Press and hold down the left mouse button and drag the cursor across the text you want to select.
    3. Press and hold down the Shift key, then press a cursor arrow key to move the cursor across the text you want to select.

To copy text from Query Pane

  1. Perform one of the following actions:
    1. Click Edit4Copy (or press Control+C).
    2. Right-click on the selected text then click Copy.

      Copied text is placed in the clipboard.

To paste within Query Pane

  1. Perform one of the following actions:
    1. Click Edit4Paste (or press Control+V).
    2. Right-click within Query Pane then click Paste.

      Text from the clipboard is placed into Query Pane.

To cut or delete text from Query Pane

Text cut from Query Pane is placed in the clipboard and is available for pasting. Text deleted from Query Pane is not placed in the clipboard.

  1. Perform one of the following actions:
    1. To cut text, click Edit4Cut, right-click within Query Pane then click Cut, or press Control+X.
    2. To delete text, press the Delete key.

To print the contents of Query Pane

  1. Click File4Print.

    A dialog appears on which you specify the options for printing.

  2. Specify any options for printing, such as printer name, printer range, and so forth.
  3. Click OK.

To layout the printed page

  1. Click File4Page Setup.

    A dialog appears on which you specify the layout for the printed page.

  2. Specify any options for the printed page, such as paper size, margins, and so forth.
  3. Click OK.

To toggle insert/overtype mode

  1. Press the Insert key to toggle the text entry mode between insert and overtype.

    Insert mode adds characters between other characters as you type in Query Pane. Existing characters are pushed to the right. Overtype mode replaces characters as you type.

    The default mode is insert.

    When the Insert/Overtype indicator shows OVR in a dimmed typeface, SQL DM is in insert mode. When OVR is boldface, SQL DM is in overtype mode.
    Insert Mode
    Overtype Mode

To set the font for Query Pane

Setting the font affects the font used in the Query Pane, the Grid Results tab, and the Text Results tab.

  1. Click View4Set Font.
  2. Specify the desired font characteristics, such as style and size.
  3. Click OK.

Execution and Results Tasks

To run a single SQL statement

A database must be open and one or more SQL statements must exist in Query Pane before you can run a statement.

  1. Move to the SQL statement you want to run. See To move the cursor within Query Pane without running any SQL statements .
  2. Perform one of the following actions:
    1. Click Tools4Query4Run Current SQL statement in grid or click .
    2. Click Tools4Query4Run Current SQL statement in text or click .

Note
SQL DM automatically uses the Text Results tab for the results of SQL statements that are not SELECT statements. Only SELECT statements use the Grid Results tab.

To run all SQL statements

A database must be open and one or more SQL statements must exist in Query Pane before you can run all statements. Two or more SQL statements in Query Pane must be delimited. See To set SQL statement separator .

  1. Click Tools4Query4Run All SQL Statement or click .

To stop running SQL statements

  1. Click Tools4Query4Stop or click .

    The statement indicator positions to the statement that was being run when the interruption occurred. The indicator is the yellow arrow .

    If you stop the running of SQL statements, or if execution stops because of an error, the Text Results tab lists the statement that was last run. Knowing the last statement run can help you troubleshoot problems.

To continue running SQL statements that were stopped

  1. Click Tools4Query4Continue.

    Execution of the SQL statements continues with the statement that contains the statement indicator. Note that you may move to a statement after execution stops. For example, suppose execution stops at statement 50 in a script that contains 100 statements. You could click on statement 60 and continue execution from there.

    Unless you move the statement indicator, execution continues with the next statement following the one that last ran.

To set the font for grid results or text results

Setting the font affects the font used in the Query Pane, the Grid Results tab, and the Text Results tab.

  1. Perform one of the following actions:
    1. Click View4Set Font
    2. Right-click within the Grid Results tab or the Text Results tab then click Font.
  2. Specify the desired font characteristics, such as style and size.
  3. Click OK.

To work with grid results

  1. Click the Grid Results tab.

    After the grid has been populated by a SELECT statement, you may directly update your database data by changing the values in an active grid. You must have the proper table permissions to affect the database data. The grid must be active before you can change values in cells. See To set Grid Results to allow changes to database data .

To set Grid Results to allow changes to database data

Note  A grid that allows changes to database data is referred to as "active." This means that if you change data in one of the grid cells, you change the data in your database provided you have the proper table permissions. By default, SQL DM starts with the Grid Results grid active.

See also To set default behavior for Grid Results tab .

  1. Click Tools4Active Grid.

    A checkmark beside the Active Grid command indicates that the grid is active. In addition, an indicator column appears as the leftmost column in the grid:

    The indicator column contains no column label.

To set default behavior for Grid Results tab

Note  A grid that allows changes to database data is referred to as "active." This means that if you change data in one of the grid cells, you change the data in your database provided you have the proper table permissions. By default, SQL DM starts with the Grid Results grid active.

See also To set default behavior for Grid Results tab .

  1. Click Tools4Properties.
  2. Click the option Active grid mode on startup to set or clear the default behavior.

    A checkmark beside the option indicates that the grid will be active each time SQL DM is started.

  3. Click OK.

To delete a row(s) from Grid Results tab

Note that the delete action has no undo. The grid must be active to allow deletion of rows. See To set Grid Results to allow changes to database data .

  1. Right-click on the row (the record) that you want to delete, then click Delete Row(s).

    You may also select and delete multiple rows. To select multiple rows, press and hold down the Shift or Control key, then click the indicator column for the desired rows. Once the desired rows are selected, right-click and click Delete Row(s).

    The indicator column is the leftmost column in the grid:

    Once a row(s) is selected, you may also press the Delete key to delete the row(s).

    Note that you cannot delete a row that contains invalid values in one or more grid columns. For example, assume that you want to delete a record from the "Person" table provided with the sample database DEMODATA.

    You run a SELECT * FROM person statement in Query Pane. You click on an ID cell in the grid and remove the ID value with the Backspace key. You then decide that you want to delete the entire record. The Delete Row(s) command does not work because the ID column contains an invalid value (it is blank). The ID column in the "Person" table does not allow NULLs. You would first need to add a valid value into the ID cell, then delete the entire record.

To copy data from Grid Results tab to clipboard

Copied data is placed in the clipboard in a tab delimited format.

  1. Perform one of the following actions to select the desired data:
    1. Click the indicator column for the row(s) that you want to copy. The indicator column is the leftmost column in the grid:

      To select multiple rows, press and hold down the left mouse button. Move the mouse pointer across the column indicator(s) for the row(s) you wish to select. You may also press and hold down the Shift or Control key, then click the indicator column for the desired row(s).

    2. Right-click on a cell (to set a starting place), then press and hold down the left mouse button. Move the mouse pointer across the cell(s) you wish to select.
    3. Left-click within a cell, then press and hold down the Shift key. Use the cursor arrow keys to extend the selection.
    4. Right-click on a single cell to select the contents of only that cell. Ensure that no other rows or cells are selected.
  2. Right-click, then click Copy.

    The copied data can be pasted into another application, such as Microsoft Excel. For example, in Excel, you would click on a cell, right-click, then click Paste.

To work with text results

  1. Click the Text Results tab.

    The Text Results tab shows in a text format the result of running SQL statements. You cannot change the data values in the database by changing the text.

To clear results from Text Results tab

  1. Right-click within the Text Results tab then click Clear.

To select text on Text Results tab

  1. Perform one of the following actions:
    1. Right-click within the Text Results tab then click Select All, or press Control+A. Either action selects all of the contents on the tab.
    2. Press and hold down the left mouse button and drag the cursor across the text you want to select.
    3. Press and hold down the Shift key, then press the cursor arrow keys to move the cursor across the text you want to select.

To copy text from Text Results tab

  1. Perform one of the following actions:
    1. Press Control+C.
    2. Right-click on the selected text then click Copy.

      Copied text is placed in the clipboard.

To cut or delete text from Text Results tab

Text must first be selected before you can cut or delete it. Text cut is placed in the clipboard and is available for pasting. Text deleted is not placed in the clipboard.

  1. Perform one of the following actions:
    1. To cut text, right-click within the Text Results tab then click Cut, or press Control+X.
    2. To delete text, press the Delete key.

Query Builder Tasks

To show or hide Query Builder Diagram

A database must be open before you can show or hide Query Builder Diagram.

  1. Click View4Query Builder Diagram or click .

    These actions function as a toggle. That is, if Query Builder Diagram is hidden, the action shows it, and vice versa. See also Behavior When Query Builder Diagram Contains No Tables .

To show or hide Query Builder Grid

A database must be open before you can show or hide Query Builder Grid.

  1. Perform one of the following actions:
    1. Click View4Query Builder Grid.
    2. Click .

      These actions function as a toggle. That is, if Query Builder Grid is hidden, the action shows it, and vice versa. See also Behavior of Query Builder Grid When Query Builder Diagram Contains No Tables .

To set query type for Query Builder

This task requires that Query Builder Diagram or Query Builder Grid be active (showing).

  1. Click Tools4Query Builder4Change Query Type or click .

    A dialog appears on which you may choose the type of query.

  2. Perform one of the following actions:
    1. Click Select to build a SELECT statement.
    2. Click Update to build an UPDATE statement.
    3. Click Insert to build an INSERT statement.
    4. Click Delete to build a DELETE statement.
  3. Click OK.

To build a SELECT statement with Query Builder

This task serves as a mini-tutorial on how to build an SQL statement with Query Builder. This task addresses building a SELECT statement, but many of the actions also apply to building an UPDATE, INSERT, or a DELETE statement. For this reason, we suggest that you complete this task before attempting to build one of the other statement types.

This task presents the steps to accomplish the following:

The database used for this task is the sample database provided with Pervasive.SQL, named DEMODATA. DEMODATA is located, by default, in the folder PVSW\demodata.

This task assumes the following:

Your first goal is to obtain the ID number, first name, and last name of all persons in your database with a first name of "James" or "Lisa" and whose last name begins with an "N," "O," "P," "Q," "R," "S," or "T." Once you have the correct records, you need to sort them by ascending ID number.

  1. Click View4Query Builder Diagram or click .

    The Add Table dialog, the Query Builder Diagram, and the Query Builder Grid appear.

  2. Click "Person" in the tables list then click Add.
  3. Click Close.

    The SQL statement is started in Query Pane as soon as you add a table to Query Builder Diagram. SQL DM should show something similar to the following:

    If you see a different type of grid, then you need to set Query Builder to build a SELECT statement. The next two steps show you how. Complete both steps even if you see the same type of grid.

  4. Click Tools4Query Builder4Change Query Type or click .

    The "Query type" dialog appears.

  5. Click Select then OK.

    Query Builder is now set to build a SELECT statement.

  6. In Query Builder Diagram, click the columns ID, First_Name, and Last_Name for table Person.

    Notice that the columns appear in the grid and that the SQL statement in Query Pane continues to be built.

  7. In the Alias column for "Person"."ID," "Person"."First_Name," and "Person"."Last_Name," type the following, respectively:
    • ID Number
    • First Name
    • Last Name

      Notice that, as you move the cursor from each cell, the AS clauses are added to the statement and that double quotes are automatically placed around what you typed.

Note
You must indicate to Query Pane that you are finished typing into a grid cell. You do this by moving the cursor from the cell once you have finished typing. For example, after you type "Last Name," move the cursor from that cell with the Tab key, cursor arrows, or mouse.

  1. In the Criteria column for "Person"."First_Name," type James.
  2. Press the Tab key.

    Notice that Query Builder automatically adds an equals sign and single quotes around 'James' because "James" is a string data type. Non-string data types are not enclosed by quotes.

  3. In the Or cell beside 'James,' type Lisa and press the Tab key.

    SQL DM should show something similar to the following:

    At this point, run the SQL statement to see what results are returned.

  4. Click Tools4Query4Run Current SQL Statement in Grid or click .

    The statement returns 24 records where first name equals "James" or "Lisa." But your goal also requires the records where the last name begins with an "N," "O," "P," "Q," "R," "S," or "T." You obtain the last name information by using a logical AND.

    Also notice that the Aliases you specified appear as the column headings on the Grid Results tab.

    Optional: If you want to view the results as text, click Tools4Query4Run Current SQL Statement in Text or click .

    Click the Grid Result tab after you finish viewing the text results.

  5. In the Criteria column for "Person"."Last_Name," type >=N (greater than or equal to N) and press the Tab key.

    Notice that the grid does not contain an AND column. You create a logical AND by adding the same database column to the grid and specifying the additional criteria.

  6. Position the mouse pointer in the empty cell below "Person"."Last_Name" then press and hold down the left mouse button. A list of available database columns appears.
  7. Click "Person"."Last_Name" in the list.
  8. Press the Tab key 5 times to position the cursor to the Criteria column.
  9. In the Criteria cell, type <=T and press the Tab key.

    SQL DM should show something similar to the following:

    Notice that the SQL statement now contains the AND clauses for last name.

  10. Click Tools4Query4Run Current SQL Statement in Grid or click .

    This time, the statement returns 10 records, but the records are not sorted. Their order is the sequence in which they were added to the database.

    You are almost finished with your first goal! All you have remaining is to sort the records by the person ID numbers.

  11. Position the mouse pointer in the Order by cell for "Person"."ID," then press and hold down the left mouse button.

  1. Click Ascending in the list then press the Tab key.

    The ORDER by clause is added to the SQL statement. Notice that the ascending symbol is added to the Person table for the ID column.

  2. Click Tools4Query4Run Current SQL Statement in Grid or click .

    The statement returns 10 records but now they are sorted in ascending sequence by ID number.

    You have now completed your first goal for this task. Your next goal is to determine from your database the average amount owed by the students. You determine this with an aggregate query that uses a Group By clause. The "Person" table does not contain the data you need so you are finished with it.

  3. In Query Builder Diagram, right-click on the title bar of the "Person" table, then click Remove.
  4. Right-click in the Query Builder Diagram, then click Add table.
  5. On the Add Table dialog, click "Billing," then Add, then Close.

    SQL DM should show something similar to the following:

  6. In Query Builder Diagram, click the column Amount_Owed.
  7. Click Tools4Query Builder4Group By or click .

    The Group By column appears on the grid, the Group By symbol is added to the "Billing" table for the Amount_Owed column, and the SQL statement now contains a Group By clause.

  8. Position the mouse pointer in the Group by cell for "Billing"."Amount_Owed," then press and hold down the left mouse button.
  9. Click AVG (for average) from the list then press the Tab key.

    Query Pane now contains a query that determines the average amount owned by students.

    Notice that the Group By part of the clause went away when an aggregate function (AVG) was specified for the column. If you also want to group by the column, position the mouse pointer in the empty cell below "Billing"."Amount_Owed," then press and hold down the left mouse button. From the list, click "Billing"."Amount_Owed." The Group by column shows GROUP BY and the statement in Query Pane contains a GROUP BY clause.

    In other words, you need one line in Query Builder Grid for each aggregate function or Group By clause that you want in the SQL statement.

  10. Click Tools4Query4Run Current SQL Statement in Grid or click .

    The average amount owed by students is $2,894.26.

    Notice that the column heading for the output is EXPR_1. What if you wanted the heading to read "Average Amount Owed." What would you change on Query Builder Grid to produce this result? You would type "Average Amount Owed" as an Alias for "Billing"."Amount_Owed." Do that and run the statement again to see if the heading changes.

    You have now completed your second goal for this task. Your last goal is to find which persons in your database are also on the faculty, and determine the department in which the faculty member teaches and the salary of the faculty member.

    Because you now have some experience using Query Builder, the steps for this goal are more generic and contain fewer images of the GUI. If you are unsure how to complete a step, review the previous steps in this task.

    You are finished with the "Billing" table.

  11. Remove the "Billing" table from Query Builder Diagram and add the tables "Person" and "Faculty."
  12. In Query Builder Diagram, click the columns ID, First_Name, and Last_Name for table "Person."
  13. In Query Builder Diagram, click the columns ID, Dept_Name, and Salary for table "Faculty."

    SQL DM should show something similar to the following:

    You are now ready to join the two tables with the ID column. The join allows you to determine which faculty members are also in the Person table. The following steps show you how to join the columns by dragging the column from one table to the corresponding column in the other column.

  14. In Query Builder Diagram, click ID and keep the left mouse button held down.

    The mouse pointer changes shape to :

  1. "Drag" the ID column to the Faculty ID column:
  2. Release the mouse button.

    A join line appears between the two tables:

    Notice that the JOIN syntax is not used in the SQL statement. Instead, a WHERE clause is added:

  3. Click Tools4Query4Run Current SQL Statement in Grid or click .

    The statement returns 95 records. Your manager arrives and reviews the results. Your manager tells you to remove the salary information because it is not needed. How do you do this?

  4. In Query Builder Grid, click on the Result option for "Faculty"."Salary."

    This action removes the "X" from the option and also removes the Salary part from the selection list in the SQL statement.

  5. Run the query again.

    You have now completed all goals for this task.

To build an UPDATE statement with Query Builder

This task assumes the following:

Your goal for this task is to change the time at which Psychology 101 begins to 10:30 am and reduce its maximum class size to 175. Before you make the changes, however, look at the information for this class.

  1. Set Query Builder to build a SELECT statement (change the query type to "Select.")
  2. Add table "Class" to Query Builder Diagram.
  3. Click the column * (All columns) then run the SQL statement for output to the Grid Results tab.

    SQL DM should show something similar to the following:

  4. Scroll the results grid until you see the record for Psychology 101 that has a start time of 11:00 am.

    This is the record you want to change. Notice that its ID is 173.

  5. Activate word wrap in Query Pane if it is not already activated.
  6. Set Query Builder to build an UPDATE statement:
  7. In Query Builder Diagram, click the columns ID, Name, Max_Size, and Start_Time.

    Notice that the symbol appears in the table window instead of the symbol that appears for a SELECT statement.

  8. In the Criteria column for "Class"."ID," type 173 and press the Tab key.
  9. In the Criteria column for "Class"."Name," type PSY 101 and press the Tab key.

    You could build your statement without the class name because the class ID uniquely identifies the record. For clarity to someone reading the SQL statement, though, include the class name.

  10. In the New Value column for "Class"."Max_Size," type 175 and press the Tab key.
  11. In the New Value column for "Class"."Start_Time," type '10:30:00' and press the Tab key (remember to use single quotes around the time).

    SQL DM should show something similar to the following:

  12. Click Tools4Query4Run Current SQL Statement in Text or click .

    The Text Results tab contains the following result: 

    SQL statement (script) has executed successfully. 
1 row(s) were affected. 

    
   Now look at the data in the "Class" table again to verify that you changed the record.
  13. Set Query Builder to build a SELECT statement (change the query type to "Select.")
  14. Click the column * (All columns) then run the SQL statement for output to the Grid Results tab.
  15. Scroll the results grid until you see the record for Psychology 101 with ID 173.

    Notice that the maximum class size is now 175 and the start time is 10:30 am.

To build an INSERT statement with Query Builder

This task assumes the following:

Your goal for this task is to add a new philosophy class to the database. Before you make the change, however, look at the information for the existing courses.

  1. Set Query Builder to build a SELECT statement (change the query type to "Select.")
  2. Add table "Course" to Query Builder Diagram.
  3. Click the column * (All columns) then run the SQL statement for output to the Grid Results tab.
  4. Scroll the results grid until you see the records for Philosophy.

    SQL DM should show something similar to the following:

  5. Activate word wrap in Query Pane if it is not already activated.
  6. Set Query Builder to build an INSERT statement:
  7. In Query Builder Diagram, click the columns Name, Description, Credit_Hours, and Dept_Name.

    Notice that the symbol appears in the table window.

  8. In the New Value column for "Course"."Name," type PHI 201 and press the Tab key.
  9. In the New Value column for "Course"."Description," type Ethics of Accounting Practices and press the Tab key.
  10. In the New Value column for "Course"."Credit_Hours," type 3 and press the Tab key.
  11. In the New Value column for "Course"."Dept_Name," type Philosophy and press the Tab key.

    SQL DM should show something similar to the following:

    Note that when you perform an INSERT, you must provide a value for all columns that cannot be null. For the Course table, "Course"."Name" and "Course"."Dept_Name" must contain values. For practice, however, this task has you provide values for all four columns.

  12. Click Tools4Query4Run Current SQL Statement in Text or click .

    The Text Results tab contains the following result: 

    SQL statement (script) has executed successfully. 
1 row(s) were affected. 

    
   Now look at the data in the "Course" table again to verify that you inserted the record.
  13. Set Query Builder to build a SELECT statement (change the query type to "Select.")
  14. Click the column * (All columns) then run the SQL statement for output to the Grid Results tab.
  15. Scroll the results grid until you find the record you inserted, PHI 201.

    The philosophy class you inserted is now part of the table.

To build a DELETE statement with Query Builder

This task assumes the following:

Your goal for this task is to remove from the database classroom 100 in the Boerner Building. Before you make the changes, however, look at the information for the existing classrooms.

  1. Set Query Builder to build a SELECT statement (change the query type to "Select.")
  2. Add table "Course" to Query Builder Diagram.
  3. Click the column * (All columns) then run the SQL statement for output to the Grid Results tab.
  4. Scroll the results grid until you see the records for the Boerner Building.

    Notice that room 100 is one of the rooms listed in the Boerner Building.

  5. Activate word wrap in Query Pane if it is not already activated.
  6. Set Query Builder to build a DELETE statement:

Notice in the table window that none of the columns are selectable for a DELETE statement.You cannot delete individual columns so check boxes do not appear in the table window. Instead of selecting columns, you specify the criteria in Query Builder Grid for the rows you want to delete, as explained next.

  1. In the Query Builder Grid, specify that you want to work with columns "Room"."Building_Name" and "Room"."Name." (Remember to press the Tab key to move from the "Room"."Name" cell.)

    SQL DM should show something similar to the following:

    Why do you need both the building name and the room number? The "Room" table contains several records for the Boerner Building. If your only deletion criteria was building name, you would delete records you need to keep. Similarly, "Room" contains records for room 100 in other buildings, so you would not want to delete all rooms with room number 100.

  2. In the Criteria column for "Room"."Building_Name," type Boerner Building and press the Tab key.
  3. In the Criteria column for "Room"."Number," type 100 and press the Tab key.

    SQL DM should show something similar to the following:

  4. Click Tools4Query4Run Current SQL Statement in Text or click .

    The Text Results tab contains the following result: 

    SQL statement (script) has executed successfully. 
1 row(s) were affected. 

    
   Now look at the data in the "Room" table again to verify that you deleted the record.
  5. Set Query Builder to build a SELECT statement (change the query type to "Select.")
  6. Click the column * (All columns) then run the SQL statement for output to the Grid Results tab.
  7. Scroll the results grid until you see the records for the Boerner Building.

    Notice that room 100 is no longer listed as one of the classrooms for the Boerner Building.

To add a table to Query Builder Diagram

This task requires that Query Builder Diagram be active (showing).

  1. Perform one of the following actions:
    1. Click Tools4Query Builder4Add Table or click .
    2. Right-click in the pane of Query Builder Diagram, then click Add table.

      A list appears that shows the tables in the active database.

  2. Click the desired table in the list.
  3. Click Add.

See also Behavior When Query Builder Diagram Contains No Tables .

To remove a table from Query Builder Diagram

This task requires that Query Builder Diagram be active (showing).

  1. Right-click on the title bar of a table in Query Builder Diagram.
  2. Click Remove.

To join tables in Query Builder Diagram

See steps 32 through 34 in To build a SELECT statement with Query Builder for an example of joining tables.

To insert a row into Query Builder Grid

This task requires that Query Builder Grid be active (showing).

  1. Right-click on the row that you want to move down.
  2. Click Insert Row.

    The inserted row is placed above the row that contained the cursor.

To clear the contents of a row(s) on Query Builder Grid

This task requires that Query Builder Grid be active (showing).

  1. Right-click on the row that you want to clear.
  2. Click Clear Row(s).

    The clear action removes the values from all of the grid cells for that row, but leaves the empty row in the grid. The SQL statement in Query Pane is rebuilt.

    You may also select and clear multiple rows. To select multiple rows, press and hold down the Shift or Control key, then click the indicator column for the desired rows. Once the desired rows are selected, right-click and click Clear Row(s).

    The indicator column is the leftmost column in the grid:

To delete a row(s) from Query Builder Grid

This task requires that Query Builder Grid be active (showing).

  1. Right-click on the row that you want to delete.
  2. Click Delete Row(s) (or press the Delete key).

    The delete action removes the row in the grid. The SQL statement in Query Pane is rebuilt.

    You may also select and delete multiple rows. To select multiple rows, press and hold down the Shift or Control key, then click the indicator column for the desired rows. Once the desired rows are selected, right-click and click Delete Row(s) (or press the Delete key).

    The indicator column is the leftmost column in the grid:

To add a Group By column to Query Builder Grid

See steps 25 through 27 in To build a SELECT statement with Query Builder for an example of adding a Group By column.

Prev
SQL Data Manager GUI Visual Reference		 Contents
Up
Check for Revisions 		Next
Pervasive System Analyzer (PSA)