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 have access to data sources. Tango Editor uses a data source--via the Data Sources Workspace--to show you the information in the form of tables and their columns. Tango Server requires the same data source, or a data source with the same name on the deployment machine, so it can access the database tables and columns specified within the application file.
Note: FileMaker Pro databases
use a different terminology. Columns are referred to as fields, and
tables are referred to as layouts.
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 the primary column key
- using data sources, including creating, modifying, and deleting them
- managing Pervasive.SQL data sources and databases
- setting up deployment data sources
- working with data source properties
- connecting to data sources
- assigning data sources to actions.
![[top]](arrow.gif){kind=icon}
[back to top] About Data Sources
Tango 2000 for Windows supports Pervasive.SQL, ODBC and Oracle data sources.
- DAM (Data Access Manager) supports connections to Butler SQL databases.
- FileMaker Pro supports connections to FileMaker Pro databases.
- ODBC (Open Database Connectivity), in conjunction with third-party drivers, supports connections to a wide variety of database types.
- Pervasive.SQL is supported as an
ODBC driver on the Macintosh platform, which can connect to Pervasive.SQL
Server or Workgroup running on Windows or Unix.
Pervasive.SQL data sources are displayed as a separate item in the Data Sources Workspace window.
DAM Data Sources
The Data Access Manager (DAM) is a part of the Mac OS that allows DAM-aware applications to communicate with databases. Tango uses DAM to give you access to Butler SQL servers. You can also access Butler SQL servers using ODBC.
Before creating a DAM data source, you must set up your Butler SQL server, create a database (using the ButlerTools application) and install it in the server's Public Databases folder. On the Tango Editor machine, you must have the ButlerLink Access database extension installed, and you must use the ButlerHosts application to create a host that connects to the Butler SQL server. Consult your Butler SQL documentation for more information.
FileMaker Pro Data Sources
Tango lets you connect to FileMaker Pro databases through a local FileMaker Pro application (version 3.0 or later) running on the same machine as Tango, or on any Mac OS computer on the same AppleTalk network.
Note: Tango cannot access
databases through FileMaker Pro Server or the Windows version of FileMaker Pro.
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.
The Tango installer installs ODBC drivers. See the Getting Started Guide for Macintosh for more information.
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: To connect to Butler SQL
on a Windows computer, you must use ODBC.
Pervasive.SQL Data Sources
Pervasive.SQL data sources are ODBC data sources and are treated separately in the Data Sources Workspace. For more information on ODBC, see the previous section.
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. For more information, see the Getting Started Guide for Macintosh.
[back to top] The Data Sources Workspace
You perform most data source operations in the Data Sources Workspace (the Data Sources section of the Workspace). To display 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 shows 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.
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 Primary Key Columns
A primary key column 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.
Primary key columns are identified in the Data Sources Workspace by the column/key icon.
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 column 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 queries the database for information to determine the primary keys. If there is no response, Tango determines the default primary keys by scanning each table for the first column with an appropriate data type (numeric or character).
To change or add a primary key column
- In the Data Sources Workspace, control+click the column and choose Primary Key from the contextual menu that appears. A check mark in the menu identifies a primary key column.
- Control+click the column in the Data Sources Workspace, choose Properties from the contextual menu that appears, and check or uncheck the Primary Key checkbox in the Properties dialog box.
- Select a column in the Data Sources Workspace and choose Primary Key from the Data Source menu. A check mark in the menu identifies a column as a primary key.
[back to top] Data Source Operations
Creating a Data Source
DAM
Before creating a DAM data source for a local database, you must set up your Butler SQL server, create a database (using the ButlerTools application), and install it in the server's Public Databases folder. The ButlerLink Access database extension must be in the Extensions folder on your hard drive. Use the ButlerHosts application to create a host that connects to the local or networked Butler SQL server.
To create a DAM data source
For more information on setting up a Butler Host, consult your Butler documentation.
- From the Data Source menu, choose New, and then DAM....
- Select the host you want, and click OK.
- Fill in the User and
Password fields, and click OK.
Note: If your Butler SQL
server has access privileges enabled, you need to enter a valid user name and
password. Otherwise, you can leave these fields empty.
The Databases dialog box appears.
- Select the database you want, and click OK.
- Type in a name for the data source, and click
OK.
The data source you have configured is displayed in the Data Sources Workspace.
FileMaker Pro
For Tango Editor to connect to a local FileMaker Pro data source, the database must be open already in FileMaker Pro, or there must be an alias of the database in the same folder as Tango Editor. For a network data source, the specified database and FileMaker Pro must be open on the machine networked to.
To create a local FileMaker Pro data source
For information on a networked FileMaker Pro data source, see "To create a networked FileMaker Pro data source".
- From the Data Source menu, choose
New, then choose FileMaker
Pro.
The FileMaker Application dialog box appears.
There are two options for creating a local FileMaker Pro data source:
- Any FileMaker application causes Tango to look for any FileMaker Pro application running on the same computer when the data source is used. If no FileMaker Pro application is running, Tango attempts to find and launch any FileMaker Pro application located on the computer.
- Named FileMaker application lets you designate a specific FileMaker Pro application to be used for the data source. You specify the FileMaker Pro application by entering its exact name in the text field or by clicking Choose and selecting the copy of FileMaker Pro you want. This option is useful if you want to run multiple FileMaker Pro applications on your Web server.
If you have only one version of FileMaker Pro running on your computer, Any FileMaker application is the quickest way to get to and open your database(s). The next steps demonstrate how to use Any FileMaker application.
- Select Any FileMaker application.
- Click OK.
- From the list of opened databases (if any are opened), select the database you want and click OK, or click Other to search your computer.
- Select the database you want, and click
Open.
The database name appears in the Opened Databases dialog box list.
- Select your database, and click OK.
- Name your data source. The default name is the name
of the database.
The data source appears in the Data Sources Workspace, under the expanded FileMaker Pro folder.
- From the Data Sources Workspace, double-click the
data source.
A login dialog box with the name of your data source appears.
Note: The
Username field is disabled for FileMaker Pro data sources.
- Enter your password, and click OK.
The data source is now active. To view the tables, expand the data source folder.
To create a networked FileMaker Pro data source
For information on local FileMaker Pro data sources, see "To create a local FileMaker Pro data source".
- From the Data Source menu, choose New, then FileMaker Pro.
- Select Networked FileMaker application.
- Click Choose.
Note: If you want to connect
to a networked FileMaker Pro data source, you must have program
linking turned on. Make sure program linking is turned on in the Sharing
Setup control panel, in the Users & Groups control panel for a Guest, and
in your FileMaker Pro application.
A network configuration dialog box appears.
- Select the zone containing the computer running FileMaker Pro, the computer, and the program link you want to connect to (FileMaker Pro).
- Click OK.
The FileMaker Application dialog box appears again.
The text fields for the networked configuration are filled in.
- Click OK.
- Double-click the database you want, or click it once then click OK.
ODBC
Before you create an ODBC data source, make sure the required ODBC drivers are installed and your database server is running (or, for drivers that access local files, your database files are available).
For a networked ODBC data source, make sure your ODBC drivers are installed.
To create an ODBC data source
- Do one of the following:
- Select the driver you want to use and click
OK.
Note: The ODBC data source
creation process depends on which ODBC driver and which version you have
installed on your machine. See your ODBC driver documentation for detailed
instructions.
- Enter the required information in this dialog box
and the subsequent dialog boxes, until you have completed the data source
creation process.
Your new data source appears in the Data Sources Workspace, under the expanded ODBC folder.
Pervasive.SQL
Before you create a Pervasive.SQL data source, you must install the required Pervasive.SQL ODBC driver on your machine, which can connect to a Pervasive.SQL database server running on the network.
Note: The Pervasive.SQL ODBC
driver for Macintosh is available for download at the Pervasive Web site. See
the following URL:
http://www.pervasive.com/products/psql/odbc_for_mac/
To create a new Pervasive.SQL data source
- Do one of the following:
- From the DataSource menu, choose New, then choose Pervasive.SQL.
- Control+click in the Data Sources Workspace, and choose New ODBC... from the contextual menu that appears.
A dialog box appears, asking you to select an ODBC driver for the new data source.
- Select Pervasive ODBC Client and click OK.
- In the Client area, enter a name in the Data Source Name field. This is the data source name that appears in the Workspace.
- In the Description field, enter an optional description of the data in the data source; for example, "e-mail addresses and phone numbers of all employees".
- In the Server area, specify the address of the server in the Address field. This can be a machine name, TCP/IP address, or an IPX/SPX address (for example, 172.16.34.121).
- In the Data Source Name field, enter the database you want to connect to.
- If you need to change the options for this data
source, click Options.
The Options dialog box appears.
Refer to the Pervasive.SQL documentation and enter the required information in this dialog box.
- Click OK to close the Options dialog box, if necessary; click OK to close the Pervasive ODBC Client DSN Setup dialog box.
Oracle
For more information about Tango 2000 Oracle requirements, see the Getting Started Guide.
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
- Do one of the following:
- In the Data Source Name field, type a name for the data source.
- Type the SQL Connect string in the field provided.
The string must be in the format required by the Oracle client software. Usually, it looks something like T:199.230.9.8:ORCL. You may, instead, enter an alias in this field. Aliases are created using the Oracle Easy Config application.
Note: Tango only supports
SQL*Net versions 7.1 and 7.3, and greater.
- Click OK.
Modifying a Data Source
To modify a data source
- Select the data source you want to modify.
- Do one of the following:
- Enter the required information in this dialog box and the subsequent dialog boxes, until you have completed the data source modification process.
Note: The ODBC data source
modification process depends on which ODBC driver and which version you have
installed on your machine. See your ODBC driver documentation for detailed
instructions.
If a modified data source is already loaded, the data source is reloaded automatically using the new settings.
Deleting a Data Source
To delete a data source
- Select the data source you want to delete.
- Do one of the following:
- Click OK.
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
The login information is as specified in the data source's login properties.
[back to top] Assigning Data Sources to Actions
To assign a data source to an action, do one of the following:
For more information, see "Setting Data
Sources for Actions".
- Drag a table or column from the Data Sources Workspace to a database action editing window or builder window.
- From the Data Source menu, choose
Set Data Source....
The Data Source Selection dialog box appears. Use this dialog box to set the data source for the action.
- Control+click on an action, and choose Set Data Source from the contextual menu that appears.
The Data Source Selection dialog box appears. Use this dialog box to set the data source for the action.
The data source icon and data source name appear next to the assigned action.
Tip: You can also use the
Set Data Source command to set data sources and data source
parameters for one or more actions. For more
information, see "Setting Data Sources for Actions"..
For more information, see "Working With Data Source Properties".
If Tango Editor has not yet connected to the data source, a login dialog box may appear. This dialog box only appears if you have the Ask each time option checked, which is the default, in the Development section of the Data Source Properties window.
Note: The Direct DBMS action
does not have a field into which you drag columns. When you drag in a Direct
DBMS action, you are prompted to select a data source.
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 and updates the table owner information to match the new data source.
[back to top] Setting Up Deployment Data Sources
Tango Editor allows you to specify deployment data source parameters that are different from development data source parameters; you can use meta tags in your application files to specify deployment data source parameters. Using deployment data sources, you can:
- execute a Tango application file against multiple data sources
- deploy a Tango application file against a data source (ODBC, OCI) other than the one that you developed with.
You can specify deployment data source parameters for each Tango action in an application file on a per-action basis; these can override the default data source settings.
Caution: Deployment data
sources must point to either the same database as the development data source
or one with the same structure and table owner names. Table owner names are
stored within the Tango application file and not within the data source, so the
development and deployment owner names cannot be different.
The following sections describe how to set the parameters of a data source, and how to set deployment (or development) data sources for actions using the Set Data Source command.
For more information, see "Disabling the Use of Meta Tags in Data Sources".
Note: In order to use meta
tags in deployment data sources, the Tango administrator must set the Tango
Server's passThroughSwitch configuration variable to on.
Setting Deployment Data Source Properties
You can set deployment properties for data sources in the Deployment section of the Data Source Properties dialog box. This allows you to specify run-time data source parameters.
To view the Deployment section of the Data Source Properties dialog box:
- Select the data source in the Data Sources Workspace.
- Do one of the following:
- Click the Deployment tab.
If you check Same as development, the Type, Name, Database, Username and Password fields are disabled; the default values of these fields are transferred from the development data source to deployment data source.
If you uncheck Same as development, specify the deployment data source parameters.
Deployment Data Source Parameters
- Type. Specify the type of data
source, or enter a meta tag that evaluates to a data source type when the Tango
application file is executed (for example, <@VAR
NAME=datasourcetype>).
The Type field must evaluate to one of the type strings shown in the combo box pop-up menu:
- Name. This field must evaluate to a
valid specifier, dependent upon the data source type:
- DAM: DAM host name (Macintosh-only)
- FileMaker: path to the FileMaker Pro application, or the yen symbol "¥" to indicate "Any" (Macintosh-only)
- Pervasive.SQL: data source name
- ODBC: data source name
- Oracle: connect string or database alias
The deployment data source name field for Oracle is NOT the name you have given to a data source in Tango Editor.
You can also enter a meta tag that evaluates to a valid specifier when the Tango application file is executed (for example, <@VAR NAME=datasourcename>).
- Database (Macintosh-only). This
field is used only for FileMaker Pro and DAM data source types and must
evaluate to a valid database name.
You can also enter a meta tag that evaluates to a valid specifier when the Tango application file is executed (for example, <@VAR NAME=databasename>).
- Username and Password. These fields may contain meta tags that are substituted when Tango Server executes the application file. Username is not used for FileMaker Pro data sources.
Meta Tags and Deployment Data Sources
All fields may contain meta tags, which are substituted when Tango Server executes the application file. When you control+click any text field, a contextual menu appears; it contains standard editing commands and the Insert Meta Tag... option.
For more information, see "Inserting Meta Tags".
Choose Insert Meta Tag... to open the Insert Meta Tag dialog box. You can insert many of the commonly-used Tango meta tags.
For more information, see "Disabling the Use of Meta Tags in Data Sources".
Before connecting to a data source, Tango checks the data source parameters for meta tags. If meta tags are found, and if the passThroughSwitch configuration variable is set to on, the substitution is performed, and the results are used to establish the connection. If no meta tags are found, the data source parameters are passed as-is.
The following example shows a user name being obtained from the user variable username. The user password is taken from the file whose name corresponds to the user name, followed by the .pwd extension.
Username: <@VAR NAME="username">
Password: <@INCLUDE FILE="<@VAR username>.pwd">
[back to top] Setting Data Sources for Actions
You can use the Set Data Source command to set development and deployment data source information independently for any selected actions.
Using Set Data Source
To set development and/or deployment data sources for one or more actions
- Select one or more actions by clicking on them. You
can select a list of actions by holding down the Shift key while selecting, or
select discontiguous actions by holding down the
key
while selecting. - Do one of the following:
- From the DataSource menu, choose Set Data Source.
- Control+click and choose Set Data Source from the contextual menu.
- To set a development data source for the selected
actions, make sure the Set development data source is checked,
and select a data source from the list.
To set a deployment data source for the selected actions, make sure Set deployment data source is checked, and select one of the following:
- Use data source default. This
option specifies that the Deployment settings from the selected data source are
applied to all the selected database actions.
You specify the deployment settings for a data source in the Deployment section of the properties of the data source. For more information, see "Working With Data Source Properties".
- Specify. This option specifies that the deployment data source settings in the text fields (Type, Name, and so on) are applied to all the selected database actions.
If you choose Specify, you can check Same as development, which causes the development data source to be used for deployment, or you may enter specifications for the deployment data source settings in the fields provided.
For more information, see "Deployment Data Source Parameters".
- Use data source default. This
option specifies that the Deployment settings from the selected data source are
applied to all the selected database actions.
Using the Action Properties Dialog Box
You can also use the Deployment tab of the Action Properties dialog box to set data source parameters for actions.
To view the Deployment section of the Action Properties dialog box
- Select the action.
- Do one of the following:
- Click the Deployment Logon tab.
- Use the Deployment section of the Action Properties dialog box the same way as the Data Source Properties dialog box
For more information, see "Setting Deployment Data Source Properties".
Disabling the Use of Meta Tags in Data Sources
The passThroughSwitch configuration variable allows you to specify whether meta tags are substituted in data source parameters when Tango application files are executed on Tango Server.
Passing through meta tags in deployment data sources is enabled in Tango by default. If you want to disable (or enable) this feature, you can do so by changing the options in the config.taf application file, in the Feature Switches screen:
Check or uncheck the checkbox beside the option.
[back to top] Working With Data Source Properties
The Properties dialog box allows you to view information about a selected data source, table, or column.
In the Data Sources Workspace, control+click on one of these items and choose Properties from the contextual menu that appears.
Note: While the
Properties dialog box is open, you can click on any item in
the Data Sources Workspace to display its properties.
The Data Source Properties dialog box contains three sections: General, Development, and Deployment. Click a tab to display the corresponding properties section.
- General. Clicking the General tab displays basic
information about the selected data source.
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.
Data Source Type
Other Information
To edit the selected data source, choose Modify.
The data source editing dialog box for the data source type appears. When you close the dialog box, new settings appear on the General tab of the Data Source Properties dialog box.
- Development. Clicking the
Development tab shows the login information required by Tango Editor for
connection to the data source.
The Development tab of the Data Source Properties dialog box asks for a user name and password for the data source.
When the Ask each time option is checked, Tango asks for connection information whenever the data source is expanded. When you set up a new data source, this option is checked.
- Deployment. Clicking the Deployment tab allows you to specify different login information to be used when Tango Server executes the action the data source is assigned to. For more information about this dialog box, see "Setting Up Deployment Data Sources".
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.
The Column Properties window allows you to edit the Title field and select the Primary Key option. The title is used by the builders as the default HTML display title for the column.
The Primary Key option is used by the builders to create actions affecting a specific record (record detail display, update and delete).
[back to top] Connecting to Data Sources
When you expand a data source in the Data Sources Workspace you have not connected to, the login information specified in the Data Source Properties Development window is used for the connection. If you checked the Ask each time option, the Log In dialog box appears, allowing you to type your user name and password.
Connecting to Large Data Sources
When Tango Editor connects to a data source containing more than 25 tables, it displays the Select Tables dialog box, allowing you to select which tables you want to work with.
You can also open the Select Tables dialog box by doing one of the following:
- Select a data source; from the Data Source menu, choose Select Tables.
- Control+click a data source and choose Select Tables from the contextual menu that appears.
Selecting Tables
The Available tables list in the Select Tables dialog box shows the tables in the data source. Drag the tables you want to work with from this list into the Tables to use list.
If you no longer want to use one or more tables, drag them from the Tables to use list to the Available tables list.
Filtering Tables
You can use the Owner and Type pop-up menus to filter the tables shown in the Available tables list of the Select Tables dialog box.
Note: The
Owner and Type filters are disabled on
FileMaker Pro data sources.
For example, to show only tables owned by a specific user, select that user from the Owner pop-up menu. To show only system tables, select SYSTEM TABLE from the Type pop-up menu. (The contents of these pop-up menus are determined by the data source; only owners and types existing in the database are listed.)
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 sections explain which pieces of data source information are stored in the file, which 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:
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:
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.
If you specified an alias, an alias with the same name must exist on the computer on which you want to use the application file (aliases are created using the Oracle Easy Config application). The alias must point to the Oracle database used when the application file was created.
FileMaker Pro Data Sources
Application files assigned FileMaker Pro data sources have these pieces of information stored in them:
- for local databases, the FileMaker Pro application name
- for remote databases, AppleTalk zone name, computer name, and FileMaker Pro application name
- database name
- password.
All of the information required to connect to a FileMaker data source is stored in the application file. You simply need to make sure the FileMaker application and database are available to the computer Tango is running on.
For application files assigned data sources using the Any FileMaker application option, Tango looks for a FileMaker Pro application on the local computer and launches it if required. If the Named FileMaker application option is used, the FileMaker Pro application, or an alias of it, must be in the Tango Editor folder on your hard drive.
For more information, see "FMDatabaseDir" in the Meta Tags and Configuration Variables manual.
In either case, the database must be open or located in the folder specified by the FMDatabaseDir configuration variable.
For more information, see "FileMaker Pro Data Sources".
For application files assigned data sources using the Networked FileMaker application option, the computer running Tango must be on the same AppleTalk network and have access to the specified remote computer. The database must be open in FileMaker Pro on the remote computer.