Skip to main content

%SYSTEM.SQL.Schema

abstract class %SYSTEM.SQL.Schema extends %SYSTEM.Help

Method Inventory

Methods

classmethod CreateLinkedProcedure(dsn As %String, externalSchema As %String, externalProcedure As %String, localPackage As %String = "LinkedSchema", localClass As %String = "LinkedProcedure", localMethod As %String = "", localSqlName As %String = "", description As %String = "") as %Status
Create a linked process

dsn - Data Source Name

externalSchema - Schema name of the linked stored procedure of external source

externalProcedure - External selected Stored Procedure name

localPackage - New Stored Procedure's Package name. Default value is "LinkedSchema".

localClass - New class name for the new Linked stored procedure. Default value is "LinkedProcedures"

localMethod - Method name for the stored procedure. Default value is externalProcedure.

localSqlName - New SQL name. Default value is externalProcedure.

description - Description. Default value is empty

classmethod CreateLinkedTable(dsn As %String, externalSchema As %String, externalTable As %String, primaryKeys As %String, localClass As %String = "User.LinkedClass", localTable As %String, ByRef columnMap As %String = "") as %Status
Create a linked table

dsn - Data Source Name

externalSchema - Schema name of the linked table of external source

externalTable - The linked table name of external source

primaryKeys - The fields for the primary key of new created table.

Multiple fields are separated by comma. The primary key's field(s) should be specified as the "new class property name" if you do not use the original field name of the external source

You at least have to specify one field for this primaryKey.

localClass - The new linked class name. The default value is "User.LinkedClass"

localTable - The new table name for the linked class. Default value is localClass

columnMap - The linked fields of the external table. Default is to map everything as read-only columns if you don't specify anything in the map. Otherwise, specify the fields in the below format. columnMap("external field name") = $lb("new class property name","new sql field name","read-only"(1/0)) or use external field name as default values by specifying columnMap("external field name") = "". "new class property name" and "new sql field name" could be defined empty and "external field name" would be used as their default values "read only" default is on.

Below is example for linking a table from MySQL

Use these commands to create a table in MySQL

in MySQL shell:

create database test1;

use test1;

create table Person(PID int,name varchar(255));

insert into Person(PID,name) values(1,"Cache")

in Cache Terminal:

$SYSTEM.SQL.Schema.CreateLinkedTable(dsn,"","Person","PID,name","User.LinkedClass","LinkedTable","")

would link all the fields of the table test1.Person from MySQL to the Cache class "User.LinkedTable"

and use both PID and name as primary keys

classmethod Default() as %Library.String [ SQLProc = DefaultSchema ]
Projected as the stored procedure: DefaultSchema
Return the default schema name for the current process in the current namespace

Example:

Set CurrentSchema = $SYSTEM.SQL.Schema.Default()
This method can also be called as a Stored Procedure named %SYSTEM_SQL.DefaultSchema()
classmethod DropTable(tablename As %Library.String, dropData As %Library.Boolean = 0) as %Status
This entry point can be used to delete a table definition.

Parameters:
tablename
Name of the SQL table to delete. The name may be qualified with a schema name: Medical.Patient. If the schema name is omitted, the default schema is used.
dropData
TRUE(1)/FALSE(0) flag which determine if the table's data is to be deleted or not. If dropData="" or undefined, use system flag to determine if data should be deleted.

Examples:

  • Do $SYSTEM.SQL.Schema.DropTable("MedLab.Patient",1)
  • Do $SYSTEM.SQL.Schema.DropTable("IscPerson",1) ; Deletes SQLUser.IscPerson
classmethod DropView(viewname As %Library.String) as %Status
This entry point can be used to delete a view definition.

Parameters:
viewname
Name of the SQL view to delete. The name may be qualified with a schema name: Medical.PatientView If the schema name is omitted, the default schema is used.

Examples:

  • Do $SYSTEM.SQL.Schema.DropView("MedLab.PatientView")
  • Do $SYSTEM.SQL.Schema.DropView("IscPersonView") ; Deletes SQLUser.IscPerson
classmethod ExportDDL(schema As %String = "*", table As %String = "*", ByRef file As %String, qualifiers As %DynamicObject = "{}") as %Status
Export a SQL DDL/DML script file containing Table/View definitions, User definitions, Role definitions, and/or Table/View Privileges based on the qualifiers argument (see below).

