Chapter Thirteen

Using Basic Database Actions

Setup and Operation of Search, Insert, Update, and Delete Actions

Tango includes several fundamental database actions that allow you to search (Search action), add (Insert action), modify (Update action), and delete (Delete action) database records.

The topics covered in this chapter include setting up and executing Search, Insert, Update, and Delete actions.

[back to top]

Searching a Database

Search actions retrieve database records matching a given criteria.

You use the Search action editing window to define what columns are selected, the order of the data retrieved, and the criteria that determine which rows are found.

Tip: The SQL Query window gives you a convenient way to look at your database values. Choose SQL Query from the Windows menu or from the context-sensitive menu that appears when you right click the Search action editing window. For more information, see "The SQL Query Window".

You use the action's Results HTML to format the results of the search.

Setting Up a Search Action

For details on the Joins tab, see xref. [working with joins, chap 18]

Select Tab

You use the Select options to select the type of search to perform, the columns to retrieve, and the ordering of the records returned.

You can perform three main types of searches with a Search action: Normal, Summaries of Groups, and Summary of All Rows.

Select which type of search you want to perform from the Select Type drop-down list.

• Normal returns rows matching specified criteria.
• Summaries of Groups returns summaries of rows whose values in given columns (the grouping columns) are the same.
• Summary of All Rows returns a single row summarizing all rows matching your criteria. This kind of search lets you get information such as the maximum or average value of a particular column in a database table.

Normal Search

The Normal type of search returns rows matching specified criteria. This is the most common type of search.

When you select Normal from the Select Type drop-down list, the Search action's Select window appears.

Specify values for the parameters in the Select window as follows:

• Select Columns. Drag into this list from the Data Sources Workspace the columns whose data is to be retrieved from the database.

For more information, see "Joining Database Tables".

You can include columns from multiple tables; if you do, you must define joins to describe how the tables are related.

• Order By Columns. Drag into this list the columns that are used to sort the results returned to the user. Ordering by columns is optional.

The order of the columns in the list determines the ordering hierarchy. For example, if the first order column is "state or province" and the second "customer name", the results are first ordered by state or province; customers in the same state or province are then ordered by name.

The triangle to the left of the column name determines whether the ordering is ascending () or descending (). To change the order direction for a column, click the triangle.

Summaries of Groups

The Summaries of Groups search type returns summaries for groups of rows with the same values in specified columns. For example, it allows you to find out the total sales for each sales region in an invoices table by selecting the sum of invoice amounts and grouping by sales region.

When you choose Summaries of Groups, the Search action's Select window appears.

• Select Columns. Drag into this list the columns you want to select. Select columns for this select type have an associated function. This function is performed on the column for all the rows in a particular group, as determined by the Group By Columns list. For example, if you selected the MAX function for a "price" column and group by the "classification" column, you would receive one row for each unique classification. Each row would contain the maximum value of the "price" column for the classification being summarized.

The following table lists the available functions.

To choose the function for a column, click the Function column and select the function from the drop-down list.

• Order By Columns. As with the normal select type, you specify in this list the ordering of results. You can drag columns from the Data Sources Workspace or from the Select Columns list. You can order only by columns appearing in the Select Columns list.
• Group By Columns. The columns in this list determine how rows are grouped before being summarized. Groups consist of all the rows that have the same values in the columns specified. For example, if you group by the "cust_state" and "cust_rep" columns in a customer table, you get one summary row for each group of rows with the same values in the "cust_state" and "cust_rep" columns.
• Show Group Criteria. Normally, all the summary rows are returned for records matching the user's criteria. You can eliminate summary rows by specifying group criteria. The group criteria have a different function from the regular criteria in that the regular criteria specify which rows are eligible for grouping, while the group criteria specify which summary rows are returned.

Note: The group criteria section is equivalent to the HAVING clause in a SQL SELECT statement.

For example, if you are grouping by classification and selecting the maximum order amount, you can use group criteria to limit the returned rows to those customers whose maximum order amount is greater than $5,000.

To specify group criteria, choose Show Group Criteria.

The Select window expands to show the area for entering group criteria.

Drag columns from either the Data Sources Workspace or the Select Columns list.

Note: You can specify only columns appearing in the Select Columns list.

For more information, see "Criteria Tab".

Except for the function option, you specify group criteria just like normal criteria.

