Chapter Eighteen

Using Advanced Database Actions

Setup and Operation of Transaction and Direct DBMS Actions, and Joining Database Tables

You can put several database actions together to create a single transaction that manages the work being performed. Using Begin Transaction and End Transaction actions you can specify where to begin, commit, and rollback database changes.

You can use the Direct DBMS action to execute specified SQL statements and return any results generated.

Relational databases let you specify joins to permit searches involving more than one table. A join tells the database how the tables are related. A standard join preserves only those rows from a search in which a match exists with the joined table. An outer join preserves all the rows in one of the tables, even if there is no match with the other table.

The topics covered in this chapter include:

• setting up and executing Transaction actions
• setting up and executing the Direct DBMS action
• understanding joins
• creating and editing joins.

[back to top]

Creating Database Transactions

Tango supports special database actions that allow you to specify where to begin, commit, and rollback database changes. Using the Begin Transaction and End Transaction actions, you can create a well-defined single transaction.

Normally, actions executed by Tango Server that change the content of databases (Insert, Update, Delete, and Direct DBMS actions) cause an immediate change to the database. This is because Tango automatically sends a COMMIT command as the final step in its execution of these actions.

However, Transaction actions let you control when database changes are made permanent and also let you undo (or ROLLBACK) the effects of actions that have been executed.

To perform a Transaction action, Tango maintains a database connection longer than it would for other actions. You should consider the impact this may have on your server and database resources before deciding to use Transactions in your application file.

Note: Tango Transaction actions have no effect on databases that do not support transactions.

Setting Up a Transaction Action

The Begin Transaction action indicates the beginning of a transaction on a particular data source.

To set up a Begin Transaction action

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

   The Data Source Selection dialog box appears.

2. Select a data source.
3. A dialog box appears where you can enter a username and password, if necessary.

   Enter the Username and Password and choose OK.

4. Choose OK.

   The Begin Transaction action editing window becomes active.

5. Specify the isolation level attribute you want to assign to the Begin Transaction action.
   • Read/Write exclusive. Locks rows that are read as part of the transaction until a COMMIT or ROLLBACK command is issued to the database server.
   • Read uncommitted. Reads rows that have been changed by other database users in a transaction, but for which the transaction has not been committed or rolled back.

End Transaction

The End Transaction action marks the end of the transaction and either commits it (saves all the changes) or rolls it back (discards all the changes).

To set up an End Transaction action

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

   The End Transaction action editing window appears.

2. Specify in the action editing window the attribute you want to assign to the End Transaction action:
   • Commit. Commits any modifications made to the database during the current transaction.
   • Rollback. Undoes any modifications made to the database during the transaction.

In the application file, the End Transaction action icon changes to reflect the associated attribute.

Executing a Transaction Action

If Tango Server detects that an End Transaction action is being executed without first executing a Begin Transaction action, it reports a runtime error. It is also an error to begin another transaction before an existing transaction is committed or rolled back.

Database actions on data sources that are not the transaction data source are automatically committed.

If the application file ends without executing an associated End Transaction action or a Return action, then a Rollback End Transaction action executes automatically.

Tip: When executing a transaction, your application could slow down; additional RAM may be required for Tango Server.

[back to top]

Executing SQL

The Direct DBMS action executes specified SQL statements and returns any results generated.

Setting Up a Direct DBMS Action

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

   The Data Source Selection dialog box appears.

2. Select a data source.
3. A dialog box appears where you can enter a username and password, if necessary.

   Enter the Username and Password and choose OK.

4. Choose OK.

   An empty Direct DBMS action editing window appears, displaying SQL and Results tabs.

   Fill in the tabbed windows as described next.

The Direct DBMS Action Editing Window

SQL Tab

Click the SQL tab to display the Direct DBMS action SQL text area in which to enter the SQL to be executed.

For information on constructing SQL statements, consult your database or ODBC driver documentation.

All statements are executed against the database specified in the data source associated with the Direct DBMS action.

You can easily enter column or table names by dragging them from the Data Sources Workspace. If you drag multiple columns, they are separated with commas.

You can also right click the Direct DBMS action editing window to display a context-sensitive menu of commands.

You can also perform standard editing operations in the Direct DBMS action editing window. Editing commands are available from the Edit menu.

You can reference any value-returning Tango meta tags in your SQL.

To insert a meta tag in the Direct DBMS window

