Home > Class Reference > ENSLIB namespace > %Library.SQLAPI
Private  Storage   

%Library.SQLAPI


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

Inventory


Parameters Properties Methods Queries Indices ForeignKeys Triggers
42 1


Summary


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
AddUser AltUser CheckFileReadOnly CheckIdentifier
CheckPriv CheckRoutine ChkChr ChkClassName
ChkIndices CreateLinkTable CreateRole DropTable
DropUser FindClassName GetCachedQueryInfo GetDatatype
GetDays GetDrives GetPassword GetQueryHistory
GetServerPlatForm GrantRole HasGrPriv HasPriv
HasRvPriv KillQueryHistory OpenClassDefinition PurgeAllQueries
PurgeQueriesForTable PurgeQuery RebuildIndices RevokeRole
SQLLogin SaveObjPriv SaveQueryHistory SaveSysPriv
SetServerInitCode TableInfoClose TableInfoExecute TableInfoFetch
ValidUser


Methods


• classmethod AddUser(user As %String, password As %String, desc As %String) as %String
Add new user user with no system privileges. Privileges maybe granted to the new user after a new user is created. Method returns 1 if success, 0 if failed.
• classmethod AltUser(user As %String, password As %String, desc As %String) as %String
Modify user login password.
• 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) 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 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 CreateLinkTable(dsn As %String, tname As %String, cols As %String, pkey As %String, extname As %String) as %String
    Create a new table from TableLink Wizard based on external datasource Parameters: dsn - Datasource connection name tname - table names delimited by ^ external table name ^ new table name (user can modify) cols - for each field delimited by @ external column name^column caption^iType^iMaxLen^iPrecision
    • classmethod CreateRole(user As %String, ByRef SQLCODE As %String, ByRef %msg As %String) as %Status
    Create a security role.
    • classmethod DropTable(tablename As %String, droptype As %String, ByRef SQLCODE As %Integer, ByRef %msg As %String) as %Status
    Check if user has privilege for a particular action.
    Parameters: droptype - "TABLE" for table, "VIEW" for view 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 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
    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
    ** DEPRECATED ***
    Given a user name, return the user's password. Return null if nothing is found.
    • classmethod GetQueryHistory(ByRef glbIndex As %Integer, ByRef currentIndex As %Integer, direction As %Integer, ByRef atend As %Integer, ByRef QueryText As %String) 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: glbIndex - The global index for this SQLMgr session. If it's not greater than 1 then set it and return. currentIndex - where we are in the history list, refence updated. direction - search direction (forward = 1, backward = -1) atend - at the top = -1, at the bottom = 1, in the middle = 0 QueryText - Passed by reference. If it's not null when passed in, then save it. At the end, return array with all history.
    • 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) 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 KillQueryHistory(ByRef glbIndex As %Integer) as %Status
    Kill of QueryHistory upon exiting from SQL Manager. Parameters: glbIndex - The global index for this SQLMgr session. If it's not greater than 1 then set it and return.
    • 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 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) as %Status
    Rebuild the indices for class classname.
    • 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 SaveObjPriv(acts As %String, type As %Integer, objs As %String, users As %String, g, revoke As %Integer, ByRef SQLCODE As %Integer, ByRef %msg 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 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.
    • classmethod SaveQueryHistory(ByRef glbIndex As %Integer, ByRef currentIndex As %Integer, ByRef QueryText As %String) as %Status
    Parameters: glbIndex - The global index for this SQLMgr session. If it's not greater than 1 then set it and return. QueryText - Passed by reference. If it's not null when passed in, then save it. At the end, return array with all history.
    • 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 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 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 TableInfo()
    Selects ClassName As %String, TableName As %String
    The TableInfo query returns a list of classes and corresponding table names.