Views That Use Other Database Tables

Most client applications need to work with data in a single data dictionary. But what do you do if you want to work with data in two different data dictionaries? Actually, this is not hard if you are willing to use more than one connection. For example, you can establish one connection to one data dictionary, and another connection to the other data dictionary. Because the licensing of the Advantage Database Server permits a single workstation to make any number of connections, there is no monetary cost associated with this approach.

As you learned in Chapter 2, ADS tables that are bound to a data dictionary cannot be accessed directly. They must be accessed through the data dictionary to which they are bound. As a result, references to database tables in a view must include a reference to the data dictionaries to which they are bound.

There are two ways to qualify a table name with the data dictionary to which it is bound, and both of these use dot notation. The first way is to qualify the table from the other data dictionary by including the name of the data dictionary in which the table resides, separating this path from the table name using a dot, or period.

The second way to qualify a data dictionary–bound table name is to precede the table name with a data dictionary link. As is the case when you use a data dictionary path, this data dictionary link is separated from the table name by a period.

Before continuing, it is worth noting that the issue of referring to tables in other data dictionaries is not actually a view issue, but a SQL issue. Specifically, the linking that is discussed in this section can be used in views and in SQL statements that you send directly to ADS.

Qualifying a Table Using a Dictionary Path

You might recall that in Chapter 2 you created a free table called CUST.ADT. Imagine that instead of being a free table, CUST.ADT was in a data dictionary other than DemoDictionary. For example, suppose that CUST.ADT was stored in a data dictionary named Shared.ADD.

Note

You are free to create a data dictionary with this name and add the CUST.ADT table to it. This would permit you to try linking to another data dictionary using a view. However, when you are done, remove CUST from that data dictionary. You remove a table from a data dictionary by right-clicking the table’s node in the Advantage Database Manager’s tree view and selecting Remove.

If the Shared.ADD data dictionary is located in the same directory as DemoDictionary, you can use CUST.ADT by preceding the CUST table name with the name of the Shared.ADD data dictionary, enclosed in double quotation marks and separated from the table name using a period. This is demonstrated in the following SQL statement:

SELECT "Customer ID", "First Name", "Last Name"  FROM "Shared.ADD".CUST

Of course, this view could include links between tables in the Shared and DemoDictionary data dictionaries, as shown in this example:

SELECT CDB."Customer ID", CDB."First Name", CDB."Last Name",   Cust.Address, Cust.City, Cust.State FROM "Shared.ADD".CUST CDB, CUSTOMER Cust WHERE CDB."Customer ID" = Cust."Customer ID"

If the data dictionaries are not in the same directory, the reference to the data dictionary table must include the path to the data dictionary. For example, if Shared.ADD is not in the same directory as DemoDictionary, instead residing in the directory C:\MyData, the preceding view would have to be represented as follows:

SELECT CDB."Customer ID", CDB."First Name", CDB."Last Name",   Cust.Address, Cust.City, Cust.State FROM "C:\MyData\Shared.ADD".CUST CDB, CUSTOMER Cust WHERE CDB."Customer ID" = Cust."Customer ID"

When you include a data dictionary name as the qualifier for an external database table, ADS uses the user name and password of the connection from which the view is being accessed to connect to the external data dictionary. If you cannot ensure that the external data dictionary includes user names and passwords for all users who will access the view, you must use a data dictionary link. Using data dictionary links is discussed in the following section.

Qualifying a Table Using Data Dictionary Links

Data dictionary links, sometimes referred to as link aliases, are objects that you define in a data dictionary that refer to another data dictionary. These links can then be used in SQL statements to refer to tables in the linked data dictionary, including SQL statements used in views. You use the link in dot notation in place of the data dictionary name described in the preceding section.

One of the more interesting characteristics of a link is that you can associate a user name and password with it. If you do this, that user name and password is used to connect to the external data dictionary to which the link refers.

Following are the steps involved in creating a data dictionary link:

1. To create a data dictionary link, right-click the LINKS node in the Advantage Database Manager tree view, and select Add. The Advantage Database Manager responds by displaying the Link dialog box shown in Figure 6-7.

   
   Figure 6-7: The Link dialog box

2. Set the Alias field to a string that you will use in your views to qualify the other data dictionary. Set Linked Data Dictionary to the path of the data dictionary to which this link will refer. This path can be either a Windows path or a UNC path.

3. Check the Static Path checkbox if you want the link to save the fully qualified path, or leave it unchecked to save only the relative path.

4. Check Authenticate Active User if you want to pass the user name and password of the connection that is accessing the link to the other data dictionary. If you do not check Authenticate Active User, and the data dictionary you are linking to requires user login, you need to set the User Name and the Password fields. The values you provide in the User Name and Password fields must be associated with a user who has at least read rights to the tables that you will use in your views.

5. Click Create when you are done.

Once you have defined a link, you must grant rights to that link to all users who also have rights to the views that use the link. The following image shows the View permissions for the ALL group, one that grants link access rights to a link named ToShared:

As mentioned earlier, links are used in place of data dictionary references in the dot notation in your view queries. For example, assuming that the link named ToShared has been created to point to the Shared.ADD data dictionary, and access rights have been assigned to this link, the following SQL statement produces a result identical to the SQL statement shown in the preceding section:

 SELECT Link."Customer ID", Link."First Name", Link."Last Name",   Cust."Address", Cust."City", Cust.State FROM   ToShared.CUST Link, CUSTOMER Cust WHERE Link."Customer ID" = Cust."Customer ID"

Note

If your data dictionary link name includes spaces or other special characters, you would need to enclose that link name in double quotation marks in your view’s SQL statement.

Once you define a link, you cannot change its name or the name of the data dictionary to which it points. If you want to change this information, you must delete the link and add a new link with the updated information. To delete a link, right-click the node for the link and select Delete.

In the next chapter, you will learn how to create stored procedures, called Advantage Extended Procedures (AEPs) in ADS.