Chapter Four

Using Data Sources

Data Source Basics, Operations, and Properties

A Tango data source contains all the information needed to connect to a particular database. You use data sources to tell your Tango applications which databases to connect to. You use Tango Editor to create and manage data sources.

Both Tango Editor and Tango Server need to see the data source required to access a particular database. Tango Editor uses a data source--via the Data Source workspace--so you can see information in the form of table and columns. Tango Server requires the same data source so it can access the database tables and columns specified within the application file.

The data source properties show the information about the data source, including information about its tables and columns.

This chapter covers the following topics:

• understanding Tango data sources
• the Data Sources workspace
• using data sources, including creating, modifying, and deleting them
• working with data source properties
• connecting to data sources
• assigning data sources to actions.

[back to top]

About Data Sources

Tango supports two types of data sources:

Tango Enterprise for
Mac OS supports two other data source types: FileMaker Pro and Butler SQL (DAM).

• ODBC, in conjunction with third-party drivers, supports connections to a wide variety of database types.
• Oracle supports connections to Oracle databases.

ODBC Data Sources

Open Database Connectivity (ODBC) is a standard developed by Microsoft to allow applications like Tango to communicate with a wide variety of databases from different vendors. An ODBC client application talks to the ODBC driver manager that in turn talks to a database driver for a specific type of database.

An ODBC driver is a kind of translator. It converts the standard ODBC requests made by the application into a format that can be understood by the target database system. ODBC drivers are available for accessing many database management systems (DBMS). Microsoft Access, Excel, Oracle, SQL Server, Informix, Sybase, and Butler SQL are some examples of databases that may be accessed through ODBC.

Before creating an ODBC data source, you must set up your database server and create or install a database on this server. Depending on the database system, you may also need to install and configure additional software to allow you to connect to the server. Consult your database software and ODBC driver documentation for specific instructions. The ODBC system software is installed automatically with Tango, and you need an ODBC driver for the type of database you are connecting to.

For more information, consult the documentation that accompanied your ODBC drivers.

Some ODBC drivers are self-contained. The driver itself accesses and updates the database files. For these ODBC drivers, no other software is required.

Note: If you are trying to connect to Butler SQL on a Windows computer, you must use ODBC.

Oracle Data Sources

Oracle is a high-performance client/server DBMS. To create and use Oracle data sources, you must have Oracle's SQL*Net installed. Tango supports SQL*Net versions 7.1 and 7.3, and greater.

[back to top]

The Data Sources Workspace

You perform most data source operations in the Data Sources workspace. To open the Data Sources workspace, click the Data Sources tab in the workspace window.

When you open the Data Sources workspace, you see a folder for each type of data source that Tango supports.

• Expanding a folder shows the defined data sources for that type.
• Expanding a data source show the tables in that data source. Depending on the settings for the data source, you may need to enter user name and password information before a connection can be made.
• Expanding a table shows the columns in that table.

The bolded data source name in the workspace indicates the currently active data source--that is, the data source assigned to the front-most open action window. If no database actions are active, no data source names are bolded.

Once a connection is made to a data source, the user name used for the connection appears in parentheses after the data source name. This avoids any confusion when different logins are being used for the same data source.

[back to top]

Using the Primary Column Key

A primary key is a column (or combination of columns) whose value uniquely identifies each row in a table. For example, a customer number might be the primary key in a customers table. In the Data Sources workspace, Tango Editor displays the column/key icon next to a column name to show that it is part of the primary key for the table.

Tango builders rely on the primary key column values in various places to identify specific records. When using the builders, it is important to first check that the primary key for each table involved is set correctly. If the specified column or columns do not uniquely identify each record in a table, unexpected results can occur when executing the file. For example, if you mistakenly set the primary key for a customer table to the "state" column (many customers likely share the same state), using the resulting file to delete a particular customer deletes all the customers in the same state.

When connecting to a data source, Tango Editor chooses default primary keys by scanning each table for the first column with an appropriate data type (numeric or character).

Primary key columns appear with a column/key icon to differentiate them. You identify a column as a primary key or not by doing one of the following:

• Right click the column to display a menu of commands, and choose Primary Key Column. A checkmark beside the command indicates a primary key column.
• From the Data Source menu, select Primary Key Column to show a checkmark or not.
• Open the column properties window, and enable or disable the Primary Key option.

[back to top]

Data Source Operations

Creating a Data Source

ODBC

