Chapter 10   Expression Builder

The Expression Builder is a tool that lets you easily create SilverStream expressions.

This chapter is organized as follows:

• Using the Expression Builder

• Building queries

• Building validation rules

• Building security expressions

• Building Link expressions

• Building aggregate expressions

    For an overview of expression in SilverStream, see Using SilverStream Expressions in the Programmer's Guide.

    For more information about the expression language, see Expressions, operators, and built-in functions in the online General Reference.

Using the Expression Builder   

You use the Expression Builder to easily create SilverStream expressions. You invoke the Expression Builder from the Properties dialog in the Designers.

The Expression Builder contains three panes: Variables, Operators, and Functions.

• The Variables pane lists the table columns and (depending on the kind of expression you are building) fields in the form or page

• The Operators pane lists operators, organized by type

• The Functions pane list the built-in SilverStream functions

    For complete information on the SilverStream operators and built-in functions, see Expressions, operators, and built-in functions in the online General Reference.

Building the expression

You can type the expression in the text box at the bottom or paste in columns, fields, operators, and built-in functions by selecting them from the lists.

When you paste in a built-in function, its syntax--including parameters--is pasted in for you; replace the place-holders with variables.

You can click the Verify button any time to check your expression's syntax.

Types of expressions

The following sections describe how to use the Expression Builder to build different types of expressions.

• Building queries

• Building validation rules

• Building security expressions

• Building Link expressions

• Building aggregate expressions

Building queries   

You can specify the data you want retrieved when first creating a form, page, or view using the Form, Page, or View Wizard.

Default behavior

When you specify data in a Wizard, the "no automatic query" option is selected bey default. This means that no data is sent to the client until the client initiates the query() with a specific request.

If you select any other option, like "go to first record", all rows are retrieved and will be sent to the client. If you are working with large tables, you will want to refine the query to return only specified rows.

Refining the default query

You can use the Expression Builder to refine the query in the Form, Page, or View Designer after creating the object.

You can specify the following components of a query:

• The WHERE clause--which rows are retrieved

• The ORDER BY clause--the order in which rows are retrieved

To specify a query:

1. Depending on the Designer you are using, do the following:

   Designer	What to do
   Form Designer	Display the Properties dialog for the form and select the Form tab. Expand the Data section. You see two fields for specifying the query: Where clause and Order by.
   Page Designer	Display the Properties dialog for the page and select the Page tab. Expand the Data section. You see two fields for specifying the query: Where clause and Order by.
   View Designer	Select the band whose data you want to specify the query for. Display the Properties dialog and select the Band tab. You see two fields for specifying the query: Where clause and Order by.

2. Build your WHERE clause, as described below.

3. If you want to eliminate duplicate rows, select Distinct in the Properties dialog (this adds the DISTINCT keyword to the generated query).

4. If you want to limit the number of rows returned, select limit number of rows, then specify the number on the Properties dialog. By default, all rows are returned when the query is initiated.

5. Optionally, you can also specify an ORDER BY clause, as described below.

6. Test your query by running the page, form, or view.

To build a WHERE clause:

1. Click the ellipses button ( ) next to the Where clause field.

   The Expression Builder displays.

   

   The primary table is listed in the Variables box. Click + to expand the display to list all columns. Also, any tables related to the primary table are listed. You can pick columns from these tables to include in your query.

       For information about relationships between tables, see Relationship Designer.

2. Type the query expression in the text field or build the query by selecting database columns, operators, and built-in functions. Note that you do not include the WHERE keyword; SilverStream does that automatically when it constructs the query at runtime.

   NOTE   You can include any of the listed columns in your query; the column itself does not need to be retrieved in the form, page, or view to participate in the query.

       For complete information on the SilverStream operators and built-in functions, see Expressions, operators, and built-in functions in the online General Reference.

3. Click Verify to confirm that the expression is valid.

4. Click OK to close the Expression Builder.

Examples

Here are some examples of a page, form, or view that retrieves employee information:

Query	Retrieves
employees.employeeid > 5	Employees whose ID is greater than 5
employees.city="Oakland"	Employees in Oakland
month(employees.birthdate) = 4	Employees born in April
employees.city="Oakland" and month(employees.birthdate) = 4	Employees in Oakland who were born in April

To build an ORDER BY clause:

1. Expand the Order by section in the Properties dialog.

2. Click Add New Clause.

3. Click the ellipses button ( ) next to the Clause1 field.

   The Expression Builder displays.

   

   The Expression Builder displays.

4. Type the ORDER BY expression in the text field or select database columns, operators, and built-in functions. Note that you do not include the ORDER BY keyword; SilverStream does that automatically when it constructs the query at runtime.

   Typically, you will simply specify a column to sort by, but you can specify any expression and use any of the listed built-in functions to sort rows by.

5. By default, rows are sorted in ascending order. To sort on descending order, click Descending.

6. Click OK.

7. If you want, add another ORDER BY clause by repeating the previous steps. Rows that sort the same based on the first ORDER BY clause will be sorted by the subsequent ORDER BY clause.

Creating a query at runtime   

You can also construct a query programmatically using the query() method. When you create a page, form, or view in the SilverStream Designer, this method is included with the AgData helper class.

Use the query() method to specify a WHERE clause and an ORDER BY clause at runtime.

• The first parameter specifies the WHERE clause

• The second parameter specifies the ORDER BY clause

Both parameters are optional. If you don't specify a WHERE clause, all rows are retrieved. If you don't specify an ORDER BY clause, the rows are retrieved in indeterminate order.

Example

The following statement retrieves all employees in department 300, sorted by last name.

  myView.query("employees.deptid=300", "employees.lastName") 

Building full-text search queries   

You can use the Expression Builder to create your own full-text search expressions. The SilverStream full text search feature is based on ANSI SQL, which is the standard interface language for accessing databases. SQL provides language extensions that support text retrieval. The combination of the queries you create in SilverStream and the search engine provided by Fulcrum enables you to:

• Search a table or tables by specifying the columns that you want to search

• Specify the search criteria to use

You can create a number of different searches. You can:

• Search for specific words or phrases.

• Create wildcard searches where the result set contains words or phrases that match the pattern you specify.

• Create thesaurus searches where the result set contains words that have the same meaning as the words or phrases you specify.

• Create multiple word searches where you specify the relevance ranking of the result set using an ORDER BY clause. For example, you can specify that the result set contain the total number of occurrences of the word that matches your search criteria.

• Specify words that you do not want included in your search results. These words are contained in a file called a stop file.

    For more information, see Full Text Search.

Building validation rules   

SilverStream supports both server-side and client-side validation of data. You can use the Expression Builder to:

• Specify validation rules for columns in the Table Designer. Such data is validated on the server.

      For more information, see Creating validation rules for tables in the Table Designer section of the Tools book in the help system.

• Specify validation rules for fields on forms in the Form Designer. Such data is validated on the client (form).

      For more information, see Creating validation rules for form fields.

• Specify validation rules for fields on pages in the Page Designer. Such data is validated on the server.

      For more information, see Creating validation rules for page fields.

NOTE   You can specify more complex validation tests in forms and pages by writing Java code (and, in the case of pages, JavaScript). For more information, see the Programmer's Guide.

Building security expressions   

You can use the Expression Builder to build security expressions, which are expressions that specify who is able to access specific SilverStream objects, including data and application objects. For example, you can specify permissions such as the following:

• Who is allowed to see the contents of a particular database table, or particular rows within a table

• Who is allowed to update a particular table

• Who is allowed to view a particular form

• Who is allowed to update a particular form

• Who is allowed to invoke a particular business object

    For more information, see setting up access control in the Administrator's Guide.

Building Link expressions   

You can use the Expression Builder to create Link expressions, which let you pass query information and parameters from one page to another.

    For more information, see Creating a Link expression.

Building aggregate expressions   

You can use the Expression Builder to create aggregate expressions, which specify summary statistics in views.

    For more information, see Creating aggregates