Parameters:
Schema
Name of the schema in the current namespace to export the privileges for. Use "*" to specify the export of SQL Privileges for all schemas in the current namespace. The default is "*". Note that when "*" is specified, '%' schemas (such as %Dictionary) are not exported. To export privileges for tables in a schema that begins with '%' you must explicitly specify that schema name in Schema.
Table
Name of the table/view in the specified schema to export the privileges for. Use "*" to specify the export of SQL Privileges for all table/s views in the specified schema(s). The default is "*". Note that if you "*" for Schema the export will behave as if you entered "*" for Table as well. For example there is no mechanism for exporting the Person table/view in each schema in the namespace.
File
Name of the file to export the SQL statement to.
qualifiers
Specify any subset of the following arguments in in either dynamic object (e.g. {"FileOpenParam":"WNS","Roles":0}), json string (e.g. "{""FileOpenParam"":""WNS"",""Roles"":""0""}"), or legacy qualifier format (e.g. "/FileOpenParam=WNS/Roles=0"). All qualifier names are case insensitive. Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}.
FileOpenParam
Parameters to use when opening the File. The default is "WNS".
Definitions
1/0 flag. Specify 1 to export the table/view definitions. The default is 1.
Users
1/0 flag. Specify 1 to export the User definition. The default is 0.
Roles
1/0 flag. Specify 1 to export the Role definition, and the GRANT statements to assign the Roles to the Users and Roles they have been granted to. The default is 1.
Privileges
1/0 flag. Specify 1 to export the Table and View privileges for the tables and views specified in the Schema specification. The default is 1.
SQLSystemPrivileges
1/0 flag. Specify 1 to export the SQL System privileges defined in the current namespace for the users and roles. The default is 1.

Examples:

  • Do $SYSTEM.SQL.Schema.ExportDDL("Sample","*","C:\PT\Sample.sql",{"FileOpenParam":"WNS","Users":0,"Roles":0,"Privileges":1,"SQLSystemPrivileges":1})
  • Do $SYSTEM.SQL.Schema.ImportDDL("C:\DDT\UsersAndRoles.sql")

The method returns a Status Code.

A file created via $SYSTEM.SQL.Schema.ExportDDL() can be imported using one of:

  • do $SYSTEM.SQL.Schema.ImportDDL(,,"IRIS")
  • do $SYSTEM.SQL.Schema.Run()

The ExportDDL() method will not export the following users and roles -

Users:
SuperUser, Admin, UnknownUser, %System, CSPSystem, _SYSTEM, _PUBLIC
Roles:
%All, %Developer, %Manager, %Operator, %SQL, %LegacyTerminal, %LegacyCacheDirect, %LegacySQL
classmethod GetAllColumns(tablename As %String = "", ByRef byName As %String, ByRef byNumber As %String) as %Status
Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name. This includes all SQL hidden columns.

Parameters:
tablename
Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
byName
Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
byNumber
Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name"
Returns: A %Status value
classmethod GetVisibleColumns(tablename As %String = "", ByRef byName As %String, ByRef byNumber As %String) as %Status
Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name. This excludes all SQL hidden columns. SQL fields are defined as hidden if they are projected by a property defined as private, a RowID field defined as SqlRowIDPrivate, a RowID field for a linked table, a serial field that is not a collection and the storage structure is known, the %%CLASSNAME field, or a list or array collection property projected as a child table.

Parameters:
tableName
Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
byName
Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
byNumber
Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name" Returns: A %Status value
classmethod ImportDDL(infile As %String = "", logfile As %String = "", DDLMode As %Library.String = "IRIS")
Import a DDL/DML script file.

Parameters:
infile
The full path name of the script file to import. This parameter is required. A $list value may also be passed in with the filename and the TranslateTable to use for the file. (For information on translate tables, see Translation Tables.) The first element is the file name and the second element is the TranslateTable for the input stream file. This is only supported for DDLMode MSSQL, Sybase, Informix, or MySQL.
logfile
The full path name of the file to report errors in. This parameter is optional. Default is the same as the infile with a _Errors.log extension.
DDLMode
Vendor from which the script file originated. This parameter is required. Supported values are:
  • IRIS
  • CACHE
  • FDBMS
  • Informix
  • Interbase
  • MSSQL
  • MSSQLServer - same as MSSQL
  • MySQL
  • Oracle
  • Sybase