Before you create an ODBC data source, make certain the required ODBC drivers are installed and your database server is running (or, for drivers that access local files, your database files are available).

To create an ODBC data source

You can also right click the Data Sources workspace to display a menu to select the New command from.

1. From the Data Source menu, click New then click ODBC from the submenu.

   The Create New Data Source dialog box appears.

   ---

   Note: The dialog box appearing may look different. The appearance varies depending on the version of ODBC you have.

   ---

2. Select System Data Source from the list of data sources.

   ---

   Note: Tango only supports System data sources.

   ---

3. Choose Next.

See your ODBC driver documentation for detailed configuration instructions.

The Create New Data Source dialog box guides you through the rest of the process. Follow the instructions that appear.

Oracle

Before creating an Oracle data source, make sure the correct Oracle client software is installed and the database server you want to connect to is available on the network.

To create an Oracle data source

You can also right click the Data Sources workspace and choose New from the context-sensitive menu.

1. From the Data Source menu, click New then click Oracle from the submenu.

   The Oracle Data Source dialog box appears.

2. In the Data Source Name field, type a name for the data source.
3. Type the SQL Connect string in the field provided.

   With current versions of SQL*Net, this should be the name of a database alias set up on your computer with the Oracle EasyConfig application.

   ---

   Note: Tango only supports SQL*Net versions 7.1 and 7.3, and greater.

   ---

4. Choose OK.

   The new data source is added to the Data Sources workspace.

Modifying a Data Source

To modify a data source

You can also right click the Data Sources workspace to display a menu to choose Modify from.

1. Select the data source you want to edit.
2. From the Data Sources menu, choose Modify.

   The data source modification dialog box appears.

3. Modify the desired settings for the data source.

For information on changing the settings for each type, see "Creating a Data Source" .

If a modified data source is already loaded, the data source is reloaded automatically using the new settings.

For Oracle data sources, modifying a data source does not affect Tango application files already using that data source, even if they are open when the modification is made. To update a document with the new settings, you must reassign the data source to the document by choosing Set Data Source from the Data Source menu.

Deleting a Data Source

To delete a data source

1. Select the data source you want to delete.
2. Do one of the following:
   • From the Data Sources menu, choose Delete.
   • From the Edit menu, choose Delete.
   • Right click the Data Sources workspace, and choose Delete.
   • From the main toolbar, click the Delete icon.

     

   • On the keyboard, press Delete.

     A confirmation dialog box appears.

     To suppress the confirmation dialog box, hold down Ctrl when deleting.

3. Choose Yes.

   The data source is deleted.

Reloading a Data Source

If the structure of your database changes while Tango Editor is open, you need to reload the data source.

To reload a data source

You can also right click the Data Sources workspace to display a menu to choose Reload from.

1. Select the data source you want to reload.
2. From the Data Sources menu, choose Reload.

   The log in information is as specified in the data source's Log In properties.

Handling Unknown Data Sources

For Oracle data sources, actions do not rely on anything in Tango Editor's list of data sources to connect to a data source. When connecting, Tango Editor uses only the information stored in the data source. For this reason, Tango Editor could connect to an action's data source while not having a data source defined with matching parameters. Such a data source is called an unknown data source.

Note: ODBC data sources are an exception. Tango ODBC data sources and those defined on the computer are the same.

For more information on the default log in settings, see "Development Tab" .

When you open an action having an undefined data source in Tango Editor, yet Tango Editor is able to connect to it, the unknown data source is added automatically to the list in the Data Sources workspace. The log in information for the new data source is set to the defaults.

This happens even if there is a data source defined with the same parameters, but with a different data source name. That is, all of the pieces of data source information must match in order for an existing one to be used.

[back to top]

Working With Data Source Properties

When you select a data source, a table, or a column in the Data Sources workspace, the corresponding Properties window appears. These windows allow you to view information about the selected object.

Setting Log In Options for a Data Source

The Data Source Properties window contains three tabs: General, Development, and Deployment. Click a tab to display the corresponding properties section.

General Tab

The General section shows the information making up the data source. The following is an example of the information for an ODBC data source called "MyDataSource".

The data source name and type appear for all data sources, but the information in the other fields depends on the type of data source.

To edit the selected data source, choose Modify.

The data source editing dialog box for the data source appears. When you close the dialog, any new settings appear in the General section.

Development Tab

