This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?

%Library.GTWCatalog

class %Library.GTWCatalog

Gateway Catalog Queries

Method Inventory

Methods

classmethod GetInfo(dsn As %String, infoType As %Integer, ByRef info As %String) as %Status
Calls SQLGriverConnect and SQLGetInfo for the corresponding datasource;

dsn is the DSN, infoType is the info type

classmethod SQLDataSourcesClose(QHandle As %Binary) as %Status
classmethod SQLDataSourcesExecute(ByRef QHandle As %Binary, direction As %Integer) as %Status
classmethod SQLDataSourcesFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLDataSourcesSortedClose(QHandle As %Binary) as %Status
classmethod SQLDataSourcesSortedExecute(ByRef QHandle As %Binary, direction As %Integer) as %Status
classmethod SQLDataSourcesSortedFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLFieldsClose(QHandle As %Binary) as %Status
classmethod SQLFieldsExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, schema As %String, catalog As %String = "") as %Status
classmethod SQLFieldsFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLFieldsJClose(QHandle As %Binary) as %Status
classmethod SQLFieldsJExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, schema As %String, catalog As %String = "") as %Status
classmethod SQLFieldsJFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLForeignKeysClose(QHandle As %Binary) as %Status
classmethod SQLForeignKeysExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, catalog As %String = "") as %Status
classmethod SQLForeignKeysFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLForeignKeysJClose(QHandle As %Binary) as %Status
classmethod SQLForeignKeysJExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, catalog As %String = "") as %Status
classmethod SQLForeignKeysJFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLPrimaryKeysClose(QHandle As %Binary) as %Status
classmethod SQLPrimaryKeysExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, catalog As %String = "") as %Status
classmethod SQLPrimaryKeysFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLPrimaryKeysJClose(QHandle As %Binary) as %Status
classmethod SQLPrimaryKeysJExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, catalog As %String = "") as %Status
classmethod SQLPrimaryKeysJFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLProcedureColumnsClose(QHandle As %Binary) as %Status
classmethod SQLProcedureColumnsExecute(ByRef QHandle As %Binary, dsn As %String, schname As %String, procname As %String, catalog As %String = "") as %Status
Get a List of all procedures
classmethod SQLProcedureColumnsFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLProcedureColumnsJClose(QHandle As %Binary) as %Status
classmethod SQLProcedureColumnsJExecute(ByRef QHandle As %Binary, dsn As %String, schname As %String, procname As %String, catalog As %String = "") as %Status
classmethod SQLProcedureColumnsJFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLProceduresClose(QHandle As %Binary) as %Status
classmethod SQLProceduresExecute(ByRef QHandle As %Binary, dsn As %String, pname As %String, schname As %String = "", catalog As %String = "") as %Status
classmethod SQLProceduresFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLProceduresJClose(QHandle As %Binary) as %Status
classmethod SQLProceduresJExecute(ByRef QHandle As %Binary, dsn As %String, pname As %String, schname As %String, catalog As %String = "") as %Status
classmethod SQLProceduresJFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLSpecialColumnsClose(QHandle As %Binary) as %Status
classmethod SQLSpecialColumnsExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, catalog As %String = "") as %Status
classmethod SQLSpecialColumnsFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLTablesClose(QHandle As %Binary) as %Status
classmethod SQLTablesExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String = "", ttype As %String = "", schema As %String = "", catalog As %String = "") as %Status
classmethod SQLTablesFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod SQLTablesJClose(QHandle As %Binary) as %Status
classmethod SQLTablesJExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String = "", ttype As %String = "", schema As %String = "", catalog As %String = "") as %Status
classmethod SQLTablesJFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status
classmethod getIndexInfoClose(QHandle As %Binary) as %Status
classmethod getIndexInfoExecute(ByRef QHandle As %Binary, dsn As %String, tname As %String, schema As %String, nonunique As %Boolean, approximate As %Boolean = "", catalog As %String = "") as %Status
classmethod getIndexInfoFetch(ByRef QHandle As %Binary, ByRef Row As %List, AtEnd As %Integer) as %Status