When DDLMode=IRIS, the following statements are supported:

  • CREATE ...
  • DROP ...
  • TRUNCATE TABLE ...
  • ALTER ...
  • INSERT ...
  • UPDATE ...
  • DELETE ...
  • SET OPTION ...
  • GRANT ...
  • REVOKE ...
  • LOAD DATA ...
  • USE [DATABASE] ...
  • TUNE TABLE ...
  • FREEZE ...
  • UNFREEZE ...
  • LOCK ...
  • UNLOCK ...
  • BUILD INDEX ...

Examples:

  • Do $SYSTEM.SQL.Schema.ImportDDL("C:\PT\Patient.sql",,"Sybase")
  • Do $SYSTEM.SQL.Schema.ImportDDL("C:\DDT\all_tables.sql",all.log,"Oracle")
classmethod ImportDDLDir(directory As %Library.String = "", DDLMode As %String = "", qualifiers As %DynamicObject = "{}") as %Status
Import all DDL/DML script file in a given directory. All files with the extension .sql in the directory will be imported.

Parameters:
directory
The full path name of the directory to import. This parameter is required.
DDLMode
Vendor from which the script file originated. This parameter is required. Supported values are:
  • Informix
  • MSSQL
  • MSSQLServer - same as MSSQL
  • Sybase
qualifiers
Specify any subset of the following optional arguments in dynamic object format (e.g. {"logFile":"log.txt","eosDelimiter":";"}). Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}, and all qualifier names are case insensitive.
logFile
The full path name of the file to report errors in. This parameter is Optional. Default is DDLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
eosDelimiter
End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.

