Programmer's Guide

Chapter 3 Using SilverStream Expressions

This chapter describes SilverStream expressions and provides an overview of the operators and built-in functions available in the SilverStream environment. It contains the following sections.

For related information see the following:

For reference material about writing expressions, see Expressions, Operators, and Built-in Functions in the online General Reference.

JDBC and SilverStream

JDBC allows you to pass query strings to an underlying database. The target database must be JDBC compliant. This means that the database must at least have the functionality of ANSI SQL92 Entry Level, which includes all the databases supported by SilverStream.

SilverStream has its own expression engine (parser and evaluator) that is used on top of the JDBC driver (a Java SQL API). This engine is used to help translate SilverStream expression statements back into database statements (mainly SQL). These expressions either do a computation on a column, a validation, or act as a WHERE and ORDER BY clause.

Some parts of the database expression are handled by the SilverStream parser, while other parts are passed through to the database for processing. The Server evaluates any non-database supported expressions in WHERE clauses as each row is retrieved. The processing for non-database supported ORDER BY clauses, on the other hand, is deferred until all the rows have been retrieved.

About expressions in SilverStream

SilverStream expressions are a powerful way to enhance the functionality of your applications. This section provides an overview of expressions and the different types of expressions you can use in the SilverStream environment.

Expression building blocks

In its simplest form, an expression is a programming statement that performs an operation on one or more variables, and returns a value. The following Java expression uses the multiplier operator on three declared int variables and assigns the return value to a fourth variable:

  int product = a * b * c;

A SilverStream expression is a series of variables, operators, and function calls that can return a single value, a set of values, or, in the case of query expressions, a data set. SilverStream supports many (but not all) of the literals, operators, variables, and functions defined in the Java programming language. The following is a summary of the building blocks of a SilverStream expression.

Variables Java data type values defined in your application, such as integers, strings, and Boolean values. Examples of variables are table column values, user names, group names, time stamps, and so on.

Operators Operations or calculations that can be performed on one or more operands.

Built-in functions A variety of SilverStream functions that return values for specific purposes.

The following example shows what an expression in SilverStream looks like:

  employees.employeeID > 100 AND employees.last_name startsWith "W";

This expression represents a WHERE clause in a database query. It uses the greater than > operator with the AND operator and a string function called startsWith to return all rows where the employeeID column is greater than 100, and the employee last name starts with the character W.

Types of expressions

The following table summarizes the different applications for using expressions.

Type of expression

Description

Database query

Compute values from different table columns and return results.

Aggregation expression

Compute values from different table columns and return the results.

Data validation

Insure database integrity by restricting the content and format of user-entered data.

Security expression

Specify criteria to restrict user access to selected database objects.

Full text search

Specify string expressions for finding text in tables.

Default column value

Write expressions for assigning a default value to a table column.

Type of expression	Description
Database query	Compute values from different table columns and return results.
Aggregation expression	Compute values from different table columns and return the results.
Data validation	Insure database integrity by restricting the content and format of user-entered data.
Security expression	Specify criteria to restrict user access to selected database objects.
Full text search	Specify string expressions for finding text in tables.
Default column value	Write expressions for assigning a default value to a table column.

The SilverStream Expression Builder

The Silverstream Expression Builder is a dialog that is tailored to the object currently selected in the Designer or the object's Property Inspector. It lets you build an expression by choosing from lists of variables, operators, and functions available for the selected object.

The Expression Builder supports the following:

Relational operators in their order of precedence
References to named variables
References to functions
References to constants

The Expression Builder also promotes values for numeric data types. Specifically, the Expression Builder:

Does integer comparisons as long as only integer data types are encountered
Does long arithmetic and aggregation, if only integer or long data types are encountered
Switches to numeric (BigDecimal) arithmetic if at least one named variable, constant, or function is a numeric (BigDecimal) type
Switches to double arithmetic, if at least one named variable, constant, or function is a double type

For information about using the Expression Builder tool, see the Expression Builder in the online Tools Guide.

About queries

The industry standard database access language is called Structured Query Language (SQL). The SilverStream Expression Builder uses SQL as the basis for its own query language. Queries are made up of a query expression and associated clauses. You use query expressions to make selections, which retrieve a subset of the rows in one or more tables, and for projections, which retrieve a subset of the columns in one or more tables. Conventionally, queries take this form:

  SELECT list_of_columns 
  FROM table 
  WHERE search_criteria 
  ORDER BY list_of_columns