Summary of All Rows

To get a summary of all rows matching a specified criteria, use the Summary of All Rows search type. Only one summary row is returned. For example, you could use this search type to find the average amount of all orders in an orders table.

As with the Summaries of Groups search type, each select column has an associated function that determines how that column is summarized. All the column values in the rows matching the criteria are aggregated using the specified function.

Criteria Tab

The Search action criteria determine which rows from the database are returned by the action. If no criteria are specified, all rows are returned; otherwise, each row in the database is compared to your criteria and only those meeting them are returned.

To specify the criteria, drag columns from the Data Sources workspace to the Criteria list. For each column, you need to specify:

• Logical Operator
• Column
• Operator
• Value
• Include Empty
• Quote Value.

Column

In the Column field, specify the column whose value you want to compare. Drag the column from the Data Sources Workspace.

Logical Operator

The first field in the criteria list is the logical operator.

You can also right click the field, and choose Edit from the context-sensitive menu. Then choose an operator from the drop-down list that appears.

Click the row, then click the field to display a drop-down list to choose an operator from. The operators are and and or.

Note: You must specify at least two columns before the logical operators are available.

The logical operator determines whether the current and previous criteria must be true for a record to be included in the result or whether a match on either the current or previous criterion causes a record to be included in the result. For example, if your criteria are:

cust_num = 5100
and cust_name Begins with A

only records matching both criteria are returned. If the logical operator is changed to or, records matching either one of the criteria are returned.

There is an implied order of operation for logical operators. Criteria joined with the and logical operator are evaluated before those joined with the or logical operator. For example, in the following criteria:

cust_num = 5100
or cust_name Begins with A
and cust_state = NY

a match is made if both the second and third criteria are true or the first criterion is true.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

You can also use the Insert Meta Tag command to enter in the Criteria window entry fields many of the commonly used meta tags.

To insert a meta tag, either click the field and choose Insert Meta Tag from the Edit menu, or right click the field and choose Insert Meta Tag from the context-sensitive menu that appears.

For more information, see "Criteria Separators".

You can use criteria separators to control the order of criteria evaluation, regardless of this default logical operator hierarchy.

Operator

In the operator field (Oper.), specify the operator to use when comparing the field.

You can also right click the field, and choose Edit from the context-sensitive menu. Then choose an operator from the drop-down list that appears.

Click the row, then click the field to display a drop-down list to choose an operator from. Possible operators include:

Text columns permit the use of any operator; for other columns, the Contains, Begins with, and Ends with operators are disabled.

You can either specify a static operator or insert a meta tag to get the value at execution time. Using a variable operator allows you, for example, to put a drop-down list on your Web page to let users choose the comparison operator.

When using a variable to specify the criterion operator, Tango requires you to use special values to represent each of the operators. The following table lists these special values:

For example, to create an operator drop-down list in an HTML form whose value you want to use as the operator in a search criterion, you could use HTML similar to the following:

<SELECT NAME="cust_name_op" SIZE=1>
<OPTION VALUE = "iseq" SELECTED>=
<OPTION VALUE = "isnt">!=
<OPTION VALUE = "gthn">&gt;
<OPTION VALUE = "lthn">&lt;
<OPTION VALUE = "gteq">&gt;=
<OPTION VALUE = "lteq">&lt;=
<OPTION VALUE = "swth">Begins With
<OPTION VALUE = "ewth">Ends With
<OPTION VALUE = "cont">Contains
</SELECT>

and set the operator in the Search action to

<@ARG cust_name_op>

The Is In operator needs some additional explanation. It matches records where a column value is in a list of values.

For example, the following criteria:

cust_num Is in 200, 300, 400

matches records in which the cust_num field has a value of 200, 300, or 400. The Is in operator can be thought of as a shortcut for a series of OR equals criteria:

cust_num = 200
or cust_num = 300
or cust_num = 400

The value specified can be a single-column or single-row array (as would be returned by the <@ARG> tag with a type attribute of ARRAY, for example) or a comma-separated list of values.

Value

In the Value field, enter the value to use in the comparison.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

The value can also contain any value-returning Tango meta tags, which are substituted when the application file is executed. Use the Insert Meta Tag command to enter many of the commonly used meta tags.

Include Empty

In the Incl. Empty field, specify whether the criterion is included, even if the comparison value is empty.

