Work with SQL Statements

Overview

Procedures

Reference

Overview

Use SQLEditor to create, view, and manage SQL statements that XLiRAD templates use for retrieving data from and posting data to the database. The SQLEditor window provides access to the menu commands and toolbar buttons used to accomplish these tasks.

Procedures

• Start SQLEditor
• Find a Statement
• Sort the Statement List
• Find Templates Based on a Statement
• Create a Statement with the SQL Wizard
• Create a New Statement "From Scratch"
• Duplicate an Existing Statement
• Delete a Statement
• View or Update a Statement
• View Database Layout
• Update the SQLEditor Display

Start SQLEditor

1. From the Start menu, point to Programs, and click SQLEditor.
2. Complete the XLiRAD Client Login dialog box and click Submit.
3. Complete the Group Selection dialog box and click Submit.

Find a Statement

1. In SQLEditor, click .
   Or click the Keyword Search menu command.
2. In the SQL Search dialog box, type a text string to search for.
3. Select the Case Sensitive check box if appropriate
4. Click Search to start the search.
   Notice that SQLEditor displays statements that match the search criteria with highlighting. Click Remove Highlighting as appropriate.

Sort the Statement List

• In SQLEditor:
  • To sort by command ID, click .
    Or click the By ID Number command.
  • To group statements that query a particular database together, click DB Name .
    Or click the By Database Name command.

Find Templates Based on a Statement

1. In SQLEditor, click .
   Or click the Template Search command.
2. In the Search Results dialog box, view the templates listed under Matching Templates.
3. When finished, click Done.

Create a New Statement with the SQL Wizard

1. In SQLEditor, click in the toolbar.
   Or from the Query menu, click the Add New Query command.
2. Click SQL Wizard.
3. Under SQL Wizard, click Next to continue.
4. Under SQL Wizard: Step 1, accept the default database name or use the up or down arrow in the Database list box to find a database for the new statement. Then click Next.
5. Under SQL Wizard: Step 2, accept the default query type or use the up or down arrow in the Query Type list box to find a query you want to use for the new statement. The options are SELECT (default), INSERT, UPDATE, and DELETE. Click Next to continue.
6. Under SQL Wizard: Step 3, click one or more tables to use under Available Tables. To include multiple tables in a SELECT statement, hold down the CTRL key while clicking the names of the tables. Click Next to continue.
7. Under SQL Wizard: Step 4 , click one or more columns to use. To include multiple columns in a SELECT statement, hold down the CTRL key while clicking the names of the columns or click Select All as appropriate. Click Next to continue.
8. Under Select Statement Order, use the up or down arrows to arrange the field-specific variables in your statement. By default the list box shows these variables in the sequence in which the fields occur in your database. Click Submit to continue.
9. Under SQL Wizard Final Step: Clauses, click Add Clause if you want to define a clause that limits the data returned by your statement. To define a clause, specify the following:
   • Column Name
   • Operator
   • Variable Name
   • Variable Type
10. Click Finish.

Create a New Statement "From Scratch"

1. In SQLEditor, click in the toolbar.
   Or from the Query menu, click the Add New Query command.
2. In the Command Editor dialog box, type the query text and the command ID of the new statement and click the name of the appropriate database. Use Add Form Data as needed.
3. Click  Execute to validate the statement. To retrieve a statement from the server, click Refresh.
4. Click Submit to save the statement to the server.

For a visual reference to the database schema, display the Database Layout window while creating a new query.

Duplicate a Statement

1. In SQLEditor, right-click a statement and click Clone.
   
   SQLEditor displays the cloned statement in the Command Editor dialog box.
2. Type a new command ID for the statement you are creating and make any other changes as needed.
3. Click Execute to validate the statement or click Submit to save the statement to the server.

View or Update a Statement

1. In SQLEditor, right-click a statement and click Update.
   Or click a statement and click Edit SQL in the toolbar.
2. In the Command Editor dialog box, change any of the following settings:
   • Statement text
   • Command ID
   • Database name
   • Form data
3. Click Execute to validate the settings or Update to save the changes.

Delete a Statement

1. In SQLEditor, right-click a statement and click Delete.
   Or click a statement and click Delete SQL on the toolbar.
2. Click Yes.

View Database Layout

1. In SQLEditor, click in the toolbar.
   Or from the Query menu, click the Database Layout command.
2. For a selected database, view the table and field information as needed. For more information, see Database Layout Window Help.
3. When finished, click Close.

Keep the Database Layout dialog box displayed while creating or updating queries as a reference to the database schema.

Update the SQLEditor Display

• In SQLEditor, click in the toolbar.
  Or from the Query menu, click Refresh List.

1. Create a new statement or view an existing statement.
2. In the appropriate dialog box, click Execute.
3. When the statement runs without error, click Submit.

Reference

Use the following user-interface elements of SQLEditor to create and manage SQL statements in XLiRAD:

• SQLEditor window
• Toolbar buttons
• Shortcut menu commands
• Menus
• Menu commands

For information about dialog boxes presented when you work with SQLEditor, see:

• XLiRAD Client Login Dialog Box Help
• Group Selection Dialog Box Help
• Command Editor Dialog Box Help
• Form Variable Specification Dialog Box Help
• Database Layout Window Help

SQLEditor Window

• Command ID
• Database queried
• Text of the command

The window also provides access to functions through toolbar buttons, window menus, and a shortcut menu.

SQLEditor displays statements stored on the server at the time you start SQLEditor. If other users subsequently create new or modify existing statements on the server, you can update the SQLEditor window to display the latest set of statements by means of the Refresh SQL button or the Refresh List command on the Query menu.

Toolbar Buttons

Shortcut Menu Commands

When you right-click a statement in the SQLEditor window, the following commands become available.

Menus

Menu Commands

File Menu

Sort Menu

Query Menu

XLiRAD Client Login Dialog Box Help

Use the XLiRAD Client Login dialog box after you install SQLEditor or TemplateMaker and every time you start the program.

Group Selection Dialog Box Help

Use the Group Selection dialog box to choose the SQL command group you want to use.

Command Editor Dialog Box Help

Form Variable Specification Dialog Box Help

Use the Form Variable Specification dialog box to specify in a SQL statement the data that a user can submit in a form on the HTML page.

Database Layout Window Help

Use the Database Layout window to view the tables and fields in a database. This information is useful in working with SQL statements.