You can construct WHERE and ORDER BY clauses using the point and click interface of the Expression Builder. The Expression Builder displays a list of table columns and variables that you can choose from to construct your query expression.

You can also construct queries programmatically using the AgiRowSetManager.query() method, which as two variants. See AgiRowSetManager in the API section of the help system.

The WHERE clause

The WHERE clause specifies the search criteria for the data you want to display. If you omit the WHERE clause, all table rows qualify for selection (this is the default when you create a page, form, or view). The WHERE clause can include column names, constants, logical operators, and relative operators.

Typically, a WHERE clause consists of one or more search conditions (predicates) connected by the logical operators AND, OR, and NOT. For example:

  employee.status="Active" and employee.salary < 50000

When SilverStream receives a WHERE or ORDER BY clause, it analyzes the parts of the clause that are linked by an and. When it reaches a part of the clause that contains a function not supported by the underlying database, it sets aside that part of the clause for post-processing. It then takes the parts of the clause that are translatable and applies them to the database. The post-processing parts of the expression are then applied to the result.

NOTE To optimize performance, do not use functions not supported by your native database in WHERE or ORDER BY clauses.

The ORDER BY clause

The ORDER BY clause allows you to sort the results of the WHERE clause by any key. You can sort in either ascending (A to Z or 1 to 10) or descending (Z to A or 10 to 1) order. For example:

ORDER BY clause

Might return

order by first_names

Julie, Leah, Lori, Mary

order by first_names ASC

Julie, Leah, Lori, Mary

order by first_names DESC

Mary, Lori, Leah, Julie

ORDER BY clause	Might return
order by first_names	Julie, Leah, Lori, Mary
order by first_names ASC	Julie, Leah, Lori, Mary
order by first_names DESC	Mary, Lori, Leah, Julie

Summary of operators

Operators perform functions on one or more operands. An operand can be a literal value, a variable, or a value returned by an expression. This section summarizes the operator types supported by SilverStream.

For details and examples, see the section on Operators in Expressions, Operators, and Built-in functions in the online General Reference.

Type of operator

Usage

Arithmetic

Perform arithmetic calculations on one or more numeric operands.

Relational

Compare two operand values and return a boolean, or compare a single operand to a set of expressions and return a boolean.

Logical

Combine two or more boolean expressions and return a boolean.

String

Combine and compare text strings for various purposes.

Other operators

Use for specific types of queries.

Type of operator	Usage
Arithmetic	Perform arithmetic calculations on one or more numeric operands.
Relational	Compare two operand values and return a boolean, or compare a single operand to a set of expressions and return a boolean.
Logical	Combine two or more boolean expressions and return a boolean.
String	Combine and compare text strings for various purposes.
Other operators	Use for specific types of queries.

Summary of built-in functions

SilverStream has its own set of built-in functions. Most of the functions are the same as or similar to those used in Java and SQL. This section summarizes the types of functions supported by SilverStream

For details and examples, see Built-in functions in Expressions, Operators, and Built-in functions in the online General Reference.

Type of function

Usage

Numeric

Do various types of arithmetic calculations and comparisons on a range of numeric types.

String

Perform various text string operations, such as extracting substrings and performing substitution.

Parse

Convert formatted strings, such as dates or numbers, into objects.

Format

Convert objects into formatted strings.

Aggregation

Perform operations on multiple results from a table.

Date/time

Write expressions that return values such as current time, dates, days, and other time values.

Security

Write security expressions that use Universally Unique Identifiers for securing server objects.

Type of function	Usage
Numeric	Do various types of arithmetic calculations and comparisons on a range of numeric types.
String	Perform various text string operations, such as extracting substrings and performing substitution.
Parse	Convert formatted strings, such as dates or numbers, into objects.
Format	Convert objects into formatted strings.
Aggregation	Perform operations on multiple results from a table.
Date/time	Write expressions that return values such as current time, dates, days, and other time values.
Security	Write security expressions that use Universally Unique Identifiers for securing server objects.

Programmer's Guide