The Development section shows the log in information required by Tango Editor for connection to the data source. The Development tab in the properties dialog box asks for a user name and password for each type of data source.

The Ask each time option means Tango Editor always asks for connection information when needed. For new data sources, this option is selected by default.

Deployment Tab

The Deployment section allows you to specify different log in information to be used when Tango Server executes the action the data source is assigned to. The Username and Password fields may also contain meta tags that are substituted when Tango Server executes the application file.

The Same as development option is the default, meaning the user name and password used during development is also used by Tango Server.

When you right click the Username and Password fields, a menu appears containing standard editing commands, as well as the Insert Meta Tag command. You can use Insert Meta Tag to insert many of the commonly used Tango meta tags.

Before connecting to a data source, Tango checks the data source parameters for the presence of meta tags. If meta tags are found, the substitution is performed, and the results are used for establishing the connection. If no meta tags are found, the data source parameters are passed literally.

The following example shows that user name and host name are obtained from the user variables username and hostname, respectively. The user password is taken from the file, whose name corresponds to the user name, anchored by the .pwd extension.

Username: <@VAR NAME="username">

Password: <@INCLUDE FILE="<@VAR username>.pwd">

Table Properties

Table Properties shows the name, owner, and type of table.Column Properties

The Column Properties window displays the name, title, data type, length, whether nulls are allowed or not, and whether the column is a primary key or not.

In this dialog box, you can edit the Title field, and select the Primary Key option.

Note: This title is used by the builders as the default HTML display title for the column. See "Column Options".

[back to top]

Connecting to Data Sources

When you expand a data source in the Data Sources workspace you have not connected to, the log in information specified in the Data Source Properties Development window is used for the connection. If you selected the Ask each time option, the Log In dialog box appears allowing you to type your user name and password.

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

If a data source has more than the specified minimum number of tables (the default is 25), the Select Tables dialog box appears, allowing you to work with a more manageable subset.

Connecting to Large Data Sources

When Tango Editor connects to a data source containing more than 25 tables, it presents the Select Tables dialog box, allowing you to select which tables you want to work with.

The Available tables list shows the tables in the data source. Drag the tables you want to work with from this list into the Tables to use list. You can use the Owner and Type drop-down lists to filter the tables shown in the Available tables list.

For example, to show only tables owned by a specific user, select that user from the Owner drop-down list. To show only system tables, select SYSTEM TABLE from the Type drop-down list. (The contents of these drop-down lists are determined by the data source; only owners and types existing in the database are listed.)

You can also invoke the Select Tables dialog box at any time to select the tables to work with. From the Data Source menu, choose Select Tables to invoke the dialog box.

Editing and Executing Files on Different Computers

When connecting to a data source, Tango relies on configuration information not included in the Tango application file itself. This becomes an issue when Tango Editor and Tango Server reside on different computers, and when editing a file created on a different computer. Tango cannot connect to the data source unless the computer is set up correctly.

The following explains what pieces of data source information are stored in the file, what ones are not, and how to ensure a file works on a computer other than the one it was created on.

ODBC Data Sources

Files assigned ODBC data sources have these pieces of information stored in them:

• ODBC data source name
• user name
• password

For the data source connection to be made on another computer, a data source with the same name pointing to the original database must exist. The user name and password must also be valid for the server pointed to by the data source.

Oracle Data Sources

Files assigned Oracle data sources have these pieces of information stored in them:

• SQL connect string or database alias name
• user name
• password

If you specified a SQL connect string (such as T:199.230.9.8:ORCL) when defining the data source, your Tango file works on any computer the string points to that has access to the Oracle database server. This is because all the connection information is stored right in the string.

[back to top]

Assigning Data Sources to Actions

To assign a data source to an action, simply drag a column from a data source to an action editing window.

Note: Some actions (for example, Transaction actions) do not have columns. If you drag a database action that has no columns you are prompted to select a data source.

For more information, see "Setting Log In Options for a Data Source".

If Tango Editor has not yet connected to the data source, a log in dialog box may appear. This dialog box only appears if you have the Ask each time option enabled, which is the default, in the Development section of the Data Source Properties window.

If an action already has a data source assigned to it and you drag a column into it from a different data source, you are asked if you want to cancel the operation or to use the new data source instead.

Note: If there are differences in the structures of the databases, changing an action's data source may cause DBMS errors when the action is executed.

If you use a new data source, Tango Editor scans the affected actions to update the table owner information to match the new data source.