%SQL.Manager.API
hidden class %SQL.Manager.API extends %Library.RegisteredObject
This is a system class that allows control of the SQL environment.Used by SQL Manager.
Method Inventory
- AltUser()
- AlterViewsOwner()
- CheckFileReadOnly()
- CheckIdentifier()
- CheckPriv()
- CheckResource()
- ChkChr()
- ChkClassName()
- ConnExists()
- CreateLinkProc()
- CreateLinkProcJ()
- CreateLinkTable()
- CreateRole()
- DropTable()
- DropUser()
- EscapeURL()
- FindClassName()
- GetDatatype()
- GetDatatypeJ()
- GetPort()
- GetQueryHistory()
- GetSchemaInfoClose()
- GetSchemaInfoExecute()
- GetSchemaInfoFetch()
- GetServerPlatForm()
- GrantRole()
- HasGrPriv()
- HasPriv()
- HasRvPriv()
- IsDTSupported()
- IsNumeric()
- OpenClassDefinition()
- PurgeAllQueries()
- PurgeAllQueriesAllNS()
- PurgeQueriesForTable()
- PurgeQuery()
- RebuildIndices()
- RevokeRole()
- SaveNamespacePriv()
- SaveObjPriv()
- SaveQuery()
- SaveQueryHistory()
- SaveSysPriv()
- SetServerInitCode()
- StatementFreezePlan()
- SupportDelimitedIdentifiers()
- TableInfoClose()
- TableInfoExecute()
- TableInfoFetch()
- TestDSN()
- TestDecodeDSN()
- TestDecodeJDBC()
- TestJDBC()
- TestJDBCGatewayConnection()
- TestSQLGatewayConnection()
- UpdateQueryHistory()
- ValidUser()
Methods
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") - InterSystems IRIS 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.
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") - InterSystems IRIS 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
Given a file name, checks whether the file is readonly.
Returns true or false.
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.
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.
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
Parameters:
classmethod CheckResource() as %Boolean
Check if user has the required resource to run web form wizard.
Check if string matches designated characters only.
type passed in maybe "A", "N", "AN", or "ROLENAME", "USERNAME".
Check a Class name or Table name
Return 1 valid name
Return 0 invalid name
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 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"
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.
Prepare query text to be passed to Show Plan page
Looks up and returns the class name associated with tablename.
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 InterSystems IRIS 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 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 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 (WIN, UNIX).
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.
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
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.
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 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.
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.
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.
Purges all cached queries that depend on table table.
Purges the cached query associated with routine routine.
Rebuild the indices for class classname.
Individual indices may be built if $LI(indices) is passed in.
Revoke one or more roles from one or more users.
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
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 InterSystems IRIS Super Server spawns processes to run SQL statements received from ODBC client applications, these processes can execute specific InterSystems IRIS 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 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 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.
Validate if the username is an actual user user.
Method returns 1 if user exists, 0 if not.
Queries
query GetSchemaInfo(%schema As %String)
query TableInfo()
Inherited Members
Inherited Methods
- %AddToSaveSet()
- %ClassIsLatestVersion()
- %ClassName()
- %ConstructClone()
- %DispatchClassMethod()
- %DispatchGetModified()
- %DispatchGetProperty()
- %DispatchMethod()
- %DispatchSetModified()
- %DispatchSetMultidimProperty()
- %DispatchSetProperty()
- %Extends()
- %GetParameter()
- %IsA()
- %IsModified()
- %New()
- %NormalizeObject()
- %ObjectModified()
- %OriginalNamespace()
- %PackageName()
- %RemoveFromSaveSet()
- %SerializeObject()
- %SetModified()
- %ValidateObject()