%SYSTEM.SQL.Schema

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

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

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()

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

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

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.

ForeignTables

1/0 flag. Specify 1 to export foreign table definitions. The default is 1.

ForeignServers

1/0 flag. Specify 1 to export foreign server definitions. The default is 0.

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

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

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

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")

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":";"})

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":";"})

Import a FDBMS DDL script file.

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).

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).

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).

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).

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).

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.

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)

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.

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.

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)

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

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)