3.4 Multi-Valued Attributes
Most eDirectory attributes are multi-valued, meaning that the attribute can contain more than one value. The order of the values is not guaranteed, and the order can vary from replica to replica. Since the default is to allow multiple values, an attribute must be defined with the DS_SINGLE_VALUED_ATTR flag to have eDirectory enforce the rule of only one value. For example, the Given Name attribute is multi-valued so that a User object can have his or her legal given name and multiple nick names, for example, Elizabeth, Liz, and Beth.
When including attributes in a report, you need to know whether the attributes are multi-valued. (Consult the attribute definition in the eDirectory Schema Reference for this information.) If your report selects multi-valued attributes, you need to select the method of reporting them:
3.4.1 Multiple Rows
The standard method of reporting multiple values in an SQL report is to put each value in a separate row. If you include two or more multi-valued attributes in a report and select to report each value on a separate row, the size of the table grows by multiples of the number of values. For example, suppose you request a report that includes a user’s Telephone Number and Group Membership attributes, and each user has 2 telephone numbers and belongs to 3 groups. The table has six rows for each user. The figure below illustrates such a table.
Figure 3-2 Multiple Rows
If your report is gathering information for 100 users, instead of a 100 row table your report generates a 600 row table. Depending on available workstation resources and the number of multi-valued attributes you have included in the report, you could run out of memory or hard disk space before the report is completed. For a solution to this problem, see Section 3.4.2, Concatenating Rows.
To determine whether an attribute is multi-valued, see the eDirectory Schema Reference.
For information on how an eDirectory syntax is translated to an SQL data type, see Section 3.5, Data Type Mappings.
3.4.2 Concatenating Rows
Concatenation allows multiple values of an attribute to be written to a single row in a column, with values separated by a specified delimiter. If your report has selected two or more attributes with multi-values, concatenating values into a single row reduces the size of the table (see Section 3.4.1, Multiple Rows for information on how a table can increase in size with multi-valued attributes). The disadvantage of concatenation is that the values can get very long. The advantage is that you can create a report that contains many multi-valued attributes without increasing the number of rows in the report.
Multi-valued attributes have two column names to select from when creating a report. The column name that ends with an _S suffix is the name for concatenating values. For example, the column name for the CN attribute, which is multi-valued, is CN. When CN is selected, the driver produces a row for each value. When the CN_S column is selected, the driver concatenates the values into a single row in the column.
The following sections supply the information you need to concatenate values in your reports:
Attributes Eligible for Concatenation
Not all multi-valued attributes can be concatenated. The Novell ODBC Driver for eDirectory supports concatenation of attributes only if they use one of the following syntaxes:
-
Case Exact String
-
Case Ignore String
-
Class Name
-
Distinguished Name
-
Facsimile Telephone Number
-
Network Address (Address column only)
-
Numeric String
-
Printable String
-
Telephone Number
-
Typed Name (ObjName column only)
Thus, an attribute must meet two conditions for concatenation: be multi-valued and use one of the supported syntaxes. You can use the eDirectory Schema Reference to verify which attributes support these conditions or use the ODBC catalog function of your SQL tool to query the eDirectory tree for a list of all columns. The tool returns the column names for all the eDirectory attributes defined in that eDirectory tree. Attributes which can be concatenated have a name with an _S suffix.
Separator Characters Used by Concatenation
Value concatenation requires the use of a separator character to indicate the end of one value and the beginning of another. Since eDirectory attributes can store a variety of data types, a single separator cannot be selected that will be appropriate for all possible values. The Novell ODBC Driver for eDirectory allows you to set the separator for each report. The default separator is a comma. You can use any single character string as the separator or one of the following escaped values for non-printing characters.
Separator Character Functions
The Novell ODBC Driver for eDirectory supports two scalar functions for managing the separator character used when concatenating values.
Table 3-3 Separator Character Functions
These functions use the standard SQL escape syntax for scalar functions:
Table 3-4 SQL Escape Syntax for Scalar Functions
|
Function Syntax |
Description |
|---|---|
|
{fn SetSeparator(’:’)} |
Sets the separator to a colon |
|
{fn SetSeparator(’\t’)} |
Sets the separator to a tab character |
|
{fn GetSeparator()} |
Gets the current separator character |
For a list of characters that SetSeparator can use, see Separator Characters Used by Concatenation.
Concatenation Query
The following example illustrates how to use the SetSeparator function to set the separator to the plus (+) character and then concatenate the values for the Member attribute into one row for GroupNDS objects.
SQL Statement:
SELECT Member_S
FROM GroupNDS WHERE { fn SetSeparator(’+’) } <>’,’
Enter this statement into the query window, execute the statement, and retrieve the data.
Result Table:
The SQL statement will generate a report similar to the following: