Class Reference
%Library.SQLCatalog
| Server:basexml |
| Instance:SOAXML |
| User:UnknownUser |
 |
|||
| Private Storage | |||
SQL Catalog Queries
|
|
|
|
|
|
Returns information about the cached query Routine.Returns a string containing the query text for the cached query. Returns the create times by reference.
Source indicates that there is source for the routine and QueryType indicates whether it is a ODBC query or a Dynamic query.
Given a tablename, return a count of Cached Queries currently defined against the table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLClassname Procedure
%SQLCatalog_SQLClassname('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns The name of the class which projected the table
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
|
|
Selects SQLCODE As%SmallInt , DESCRIPTION As%String
Retuens a list of all SQLCODE values and descriptions
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLCODEList Procedure
%SQLCatalog_SQLCODEList()
Returns list of all SQLCODE values and descriptions
ROWSPEC = SQLCODE:%SmallInt - SQLCODE number
DESCRIPTION:%String - SQLCODE Description
Rows are returned in SQLCODE order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects Routine As%String , Query As%String , CreateTime As%TimeStamp , Source As%Boolean , QueryType As%Integer , QueryTypeExt As%String , Cost As%Numeric
The CachedQueryInfo query returns a list of cached queries.
Note that only the first 80 characters of a query are returned.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLCachedQueryInfo Procedure
%SQLCatalog_SQLCachedQueryInfo()
Returns Cached Queries the current user is privileged to see
ROWSPEC = ROUTINE:%String - Name of the Cached Query's routine
QUERY:%String - Cached Query's SQL Text
CREATE_TIME:%TimeStamp - Date/Time of Cached Query creation
SOURCE:%Boolean - Cached Query source there?
QUERYTYPE:%Integer - 6 or 7
QUERYTYPE_EXT:%String - ODBC QUERY OR DYNAMIC QUERY
COST:%Numeric - QO Generated Cost of the query
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects Routine As%String , Query As%String , CreateTime As%TimeStamp , Source As%Boolean , QueryType As%Integer , QueryTypeExt As%String , Cost As%Numeric
The CachedQueryInfo query returns a list of cached queries for a given table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLCachedQueryTable Procedure
%SQLCatalog_SQLCachedQueryTable('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a table.
Returns Cached Queries on this table the current user is privileged to see
ROWSPEC = ROUTINE:%String - Name of the Cached Query's routine
QUERY:%String - Cached Query's SQL Text (First 80 characters)
CREATE_TIME:%TimeStamp - Date/Time of Cached Query creation
SOURCE:%Boolean - Cached Query source there?
QUERYTYPE:%Integer - 6 or 7
QUERYTYPE_EXT:%String - ODBC QUERY OR DYNAMIC QUERY
COST:%Numeric - QO Cost for ther query
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects TABLE_NAME As%String (MAXLEN=128), COLLECTION_TYPE As%String (MAXLEN=12), ELEMENT_TYPE As%String (MAXLEN=10), CLASS_NAME As%String (MAXLEN=128), ORIGINATING_PROPERTY As%String (MAXLEN=128), INVERSE_RELATIONSHIP As%String (MAXLEN=128)
SQLChildTables Procedure
%Library.SQLCatalog_SQLChildTables('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns any child tables this table has
ROWSPEC =
TABLE_NAME:%String - Name of the Child Table
COLLECTION_TYPE:%String - 'ARRAY' or 'LIST' if the table was projected as
the result of a collection, else RELATIONSHIP
ELEMENT_TYPE:%String - If COLLECTION_TYPE is ARRAY or LIST, this will
contain the type of elements in the collection
'PERSISTENT', 'SERIAL', or 'DATATYPE'. If
COLLECTION_TYPE is RELATIONSHIP, this field is NULL
CLASS_NAME:%String - Name of the class which projected the table
ORIGINATING_PROPERTY:%String - Name of the property which produced the
projection of this Child Table.
INVERSE_RELATIONSHIP:%String - If this child table is the result of a
RELATIONSHIP, the relationship in the Parent table
that is the inverse of the ORIGINATING_PROPERTY
relationship.
NOTE: If the child table is projected as a result
of the deprecated IDENTIFIEDBY class parameter,
this INVERSE_RELATIONSHIP will be NULL, even
though the COLLECTION_TYPE is reported as
RELATIONSHIP.
Rows are returned in no particular order
Selects CONSTRAINT_NAME As%Library.String (MAXLEN=64), CONSTRAINT_TYPE As%String (MAXLEN=15), CONSTRAINT_DATA As%String (MAXLEN=255), CONSTRAINT_OBJECT_NAME As%Library.String (MAXLEN=64)
Get a List of all constraints from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLConstraints Procedure
%SQLCatalog_SQLConstraints('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns all fields in the table:
ROWSPEC = CONSTRAINT_NAME:%String - Name of the constraint
TYPE:%String - Type of constraint
CONSTRAINT_DATE:%String - Constraint info depends on type
CONSTRAINT_OBJECT_NAME:%String - Object name of the constraint
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects FIELD_NAME As%String , DATATYPE As%String , COLUMN_NUMBER As%Integer , REQUIRED As%String , UNIQUE As%String , COLLATION_FUNCTION As%String , HIDDEN As%String , MAXLEN As%String , BLOB As%String , REFERENCE As%String , CONTAINER_FIELD As%Library.String , ODBC_DATATYPE_CODE As%Library.SmallInt , JDBC_DATATYPE_CODE As%Library.SmallInt , COLLECTION_TYPE As%Library.String , REF_ID_DATATYPE As%Library.String , ELEMENT_TYPE As%Library.String , REFERENCE_CLASS As%Library.String , INSERTABLE As%Library.String (MAXLEN=3), UPDATEABLE As%Library.String (MAXLEN=3), DEFAULT As%String (MAXLEN=255), ROWIDWITHODBCVALUES As%String (MAXLEN=3), SERIAL_FIELD As%String (MAXLEN=3), ROWID_FIELD As%SmallInt , ORIGINATING_PROPERTY As%Library.String (MAXLEN=40)
Get a List of all Fields from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLFields Procedure
%SQLCatalog_SQLFields('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns all fields in the table:
ROWSPEC = FIELD_NAME:%String - Name of the field
DATATYPE:%String - Field's datatype
COLUMN_NUMBER:%Integer - Field's internal column number
REQUIRED:%String - Is field required, 'Yes' or 'No'
UNIQUE:%String - Is field unique, 'Yes', or 'No'
COLLATION_FUNCTION:%String - ALPHAUP, UPPER, STRING, EXACT, PLUS or MINUS
HIDDEN:%String - Is field hidden, 'Yes' or 'No'
MAXLEN:%String - MAXLEN parameter of field (or NULL)
BLOB:%String - Is field a BLOB, 'Yes' or 'No'
REFERENCE:%String - Name of the referenced table or NULL if not a reference
CONTAINER_FIELD:%Library.String - If this is a sub-field of a serial container, the name of the serial field
ODBC_DATATYPE_CODE - ODBC Datatype code for this field
JDBC_DATATYPE_CODE - ODBC Datatype code for this field
COLLECTION_TYPE - Type of collection, 'LIST', 'ARRAY' or ""
REF_ID_DATATYPE - If this is a reference field, the DATATYPE for the ID of the referenced table
ELEMENT_TYPE - If COLLECTION_TYPE is non-NULL, this will contain the type of elements in the collection
'PERSISTENT', 'SERIAL', or 'DATATYPE'. If COLLECTION_TYPE is NULL, this field is NULL
REFERENCE_CLASS:%String - Name of the referenced table's projecting class or NULL if not a reference
INSERTABLE:%String - Can a value for the field be supplied in an SQL INSERT statement, 'Yes' or 'No'.
UPDATEABLE:%String - Can a value for the field be supplied in an SQL UPDATE statement, 'Yes' or 'No'.
DEFAULT:%String - Field's default value expression (if any)
ROWIDWITHODBCVALUES:%String - If this is the ROWID field and the ROWID field is a datatype which
has OdbcToLogical conversion, return Yes, otherwise No. If this is not the
ROWID field, return NULL.
SERIAL_FIELD:%String - Is this a serial field? 'Yes' or 'No'.
ROWID_FIELD:%SmallInt - Is this a RowID field? 0 = No, 1 = Yes, 2 = A field the RowID is based on.
ORIGINATING_PROPERTY:%String - Name of the class property which projected this field
Rows are returned in table column order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects FOREIGN_KEY_NAME As%String (MAXLEN=128), FOREIGN_KEY_FIELDS As%String , TARGET_TABLE As%String (MAXLEN=128), TARGET_CLASS As%String (MAXLEN=31), TARGET_KEY As%String (MAXLEN=128), TARGET_KEY_FIELDS As%String , UPDATE_ACTION As%SmallInt , DELETE_ACTION As%SmallInt , FOREIGN_KEY_TYPE As%String (MAXLEN=12)
Get a List of all constraints from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLForeignKeys Procedure
%SQLCatalog_SQLForeignKeys('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns all ForeignKeys defined in the table:
ROWSPEC = FOREIGN_KEY_NAME:%String - Name of the FKey constraint
FOREIGN_KEY_FIELDS:%String - List of field in the FKey constraint
TARGET_TABLE:%String - Name of the table referenced in the FKey constraint
TARGET_CLASS:%String - Name of the class which projects the table referenced in the FKey constraint
TARGET_KEY:%String - SQLName of the key in the referenced table
TARGET_KEY_FIELDS:%String - List of fields in the reference table this FKey references
UPDATE_ACTION:%SmallInt - 0 = KeyCascade, 1 = KeyRestrict, 2 = SetNull, 3 = NoAction, 4 = SetDefault
DELETE_ACTION:%SmallInt - 0 = KeyCascade, 1 = KeyRestrict, 2 = SetNull, 3 = NoAction, 4 = SetDefault
FOREIGN_KEY_TYPE:%String - RELATIONSHIP if the FKey was projected from a one-many relationship or FOREIGNKEY if the KFey
was projected from a foreign key definition.
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects INDEX_NAME As%String , FIELDS As%String , UNIQUE As%SmallInt , PAGES As%Integer
Get a List of all Indices from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLIndices Procedure
%SQLCatalog_SQLIndices('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns all Indices in the table:
ROWSPEC = INDEX_NAME:%String - Name of the index
FIELDS:%String - Indexed Fields
UNIQUE:%SmallInt - 1 = UNIQUE Index, 0 NON_UNIQUE
PAGES:%Integer - # of map pages
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects PARENT_TABLE As%String (MAXLEN=128), PARENT_CLASS As%String (MAXLEN=128), PARENT_PROPERTY As%String (MAXLEN=128)
SQLParentTable Procedure
%Library.SQLCatalog_SQLParentTable('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns any parent table this table has
ROWSPEC =
PARENT_TABLE:%String - Name of the Parent Table
PARENT_CLASS:%String - Name of the class that projected the parent table
PARENT_PROPERTY:%String - Name of the property in the parent class (if from a collection) that projected this table
Rows are returned in no particular order
Selects CLASS_NAME As%String , PROCEDURE_TYPE As%String , METHOD_OR_QUERY_NAME As%String , DESCRIPTION As%String , NUMBER_INPUT_PARAMS As%Integer , NUMBER_OUTPUT_PARAMS As%Integer , RETURN_VALUE As%Integer , NUMBER_OF_COLUMNS As%Integer
Gets a detailed information about a single Stored Procedure
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLProcedureInfo Procedure
%SQLCatalog_SQLProcedureInfo('proc_name')
Takes 1 %String/VARCHAR type parameter which is an exact Procedure name
Returns information for the Procedure
ROWSPEC = CLASS_NAME:%String - Name of the class procedure method or query reside in
PROCEDURE_TYPE:%String - Type of Stored procedure
METHOD_OR_QUERY_NAME:%String - Name of the method or query the procedure is generated from
DESCRIPTION:%String - Procedure's description
NUMBER_INPUT_PARAMS:%Integer - Number of input paramaters
NUMBER_OUTPUT_PARAMS:%Integer - Number of output parameters
RETURN_VALUE:%Integer - Has a return value?
NUMBER_OF_COLUMNS:%Integer - Number of columns
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects PROCEDURE_NAME As%String (MAXLEN=128), PROCEDURE_TYPE As%String (MAXLEN=10), CLASS_NAME As%String (MAXLEN=63), METHOD_OR_QUERY_NAME As%String (MAXLEN=32)
Gets a description of the stored procedures available in the catalog.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLProcedures Procedure
%SQLCatalog_SQLProcedures('proc_name')
Takes 1 %String/VARCHAR type parameter which is an optional wildcard
match for the name of a Stored Procedure
Returns all SQL Procedures which match the name given
ROWSPEC = PROCEDURE_NAME:%String - Name of the Stored procedure, LIKE Pattern allowed
PROCEDURE_TYPE:%String - Type of Stored procedure
PROCEDURE_CLASS:%String - Name of the class which projected the Procedure
METHOD_OR_QUERY_NAME:%String - Name of the method or query the procedure is generated from
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects RELATIONSHIP_NAME As%String (MAXLEN=128), RELATIONSHIP_CLASS As%String (MAXLEN=31), RELATIONSHIP_TABLE As%String (MAXLEN=128), INVERSE As%String (MAXLEN=128), CARDINALITY As%String (MAXLEN=20), RELATIONSHIP_JDBC_TYPE As%Integer , RELATIONSHIP_JDBC_TYPENAME As%String (MAXLEN=20), INVERSE_JDBC_TYPE As%Integer , INVERSE_JDBC_TYPENAME As%String
Get a List of all constraints from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLRelationships Procedure
%SQLCatalog_SQLRelationships('tablename','cardinality')
Parameters:
tablename - %String - required - fully qualified table name
cardinality - %String - optional - default is '*'
Comma delimited list of cardinality values
MANY, ONE, PARENT, CHILDREN
'%', or '*' means any/all cardinality values
Returns Relationships defined in the class (including inherited relationships) which projected the table.
ROWSPEC = RELATIONSHIP_NAME:%String - SQL Name of the Relationship Property
RELATIONSHIP_CLASS:%String - Name of the class the relationship property references
RELATIONSHIP_TABLE:%String - Name of the table the relationship property references
INVERSE:%String - SQL Name of the inverse property
CARDINALITY:%String - Relationship's cardinality
RELATIONSHIP_JDBC_TYPE:%Integer - JDBC Datatype number of the relationship property
RELATIONSHIP_JDBC_TYPENAME:%String - JDBC Datatype name of the relationship property
INVERSE_JDBC_TYPE:%Integer - JDBC Datatype number of the inverse property
INVERSE_JDBC_TYPENAME:%String - JDBC Datatype name of the inverse property
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects SQL_RESERVED_WORD As%String (MAXLEN=25)
Gets a detailed information about a single Stored Procedure -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- SQLReservedWords Procedure %SQLCatalog_SQLReservedWords() Returns a list of SQL Reserved words ROWSPEC = SQL_RESERVED_WORD:%String - Name of the SQL Reserved Word Rows are returned in Reserved Word order using UPPER collation -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects RELATION_NAME As%String , TYPE As%String , OWNER As%String , LAST_COMPILED As%TimeStamp
Get a List of all Basetables and Views.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLTables Procedure
%SQLCatalog_SQLTables()
Privileges are checked against pre-define $Username.
Returns a list of tables and views defined in the current namespace.
ROWSEPC = RELATION_NAME:%String - Name of the table or view
TYPE:%String - 'TABLE' or 'VIEW'
OWNER:%String - Owner of the table/view
LAST_COMPILED:%TimeStamp - Time of last compilation
Rows are returned in order by RELATION_NAME
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects TRIGGER_NAME As%String , TIME_EVENT As%String , ORDER As%Integer , CODE As%String
Get a List of all Triggers from a table.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLTriggers Procedure
%SQLCatalog_SQLTriggers('tablename')
Takes 1 %String/VARCHAR type parameter which is the name of a BaseTable.
Returns all triggers in the table:
ROWSPEC = TRIGGER_NAME:%String - Name of the trigger
TIME_EVENT:%String - Trigger's TIME and EVENT
ORDER:%SmallInt - Trigger's order with the TIME_EVENT
CODE:%String - Trigger code
Rows are returned in no particular order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects FIELD_NAME As%String , DATATYPE As%String , MAXLEN As%String , BLOB As%String
Get a List of all Fields from a view.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLViewFields Procedure
%SQLCatalog_SQLViewFields('viewname')
Takes 1 %String/VARCHAR type parameter which is the name of a View.
Returns all fields in the table:
ROWSPEC = FIELD_NAME:%String - Name of the field
DATATYPE:%String - Field's datatype
MAXLEN:%String - MAXLEN parameter of field (or NULL)
BLOB:%String - Is field a BLOB, 'Yes' or 'No'
Rows are returned view column order
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Selects VIEW_QUERY As%String
Get full View definition.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
SQLViewInfo Procedure
%SQLCatalog_SQLViewInfo('viewname')
Takes 1 %String/VARCHAR type parameter which is the name of a View.
Returns all fields in the table:
ROWSPEC = VIEW_QUERY:%String - Query Text of the View
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-