1. Click the action editing window where you want to enter a meta tag.
2. Do either of the following.
   • From the Edit menu, choose Insert Meta Tag.
   • Right click the action editing window, and choose Insert Meta Tag from the context-sensitive menu that appears.

For information on filling in the Insert Meta Tag dialog box, see "Inserting Meta Tags".

The Insert Meta Tag dialog box appears allowing you to specify a meta tag and inserts it at the insertion point in the SQL text area.

For an example of executing different SQL based on the type of data source in use, see "<@DSNUM>" in the Meta Tags and Configuration Variables manual.

You can use the <@IF>, <@IFEQUAL>, and <@IFEMPTY> meta tags in the SQL text to include or exclude SQL based on the result of a comparison at execution time. For example, you could use this capability to execute different SQL based on the type of data source in use.

Direct DBMS SQL Auto-Encoding

For more information, see "SQL" in the Meta Tags and Configuration Variables manual.

Tango Server automatically performs SQL encoding on meta tag values substituted in Direct DBMS SQL. For example, if a variable called myName contains "O'Brien":

SELECT * FROM customer WHERE cust_name = '<@VAR NAME=myName>'

This results in:

SELECT * FROM customer WHERE cust_name = 'O''Brien'

If Tango did not do this, the result would be:

SELECT * FROM customer WHERE cust_name = 'O'Brien'

and a DBMS error would result due to the unescaped quote.

For more information, see "Encoding Attribute" in the Meta Tags and Configuration Variables manual.

If your Direct DBMS SQL contains meta tags that evaluate to an entire (or partial) SQL statement constructed elsewhere, the quote-doubling may cause DBMS errors. This is because all single quotes are doubled, even those meant to delimit a string. In this case, the solution is to modify the meta tag(s) returning your SQL by adding the ENCODING=none attribute. For example:

<@VAR NAME=mySQL ENCODING=none>

Results Tab

Click the Results tab to display the results options you can set for the Direct DBMS action.

You can specify options for the maximum number of records to retrieve from the data source and at which result record number retrieval begins.

Number of rows to retrieve

To return all matching rows, select No Maximum.

To limit how many records are returned by the action, select Limit To and enter the maximum number of rows to retrieve.

Start retrieval at match number

Use this option to skip some of the matching records. Enter "1" (the default) to start retrieval with the first matching record. When a value other than "1" is entered into this field, the Direct DBMS action returns records starting at that number, skipping any records before it.

Both of these fields can contain meta tags which return values.

Executing a Direct DBMS Action

When Tango Server executes a Direct DBMS action, the specified SQL is sent to the data source for execution.

Any result rows are returned to Tango and may be accessed in the action's Results HTML. As with the Search action, a <@ROWS>
<@/ROWS> block is used to iterate through the records returned. You must specify column references differently, however.

You can use <@COLUMN> to refer to your columns by name for ODBC data sources, but for non-ODBC data sources, you must refer to your columns in Results HTML by number, using the <@COL> meta tag.

For example, if your Direct DBMS action executed the following statements with a non-ODBC data source:

SELECT maintable.price, maintable.classification, maintable.manufacturer
FROM maintable

the following Results HTML would print the database results:

<@ROWS>
maintable.price: <@COL 1><BR>
maintable.classification: <@COL 2><BR>
maintable.manufacturer: <@COL 3><BR>
<HR>
</@ROWS>

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

Note:

• The Tango Oracle data source type does not permit the retrieval of results from a procedure execution specified in a Direct DBMS action. Only SELECT's return data for Oracle data sources.
• When calling a stored SQL server procedure from a Direct DBMS action with an ODBC data source, you should use the following syntax:

{call procedureName(param1,param2,paramX)}

[back to top]

Joining Database Tables

For more information on joins, consult your DBMS documentation, such as, The Practical SQL Handbook (J.S. Bowman, et. al., ISBN: 0-201-62623-3), or any other good SQL reference guide.

To understand how joins work, consider a database with vendor and associated account manager information in two different tables. You want to create a search to find the account manager for any given vendor, and display in your browser the vendor information with the corresponding account manager's name. Because the vendor table contains only the account manager's identifier, you have to join the two tables to get the account manager's full name.

The vendor table (VENDOR) has a record for each vendor including a vendor identifier, name, contact information, payment terms, and an account manager identifier. The account manager table (ACCTMGR) has a record for each account manager including the account manager's identifier, name, and telephone number. The vendor table is related to the account manager table by an identifier in the AcctMgr column; the account manager table has a MgrID column that contains the identifier corresponding to the AcctMgr column in the vendor table.

