Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

For information on migrating to InterSystems IRISOpens in a new tab, see Why Migrate to InterSystems IRIS?

%SQL.Manager.API

This is a system class that allows control of the Cache SQL environment.
Used by SQL Manager.

Method Inventory

Methods

classmethod AddUser(Username As %String = "", Roles As %String = "", Password As %String = "", FullName As %String = "", NameSpace As %String = "", Routine As %String = "", ExpirationDate As %String = 0, ChangePassword As %Boolean = 0, Enabled As %Boolean = 1, Comment As %String, PhoneNumber As %String, PhoneProvider As %String, ByRef %msg As %String) as %String
Create a new user user Privileges maybe granted to the new user after a new user is created. Method returns 1 if success, 0 if failed. Roles is a comma-delimited list of Role names (i.e. "Role1,Role2,Role3") ExpirationDate is in default $zd date format.
classmethod AltUser(Username As %String = "", ByRef Properties As %String, ByRef %msg As %String) as %String
Modify user login password and/or modify user's %THRESHOLD value Username - Name of the user to modify
Properties - Array of properties to modify.
Properties("Enabled") - 0=Disabled, 1=Enabled Properties("ExpirationDate") - Expiration date of account 0=no expiration Properties("FullName") - Full name of the user Properties("InvalidLoginAttempts") - Number of invalid login attempts since last success Properties("InvalidLoginDateTime") - $h value of last invalid login attemp Properties("InvalidLoginDevice") - Last device for invalid login attempt Properties("InvalidLoginStatus") - Last error status for an invalid login attempt Properties("InvalidLoginService") - Last service used for an invalid login attempt Properties("LegacyPassword") - Legacy password for Cache Direct Properties("LoginDateTime") - $h value for last valid login attempt Properties("LoginDevice") - Last valid login device Properties("LoginService") - Last valid login service Properties("NameSpace") - Default Namespace for terminal login Properties("Password") - Cache Authentication password Properties("Roles")- Comma-separated List format of roles Roles are in the format:
"Role1,Role2"
For example:
s Properties("Roles")="%Developer,%Operator"
Properties("Namespace") - Namespace of the user for terminal access
Properties("Routine") - Routine the user runs for terminal access. Routine="" means programmer mode.
If a specific property is not passed in the properties array, the value is not modified.
classmethod AlterViewsOwner(viewname As %String, NewOwner As %String, ByRef %msg As %String) as %Integer
Given a view name and an SQL User name, change the view to be owned by the SQL User. Returns 1 = success 0 = failure Owner = null is allowed and would be defaulted to
classmethod CheckFileReadOnly(sFileName As %String, ByRef status As %Integer) as %Status
Given a file name, checks whether the file is readonly.

Returns true or false.

classmethod CheckIdentifier(ByRef propertylist As %String, %IDNoQuote As %Boolean = 0) as %Status
Input is a string of properties delimited by comma.
Check each property name. If the property name is a reserved word,
then set appropriate delimited identifier around it.
The propertylist will be updated and returned.
classmethod CheckPriv(Username As %String, Object As %String, Action As %String, Namespace As %String, Grant As %Integer) as %Integer
Check if user has privilege for a particular action. 
Parameters: 
	
  • Username - the login name
	
  • Object - "ObjectType,QualifiedName"
			ObjectType = 1 for table, 3 for view, 9 for procedure.
			Example: "1,Sample.Person"
	
  • Action - Comma delimited string of actions letters to check privileges for:
			"a,s,i,u,d,r,e" (in any order) which stands for
			ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE
	
  • Namespace - Namespace object resides in (optional) default is current namespace
	
  • Grant - 1/0 flag - check grant privileges (optional) default is 0
Returns: 1 if the user has the privilege, otherwise 0
    • classmethod CheckResource() as %Boolean
    Check if user has the required resource to run web form wizard.
    classmethod CheckRoutine(rtnname As %String) as %Integer
    Check if routine can be opened (must exist). Return True or False.
    classmethod ChkChr(string As %String, type As %String) as %Integer
    Check if string matches designated characters only. type passed in maybe "A", "N", "AN", or "ROLENAME", "USERNAME".
    classmethod ChkClassName(name As %String, type As %Integer) as %Integer
    Check a Class name or Table name Return 1 valid name Return 0 invalid name
    classmethod ChkIndices(classname As %String) as %Status
    Determine if Rebuilding indices for class classname is a valid option.
    classmethod ConnExists(nm As %String) as %Boolean
    classmethod CreateLinkProc(dsn As %String, schname As %String, procname As %String, classname As %String, packagename As %String, sqlname As %String, clprocname As %String, descr As %String) as %Status
    classmethod CreateLinkProcJ(dsn As %String, schname As %String, procname As %String, classname As %String, packagename As %String, sqlname As %String, clprocname As %String, descr As %String) as %Status
    classmethod CreateLinkTable(dsn As %String, tname As %String, cols As %String, pkey As %String, extname As %String) as %Status 
    Create a new table from TableLink Wizard based on external datasource
Parameters:
dsn - Datasource connection name
tname - external table name ^ new table name (user can modify)
  p1 = the "New Cache table name" from the wizard, without schema qualifier
  p2 = the "New class name" from the wizard with schema qualifier.
cols - for each field delimited by @
  p1 = column name from the external table
  p2 = new column caption, defaulted to the column name
  p3 = iType, data type of the column, from query
  p4 = iMaxLen, column size
  p5 = iPrecision
     p3-p5 are from query "%GTWCatalog:SQLFields":
       Fields(field).iType = ResultSet.Get("DATATYPE")
       Fields(field).iMaxLen = Val(ResultSet.Get("COLUMN_SIZE"))
       Fields(field).iPrecision = ResultSet.Get("DECIMAL_DIGITS")
  p6 = new column name, defaulted to column name
  p10 = ResultSet.Get("DATATYPE_NAME")
    classmethod CreateRole(Rolename As %String = "", Description As %String = "", Resources As %String = "", ByRef %msg As %String) as %String
    Create a Role.
    Create a Role in the Security database.
    Parameters:
    Name - Name of the role to create
    Description - Description of the role
    Resources - List format of resource/permission pairs to assign to the role
    Resources are in the format:
    ResourceName:Permission,ResourceName:Permission
    For example:
    s Resources="MyResource:RW,MyResource1:RWU"
    s Resources="" would create a role with no resources.
    Permision values are as follows:
    "R"
    "W"
    "U"
    classmethod DropTable(tablename As %String, droptype As %String, ByRef SQLCODE As %Integer, ByRef %msg As %String) as %Status
    Drop a table, view, or procedure. 
    Parameters:
	droptype  - "TABLE" for table, "VIEW" for view, "PROCEDURE" for stored procedure
	tablename - name of the table to delete (required)
	SQLCODE   - Passed by reference.  Returns SQLCODE
	contains 0 (success) or number < 0 (error)
	%msg      - Passed by reference.  If SQLCODE<0, contains error message.
    classmethod DropUser(user As %String, droptype As %String, ByRef SQLCODE As %Integer, ByRef %msg As %String) as %Status
    Drop a user or a role. 
    Parameters:
user      - user or role name to be dropped
droptype  - "USER" or "ROLE"
SQLCODE   - Passed by reference.  Returns SQLCODE
contains 0 (success) or number < 0 (error)
%msg      - Passed by reference.  If SQLCODE<0,
contains error message.
    classmethod EscapeURL(QueryText As %String) as %String 
    Prepare query text to be passed to Show Plan page
    classmethod FindClassName(tablename As %String) as %String
    Looks up and returns the class name associated with tablename.
    classmethod GetCachedQueryInfo(Routine As %String, ByRef CreateTime As %TimeStamp, ByRef LastPrepareTime As %TimeStamp, ByRef Source As %Boolean, ByRef QueryType As %Integer, ByRef Cost As %Numeric) as %String
    Returns information about the cached query Routine.

    Returns a string containing the query text for the cached query. Returns the create and last prepare 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.
    NOTE: LastPrepareTime is obsolete and will not be set

    classmethod GetDatatype(name As %String, precision As %Integer, scale As %Integer, ByRef parms) as %String
    classmethod GetDatatypeJ(name As %String, precision As %Integer, scale As %Integer, ByRef parms, name1 As %String) as %String 
    Return a Caché datatype based on an ODBC datatype
Parameters:
name - Character or integer ODBC type
precision - ODBC precision
scale - ODBC scale
parms - array, passed by reference, which will be filled with parameters
    classmethod GetDays() as %String
    ** DEPRECATED ***
    Return Days Before Purge value. Use value set for this namespace if it exists, else use server setting.
    classmethod GetDrives(ByRef Drives As %String) as %Status
    Returns drives on the server Server.

    Returns a string containing all the available drives.

    classmethod GetPassword(user As %String) as %String
    Given a user name, return the user's password. Return null if nothing is found.
    classmethod GetPort() as %String 
    Return client configuration default port for Show Plan
    classmethod GetQueryHistory(max As %Integer, ByRef currentIndex As %Integer, direction As %Integer, ByRef atend As %Integer, ByRef QueryText As %String, gsUsername As %String, gsNamespace) as %Status 
    Get the next query text searching backwards or forwards depending on direction given and