You can also right click the field, and choose Edit from the context-sensitive menu. Then choose a value from the drop-down list that appears.

Click the row, then click the field to display a drop-down list to choose a value from. The values appear as false and true.

false omits the criterion if the value (after meta tag substitution) is empty; true includes the criterion regardless of the value's contents.

This option is used mainly for columns whose search value is taken from a search form on a Web page. For example, you may have a search form that allows you to enter search values for several columns, but you want the search done only on the columns you enter values for. To do this, set the Incl. Empty option for each of the corresponding Search action criteria to false.

There are cases where you do want a criterion included, even if the value is empty. For example, suppose you have a page that asks for a user name and password, and a corresponding Search action that finds the user in a users' database. In the Search action, you probably want to set the Incl. Empty option for each of the values to true. If you do not, and the user leaves both fields empty, the Search action omits both criteria and returns all user records.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

You can right click the Incl. Empty field, and choose Insert Meta Tag to enter many of the commonly used meta tags.

Quote Value

In the Quote Value field, specify whether Tango puts quotation mark characters around the value in the SQL it generates for this criterion.

You can also right click the field, and choose Edit from the context-sensitive menu. Then choose a value from the drop-down list that appears.

Click the row, then click the field to display a drop-down list to choose a value from. The values appear as false and true.

For text, date, time, and timestamp columns, you should set this option to true. For date, time, and timestamp columns, this option has special meaning. true converts the specified value from the default Tango format to the format required by the database server; false passes the value specified as is to the database server.

If you want to use an expression that the database server evaluates (instead of a literal Tango-supplied value), set the Quote Value option to false and enter the expression in the Value field.

For numeric and Boolean types, you should set the Quote Value option to false.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

You can right click the Quote Value field, and choose Insert Meta Tag to enter many of the commonly used meta tags.

Criteria Separators

You can also right click where you want to insert the criteria separator and choose Insert Criteria Separator from the context-sensitive menu that appears.

To group criteria, select the position between the criteria you want to group and choose the Insert Criteria Separator command from the Edit menu. Only the logical operator cell can be edited for separator items.

Upon execution, the criteria before the separator are combined with the criteria after the separator using the logical operator specified in the separator line in the criteria list.

Results Tab

In the Results window, you specify the maximum number of records to retrieve from the data source, at which result record number retrieval begins, and whether Tango gets the count of matching records.

Number of rows to retrieve

To return all matching records, select No Maximum.

To limit how many records you want the search to return, select Limit To and enter the maximum number of records to retrieve.

The following options are only available for the Normal search type.

Start retrieval at row number

Select this option if you want to skip some of the matching records. Specify the row number you want the Search action to start retrieval at. The default is "1". When the value is other than "1", the search action returns records starting at that number, skipping any records before it.

This option is most useful when you use a variable (such as <@SEARCHARG start>) for the starting record number.

For more information, see "Show Multiple Pages If Limit Exceeded".

For an example of how to use this option to provide results paging for large result sets, look at the Search action in a Search Builder-generated file created with the Show Multiple Pages If Limit Exceeded option selected.

Retrieve distinct rows only

If you select this option, Tango Server adds the DISTINCT keyword after the SELECT keyword in the generated SQL. The DISTINCT keyword specifies whether duplicate rows are to be eliminated from the result set. For example,

SELECT c1.cust_state, c1.cust_zip FROM customer c1;

becomes

SELECT DISTINCT c1.cust_state, c1.cust_zip FROM
customer c1;

Get total number of matching rows

You use this option to retrieve the number of records matching the search criteria, irrespective of how many records are actually retrieved.

Using this option, you can access the value in the Search action's Results HTML by using the <@TOTALROWS> meta tag.

Note: Selecting this option involves an extra database operation, so unless you require the information it provides, you should not select it.

Executing a Search Action

When Tango Server executes a Search action, the search is performed against the associated data source. The result rowset is automatically stored as an array in the local variable resultSet. The Results HTML for the Search action is then processed.

For more information, see "<@ROWS></@ROWS>", "<@COL></@COL>", and "<@COLUMN>" in theMeta Tags and Configuration Variables manual.

The HTML in the <@ROWS><@/ROWS> block, if any, is processed once for each record in the results. Use <@COLUMN> or <@COL> meta tags to include field values.

If the Search action generates no results, and you have specified No Results HTML for the action, that HTML is processed instead of the Results HTML.