Examples:

  • Do $SYSTEM.SQL.Schema.ImportDDLDir("C:\Work\db1\","Informix",{"logFile":"C:\Work\db1\import.log","eosDelimiter":";"})
  • Do $SYSTEM.SQL.Schema.ImportDDLDir("C:\Work\db1\","Informix",{"logFile":1,"eosDelimiter":";"})
classmethod ImportDir(dialect As %String = "", directory As %Library.String = "", qualifiers As %DynamicObject = "{}") as %Status
Import all DDL/DML script files in a given directory. All files with the extension .sql in the directory will be imported. The caller can optionally specify a comma delimited list of file extensions to import. Subdirectories are also recursively processed if the caller specifies the recurse qualifier as 1. The log for this command can be found in SQLImportDir.log within the input directory.

Parameters:
dialect: Vendor from which the script file originated. This parameter is required. Supported values are:
  • MSSQL
  • MSSQLServer - same as MSSQL
  • Sybase
directory
The full path name of the directory to import. This parameter is required.
qualifiers
Specify any subset of the following optional arguments in dynamic object format (e.g. {"logFile":"importdir.log","echoMode":0}). Note that variables can be embedded in dynamic objects with the format {"ParamName":(varName)}, and all qualifier names are case insensitive.
logFile
The full path name of the file to report errors in. Default is SQLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
extensions
A comma delimited list of file extensions to import. Defaults to "sql".
eosDelimiter
End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode.
recurse
0/1. If 1, then subdirectories of directory will be recursively imported. This parameter defaults to 0.
messageMode
0/1. If true then all messages reported by executing imported statements will be displayed on the current device. Default is true.
echoMode
0/1. If true, all statement source is displayed on the current device. Default is true.
errorPause
The number of seconds to pause when an error is reported. The default is five seconds.

Examples:

  • Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"logFile":"C:\Work\db1\import.log","extensions":"sql","eosDelimiter":";"})
  • Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"logFile":"C:\Work\db1\import.log","extensions":"sql,tab,sp","eosDelimiter":";","messageMode":1})
  • Do $SYSTEM.SQL.Schema.ImportDir("Sybase","C:\Work\db1\",{"eosDelimiter":";"})
classmethod LoadFDBMS() as %Library.String
Import a FDBMS DDL script file.
classmethod LoadInformix() as %Library.String
Import an Informix DDL/DML script file.
The Informix DDL/DML Import Utility supports the following statements:
  • CREATE TABLE ...
  • ALTER TABLE ...
  • CREATE INDEX ...
  • CREATE VIEW ...
  • SET OPTION ...
  • GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
classmethod LoadInterBase() as %Library.String
Import an Interbase DDL/DML script file.
The Interbase DDL/DML Import Utility supports the following statements:
  • CREATE TABLE ...
  • ALTER TABLE ...
  • CREATE INDEX ...
  • CREATE UNIQUE INDEX ...
  • CREATE VIEW ...
  • CREATE ROLE ...
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
classmethod LoadMSSQLServer() as %Library.String
Import an MS SQL Server DDL/DML script file.
The MS SQL Server DDL/DML Import Utility supports the following statements:
  • CREATE [GLOBAL TEMPORARY] TABLE ...
  • CREATE VIEW ...
  • ALTER TABLE ...
  • CREATE INDEX ...
  • CREATE CLUSTERED INDEX ...
  • CREATE UNIQUE INDEX ...
  • INSERT ...
  • UPDATE ...
  • DELETE ...
  • SET OPTION ...
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
classmethod LoadOracle() as %Library.String
Import an Oracle DDL/DML script file.
The Oracle DDL/DML Import Utility supports the following statements:
  • CREATE TABLE ...
  • CREATE VIEW ...
  • ALTER TABLE ...
  • CREATE INDEX ...
  • CREATE UNIQUE INDEX ...
  • DROP TABLE ...
  • DROP VIEW ...
  • DROP INDEX ...
  • INSERT ...
  • UPDATE ...
  • DELETE ...
  • SET OPTION ...
  • CREATE USER ...
  • CREATE ROLE ...
  • GRANT { SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
classmethod LoadSybase() as %Library.String
Import a Sybase DDL/DML script file.
The Sybase DDL/DML Import Utility supports the following statements:
  • CREATE [GLOBAL TEMPORARY] TABLE ...
  • CREATE VIEW ...
  • ALTER TABLE ...
  • CREATE INDEX ...
  • CREATE CLUSTERED INDEX ...
  • CREATE UNIQUE INDEX ...
  • INSERT ...
  • UPDATE ...
  • DELETE ...
  • SET OPTION ...
  • GRANT CONNECT ... (Same as SQL CREATE USER ...)
  • GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
classmethod LoadTSQL() as %Library.String
Import a TSQL script file.
TSQL source files can contain any TSQL syntax supported by InterSystems IRIS TSQL.
This API will put the caller to the SQL Shell in the default TSQL dialect as defined in the TSQL Compatibility Settings.
The caller can then use the run [filename] command from the shell to import the script file.
classmethod ProcedureExists(procname As %Library.String = "", ByRef metadata As %String) as %Library.Boolean [ SQLProc = Schema_ProcedureExists ]
Projected as the stored procedure: Schema_ProcedureExists
This entry point can be used to determine if a stored procedure exists.

Parameters:
procname
Name of the stored procedure to check.
procname can be qualified or unqualified. If unqualified, the default schema is applied.
metadata
Passed by reference, optional argument.
If the stored procedure exists, returns a $LIST with the following information about the procedure: $ListBuild(SchemaName,ProcedureName,Classname that projected the stored procedure,Procedure type)

Examples:

  • Write $SYSTEM.SQL.Schema.ProcedureExists("SQLUser.stpSalaryReport") // Writes a 1 if procedure SQLUser.stpSalaryReport exists
  • Write $SYSTEM.SQL.Schema.ProcedureExists("stpSalaryReport",.metadata) // Writes a 1 if procedure [DefaultSchema].stpSalaryReport exists, returns metadata=$lb("SQLUser","stpSalaryReport","User.stpSalaryReport","function")

Notes:

  • If the user calling the function does not hold any privileges for the procedure, 0 will be returned.
  • If a class exists that would project this procedure to SQL during compilation, but the class has not been compiled, 0 will be returned.
  • If a procedure is marked as hidden, 0 will be returned.
  • metadata will be set to "" if 0 is returned by the function.
This method can also be called as a Stored Procedure named %SYSTEM_SQL.Schema_ProcedureExists(procname)
classmethod Run() as %Status
Import an IRIS SQL script file. For InterSystems IRIS SQL script files, the default end-of-statement marker is the string GO. The end-of-statement marker must be on a line by itself after the statement.
classmethod SetDefault(schema As %Library.String = "", ByRef oldval As %Library.String, Namespace As %Library.Boolean = 0) as %Library.Status
Sets the default schema used by SQL.
This configuration setting provides the ability to define a default schema name other than SQLUser, the default. When an unqualified table name is encountered in an SQL statement (and there is no #import statement specified), the default schema will be used. This setting has nothing to do with the mappings between SQL schema names and the class package name, it only specifies the default schema.

Parameter:
schema
String containing the default SQL schema name. If schema is "" or not defined, the default schema will be set to SQLUser.
oldval
Passed By Reference. Contains the previous value of the setting.
Namespace
Boolean 1/0 flag. If TRUE, set the default schema for the current namespace only. The default for Namespace is FALSE
Returns:
Status Code
NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting.
- Changing this configuration setting will affect all processes immediately.
classmethod TableExists(tablename As %Library.String = "", ByRef metadata As %String) as %Library.Boolean [ SQLProc = Schema_TableExists ]
Projected as the stored procedure: Schema_TableExists
This entry point can be used to determine if a base table exists.

Parameters:
tablename
Name of the table to check.
tablename can be qualified or unqualified. If unqualified, the default schema is applied.
metadata
Passed by reference, optional argument.
If the table exists, returns a $LIST with the following information about the table: $ListBuild(SchemaName,TableName,Classname that projected the table,System Flag)

Examples:

  • Write $SYSTEM.SQL.Schema.TableExists("Sample.Person") // Writes a 1 if table Sample.Person exists
  • Write $SYSTEM.SQL.Schema.TableExists("Sample.Person",.metadata) // Writes a 1 if table Sample.Person exists, returns metadata=$lb("Sample","Person","Sample.Person","0")

Notes:

  • If the user calling the function does not hold any privileges for the table, 0 will be returned.
  • If a class exists that would project this table to SQL during compilation, but the class has not been compiled, 0 will be returned.
  • metadata will be set to "" if 0 is returned by the function.
This method can also be called as a Stored Procedure named %SYSTEM_SQL.Schema_TableExists(tablename)
classmethod ValidateTable(tablename As %Library.String = "", index As %BigInt = 0) as %Library.Status [ SQLProc = Schema_ValidateTable ]
Projected as the stored procedure: Schema_ValidateTable
Validate data for a table
This utility can be called via $SYSTEM.SQL.Schema.ValidateTable(tablename) or as the %SYSTEM_SQL.Schema_ValidateTable(tablename) stored procedure. This method/procedure returns a resultset that contains a row for each issue found with the table's data. If the resultset is empty, the table has no data validation issues. The table's data is checked for the following:
  • Each field's validation code from the IsValid method of the field's datatype
  • Required fields do not have a null value
  • Unique constraints do not have duplicate values
  • Foreign Key fields reference valid rows in the referenced table


  • Parameters:
    tablename
    Name of the table to validate data. Must be a table name, not a view name.
    index
    Internal use only

    Returns:

    Status Code

    Example:

  • Set status=$SYSTEM.SQL.Schema.ValidateTable("Sample.Person") // Validates the data in the Sample.Person table
  • Set status=$SYSTEM.SQL.Schema.ValidateTable("Company") // Validates the data in the SQLUser.Company table
  • call %SYSTEM_SQL.Schema_ValidateTable('Sample.Person') // Validates the data in the Sample.Person table via SQL
  • Notes:

    • After calling $SYSTEM.SQL.Schema.ValidateTable(...), the resultset will be held in the %sqlcontext object. Do %sqlcontext.%Display() will dump the results to the current device
    • If the table is sharded, this should be called on the shard master table
    • There is no locking performed by the utility. If run on a table in a live system, you could receive false-positive error reports
    classmethod ViewExists(viewname As %Library.String = "", ByRef metadata As %String) as %Library.Boolean [ SQLProc = Schema_ViewExists ]
    Projected as the stored procedure: Schema_ViewExists
    This entry point can be used to determine if a view exists.

    Parameters:
    viewname
    Name of the view to check.
    viewname can be qualified or unqualified. If unqualified, the default schema is applied.
    metadata
    Passed by reference, optional argument.
    If the view exists, returns a $LIST with the following information about the view: $ListBuild(SchemaName,ViewName,Classname that projected the view,System Flag)

    Examples:

    • Write $SYSTEM.SQL.Schema.ViewExists("SQLUser.STestView") // Writes a 1 if view SQLUser.STestView exists
    • Write $SYSTEM.SQL.Schema.ViewExists("STestView",.metadata) // Writes a 1 if view [DefaultSchema].STestView exists, returns metadata=$lb("SQLUser","STestView","User.STestView","0")

    Notes:

    • If the user calling the function does not hold any privileges for the view, 0 will be returned.
    • If a class exists that would project this view to SQL during compilation, but the class has not been compiled, 0 will be returned.
    • If a class that projects the view is marked as hidden, 0 will be returned.
    • metadata will be set to "" if 0 is returned by the function.
    This method can also be called as a Stored Procedure named %SYSTEM_SQL.Schema_ViewExists(viewname)

    Inherited Members

    Inherited Methods

    FeedbackOpens in a new tab