For more information, see "Creating a Join in a Search Action".

Using a Tango search, you select the columns you want to relate and define the type of join in the Join Definition dialog box.

In addition to the standard join, you can define an outer join, which can be left or right. A left outer join means all rows in the left-specified table are returned, including those with no match in the right-specified table. A right outer join means all rows in the right-specified table are returned, including those with no match in the left-specified table.

For this example, you would select the MgrID column in the left table, ACCTMGR, and the AcctMgr column in the right table, VENDOR. Then, from the drop-down list you select the type of join you want to use.

• If you select a standard join (=), the search returns only rows of vendor information where a valid account manager's identifier is found. If none is found, the corresponding row is not returned. For instance, if a vendor has not been assigned an account manager and thus the MgrID column is blank for that record, that vendor is not returned.
• In contrast, if you define a right outer join (=*), the search returns all rows of vendor information, regardless of whether an account manager is found or not.
• A left outer join (*=) returns rows of vendor information based on the account managers found, including any account managers without vendors assigned.

Working With Joins

You can include columns from more than one table in a search, if you define joins for the tables.

If you select columns from more than one table in a search, a message appears telling you to define a join.

Choose either Define to create the join definition now or Later if you want to define the join at a later time.

When you define the join, it adds the columns to the search. In the Search Builder, you must define the join before you build the actions for the search or save the application file.

You create, modify, and delete joins using the Search editing window, under the Joins tab. To work with joins, you must first have your Search action or Search Builder action editing window open.

Note: In earlier versions of Tango you could get join information by using the Attribute menu's Joins command or the Joins icon in the Attributes bar. Join information is viewed in the Search action or Search Builder editing window.

Creating a Join in a Search Action

To create a new join in a Search action

To create a join, drag the columns from the Data Sources Workspace into the Joins window.

Note: You cannot create a join from two different data sources.

1. In the Search editing window, click the Joins tab.

   The Joins window opens.

   If you added columns from different tables to the Select Columns list (under the Select tab), a join definition already appears, showing you the tables selected and the first column added from each table. The default operator is "=".

2. From the Table drop down lists, select the left and right tables for the join.

   To select an entry for any definition field, do either of the following:

   • Click the field twice.
   • Right click and choose Edit from the context sensitive menu.

   The field changes to a drop down list box so you can choose a different entry.

3. From the Column lists, select the columns you want to join in each table. A table's first column appears as the default in the list.
4. From the Oper. drop down list, select a join operator.

   Join Operator	Description
   =	Standard join (the default). Only records matching the join criterion are returned.
   *=	Left outer join. All left-table rows are returned, including those with no match in the right table.
   =*	Right outer join. All right-table rows are returned, including those with no match in the left table.

   To add additional join definitions, right click the Joins window and choose Insert from the context sensitive menu.

   A new row is added to the window.

Editing a Join

To edit an existing join definition

1. Click the Joins tab in the Search action editing window.

   The Joins window appears, showing you the current join definition(s) including table names, joined columns, and join operator.

2. Do either of the following to edit a definition field:
   • Click the field twice.
   • Right click and choose Edit from the context sensitive menu.

The field changes to a drop down list box so you can choose a different entry.

Deleting a Join

To delete an existing join definition

You can Shift-click (contiguous rows) or Ctrl-click (discontiguous rows) to select multiple join definitions.

1. Click the Joins tab in the Search action editing window.

   The Joins window appears, showing you the current join definition(s) including table names, joined columns, and join operator.

2. Do either of the following to delete a join definition or definitions from the Joins window:
   • Select the join definition(s) and press Delete.
   • Right click the selected join definition(s) and choose Delete from the context sensitive menu.

   A message appears asking you to confirm that you want to delete the selected row(s).

3. Click Yes to delete the selected rows or No to cancel.

Note: If your Search action refers to columns from the deleted joined table, you need to remove these columns and references from the action or builder window manually.

Creating a Join in the Search Builder

To create a new join in the Search Builder

To create a new join in the Search Builder, drag the columns from the Data Sources Workspace into the Joins window.

The Search and Record List option groups share the same join information because they both apply to the same generated Search action. You can specify separate join information for the Record Detail option group.

You switch from the Search, Record List, or Record Detail window to the Joins window by clicking the General and Joins tabs, respectively, at the bottom of the main Search Builder window.

Note: You cannot create a join between two different data sources.

You create, edit, and delete joins in the Joins window the same way you do for a Search action. See "Creating a Join in a Search Action".