Skip to main content

Alternative Projections of Collections

Alternative Projections of Collections

This section discusses the STORAGEDEFAULT, SQLTABLENAME, and SQLPROJECTION property parameters, which affect how collection properties are stored and projected to SQL.

STORAGEDEFAULT Parameter

You can store a list property as a child table, and you can store an array property as a $LIST. In both cases, you specify the STORAGEDEFAULT parameter of the property:

  • For a list property, STORAGEDEFAULT is "list" by default. If you specify STORAGEDEFAULT as "array", then the property is store and projected as a child table. For example:

    Property MyList as list of %String (STORAGEDEFAULT="array");
    

    For details on the resulting projection, see “Default Projection of Array Properties.”

  • For an array property, STORAGEDEFAULT is "array" by default. If you specify STORAGEDEFAULT as "list", then the property is stored and projected as a $LIST. For example:

    Property MyArray as array of %String (STORAGEDEFAULT="list");
    

    For details on the resulting projection, see “Default Projection of List Properties.”

Important:

The STORAGEDEFAULT property parameter affects how the compiler generates storage for the class. If the class definition already includes a storage definition for the given property, the compiler ignores this property parameter.

SQLTABLENAME Parameter

If a collection property is projected as a child table, you can control the name of that table. To do so, specify the SQLTABLENAME parameter of the property. For example:

Property MyArray As array Of %String(SQLTABLENAME = "MyArrayTable");

Property MyList As list Of %Integer(SQLTABLENAME = "MyListTable", STORAGEDEFAULT = "array");

The SQLTABLENAME parameter has no effect unless the property is projected as a child table.

SQLPROJECTION Parameter

By default, if a collection property is stored as a child table, it is also projected as a child table, but it is not available in the parent table. To make such a property also available in the parent table, specify the SQLPROJECTION parameter of the property as "table/column"

For example, consider the following class definition:

Class Sample.Sample Extends %Persistent
{

Property Property1 As %String;

Property Property2 As array Of %String(SQLPROJECTION = "table/column");

}

The system generates two tables for this class: Sample.Sample and Sample.Sample_Property2

The table Sample.Sample_Property2 stores the data for the array property Property2, as in the default scenario. Unlike the default scenario, however, a query can refer to the Property2 field in the Sample.Sample table. For example:

SAMPLES>>SELECT Property2 FROM Sample.Sample where ID=7
13.     SELECT Property2 FROM Sample.Sample where ID=7
 
Property2
"1     value 12       value 23       value 3"

The SELECT * query, however, does not return the Property2 field:

SAMPLES>>SELECT * FROM Sample.Sample where ID=7
14.     SELECT * FROM Sample.Sample where ID=7
 
ID      Property1
7       abc
Note:

There are other possible values of the SQLPROJECTION property parameter, but those values have an effect only in MV-enabled classes.

FeedbackOpens in a new tab