Queries

query SQLDataSources(direction As %Integer)
Selects DSN As %String, DESCRIPTION As %String
return list of ODBC datasources

direction could be one of the following: SQL_FETCH_NEXT 1 SQL_FETCH_FIRST 2 SQL_FETCH_FIRST_USER 31 SQL_FETCH_FIRST_SYSTEM 32

query SQLDataSourcesSorted(direction As %Integer)
Selects DSN As %String, DESCRIPTION As %String
return list of ODBC datasources, lexicographically sorted

direction could be one of the following: SQL_FETCH_NEXT 1 SQL_FETCH_FIRST 2 SQL_FETCH_FIRST_USER 31 SQL_FETCH_FIRST_SYSTEM 32

query SQLFields(dsn As %String, tname As %String, schema As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, TABLE_NAME As %String, COLUMN_NAME As %String, DATATYPE As %Integer, DATATYPE_NAME As %String, COLUMN_SIZE As %Integer, DECIMAL_DIGITS As %Integer, NULLABLE As %String, REMARKS As %String, SUPPORTED As %Boolean, SQLCOLUMNNUMBER As %Integer
Get a List of all Fields from a table for an ODBC based SQL Gateway connection;

dsn is the SQLGateway connection name, tname is the string search pattern for table names, schema is the string search pattern for schema names

query SQLFieldsJ(dsn As %String, tname As %String, schema As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, TABLE_NAME As %String, COLUMN_NAME As %String, DATATYPE As %Integer, DATATYPE_NAME As %String, COLUMN_SIZE As %Integer, DECIMAL_DIGITS As %Integer, NULLABLE As %String, REMARKS As %String, COLUMN_DEF As %String, SUPPORTED As %Boolean, SQLCOLUMNNUMBER As %Integer
Get a List of all fields in a table for a JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the string search pattern for table names, schema is the string search pattern for schema names

query SQLForeignKeys(dsn As %String, tname As %String, catalog As %String = "")
Selects PKTABLE_CAT As %String, PKTABLE_SCHEM As %String, PKTABLE_NAME As %String, PKCOLUMN_NAME As %String, FKTABLE_CAT As %String, FKTABLE_SCHEM As %String, FKTABLE_NAME As %String, FKCOLUMN_NAME As %String, KEY_SEQ As %Integer, UPDATE_RULE As %Integer, DELETE_RULE As %Integer, FK_NAME As %String, PK_NAME As %String
Get a List of foreign keys for an ODBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the table name in the format SCHEMA_NAME.TABLE_NAME

query SQLForeignKeysJ(dsn As %String, tname As %String, catalog As %String = "")
Selects PKTABLE_CAT As %String, PKTABLE_SCHEM As %String, PKTABLE_NAME As %String, PKCOLUMN_NAME As %String, FKTABLE_CAT As %String, FKTABLE_SCHEM As %String, FKTABLE_NAME As %String, FKCOLUMN_NAME As %String, KEY_SEQ As %Integer, UPDATE_RULE As %Integer, DELETE_RULE As %Integer, FK_NAME As %String, PK_NAME As %String
Get a List of foreign keys for an JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the table name in the format SCHEMA_NAME.TABLE_NAME

query SQLPrimaryKeys(dsn As %String, tname As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, TABLE_NAME As %String, COLUMN_NAME As %String, KE_SEQ As %Integer, PK_NAME As %String
Get a List of primary keys for an ODBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the table name in the format SCHEMA_NAME.TABLE_NAME

query SQLPrimaryKeysJ(dsn As %String, tname As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, TABLE_NAME As %String, COLUMN_NAME As %String, KE_SEQ As %Integer, PK_NAME As %String
Get a List of primary keys for a JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the table name in the format SCHEMA_NAME.TABLE_NAME

query SQLProcedureColumns(dsn As %String, schname As %String, procname As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, PROC_NAME As %String, COL_NAME As %String, COL_TYPE As %Integer, DATA_TYPE As %Integer, TYPE_NAME As %String, COL_SIZE As %Integer, DEC_DIGITS As %Integer, RADIX As %Integer, NULLABLE As %Integer, REMARKS As %String, SUPPORTED As %Boolean
return a list of the procedure columns for an ODBC based SQL Gateway connection

dsn is the SQLGateway connection name, procname is the string search pattern for procedure names, schema is the string search pattern for schema names

query SQLProcedureColumnsJ(dsn As %String, schname As %String, procname As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, PROC_NAME As %String, COL_NAME As %String, COL_TYPE As %Integer, DATA_TYPE As %Integer, TYPE_NAME As %String, COL_SIZE As %Integer, DEC_DIGITS As %Integer, RADIX As %Integer, NULLABLE As %Integer, REMARKS As %String, COLUMN_DEF As %String, SUPPORTED As %Boolean
return a list of the procedure columns for a JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, procname is the string search pattern for procedure names, schema is the string search pattern for schema names

query SQLProcedures(dsn As %String, pname As %String, schname As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, PROC_NAME As %String, PROC_TYPE As %Integer, REMARKS As %String
Get a List of all procedures for an ODBC based SQL Gateway connection

dsn is the SQLGateway connection name, pname is the string search pattern for procedure names, schema is the string search pattern for schema names

query SQLProceduresJ(dsn As %String, pname As %String, schname As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, PROC_NAME As %String, PROC_TYPE As %Integer, REMARKS As %String
Get a List of all procedures for a JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, pname is the string search pattern for procedure names, schema is the string search pattern for schema names

query SQLSpecialColumns(dsn As %String, tname As %String, catalog As %String = "")
Selects SCOPE As %Integer, DATATYPE As %Integer, TYPE_NAME As %String, COLUMN_SIZE As %Integer, BUFFER_LENGTH As %Integer, DECIMAL_DIGITS As %Integer, PSEUDO_COLUMN As %Integer, UPDATE_RULE As %Integer
Get a List of special columns for an ODBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the table name in the format SCHEMA_NAME.TABLE_NAME

query SQLTables(dsn As %String, tname As %String = "", ttype As %String = "", schema As %String = "", catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, TABLE_NAME As %String, TABLE_TYPE As %String, REMARKS As %String
Get a List of all tables for an ODBC based SQL Gateway connection;

dsn is the SQLGateway connection name, tname is the string search pattern for table names, schema is the string search pattern for schema names, ttype - list of table types to match

query SQLTablesJ(dsn As %String, tname As %String, ttype As %String, schema As %String, catalog As %String = "")
Selects CATALOG_NAME As %String, SCHEMA_NAME As %String, TABLE_NAME As %String, TABLE_TYPE As %String, REMARKS As %String
Get a List of all tables for a JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the string search pattern for table names, schema is the string search pattern for schema names, ttype - list of table types to match

query getIndexInfo(dsn As %String, tname As %String, schema As %String, nonunique As %SmallInt, catalog As %String = "")
Selects TABLE_CAT As %String(MAXLEN=128), TABLE_SCHEM As %String(MAXLEN=128), TABLE_NAME As %String(MAXLEN=128), NON_UNIQUE As %SmallInt, INDEX_QUALIFIER As %String(MAXLEN=128), INDEX_NAME As %String(MAXLEN=128), TYPE As %SmallInt, ORDINAL_POSITION As %SmallInt, COLUMN_NAME As %String(MAXLEN=128), ASC_OR_DESC As %String(MAXLEN=1), CARDINALITY As %Integer, PAGES As %Integer, FILTER_CONDITION As %String(MAXLEN=128)
Get a List of all indices in a table for a JDBC based SQL Gateway connection

dsn is the SQLGateway connection name, tname is the string search pattern for table names, schema is the string search pattern for schema names

Feedback