Link tables programmatically

InterSystems FAQ rubric

In InterSystems IRIS, you can create linked tables using commands, instead of using System Explorer > SQL > Wizard > Linked Tables in the Management Portal:

To create a linked table, use the CreateLinkedTable method of the %SYSTEM.SQL.Schema class. See the class reference for details.

To execute it, follow these steps:

set sc = $SYSTEM.SQL.Schema.CreateLinkedTable("<dsn>","<Schema>","<Table>","<primaryKeys>","<localClass>","<localTable>","")

/// 1st argument: dsn - SQL Gateway connection name
/// 2nd argument: Schema - Source schema name
/// 3rd argument: Table - Source table name
/// 4th argument: primaryKeys - Primary key
/// 5th argument: localClass - Linked class name (e.g., User.LinkedClass)
/// 6th argument: localTable - Linked SQL table name (SqlTableName)
/// 7th argument: columnMap - Linked field information

If you run it this way, the linked table will be created with the ReadOnly attribute. If you want to remove the ReadOnly attribute, you need to specify it in the seventh argument, columnMap.

set columnMap("external field name") = $lb("new class property name","new sql field name","read-only(1/0)")

In this  sample, a columnMap is created that sets ReadOnly to 0 for all fields (columns), and a linked table is created. The primaryKey is set to inherit the primaryKey of the linked table. The usage is as follows:

do ##class(ISC.LinkUtils).LinkTable("<dsn>","<Schema>","<Table>","<localClass>")

/// First argument: dsn - SQL Gateway connection name
/// Second argument: Schema - Link source schema name
/// Third argument: Table - Link source table name
/// Fourth argument: localClass - Link destination class name (e.g., User.LinkedClass)

You can also see the sample used here: https://github.com/Intersystems-jp/CreateLinkedTable