Class utility.StatementColumns Extends %SQL.CustomQuery
{

Parameter SQLNAME As String = "statement_columns";

Property columns As %Collection.ListOfObj [ Internal, Private ];

Property columnPtr As %String [ Internal, Private ];

Property atEnd As %Boolean [ Internal, Private ];

Property columnType As %String;

Property colName As %String(MAXLEN = 255);

Property ODBCType As %Integer;

Property precision As %Integer;

Property scale As %Integer;

Property isNullable As %Boolean;

Property label As %String(MAXLEN = 255);

Property tableName As %String;

Property schemaName As %String;

Property qualifier As %String;

Property isAutoIncrement As %Boolean;

Property isCaseSensitive As %Boolean;

Property isCurrency As %Boolean;

Property isReadOnly As %Boolean;

Property isRowVersion As %Boolean;

Property isUnique As %Boolean;

Property isAliased As %Boolean;

Property isExpression As %Boolean;

Property isHidden As %Boolean;

Property isIdentity As %Boolean;

Property isKeyColumn As %Boolean;

Property isRowId As %Boolean;

Property isList As %Boolean;

Property property As %Dictionary.CompiledProperty;

/// The objects type class
Property typeClass As %Dictionary.CompiledClass;

Property clientType As %Integer;

Method %OpenCursor(statement As %String) [ Private ]
{
    set ..columns = $system.SQL.Prepare(statement).%Metadata.columns
    set ..atEnd = 0
    set ..columnPtr = ""
}

Method %FetchCursor() As %Library.Integer
{
    set response = 0
    if '..atEnd {
        set next = ..columnPtr
        set column = ..columns.GetNext(.next)
        if next '= "" {
            set response = 1
            set ..columnPtr = next
            do ..mapColumnToRow(column)
        } else {
            set ..atEnd = 1
            set ..columnPtr = ""
            do ..clearRow()
        }
    }
    return response
}

Method mapColumnToRow(column As %SQL.StatementColumn)
{
    set ..columnType = "SQLRESULTCOL"
    set ..colName = column.colName
    set ..ODBCType = column.ODBCType
    set ..precision = column.precision
    set ..scale = column.scale
    set ..isNullable = column.isNullable
    set ..label = column.label
    set ..tableName = column.tableName
    set ..schemaName = column.schemaName
    set ..qualifier = column.qualifier
    set ..isAutoIncrement = column.isAutoIncrement
    set ..isCaseSensitive = column.isCaseSensitive
    set ..isCurrency = column.isCurrency
    set ..isReadOnly = column.isReadOnly
    set ..isRowVersion = column.isRowVersion
    set ..isUnique = column.isUnique
    set ..isAliased = column.isAliased
    set ..isExpression = column.isExpression
    set ..isHidden = column.isHidden
    set ..isIdentity = column.isIdentity
    set ..isKeyColumn = column.isKeyColumn
    set ..isRowId = column.isRowId
    set ..isList = column.isList
}

Method clearRow()
{
    set ..columnType = ""
    set ..colName = ""
    set ..ODBCType = ""
    set ..precision = ""
    set ..scale = ""
    set ..isNullable = ""
    set ..label = ""
    set ..tableName = ""
    set ..schemaName = ""
    set ..qualifier = ""
    set ..isAutoIncrement = ""
    set ..isCaseSensitive = ""
    set ..isCurrency = ""
    set ..isReadOnly = ""
    set ..isRowVersion = ""
    set ..isUnique = ""
    set ..isAliased = ""
    set ..isExpression = ""
    set ..isHidden = ""
    set ..isIdentity = ""
    set ..isKeyColumn = ""
    set ..isRowId = ""
    set ..isList = ""
}

}

Robert - what do you think? Should I just post the class text (single class, simple) or try to put it on GitHub?

I agree with Tim but I'll take it one step further. Foreign keys are much more useful than relationships. After all, a relationship is simply a foreign key that maintains references to instances of the related class in memory. The projection of a relationship to SQL is simply as a foreign key. The set of related objects is simply populated using an SQL query. The problem with relationships is that they are extremely sticky and that can cause large numbers of objects to be inadvertently swizzled into memory. With foreign keys you have no in-memory model. That means with a foreign key you have to manage desired swizzling. Some view that as a problem, I view it as an advantage.

Another advantage of using foreign keys is that you can define multiple foreign keys using the same key component properties. No need to define a direct reference.

Creative minds might come up with a calculated property whose value is derived from the foreign key components, adding property methods to manipulate the related object/objects. This calculated property could be the direct reference. Perhaps transient would be better as a transient property also has instance memory allocated for it.