[back to top]

Adding Records to a Database

The Insert action adds a record to a table in a database.

Setting Up an Insert Action

To set up an Insert action

1. From the Data Sources Workspace, drag into the Column list the columns whose values you want to set in the new record.

   ---

   Note: You can select columns from only one table.

   ---

   If you do not add all of the table's columns to the Insert action, the omitted columns are given the default values defined when the database was created.

2. In the Value field for each column, enter the value for that column in the new record. The value can contain any of the value-returning Tango meta tags, which are substituted upon execution of the application file.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

To insert a meta tag, either click the field and choose Insert Meta Tag from the Edit menu, or right click the field and choose Insert Meta Tag from the context-sensitve menu that appears.

For more information, see "Criteria Tab".

The Quote Value option operates the same as it does in search criteria.

Executing an Insert Action

When Tango Server executes an Insert action, a record is added to the database with the column values specified. The Insert action returns no results.

[back to top]

Modifying a Database Record

The Update action modifies database records matching specified criteria.

Setting Up an Update Action

To set up an Update action

For more information, see "Criteria Tab".

1. In the criteria list at the top of the action's editing window, specify which records you want to update.

   You edit the criteria list the same way you edit the Search action's criteria list.

   ---

   Caution: For an Update action, be extremely careful when setting the Incl. Empty option to false. You may end up affecting more rows than you intend, possibly even updating all the records from your database table. Just like leaving Incl. Empty set to false in a Search action returns all the records, leaving it to false in an Update action updates all records.

   ---

2. From the Data Sources workspace, drag the columns whose values you want to update into the update columns list at the bottom of the action's editing window.

   ---

   Note: You can specify columns from only one table. If you want to update multiple tables, use an Update action for each table. In this case, consider using a Transaction action to make sure all or none of the updates are processed.

   ---

   Only the values in the columns you specify are modified when the action is executed.

3. Under Value for each column, enter the new value for that column.

   The value can contain any of the value-returning Tango meta tags, which are substituted upon execution of the application file.

For more information about inserting meta tags in entry fields, see "Inserting Meta Tags".

To insert a meta tag, either click the field and choose Insert Meta Tag from the Edit menu, or right click the field and choose Insert Meta Tag from the context-sensitve menu that appears.

If you always want to update a column with a fixed value, simply enter that value.

For more information, see "Quote Value".

The Quote Value option operates in the same way it does in search criteria.

Executing an Update Action

When Tango Server executes an Update action, Tango searches for records matching the specified criteria and updates them with the specified column values.

The Update action returns no results.

[back to top]

Removing a Database Record

The Delete action removes database records that match the specified criteria.

For more information, see "Criteria Tab".

You edit the criteria list the same way you edit the Search action's criteria list.

Setting Up a Delete Action

1. Drag the Delete action icon from the Actions bar into an application file.

   The Delete action editing window appears.

2. In the criteria list of the Delete action's editing window, specify which records you want to delete.

Note: You can specify columns from only one table. If you want to delete multiple tables, use a Delete action for each table. In this case, consider using a Transaction action to make sure all or none of the deletes are processed.

You must specify at least one criterion for the Delete action to be valid.

Caution: For a Delete action, be extremely careful when setting the Incl. Empty option to false. You may end up affecting more rows than you intend, possibly even deleting all the records from your database table. Just like leaving Incl. Empty set to false in a Search action returns all the records, leaving it to false in a Delete action deletes all records.

Executing a Delete Action

When Tango Server executes a Delete action, records matching the specified criteria are deleted.

The Delete action returns no results.

[back to top]

Adding Custom Columns to Database Actions

A custom column entry lets you enter any text as the column reference. You can use custom columns wherever Tango accepts columns dragged from the Data Sources Workspace.

Make sure the text entered makes sense in the database action. For example, in a Search action, you could enter the following calculation as a Select column:

orders.order_amt + 20

To add a custom column to a database action

1. Do either of the following:
   • From the Edit menu, choose Insert Custom Column.
   • Right click any database action editing window where you can add columns and choose Insert Custom Column from the context-sensitive menu that appears.

   The Custom Column Entry dialog box appears.

2. Enter the text to use as the column reference.

   You can insert meta tags here.

3. Choose OK.

   Custom columns can be edited later by double clicking the column reference in the list.