Chapter Twenty

Using Advanced Database Actions

Transaction and Direct DBMS Actions, and Joining of 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 cannot use transactions with FileMaker Pro data sources.)

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, such as FileMaker Pro.

Setting Up a Transaction Action

Begin Transaction

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

To set up a Begin Transaction action

For more information, see "Setting Data Sources for Actions".

For more information, see "Connecting to Large Data Sources"..

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

   The Data Source Selection dialog box appears.

2. Select a data source and click OK.

   If username and password are required for this data source, a dialog box appears, where you can enter your username and password.

3. Enter your Username and Password into the respective fields, and click OK.

   If there are more than the maximum number of tables in the data source, and this is the first time you have accessed this data source in this Tango Editor session, the Select Tables dialog box appears, allowing you to choose which tables you want visible in the data source. Select the tables and click OK.

   The Begin Transaction action dialog box becomes active.

4. Click a radio button to select the isolation level 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.

5. Click anywhere outside the Begin Transaction dialog box to close it.

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 palette into an application file.

   The End Transaction action dialog box appears.

2. Click a radio button to select the option you want 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.

3. Click anywhere outside the End Transaction dialog box to close it.

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.

For FileMaker Pro data sources, this action type allows you to execute a FileMaker Pro script: see "Executing a FileMaker Pro Script (Direct DBMS Action)".

Setting Up a Direct DBMS Action

To set up a Direct DBMS action

For more information, see "Connecting to Large Data Sources".

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

   The Data Source Selection dialog box appears.

   If there are more than the maximum number of tables in the data source, and this is the first time you have accessed this data source in this Tango Editor session, the Select Tables dialog box appears, allowing you to choose which tables you want visible in the data source.

2. Select the data source you want to perform SQL Query window operations against, and click OK to load the tables and columns of that database.

   If username and password are required for this data source, a dialog box appears, allowing you to enter your username and password.

3. Enter your Username and Password into the respective fields.

4. Click 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 perform standard editing operations in the Direct DBMS action editing window by one of the following methods:

• From the Edit menu, choose the editing command you want.

• Click the appropriate editing icon on the main toolbar.

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 one of the following:

   • From the Edit menu, choose Insert Meta Tag.

   • Control+click the action editing window, and choose Insert Meta Tag from the contextual 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>

When you perform an SQL query where you are selecting an aggregate function or calculated column, the column name depends on the database:

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.

You can use bound values in SQL by using the <@BIND> meta tag. For more information, see "<@BIND>" in the Meta Tags and Configuration Variables manual.

Note: 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)}

Executing a FileMaker Pro Script (Direct DBMS Action)

You can use the Direct DBMS action to execute a FileMaker Pro script.

To execute a FileMaker Pro script

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

   The Data Source Selection dialog box appears.

2. Choose your FileMaker Pro data source.

3. Click OK.

   The Direct DBMS action editing window appears. This window is specific to FileMaker Pro.

   You may enter the script name manually, select one from the pop-up menu, or choose Variable from the pop-up menu to enter a meta tag which is evaluated at execution time.

Note: The scripts in the pop-up menu are the scripts that are defined in the FileMaker Pro database you are connected to. The Results tab does not pertain to FileMaker Pro and is inoperative.

When Tango Server executes a Direct DBMS action for a FileMaker Pro database, the records specified by the script are returned.

[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 company and user information in two different tables. You want to create a search to find the company for any given user, and display in your browser the company information with the corresponding user's name. Because the user table contains only the company's identifier, you have to join the two tables to get full information on the company.

Note: It is not possible to perform joins with Filemaker Pro layouts.

The customer table (COMPANY) has a record for each company including a company name, address, manager, and Web site. The user table (USERS) has a record for each user including their company, name, and telephone number. The COMPANY table is related to the USERS table by an identifier in the COMPANY column: both tables have COMPANY columns.

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 joins tab of the Search action or Search builder.

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 COMPANY column in the left table, COMPANY, and the COMPANY column in the right table, USER. Then, from the pop-up menu, you select the type of join you want to use.

• If you select a standard join (=), the search returns only rows of company information where a valid company is found. If none is found, the corresponding row is not returned. For instance, if a user has not been assigned a company and thus the COMPANY column is blank for that record, that user is not returned.

• In contrast, if you define a right outer join (=*), the search returns all rows of user information, regardless of whether a company is found or not.

• A left outer join (*=) returns rows of information based on the companies found, including any companies without users assigned.

Working With Joins

To work with joins, you must first have your Search action or Search Builder action editing window open.

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. Choosing Define opens the Joins window of the current dialog box.

You create, modify, and delete joins using the Joins tab of the Search editing window or Search Builder.

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.

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 join, drag the columns you want to include in the search from the Data Sources Workspace into the Joins window.

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

To create a new join in a Search action

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

   The Joins window appears.

   Drag columns from a data source in the Data Source Workspace to this window.

2. From the Table pop-up menus, select the left and right tables for the join.

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. pop-up menu, 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.

Inserting a Join

To insert a join definition

Do one of the following:

• Click in the list area of the Joins window. From the Edit menu, choose Insert.

• Control+click the Joins window and choose Insert from the contextual menu that appears.

Editing a Join

To edit a 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 one of the following to edit a definition field:

   • Click the field twice.

   • Control+click and choose Edit from the contextual menu that appears.

The field changes to a pop-up menu so you can choose a different entry.

Deleting a Join

To delete a 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. Select the join definition you want to delete, and do one of the following:

   • From the Edit menu, choose Clear.

   • Press Delete on your keyboard.

   • Control+click the selected join definition(s) and choose Delete from the contextual menu that appears.

   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.

   ---

   Tip: You can bypass the confirmation dialog box by holding down the option key when choosing Delete.

   ---

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

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".

The Search and Record List option groups of the Search Builder 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 associated Joins window by clicking the General and Joins tabs, respectively, at the top of the main Search Builder window.

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