the base point is the current index location.
Parameters:
max  		- The maximum number of queries to save.
currentIndex - ByRef Indicating the current index in the history list
direction 	- search direction (Previous = 1, Next = -1)
atend     	- at the top = -1, at the bottom = 1, in the middle = 0
QueryText 	- ByRef The query text passed in. If it is null then user is deleting it.
gsUsername 	- User SQL Login name
msNamespace - Namespace it is connected (currently not in use)
    classmethod GetSchemaInfoClose(ByRef qHandle As %Binary) as %Status
    classmethod GetSchemaInfoExecute(ByRef qHandle As %Binary, %schema As %String) as %Status
    classmethod GetSchemaInfoFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) as %Status
    classmethod GetServerPlatForm(ByRef Server As %String, ByRef PlatForm As %String, ByRef Dir As %String, ByRef Version As %String) as %Status
    Returns server name, platform, current directory and version about the server Server.

    Returns a string containing the server name, current directory and the platform (VMSDSM, WIN, UNIX, VMS, or DSM).

    classmethod GrantRole(userlist As %String, rolelist As %String, username As %String, adminoption As %Integer = 0) as %String
    Grant one or more roles to one or more users.
    classmethod HasGrPriv(username As %String, privs As %String) as %String
    Check if user has system privilege to Grant a particular operation. 
    Parameters: Username - the login name
Privs - a list of privileges in the form of 1 or 0 seperated by commas
    classmethod HasPriv(username As %String, privs As %String, admin As %Integer) as %String
    Check if user has system privilege for a particular operation. 
    Parameters: Username - the login name
Privs - a list of privileges in the form of 1 or 0 seperated by commas
Admin flag - 0 or 1 for GRANT system privileges.
    classmethod HasRvPriv(username As %String, privs As %String) as %String
    Check if user has system privilege to Revoke a particular operation. 
    Parameters: Username - the login name
Privs - a list of privileges in the form of 1 or 0 seperated by commas
    classmethod IsDTSupported(dt As %Integer) as %Boolean
    classmethod IsNumeric(name As %String) as %Integer
    classmethod IsQueryDuplicate(glbIndex As %Integer, QueryText As %String, gsUsername As %String, msNamespace As %String, lasttime As %String) as %Integer 
    Store QueryHistory upon exiting from SQL Manager. Duplicates are skipped. Then clean up.
User can define how many they want to save. The latest used queries up to that number are saved.
Parameters:
glbIndex  - The global index for this SQLMgr session.
If it is not greater than 1 then set it and return.


    Check if this query is already saved. Don't save duplicates.
    classmethod NamespaceSecurityEnabled() as %Boolean
    Return 1 if SQL Namespace security is enabled. Return 0 if SQL namespace security is not enabled.
    classmethod OpenClassDefinition(tablename As %String) as %ClassDefinition
    Opens and returns an OREF for an instance of %ClassDefinition object associated with table tablename.

    The caller is responsible for closing the returned object.

    classmethod PurgeAllQueries(days As %Integer = 0) as %Status
    Purges all cached queries that have not had a Prepare in the last days days. If days is 0 (zero), then purges all cached queries.
    classmethod PurgeAllQueriesAllNS(days As %Integer = 0) as %Status
    Purges all cached queries in all namespaces that have not had a Prepare in the last days days. If days is 0 (zero), then purges all cached queries.
    classmethod PurgeQueriesForTable(table As %String) as %Status
    Purges all cached queries that depend on table table.
    classmethod PurgeQuery(routine As %String) as %Status
    Purges the cached query associated with routine routine.
    classmethod RebuildIndices(classname As %String, indices As %String = "") as %Status
    Rebuild the indices for class classname. Individual indices may be built if $LI(indices) is passed in.
    classmethod RevokeRole(userlist As %String, rolelist As %String, username As %String) as %String
    Revoke one or more roles from one or more users.
    classmethod SQLLogin(ByRef username As %String, password As %String) as %String
    Validate SQL Login with username and password. Return 1 if validation successful, 0 if failed.
    classmethod SaveNamespacePriv(type As %String, user As %String, namespace As %String, grantopt As %String) as %String
    Save user's Namespace privileges. type = 1 for GRANT; type = 0 for REVOKE grantopt = 1 for WITH GRANT OPTION GRANT ACCESS ON TO [WITH GRANT OPTION] REVOKE ACCESS ON FROM
    classmethod SaveObjPriv(acts As %String, type As %Integer, objs As %String, users As %String, g As %Integer, revoke As %Integer, ByRef SQLCODE As %Integer, ByRef %msg As %String, GrantedBy As %String = "", ByRef fields As %String = "") as %Status 
    Parameters:
acts	    - actions to grant. * for all actions
a for Alter
s for Select
i for Insert
u for Update
d for Delete
r for References
or any combination
type      - "1" for table, "3" for view, "9" for procedure
objs      - name of the table or view
users     - comma delimited list of users
g         - 0/1 for WITH GRANT OPTION
revoke    - 1 if revoke (-1 otherwise)
SQLCODE   - Passed by reference.  Returns SQLCODE
contains 0 (success) or number < 0 (error)
%msg      - Passed by reference.  If SQLCODE<0, contains error message.
GrantedBy - Who the privilege was granted by.  If revoke, revoke from user it was granted by
fields    - optional, $List of field names to grant or revoke the privs on the objs
    classmethod SaveQuery(QueryText As %String, gsUsername As %String) as %Status 
    This method saves a query on the server for ShowPlan to grab.
    classmethod SaveQueryHistory(max As %Integer, ByRef currentIndex As %Integer, ByRef QueryText As %String, gsUsername, msNamespace) as %Status 
    Insert new record (query history) to the top and push the rest of records down one. 
If total index reaches maximum then delete the last record
Parameters:
max  		- The maximum number of queries to save.
QueryText 	- ByRef If it is not null when passed in, then save it.
currentIndex - ByRef Indicating the current index in history list - the query textbox is sitting in
QueryText 	- ByRef The query text passed in. If it is null then user is deleting it.
gsUsername 	- User SQL Login name



msNamespace - Namespace it is connected (currently not in use)
    classmethod SaveSysPriv(type As %String, userlist As %String, privs As %String, grantopt As %String) as %String
    Save user's system privileges.
    classmethod SetServerInitCode() as %Integer
    Sets SQL/ODBC server initialization code. When the Caché super server spawns processes to run SQL statements received from ODBC client applications, these processes can execute specific Caché ObjectScript commands when they are started. This method sets the code that these processes execute. Return 0 Success Return 453 An error occurred in the user's initialization code.
    classmethod StatementFreezePlan(ByRef sql As %String, packages As %String, %switch As %String) as %String
    classmethod SupportDelimitedIdentifiers() as %Boolean
    Return user definition for Support Delimited Identifier
    classmethod TableInfoClose(QHandle As %Binary) as %Status
    classmethod TableInfoExecute(ByRef QHandle As %Binary) as %Status
    classmethod TableInfoFetch(ByRef QHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) as %Status
    classmethod TestDSN(dsn As %String, usr As %String, pwd As %String, timeout As %Integer = "", ByRef err As %String = "") as %Boolean
    classmethod TestDecodeDSN(dsn As %String, usr As %String, pwd As %String, timeout As %Integer = "", ByRef err As %String = "") as %Boolean
    classmethod TestDecodeJDBC(url As %String, usr As %String, pwd As %String, driver As %String, cp As %String, ByRef err As %String = "") as %Boolean
    classmethod TestJDBC(url As %String, usr As %String, pwd As %String, driver As %String, cp As %String = "", ByRef err As %String = "") as %Boolean
    classmethod TestJDBCGatewayConnection(name As %String, ByRef err As %String = "") as %Boolean
    classmethod TestSQLGatewayConnection(name As %String, ByRef err As %String = "") as %Boolean
    classmethod UpdateQueryHistory() 
    This method rearrange the index numbers for the query history and is being called when user
clicked the Execute query button or Show plan button.
    classmethod ValidUser(username As %String) as %String
    Validate if the username is an actual user user. Method returns 1 if user exists, 0 if not.

    Queries

    query GetSchemaInfo(%schema As %String)
    Selects TABLE_EXISTS As %Boolean, VIEW_EXISTS As %Boolean, CACHED_QUERY_EXISTS As %Boolean, PROCEDURE_EXISTS As %Boolean
    query TableInfo()
    Selects ClassName As %String, TableName As %String
    The TableInfo query returns a list of classes and corresponding table names.

    Inherited Members

    Inherited Methods

    FeedbackOpens in a new tab