Class Reference
%SYSTEM.SQL
| Server:basexml |
| Instance:SOAXML |
| User:UnknownUser |
 |
|||
| Private Storage | |||
The %SYSTEM.SQL class provides an interface for managing
Caché SQL configurations.
You can use it via the special $system object:
Do $system.SQL.CurrentSettings()
You can call help to get a list of all entry points:
Do $system.SQL.Help()
|
|
|
|
|
|
ABS returns the absolute value, which is always zero or a positive number$SYSTEM.SQL.ABS(numeric-expression)
ABS returns the same data type as numeric-expression. If numeric-expression is not a number (for example, the string 'abc') ABS returns 0. ABS returns NULL when passed a NULL value.
- numeric-expression
- A number whose absolute value is to be returned
ALPHAUP returns the Alphaup collation of the passed in value
- $SYSTEM.SQL.ALPHAUP(string-exp)
- string-exp Any string expression value
ALPHAUP converts all alphabetic characters to upper case (i.e., the ALPHAUP format) and removes all punctuation except commas and question marks.
CEILING is a scalar numeric function that returns the smallest integer greater than or equal to a given numeric expression$SYSTEM.SQL.CEILING(numeric-expression)
- numeric-expression
- A number whose ceiling is to be calculated
CONVERT is a SQL function that converts a given expression to a specified data type.
$SYSTEM.SQL.CONVERT(expr,convert-to-type,convert-from-type)
CONVERT is a SQL function that converts expression expr from type convert-from-type to the specified data type convert-to-type.
- expr
- The expression to be converted
- convert-to-type
- The data type to which expr is to be converted. Currently, only "SQL_TIMESTAMP", "SQL_TIME", "SQL_DATE", "SQL_VARCHAR", "SQL_INTEGER", and "SQL_DOUBLE" are supported
- convert-from-type
- The data type of expr. Currently, only "SQL_TIMESTAMP", "SQL_TIME", "SQL_DATE", "SQL_VARCHAR", "SQL_INTEGER", and "SQL_DOUBLE" are supported
The convfrom value is expected to be a Logical value for SQL_DATE and SQL_TIME. When converting from SQL_DATE or SQL_TIME to SQL_TIMESTAMP, the input value is expected to be a Logical Date or Time value. When converting from SQL_VARCHAR to SQL_TIME, the input value is expected to be an ODBC Time formatted value. When converting from SQL_VARCHAR to SQL_DATE, the input value is expected to be an ODBC Date formatted value. When converting from SQL_DATE or SQL_TIME to SQL_VARCHAR, the returned value will contain the ODBC format of the date or time value.
Import a Caché SQL script file.
Check if user has SQL privilege for a particular action.
Parameters:
- Username
- Name of the user to check. Required.
- Object
- ObjectType,ObjectName of the table, view, or procedure to check the privilege of. ObjectTypes are 1 (table), 3 (view), 9 (procedure). For example, "1,Sample.Person" or "9,SQLuser.My_Procedure". Required.
- Action
- Comma delimited string of actions letters to check privileges for. Actions are one or more of the letters "a,s,i,u,d,r,e" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE. Privilege "e" is only allowed for Procedures. CheckPriv will only return 1 if the user has privileges on all Actions specified. Required.
- 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 Username does have the privilege 0 - if the Username does not have the privilege %Status - if CheckPriv call is reporting an error Notes:
If Username is a user with the %All role, CheckPriv will return 1 even if the Object does not exist. If the user calling CheckPriv is not the same as Username, the calling user must hold the %Admin_Secure:"U" privilege. Examples:
- Do $SYSTEM.SQL.CheckPriv($username,"1,HHR.ProductionValues","s,i,u,d","USER",1)
- Do $SYSTEM.SQL.CheckPriv("Miranda","3,SQLUser.Person","s","PRODUCT",0)
Displays all the current SQL settings to the current device.
DATEADD is a general date/time function that returns a date calculated by adding a certain number of date parts (such as hours or days) to a specified timestamp$SYSTEM.SQL.DATEADD(datepart,numeric-exp,date-exp)
- datepart
- The full name or abbreviation of a date or time part.
The following date parts are supported for DATEADD
year yy yyyy
quarter qq q
month mm m
week wk ww
dayofyear dy y
day dd d
hour hh
minute mi n
second ss s
millisecond ms
- numeric-exp
- A numeric expression of any number type. The value is truncated to an integer. The value indicates the number of dateparts that will be added to date-exp.
- date-exp
- The date/time expression that will be modified.
The date-exp parameter can be in any of the following formats, and may include or omit fractional seconds:If the year is given as two digits, Caché checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
- A Caché %Date logical value (+$H)
- A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
- A Caché %String (or compatible) value
The Caché %String (or compatible) value can be in any of the following formats:
99999,99999 ($H format)
Sybase/SQL-Server-date Sybase/SQL-Server-time
Sybase/SQL-Server-time Sybase/SQL-Server-date
Sybase/SQL-Server-date (default time is 00:00:00)
Sybase/SQL-Server-time (default date is 01/01/1900)
Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
where delimiter is a slash (/), hyphen (-), or period (.).
Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
DATEDIFF is a general date/time function that returns the interval between two dates$SYSTEM.SQL.DATEDIFF(datepart,startdate,enddate)
Startdate is subtracted from enddate to determine how many datepart intervals are between the two dates.
- datepart
- The date part in which the interval is to be measured.
The following date parts are supported for DATEDIFF
year yy yyyy
month mm m
week wk ww
day dd d
hour hh
minute mi n
second ss s
millisecond ms
- startdate
- The starting date for the interval.
- enddate
- The ending date for the interval.
The startdate and enddate parameters can be in any of the following formats:If the year is given as two digits, Caché checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
- A Caché %Date logical value (+$H)
- A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
- A Caché %String (or compatible) value
- The Caché %String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:
99999,99999 ($HOROLOG format)
Sybase/SQL-Server-date Sybase/SQL-Server-time
Sybase/SQL-Server-time Sybase/SQL-Server-date
Sybase/SQL-Server-date (default time is 00:00:00)
Sybase/SQL-Server-time (default date is 01/01/1900)
- Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
where delimiter is a slash (/), hyphen (-), or period (.).- Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
DATENAME is a general date/time function that returns a CHARACTER STRING containing the name of the specified date part of a date/time value.$SYSTEM.SQL.DATENAME(datepart,dateexp)
The dateexp parameter can be in any of the following formats:
- datepart
- The type of date part that the returned value will represent.
The following date parts are supported for DATENAME
year yy yyyy
quarter qq q
month mm m
week wk ww
weekday dw
dayofyear dy y
day dd d
hour hh
minute mi n
second ss s
millisecond ms
- dateexp
- A date/time expression from which the date part is to be returned.
If the year is given as two digits, Caché checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
- A Caché %Date logical value (+$H)
- A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
- A Caché %String (or compatible) value
- The Caché %String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:
99999,99999 ($HOROLOG format)
Sybase/SQL-Server-date Sybase/SQL-Server-time
Sybase/SQL-Server-time Sybase/SQL-Server-date
Sybase/SQL-Server-date (default time is 00:00:00)
Sybase/SQL-Server-time (default date is 01/01/1900)
- Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
where delimiter is a slash (/), hyphen (-), or period (.).- Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
DATEPART is a general date/time function that returns an INTEGER representing the specified date/time part of the specified date/time expression.$SYSTEM.SQL.DATEPART(datepart,dateexp)
dateexp must contain a value of type datepart.
- datepart
- The type of date part that the returned value will represent.
The following date parts are supported for DATEPART
year yy yyyy
quarter qq q
month mm m
week wk ww
weekday dw
dayofyear dy y
day dd d
hour hh
minute mi n
second ss s
millisecond ms
sqltimestamp sts
The sqltimestamp (abbreviated sts) datepart value is for use only with DATEPART. Do not attempt to use this parameter value in other contexts- dateexp
- A date/time expression from which the date part is to be returned.
The dateexp parameter can be in any of the following formats:If the year is given as two digits, Caché checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
- A Caché %Date logical value (+$H)
- A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
- A Caché %String (or compatible) value
- The Caché %String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:
99999,99999 ($HOROLOG format)
Sybase/SQL-Server-date Sybase/SQL-Server-time
Sybase/SQL-Server-time Sybase/SQL-Server-date
Sybase/SQL-Server-date (default time is 00:00:00)
Sybase/SQL-Server-time (default date is 01/01/1900)
- Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
where delimiter is a slash (/), hyphen (-), or period (.).- Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
DAYNAME is a date/time function that returns a character string containing the name of the day in a given date expression.$SYSTEM.SQL.DAYNAME(dateexp)
The startdate and enddate parameters can be in any of the following formats:
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
If the year is given as two digits, Caché checks the sliding window to interpret the date. The system default for the sliding window can be set via the %DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
- A Caché %Date logical value (+$H)
- A Caché %TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
- A Caché %String (or compatible) value
- The Caché %String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:
99999,99999 ($HOROLOG format)
Sybase/SQL-Server-date Sybase/SQL-Server-time
Sybase/SQL-Server-time Sybase/SQL-Server-date
Sybase/SQL-Server-date (default time is 00:00:00)
Sybase/SQL-Server-time (default date is 01/01/1900)
- Sybase/SQL-Server-date is one of these five formats:
mmdelimiterdddelimiter[yy]yy
dd Mmm[mm][,][yy]yy
dd [yy]yy Mmm[mm]
yyyy Mmm[mm] dd
yyyy [dd] Mmm[mm]
where delimiter is a slash (/), hyphen (-), or period (.).- Sybase/SQL-Server-time represents one of these three formats:
HH:MM[:SS:SSS][{AM|PM}]
HH:MM[:SS.S]
HH['']{AM|PM}
DAYOFMONTH is a date/time function that returns an integer from 1 to 31 that corresponds to the day of the month in a given date expression.$SYSTEM.SQL.DAYOFMONTH(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
DAYOFWEEK is a date/time function that returns an integer from 1 to 7 that corresponds to the day of the week in a given date expression.$SYSTEM.SQL.DAYOFWEEK(dateexp)
The returned values represent these days: 1 - Sunday, 2 - Monday, 3 - Tuesday, 4 - Wednesday, 5 - Thursday, 6 - Friday, 7 - Saturday
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
DAYOFYEAR is a date/time function that returns an integer from 1 to 366 that corresponds to the day of the year in a given date expression.$SYSTEM.SQL.DAYOFYEAR(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
Import a DDL/DML script file.
Parameters:
- DDLMode
- Vendor from which the script file originated. This parameter is required. Supported values are:
- CACHE
- FDBMS
- Informix
- Interbase
- MSSQLServer
- MySQL
- Oracle
- Sybase
- SQLUser
- Caché SQL username to import the file as. This parameter is required.
- infile
- The full path name of the script file to import. This parameter is required.
- outfile
- The full path name of the file to report errors in. This parameter is Optional. Default is the same as the infile with a _Errors.log extension.
- nosup
- TRUE(1)/FALSE(0) flag. Determines if unsupported statements from the script file should be recorded in the nosupfile. This parameter is optional.
- nosupfile
- If nosup is true, the name of the file to log the unsupported statements in. Default is the same as the infile with a _Unsupported.log extension. This parameter is optional.
- deos
- End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.
- errpause
- Number of seconds to pause after an error occurs. The default is 5 seconds. This parameter is optional.
Examples:
- Do $SYSTEM.SQL.DDLImport("Sybase","_SYSTEM","C:\PT\Patient.sql")
- Do $SYSTEM.SQL.DDLImport("Oracle","DAVE","C:\DDT\all_tables.sql",all.log,0,"",";",2)
Import all DDL/DML script file in a given directory. All files with the extension .sql in the directory will be imported.
Parameters:
- DDLMode
- Vendor from which the script file originated. This parameter is required. Supported values are:
- Informix
- MSSQL
- MSSQLServer - same as MSSQL
- Sybase
- directory
- The full path name of the directory to import. This parameter is required.
- logFile
- The full path name of the file to report errors in. This parameter is Optional. Default is DDLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
- eosDelimiter
- End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.
Examples:
- Do $SYSTEM.SQL.DDLImportDir("Informix","C:\Work\db1\,"C:\Work\db1\import.log",";")
- Do $SYSTEM.SQL.DDLImportDir("Informix","C:\Work\db1\,1,";")
Return the default schema name for the current process in the current namespace
Example:
Set CurrentSchema = $SYSTEM.SQL.DefaultSchema()
This methods closes all the open ODBC/JDBC Gateway connections and unloads the SQL Gateway shared library.
This entry point can be used to delete a table definition.
Parameters:
- tablename
- Name of the SQL table to delete. The name may be qualified with a schema name: Medical.Patient. If the schema name is omitted, the default schema is used.
- deldata
- TRUE(1)/FALSE(0) flag which determine if the table's data is to be deleted or not. If deldata="" or undefined, use system flag to determine if data should be deleted.
- SQLCODE
- Passed by reference. Returns SQLCODE. Contains 0 (success) or number < 0 (error).
- %msg
- Passed by reference. If SQLCODE<0, contains error message.
Examples:
- Do $SYSTEM.SQL.DropTable("MedLab.Patient",1,.SQLCODE,.%msg)
- Do $SYSTEM.SQL.DropTable("IscPerson",1,.SQLCODE,.%msg) ; Deletes SQLUSer.IscPerson
This entry point can be used to delete a view definition.
Parameters:
- viewname
- Name of the SQL view to delete. The name may be qualified with a schema name: Medical.PatientView If the schema name is omitted, the default schema is used.
- SQLCODE
- Passed by reference. Returns SQLCODE. Contains 0 (success) or number < 0 (error)
- %msg
- Passed by reference. If SQLCODE<0, contains error message.
Examples:
- Do $SYSTEM.SQL.DropView("MedLab.PatientView",.SQLCODE,.%msg)
- Do $SYSTEM.SQL.DropView("IscPersonView",.SQLCODE,.%msg) ; Deletes SQLUSer.IscPerson
Export a Caché SQL DDL/DML script file containing User definitions, Role definitions, and/or Privileges.
Parameters:
- Schema
- Name of the schema in the current namespace to export the privileges for. Use "*" to specify the export of SQL Privileges for all schemas in the current namespace. The default is "*". Note that when "*" is specified, '%' schemas (such as %Dictionary) are not exported. To export privileges for tables in a schema that begins with '%' you must explicitly specify that schema name in Schema.
- Table
- Name of the table/view in the specified schema to export the privileges for. Use "*" to specify the export of SQL Privileges for all table/s views in the specified schema(s). The default is "*". Note that if you "*" for Schema the export will behave as if you entered "*" for Table as well. For example there is no mechanism for exporting the Person table/view in each schema in the namespace.
- File
- Name of the file to export the SQL statement to.
- FileOpenParam
- Parameters to use when opening the File. The default is "WNS".
- Users
- 1/0 flag. Specify 1 to export the User definition. The default is 1.
- Roles
- 1/0 flag. Specify 1 to export the Role definition, and the GRANT statements to assign the Roles to the Users and Roles they have been granted to. The default is 1.
- Privileges
- 1/0 flag. Specify 1 to export the Table and View privileges for the tables and views specified in the Schema/Table specification. The default is 1.
- SQLSysPrivileges
- 1/0 flag. Specify 1 to export the SQL System privileges defined in the current namespace for the users and roles. The default is 1.
Examples:
- Do $SYSTEM.SQL.Export("Sample","*","C:\PT\Sample.sql","WNS",0,0,1,1)
- Do $SYSTEM.SQL.DDLImport("*","*","C:\DDT\UsersAndRoles.sql","WNS",1,1,0,0)
The method returns a Status Code.
A file created via $SYSTEM.SQL.Export() can be imported using one of:
- Do $SYSTEM.SQL.DDLImport("CACHE",...)
- Do $SYSTEM.SQL.Cache()
The Export() method will not export the following users and roles -
- Users:
- SuperUser, Admin, UnknownUser, %System, CSPSystem, _SYSTEM, _PUBLIC
- Roles:
- %All, %Developer, %Manager, %Operator, %SQL, %LegacyTerminal, %LegacyCacheDirect, %LegacySQL
Import a Caché or Open M with SQL FDBMS DDL script file.
FLOOR is a numeric function that returns the largest integer less than or equal to a given numeric expression$SYSTEM.SQL.FLOOR(dateexp)
- numeric-exp
- A number whose floor is to be calculated.
Function returns the current Compile Mode setting, either Deferred or Immediate.
Return the current IDENTITY_INSERT option value. Possible values are:
0 IDENTITY cannot be set 1 IDENTITY can be set
Function returns the current contents of the %ROWID variable. It can be called after INSERT to get %ROWID value of the row inserted.
Return the current SELECTMODE value. Possible values are:
0 LOGICAL 1 ODBC 2 DISPLAY
Returns ODBC/JDBC disconnect code. Upon disconnect any ObjectScript code defined in this setting will be executed immediately before the server process Halts. This code will also attempt to be executed anytime the server process encounters an unrecoverable error that causes the server process to Halt. It will not be executed if the server process encounters a Halt in other user defined code, if the process crashes, if the process is stopped, or Caché is stopped or forced down. If no disconnect code is defined for this namespace, "" is returned. The disconnect code is defined on a per-namespace basis.
Examples:
- Write $system.SQL.GetServerDisconnectCode()
Returns ODBC/JDBC/SQL Manager initialization code. This Initialization code is executed at login time when connecting to Caché SQL through ODBC, JDBC, or the SQL Manager. If no initialization code is defined for this namespace, "" is returned. Initialization code is defined on a per-namespace basis.
Examples:
- Write $system.SQL.GetServerInitCode()
HOUR is a date/time function that returns an integer from 0 to 23 that corresponds to the hour component in a given date-time expression.$SYSTEM.SQL.HOUR(timeexp)
- timeexp
- An expression that is a logical %Library.Time value or timestamp literal.
INSTR is a function that searches string for substring and returns an integer indicating the position of the character in string that is the first character of this occurrence.$SYSTEM.SQL.INSTR(string,substring,position,occurrence)
- string
- The string to search in.
- substring
- The string to search for in string.
- position
- A nonzero integer indicating the character of string where Caché begins the search. If position is negative, then Caché counts backward from the end of string and then searches backward from the resulting position.
- occurrence
- An integer indicating which occurrence of substring Caché should search for. If occurrence is greater than 1, then Caché searches for the second occurrence beginning with the second character in the first occurrence of string, and so forth. An occurrence of less than 1 is treated the same as 1.
Import all DDL/DML script files in a given directory. All files with the extension .sql in the directory will be imported. The caller can optionally specify a comma delimited list of file extensions to import. Subdirectories are also recursively processed if the caller specifies the recurse argument as 1.
Parameters:
- pDialect
- Vendor from which the script file originated. This parameter is required. Supported values are:
- MSSQL
- MSSQLServer - same as MSSQL
- Sybase
- pDirectory
- The full path name of the directory to import. This parameter is required.
- logFile
- The full path name of the file to report errors in. This parameter is Optional. Default is SQLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
- pExtensions
- A comma delimited list of file extensions to import. This parameter is optional and defaults to "sql".
- eosDelimiter
- End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.
- pRecurse
- If pRecurse is 1 then subdirectories of pDirectory will be recursively imported. This parameter is optional and defaults to 0.
- pMessageMode
- if true then all messages reported by executing imported statements will be displayed on the current device.
- pEchoMode
- if true, all statement source is displayed on the current device.
- pErrorPause
- the number of seconds to pause when an error is reported. The default is five seconds.
Examples:
- Do $SYSTEM.SQL.ImportDir("Sybase","C:\Work\db1\,"C:\Work\db1\import.log",";")
- Do $SYSTEM.SQL.ImportDir("Sybase","C:\Work\db1\,"C:\Work\db1\import.log","sql,tab,sp",";",1)
- Do $SYSTEM.SQL.ImportDir("Sybase","C:\Work\db1\,1,";")
Import an Informix DDL/DML script file.
The Informix DDL/DML Import Utility supports the following statements:Statements other than the ones above are not handled by the utility and must be added to your Caché application manually (if applicable).
- CREATE TABLE ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE VIEW ...
- SET OPTION ...
- GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
Import an Interbase DDL/DML script file.
The Interbase DDL/DML Import Utility supports the following statements:Statements other than the ones above are not handled by the utility and must be added to your Caché application manually (if applicable).
- CREATE TABLE ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE UNIQUE INDEX ...
- CREATE VIEW ...
- CREATE ROLE ...
This entry point can be used to determine if a string is an SQL Reserved word.
Parameters:
- word
- Name of the word to check against the SQL Reserved Word list.
Examples:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_IsReservedWord(word)
- Write $SYSTEM.SQL.IsReservedWord("select") // Writes a 1
- Write $SYSTEM.SQL.IsReservedWord("football") // Writes a 0
This entry point can be used to determine if an SQL identifier is a valid regular SQL identifier. An SQL regular identifier must begin with '%', '_', or a letter followed by 0 or more letters, numbers, '_', '@', '#', or '$' characters. It must also not be an SQL Reserved word. Identifiers which do not qualify as Regular identifiers must be specified as Delimited identifiers in SQL statements.
Parameters:
- identifier
- Name of the identifier to check.
Examples:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_IsValidRegularIdentifier(identifier)
- Write $SYSTEM.SQL.IsValidRegularIdentifier("select") // Writes a 0 (reserved word)
- Write $SYSTEM.SQL.IsValidRegularIdentifier("football") // Writes a 1
- Write $SYSTEM.SQL.IsValidRegularIdentifier("%Correct") // Writes a 1
- Write $SYSTEM.SQL.IsValidRegularIdentifier("%Correct_$Amount") // Writes a 1
LASTDAY is a scalar date/time function that returns the date of the last day of the month for a given date expression.$SYSTEM.SQL.LASTDAY(dateexp)
LASTDAY returns the date of the last day of the specified month as an integer in $HOROLOG format. Leap years differences are calculated.
- dateexp
- A date integer (in $HOROLOG format) or a datetime string. A datetime string is of the format: yyyy-mm-dd hh:mm:ss The time portion of this string is optional.
LASTDAY returns 0 when an invalid date is specified: a year earlier than 1841; the day or month as zero; the month greater than 12; or the day larger than the number of days in that month on that year.
MINUTE is a date/time function that returns an integer from 0 to 59 that corresponds to the minute component in a given date-time expression.$SYSTEM.SQL.MINUTE(timeexp)
- timeexp
- An expression that is a logical %Library.Time value or timestamp literal.
MONTH is a date/time function that returns an integer from 1 to 12 that corresponds to the month in a given date expression.$SYSTEM.SQL.MONTH(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
MONTHNAME is a date/time function that returns a character string containing the name of the month in a given date expression.$SYSTEM.SQL.MONTHNAME(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
Import an MS SQL Server DDL/DML script file.
The MS SQL Server DDL/DML Import Utility supports the following statements:Statements other than the ones above are not handled by the utility and must be added to your Caché application manually (if applicable).
- CREATE [GLOBAL TEMPORARY] TABLE ...
- CREATE VIEW ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE CLUSTERED INDEX ...
- CREATE UNIQUE INDEX ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
MVR returns the MVR collation of the passed in value$SYSTEM.SQL.MVR(string-exp)
MVR performs collation translations needed for MultiValue
- string-exp
- Any string expression value
Import an Oracle DDL/DML script file.
The Oracle DDL/DML Import Utility supports the following statements:Statements other than the ones above are not handled by the utility and must be added to your Caché application manually (if applicable).
- CREATE TABLE ...
- CREATE VIEW ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE UNIQUE INDEX ...
- DROP TABLE ...
- DROP VIEW ...
- DROP INDEX ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
- CREATE USER ...
- CREATE ROLE ...
- GRANT { SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
This entry point can be used to determine if a stored procedure exists.
Parameters:
- procname
- Name of the stored procedure to check
procname can be qualified or unqualified. If unqualified, the default schema is applied.- metadata
- Passed by reference, optional argument.
If the stored procedure exists, returns the following information about the procedure: $ListBuild(SchemaName,ProcedureName,Classname that projected the stroed procedure,procedure type)Examples:
- Write $SYSTEM.SQL.ProcedureExists("SQLUser.stpSalaryReport") // Writes a 1 if procedure SQLUser.stpSalaryReport exists
- Write $SYSTEM.SQL.ProcedureExists("stpSalaryReport",.metadata) // Writes a 1 if procedure [DefaultSchema].stpSalaryReport exists, returns metadata=$lb("SQLUser","stpSalaryReport","User.stpSalaryReport","function")
Notes:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_ProcedureExists(procname)
- If the user calling the function does not hold any privileges for the procedure, 0 will be returned.
- If a class exists that would project this procedure to SQL during compilation, but the class has not been compiled, 0 will be returned.
- If a procedure is marked as hidden, 0 will be returned.
- metadata will be set to "" if 0 is returned by the function.
Purges Cached Queries.
Parameter:
- days
- Number of days. Purge cached queries not prepared in more than days days.
Examples:
- Do $system.SQL.Purge(0) // Purge all Cached Queries
- Do $system.SQL.Purge() // Purge all Cached Queries
- Do $system.SQL.Purge(30) // Purge all Cached Queries that have not be used (prepared) in the last 30 days
Purges Cached Queries in all namespaces on this system
Example:
- Do $system.SQL.PurgeAllNamespaces() // Purge all Cached Queries on this system
Purges Cached Queries given the name(s) of the routine.
Parameter:
- routine
- The name of the Cached Query routine to purge, or a comma delimited list of routine names to purge.
Examples:
- Do $system.SQL.PurgeForRoutine("CacheSql243") ; Purge this Cached Queries
- Do $system.SQL.PurgeForRoutine("CacheSql1,CacheSql2,CacheSql3") ; Purge these 3 Cached Queries
Purges all Cached Queries which use table table.
Parameter:
- table
- The qualified name of the table to Purge the Cached Queries for If the schema is not specified, the default schema will be used.
Examples:
- Do $system.SQL.PurgeForTable("MedLab.Patient")
- Do $system.SQL.PurgeForTable("IscPerson") ; Purges Cached Queries for SQLUser.IscPerson
QUARTER is a date/time function that returns an integer from 1 to 4 that corresponds to the quarter of the year in a given date expression.$SYSTEM.SQL.QUARTER(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
Turn the results of a query into a table definition. This utility takes a query and the name of a new table and executes the query. The results of the query are turned into a new table of name 'table'.
Parameters:
- query
- The query text to execute. query can be of the format:
query="sql text" OR query = # of lines query(1) = sql line 1 query(n) = sql line n- table
- Name of the new SQL table to generate. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.
- display
- TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is FALSE (0).
- error
- Array of error messages returned if there is a problem. The format is:
error = # of error lines error(1) = error line 1 error(n) = error line nExamples:
Do $SYSTEM.SQL.QueryToTable("SELECT * FROM MedLab.Patient WHERE Sex = 'Male'","MedLab.MalePatient",1,.errors) Set query=3 Set query(1)="SELECT *" Set query(2)=" FROM ""Med Lab"".Patient"" Set query(3)=" WHERE Sex = 'Male'" Do $SYSTEM.SQL.QueryToTable(.query,"""Med Lab"".MalePatient",1,.errors) Do $SYSTEM.SQL.QueryToTable("SELECT * FROM Patient WHERE Sex = 'Male'","MalePatient",1,.errors) ; Creates SQLUser.MalePatient
This entry point can be used to determine if a role exists
Parameters:
- rolename
- Name of the role to check
Examples:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_RoleExists(rolename)
- Write $SYSTEM.SQL.RoleExists("SalesManager") // Writes a 1 if role SalesManager exists
SECOND is a date/time function that returns an integer from 0 to 59 that corresponds to the second component in a given date-time expression.$SYSTEM.SQL.SECOND(timeexp)
- timeexp
- An expression that is a logical %Library.Time value or timestamp literal.
This entry point can be used to return the description of an SQLCODE value.
Parameters:
- SQLCODE
- SQLCODE value.
Examples:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_SQLCODE(SQLCODE)
- Write $SYSTEM.SQL.SQLCODE(-105)
- Write $SYSTEM.SQL.SQLCODE(100)
SQLSTRING returns the SqlString collation of the passed in value$SYSTEM.SQL.SQLSTRING(string-exp,maxlen)
- string-exp
- Any string expression value
- maxlen
- Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen
SQLSTRING converts string-exp to a format that is sorted as a (case-sensitive) string. SQLSTRING strips trailing white space (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as SqlString is " "_$Char(0).
SQLUPPER returns the SqlUpper collation of the passed in value$SYSTEM.SQL.SQLUPPER(string-exp,maxlen)
SQLUPPER converts string-exp to a format that is sorted as a (case-insensitive) upper-case string. SQLUPPER converts all alphabetic characters to uppercase, strips trailing white space (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as SqlUpper is " "_$Char(0).
- string-exp
- Any string expression value
- maxlen
- Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen
STRING returns the String collation of the passed in value$SYSTEM.SQL.STRING(string-exp,maxlen)
STRING converts all alphabetic characters are uppercased; all punctuation characters are removed, except the comma; a single space is added to the beginning of the expression. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as String is " "_$Char(0).
- string-exp
- Any string expression value
- maxlen
- Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen
STRING converts string-exp to a STRING collation format.
Sets the AUTO_COMMIT mode for this Caché process. Setting takes effect immediately and lasts for the duration of the process or until SetAutoCommit is called again.
Parameter:Returns:
- flag
- 0 - No AutoCommit
1 - AutoCommit ON
2 - AutoCommit OFF
- Old value (0, 1, or 2) of the AutoCommit setting.
Defines the lock timeout length used for Cached Queries when a lock needs to be acquired on Cached Query metadata. The default is 120 seconds. The setting in on a system-wide basis.
Parameter:
- timeout
- Number of seconds the lock command should timeout after when attempting to lock cached query definitions.
- oldval
- Passed By Reference. Contains the previous value of the setting
Returns:
Status Code
Examples:
NOTES:
- Set sc=$system.SQL.SetCachedQueryLockTimeout(60) // Sets cached query lock timeout to 60 seconds
- Set sc=$system.SQL.SetCachedQuerySaveSource(300,.oldval) // Sets lock timout to 5 minutes, returns previous lock timout setting in oldvar.
- This is a system-wide setting.
THIS FUNCTION IS NO LONGER SUPPORTED Defines what routine prefix name will be used for Cached Query routines.
Parameters:
- prefix
- A string to be used as the routine prefix for Cached Queries. For example: "CQ" - means all Cached Queries will begin with the string "CQ". If prefix is not specified, the default "CacheSql" prefix will be used.
- system
- TRUE(1)/FALSE(0) flag to set the Cached Query routine prefix for the entire system, or just for the current namespace. If TRUE (1), the system wide flag is changed. If FALSE (0), it pertains to this namespace only.
- oldval
- Passed By Reference. Contains the previous value of the setting
Returns:
Status Code
Examples:
NOTE: You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. THIS FUNCTION IS NO LONGER SUPPORTED
- Set sc=$system.SQL.SetCachedQueryPrefix("CQ") // System wide setting
- Set sc=$system.SQL.SetCachedQueryPrefix("abc",0) // For this namespace
- Set sc=$system.SQL.SetCachedQueryPrefix("",0,.oldprefix) // Restore default, for this namespace
Defines whether or not the source code for Cached Queries is retained. The default is no source is saved. The setting in on a per system basis.
Parameter:
- flag
- TRUE(1)/FALSE(0) flag to set the flag to retain source code for all Cached Queries. If TRUE (1), source is retained. If FALSE (0), source is deleted after the Cached Query has compiled.
- oldval
- Passed By Reference. Contains the previous value of the setting
Returns:
Status Code
Examples:
NOTES:
- Set sc=$system.SQL.SetCachedQuerySaveSource(1) // Retain source
- Set sc=$system.SQL.SetCachedQuerySaveSource(0,.oldval) // Do not retain source
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will take effect immediately for all processes in Caché after this function is called. Any new cached queries created by any process will immediately begin saving (or not saving) cached query source. Any existing cached queries will only have source saved if the system was configured to save source at the time the statement was prepared. - This is a system-wide setting.
Set the namespace wide flag which turns on Deferred Compilation mode. Changing this setting will only take effect for this process.
Set the namespace wide flag which turns on Immediate Compilation mode. Immediate Compile Mode is the default compilation mode. If there are pending compilations when switching from Deferred/Install Compile Mode, they will be compiled immediately. Changing this setting will only take effect for this process.
Set the namespace wide flag which turns on Install Compilation mode. This mode should only be used for installation procedures where no data exists for any of the tables being created. If data exists for the tables definitions being manipulated through DDL statement, use Deferred Compile Mode instead. Changing this setting will only take effect for this process.
Set the namespace wide flag which turns on Nocheck Compilation mode. This mode is the same as immediate mode except that existing data is not validated against new constraints. For example if you add a unique constraint to a table that already has data, Nocheck mode will not validate that the constraint is valid. This compile mode must be used with extreme caution. You could end up with data integrity problems in your application. Changing this setting will only take effect for this process.
Sets the flag which determines if a DDL DROP TABLE statement deletes the table's data.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide flag to DELETE the table's data when the table is dropped. Otherwise, the data is not deleted.
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDL Identifier Tanslation mappings configuration settings.
This is for filtering/modifying valid SQL identifier characters when translating SQL identifiers into Objects identifiers. When converting an SQL identifier to an Objects Identifier at DDL run time, the characters in the 'from' list will be converted to the characters in the 'to' list. This is done through the Caché $Translate function. For example:
SQL Table name = "My Table!"
from = '"! '
to = ''
Class name = $Translate("My Table!",from,to) = MyTable
Parameter:Returns:
- from
- A string of characters to translate from
- to
- A string of characters to translate to (by position in the string) Optional. Default is "".
- oldfrom
- Passed By Reference. Contains the previous value of the 'from' setting
- oldto
- Passed By Reference. Contains the previous value of the 'to' setting
NOTES:
- Status Code
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo201 configuration setting to [dis]allow CREATE TABLE for existing table. This flag determines if an SQLCODE -201 error is returned when the attempt is made to CREATE through DDL a table which already exists. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made to CREATE a table through DDL that already exists. The existing table will be dropped, and then the CREATE TABLE will occur. Otherwise, return SQLCODE=-201. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo30 configuration setting to [dis]allow DROP TABLE for a non-existing table. This flag determines if an SQLCODE -30 error is returned when the attempt is made to DROP through DDL a table which does not exists. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made to DROP a non-existent table through DDL. Otherwise, return SQLCODE=-30. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo307 configuration setting to [dis]allow DDL ADD PRIMARY KEY when the table already has an existing PRIMARY KEY. This flag determines if an SQLCODE -307 error is returned when an attempt is made to add through DDL a primary key constraint to a table which already has a primary key constraint defined. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made add a primary key constraint to a table through DDL when a primary key constraint already exists for the table. The existing primary key constraint will be dropped, and then new primary key constraint added. Otherwise, return SQLCODE=-307. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo311 configuration setting to [dis]allow DDL ADD FOREIGN KEY when existing FOREIGN KEY of the same name already exists for the table. This flag determines if an SQLCODE -311 error is returned when an attempt is made to create, through DDL, a foreign key constraint when a foreign key constraint with the same name already exists for the table. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made to CREATE a duplicate foreign key constraint through DDL. Otherwise, return SQLCODE=-311. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo315 configuration setting to [dis]allow DROP CONSTRAINT for non-existing constraint. This configuration setting determines if an SQLCODE -315 error is returned when the attempt is made to DROP, through DDL, a non-existing constraint. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made to DROP a non-existent constraint through DDL. Otherwise, return SQLCODE=-315. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo324 configuration setting to [dis]allow CREATE INDEX for existing index. This configuration setting determines if an SQLCODE -324 error is returned when an attempt is made to CREATE, through DDL, an index on a table which already has an index of the same name. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made to CREATE an index through DDL that already exists. The existing index will be dropped, and then the CREATE INDEX will occur. Otherwise, return SQLCODE=-324. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Sets the DDLNo333 configuration setting to [dis]allow DROP INDEX for non-existing index. This configuration setting determines if an SQLCODE -333 error is returned when an attempt is made to DROP, through DDL, a non-existing index. The default is that an error is returned.
Parameters:Returns:
- flag
- 1 or 0. If TRUE, set the system wide configuration setting for the current configuration to not return an error if an attempt is made to DROP a non-existent index through DDL. Otherwise, return SQLCODE=-333. The default is 0 (FALSE).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Set configuration setting for PRIMARY KEY in DDL also being the IDKey index. This configuration setting determines if a primary key constraint, specified through DDL, also becomes the IDKey index in the class definition.
By default, the primary key does NOT also become the idkey index.
Having the primary key index also be an IDKey index generally gives better performance, but it means the Primary key fields cannot be updated.
Parameter:Returns:
- flag
- TRUE(1)/FALSE(0) If flag is TRUE (1), set the system wide configuration setting for the current configuration to NOT make Primary Key constraints become IDKey indices. If flag is FALSE (0), the Primary Key index will also become the IDKey index. The default is TRUE (1).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
THIS FUNCTION IS NO LONGER SUPPORTED Defines how many days a Cached Query remain defined past its last day of use.
Parameters:
- days
- Number of days a Cached Query should remained defined after its last date of use before being deleted. If days does not evaluate to a number, the DaysBeforePurge setting will be deleted.
- system
- TRUE(1)/FALSE(0) flag to set/change the DaysBeforePurge setting for the entire system, or just for the current namespace. If TRUE (1), the system wide flag is changed. If FALSE (0), it pertains to this namespace only.
- oldval
- Passed By Reference. Contains the previous value of the setting
Examples:
- Set sc=$system.SQL.SetDaysBeforePurge(14) // System wide setting
- Set sc=$system.SQL.SetDaysBeforePurge(21,0) // For this namespace
- Set sc=$system.SQL.SetDaysBeforePurge("",0,.oldval) // For this namespace
Parameter:timeout gives the number of seconds to set the lock timeout to. The default is 10 seconds.
Returns:
Status Code NOTE: You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. THIS FUNCTION IS NO LONGER SUPPORTED
Sets the default schema used by SQL.
This configuration setting provides the ability to define a default schema name other than SQLUser, the default. When an unqualified table name is encountered in an SQL statement (and there is no #import statement specified), the default schema will be used. You may specify _CURRENT_USER for the default schema name if you wish to use the SQL username the process logged in as the name of the default schema. If the process has not logged in to SQL, SQLUser will be used as the default schema name. You may also specify _CURRENT_USER/. In this case, if the process has not logged in to SQL, will be used as the default schema name. For example: _CURRENT_USER/HMO will use HMO as the default schema if the process has not logged in to SQL. This setting has nothing to do with the mappings between SQL schema names and the class package name, it only specifies the default schema.
Parameter:Returns:
- schema
- String containing the default SQL schema name. If schema is "" or not defined, the default schema will be set to SQLUser.
- oldval
- Passed By Reference. Contains the previous value of the setting
- Namespace
- Boolean 1/0 flag. If TRUE, set the default schema for the current namespace only. The default for Namespace is FALSE
NOTES:
- Status Code
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately.
Set the Default precision for the Time component of the value returned by the GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP SQL Scalar functions.
Parameter:Returns:
- value
- Precision (number of decimal places for the millisecond portion of the time value). The default is 0, milliseconds are not returned in the values returned by the GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP functions.
- oldval
- Passed By Reference. Contains the previous value of the setting
NOTES:
- Status Code
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Set the configuration setting which determines if double quote ("") in an SQL statement is used for delimited (quoted) identifiers or string constants. The default is delimited identifiers are supported.
Parameter:Returns:
- flag
- TRUE(1)/FALSE(0) If flag is TRUE (1), "..." is treated as an identifier. If flag is FALSE (0), "..." is treated as a string literal. The default is TRUE (1).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Defines whether or not SQL SELECT statements perform a $system.ECP.Sync() call in the OPEN code The default is ECP Sync is OFF. The setting in on a per system basis.
Parameter:
- flag
- TRUE(1)/FALSE(0) flag to define the setting to Perform ECP Syncs for Select queries. If TRUE (1), ECP Sync is turned on. If FALSE (0), ECP Sync is turned OFF.
- oldval
- Passed By Reference. Contains the previous value of the setting
Returns:
Status Code
Examples:
NOTES:
- Set sc=$system.SQL.SetECPSync(1) // ECP Sync ON
- Set sc=$system.SQL.SetECPSync(0,.oldval) // ECP Sync OFF
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Defines whether or not SQL DISTINCT is optimized to use indexes. The default is DISTINCT optimizations are ON. The setting in on a per system basis.
Parameter:
- flag
- TRUE(1)/FALSE(0) flag to define the setting to allow SQL optimizations of DISTINCT. If TRUE (1), DISTINCT optimization is turned on. If FALSE (0), DISTINCT optimization is turned OFF.
- oldval
- Passed By Reference. Contains the previous value of the setting
Returns:
Status Code
Examples:
- Set sc=$system.SQL.SetFastDistinct(1) // DISTINCT Optimization ON
- Set sc=$system.SQL.SetFastDistinct(0,.oldval) // DISTINCT Optimization OFF
If true (the default) many SQL queries involving DISTINCT (and GROUP BY) will run much more efficiently by making better use of indices (if available). This is an optimization added in Caché 5.1. The downside of this is that the values returned by such queries will be collated in the same way they are stored within the index (i.e., results may be in upper case). Some applications care about the case of values returned by such queries. If "Fast DISTINCT" is set to false (0), the SQL will revert to its pre-5.1 behavior with regards to DISTINCT behavior. NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Set the SELECTIVITY of a field and property to the value of the given selectivity
Parameter:
- schema
- Name of the table's schema. Default is the default schema.
- tablename
- Name of the table the field is in (required).
- fieldname
- Name of the field to set the SELECTIVITY for (required).
- selectivity
- New selectivity value for the field (required). The selectivity of a property specifies the approximate frequency of specific values within the entire distribution of values. The Selectivity value for a column is generally the percentage of rows within a table that would be returned as a result of query searching for a typical value of the column. For example, suppose a table contains a Gender column whose value is evenly distributed between "M" and "F". The Selectivity value for the Gender column would be 50%, as a typical query based on Gender would find 50% of the rows within the table. The Selectivity value for a more unique property, such as TaxID, is typically a small percentage that varies according to the size of the table. Examples of values you can specify here are:
- "10%" - Means that typical values for this column will return 10% of the rows in the table
- "1" - Means this field is unique. For any given value, it will return 1 row from the table.
- A pure number will calculate the selectivity as EXTENTSIZE/selectivity. For example, if EXTENTSIZE is 100000 and selectivity is 1000, this will set the selectivity to 1%. - "NUMROWS" - This is the same as specifying "1", it means the field is unique. This is allowed for legacy support of M/SQL tables that have been converted to Caché class definitions.
- NUMROWS/positive_integer - This will calculate the SQL SELECTIVITY as EXTENTSIZE/positive_integer. For example if EXTENTSIZE is 100000 and you specify NUMROWS/5000, this will set the SQL SELECTIVITY to 20, which means for a typical value for the field, 20 rows of the table will be returned. This is allowed for legacy support of M/SQL tables that have been converted to Caché class definitions.
- There is no validation of the value you enter for the SELECTIVITY. If you enter something not recognized as a valid SELECTIVITY, such as the string "nonsense", it will be turned into a value of 0. If the SQL query processor sees a SELECTIVITY of 0, it will attempt to come up with a typical SELECTIVITY value or the field based on how many rows are in the table and whether or not the field is a reference column, is part of the IDKEY field, has a VALUELIST specification, etc.
- KeepClassUpToDate
- TRUE(1)/FALSE(0) flag. If TRUE the class definition will be updated with the new SELECTIVITY value, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.
Returns:
Status Code
Example:
- Do $SYSTEM.SQL.SetFieldSelectivity("MedLab","Patient","Home_Phone","2.5%",0)
Set the configuration setting which determines if Foreign Key Referential Integrity checks are performed in the SQL Filer. Turning off SQL Filer Referential Integrity checking will suppress any SQLCODE -121, -122, -123, and -124 errors. The default value is TRUE (Validation checks are performed).
Parameter:Returns:
- flag
- TRUE(1)/FALSE(0) flag to determine if SQL Filer referential integrity checks are performed. If flag is FALSE (0), the SQL Filer will skip referential integrity checks. Checks will be performed if flag is TRUE (1). The default is TRUE (1).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- - You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting.
- - Changing this configuration setting will only take effect for this process and new processes starting in Caché after this function is called. Any existing Caché processes will still use the old setting.
- - This is a system-wide setting.
Set the IDENTITY_INSERT option for this Caché process.
IDENTITY_INSERT controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicity IDENTITY or ROWID value when saving a new object (Caché Objects) or inserting a new ROW (SQL) then an error condition is reported.
Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again.
Valid values are:
0 IDENTITY cannot be set 1 IDENTITY can be set If a valid value is passed in then the IDENTITY_INSERT option for the current process will be set to that value and the previous IDENTITY_INSERT value is returned. Otherwise the IDENTITY_INSERT setting is left unchanged and pStatus will contain a %Status value describing the error and the current IDENTITY_INSERT value is returned.
Set the Lock Threshold for Caché locks acquired during filing of rows within a single transaction.
Parameter:Returns:
- value
- Number of to acquire before escalating to a table lock. The default is 1000.
- oldval
- Passed By Reference. Contains the previous value of the setting
NOTES:
- Status Code
- - You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting.
- - Changing this configuration setting will only take effect for this process and new processes starting in Caché after this function is called. Any existing Caché processes will still use the old setting.
- - This is a system-wide setting.
Set the Lock timeout for Caché locks acquired during execution of SQL statements.
Parameter:Returns:
- timeout
- Number of seconds to set the lock timeout to. The default is 10 seconds.
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- - You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting.
- - Changing this configuration setting will only take effect for this process and new processes starting in Caché after this function is called. Any existing Caché processes will still use the old setting.
- - This is a system-wide setting.
Defines whether or not all class queries project as SQL Stored Procedures regardless of the query's SqlProc value. The default is class queries are not projected as stored procedures unless the query SqlProc setting is TRUE. The setting in on a per system basis.
Parameter:
- flag
- TRUE(1)/FALSE(0) flag to define the setting force all class queries to be projected as stored procedures. If FALSE (0), only class queries with SqlProc = TRUE are projected as stored procedures. If TRUE (1), all class queries are projected as stored procedures.
- oldval
- Passed By Reference. Contains the previous value of the setting
Returns:
Status Code
Examples:
NOTES:
- Set sc=$system.SQL.SetQueryProcedures(1) // All class queries projected as procedures
- Set sc=$system.SQL.SetQueryProcedures(0,.oldval) // Only SqlProc=TRUE class queries projected as procedures
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Set the configuration setting which determines if embedded SQL statements are retained as comments in the .INT code version of the routine. The default is no SQL comments are retained.
Parameter:Returns:
- flag
- TRUE(1)/FALSE(0) If flag is TRUE (1), SQL text will be retained as comments in the .INT code. No comments will be created if flag is FALSE (0). The default is FALSE (0).
- oldval
- Passed By Reference. Contains the previous value of the setting
NOTES:
- Status Code
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Set the configuration setting which determines if SQL security is enabled. If SQL security is ON, all Caché SQL security is active. This means:If SQL security is OFF, Caché SQL security is inactive. This means:
- Privilege-based table/view security is active. A user may only perform actions on a table or view they have been granted privilege for.
Parameter:
- Privilege-based table/view security is suppressed. A user may perform actions on a table or view even if they have not been granted privileges to do so.
Returns:
- flag
- TRUE(1)/FALSE(0) If flag is TRUE (1), SQL security is ON If flag is FALSE (0), SQL security is OFF. The default is TRUE(1).
- oldval
- Passed By Reference. Contains the previous value of the setting
NOTES:
- Status Code
- - You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting.
- - Changing this configuration setting will only take effect for this process and new processes starting in Caché after this function is called. Any existing Caché processes will still use the old setting.
- - This is a system-wide setting.
Sets the flag that controls whether or not we gather SQL Statistics about each run of a query
Parameter:
- flag
- 4 values 0 - Default, do not generate stats, 1 - Generate Stats For all queries, 2 - Record Stats for for just the outer loop of the query, 3 - Record stats for all modules of the query
Returns:
Old Value
Set the SELECTMODE for this Caché process. Setting takes effect immediately and lasts for the duration of the process or until SetSelectMode is called again.
Valid values are:
If a valid value is passed in then the SELECTMODE for the current process will be set to that value and the previous SELECTMODE value is returned. Otherwise the SELECTMODE setting is left unchanged and pStatus will contain a %Status value describing the error and the current SELECTMODE value is returned. This method call is similar to the preprocessor directive: #sqlcompile select =
0 LOGICAL 1 ODBC 2 DISPLAY
Define ODBC/JDBC disconnect code. Upon disconnect any ObjectScript code defined in this setting will be executed immediately before the server process Halts. This code will also attempt to be executed anytime the server process encounters an unrecoverable error that causes the server process to Halt. It will not be executed if the server process encounters a Halt in other user defined code, if the process crashes, if the process is stopped, or Caché is stopped or forced down. The disconnect code is defined on a per-namespace basis.
Parameter:
- code
- A single line of Caché ObjectScript to be executed. Call with code="" or undefined to delete disconnect code for this namespace.
Examples:
- Do $system.SQL.SetServerDisconnectCode("Do Cleanup^%ZMedPatUtil")
- Do $system.SQL.SetServerDisconnectCode("")
- Changing this configuration setting will take effect immediately for all server processes in Caché after this function is called. Any existing Caché processes will execute the server disconnect code disconnection.
Define ODBC/JDBC/SQL Manager initialization code. This Initialization code is executed at login time when connecting to Caché SQL through ODBC, JDBC, or the SQL Manager. Initialization code is defined on a per-namespace basis.
Parameter:
- code
- A single line of Caché ObjectScript to be executed. Call with code="" or undefined to delete initialization code for this namespace.
Examples:
- Do $system.SQL.SetServerInitCode("Do Setup^%ZMedPatSetup")
- Do $system.SQL.SetServerInitCode("")
- Changing this configuration setting will only take effect for new processes starting in Caché after this function is called. Any existing Caché processes will not have executed the server init code upon connection.
Set the TCP Keep Alive interval for Caché xDBC connections.
Parameter:Returns:
- seconds
- Number of seconds to set the TCP Keep Alive interval to. The default is 300 seconds (5 minutes).
- oldval
- Passed By Reference. Contains the previous value of the setting
Status Code NOTES:
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Set the default format for the SQL TO_DATE() function.
Parameter:Returns:
- value
- String value with the default format the TO_DATE function will return.
- oldval
- Passed By Reference. Contains the previous value of the setting
NOTES:
- Status Code
- You must have the "USE" permission on the %Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all Caché processes immediately. - This is a system-wide setting.
Interactive SQL Shell. Execute SQL statements from the command line. Refer to %SQL.Shell for more info.
Import a Sybase DDL/DML script file.
The Sybase DDL/DML Import Utility supports the following statements:Statements other than the ones above are not handled by the utility and must be added to your Caché application manually (if applicable).
- CREATE [GLOBAL TEMPORARY] TABLE ...
- CREATE VIEW ...
- ALTER TABLE ...
- CREATE INDEX ...
- CREATE CLUSTERED INDEX ...
- CREATE UNIQUE INDEX ...
- INSERT ...
- UPDATE ...
- DELETE ...
- SET OPTION ...
- GRANT CONNECT ... (Same as Caché SQL CREATE USER ...)
- GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
TOCHAR is a general SQL string function that converts a given date or number value to a string.$SYSTEM.SQL.TOCHAR(expr,format)
The first use of TOCHAR is to convert a date expression to a string. The value for expr must be a valid Logical date. The second use of TOCHAR is to convert a number to a string. See the TO_CHAR Documentation in the Caché SQL Reference for complete details.
- expr
- A Logical date or number expression to be converted.
- format
- A date or number format specifying the format for the expr conversion. If format contains the characters "Y", "MM", "RR", or "DD", expr is assumed to be a date value; otherwise, it is a numeric value.
TODATE is a general SQL string function that converts a given string expression to a value of DATE data type.$SYSTEM.SQL.TODATE(expr,format)
The use of TODATE is to specify the input format of a string value containing a date to be converted to a Logical %Date value. The format of expr is specified in the format parameter. format will be used as a key to translate expr into a valid %Date logical value.
- expr
- The expression to be converted. The expression can be a string date expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR. It can also be an integer between 1 and 2980013.
- format
- A date format specifying the format for expr. If 'J' is specified, expr must be an integer. If format is omitted, 'DD MON YYYY' is the default value.
See the TO_DATE Documentation in the Caché SQL Reference for complete details.
TOTIMESTAMP is a general SQL string function that converts a given string expression to a value of TIMESTAMP data type.$SYSTEM.SQL.TOTIMESTAMP(stringexp,format)
The use of TOTIMESTAMP is to specify the input format of a string value containing a datetime to be converted to a Logical %TimeStamp value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid %TimeStamp logical value. If an error occurs during the execution of TOTIMESTAMP, the function will return a value of 0 and an error message will be defined in the %msg variable.
- stringexp
- The expression to be converted. The expression can be a string datetime expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR.
- format
- A datetime format code specifying the format for stringexp. If format is omitted, 'DD MON YYYY HH:MM:SS' is the default value.
See the TO_TIMESTAMP Documentation in the Caché SQL Reference for complete details.
TRUNCATE returns the Truncate collation of the passed in value$SYSTEM.SQL.TRUNCATE(string-exp,maxlen)
- string-exp
- Any string expression value
- maxlen
- Optional. An integer, which specifies that the collated value will be truncated to the length of maxlen if maxlen is not specified, TRUNCATE behaves the same as EXACT.
SQLSTRING converts string-exp to a exact format and is sorted as a (case-sensitive) string. TRUNCATE simply returns fhe first maxlen characters of the expression.
Import a TSQL script file.
TSQL source files can contain any TSQL syntax supported by Caché TSQL.
This entry point can be used to invoke the TSQL shell.
(no parameters or result)
Example:
- Do $SYSTEM.SQL.TSQLShell()
In the shell type ? for help
This entry point can be used to determine if a base table exists.
Parameters:
- tablename
- Name of the table to check
tablename can be qualified or unqualified. If unqualified, the default schema is applied.- metadata
- Passed by reference, optional argument.
If the table exists, returns the following information about the table: $ListBuild(SchemaName,TableName,Classname that projected the table,System Flag)Examples:
- Write $SYSTEM.SQL.TableExists("Sample.Person") // Writes a 1 if table Sample.Person exists
- Write $SYSTEM.SQL.TableExists("Sample.Person",.metadata) // Writes a 1 if table Sample.Person exists, returns metadata=$lb("Sample","Person","Sample.Person","0")
Notes:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_TableExists(tablename)
- If the user calling the function does not hold any privileges for the table, 0 will be returned.
- If a class exists that would project this table to SQL during compilation, but the class has not been compiled, 0 will be returned.
- If a class that projects the table is marked as hidden, 0 will be returned.
- metadata will be set to "" if 0 is returned by the function.
Calculate accurate extentsize and selectivity for all classes/tables and their properties/fields within a schema.
Parameter:
- schema
- Name of a schema to tune tables. If the schema name is omitted, the default schema is used.
- update
- TRUE(1)/FALSE(0) flag. Tells tuner whether to update the tables and class definitions with the new extentsize and selectivity values. If update=1, the values will be updated in the table and class definition. The default is FALSE (0).
- display
- TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is FALSE (0).
- pMessage
- Passed by reference. May return error information.
- KeepClassUpToDate
- TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the class definition will be updated with the new EXTENTSIZE and SELECTIVITY values, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.
- ClearValues
- TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the SELECTIVITY and EXTENTSIZE settings will be cleared from the class and table definition. Note that if the class is deployed the class definition will not be updated.
- LogFile
- Optional name of a file to log the output of the TuneTable utility to. if display is also TRUE, output will go to the current device and the log file.
Examples:
- Do $SYSTEM.SQL.TuneSchema("MedLab",1,1,.errors,1)
- Do $SYSTEM.SQL.TuneSchema("""Medical Lab""",1,1,.errors,0)
- Do $SYSTEM.SQL.TuneSchema("") ; Tunes SQLUser schema
Calculate accurate extentsize and selectivity for a class/table and its properties/fields.
Parameter:
- table
- Name of a table or '*' to tune all tables. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.
- update
- TRUE(1)/FALSE(0) flag. Tells tuner whether to update the table and class definitions with the new extentsize and selectivity values. If update=1, the values will be updated in the table and class definition. The default is FALSE (0).
- display
- TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is FALSE (0).
- pMessage
- Passed by reference. May return error information.
- KeepClassUpToDate
- TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the class definition will be updated with the new EXTENTSIZE and SELECTIVITY values, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.
- ClearValues
- TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the SELECTIVITY and EXTENTSIZE settings will be cleared from the class and table definition. Note that if the class is deployed the class definition will not be updated.
- LogFile
- Optional name of a file to log the output of the TuneTable utility to. if display is also TRUE, output will go to the current device and the log file.
Examples:
- Do $SYSTEM.SQL.TuneTable("MedLab.Patient",1,1,.errors,1,,"Tune.log")
- Do $SYSTEM.SQL.TuneTable("""Medical Lab"".Patient",1,1,.errors,0)
- Do $SYSTEM.SQL.TuneTable("IscPerson") ; Tunes SQLUser.IscPerson
UPPER returns the Upper collation of the passed in value$SYSTEM.SQL.UPPER(string-exp)
UPPER converts all alphabetic characters to upper case (i.e., the UPPER format). Note that punctuation is not changed.
- string-exp
- Any string expression value
This entry point can be used to determine if a user exists
Parameters:
- username
- Name of the user to check
Examples:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_UserExists(username)
- Write $SYSTEM.SQL.UserExists("Robert") // Writes a 1 if user Robert exists
This entry point can be used to determine if a view exists.
Parameters:
- viewname
- Name of the view to check
viewname can be qualified or unqualified. If unqualified, the default schema is applied.- metadata
- Passed by reference, optional argument.
If the view exists, returns the following information about the view: $ListBuild(SchemaName,ViewName,Classname that projected the view,System Flag)Examples:
- Write $SYSTEM.SQL.ViewExists("SQLUser.STestView") // Writes a 1 if view SQLUser.STestView exists
- Write $SYSTEM.SQL.ViewExists("STestView",.metadata) // Writes a 1 if view [DefaultSchema].STestView exists, returns metadata=$lb("SQLUser","STestView","User.STestView","0")
Notes:
This method can also be called as a Stored Procedure named %SYSTEM.SQL_ViewExists(viewname)
- If the user calling the function does not hold any privileges for the view, 0 will be returned.
- If a class exists that would project this view to SQL during compilation, but the class has not been compiled, 0 will be returned.
- If a class that projects the view is marked as hidden, 0 will be returned.
- metadata will be set to "" if 0 is returned by the function.
WEEK is a date/time function that returns an integer from 1 to 53 that corresponds to the week of the year in a given date expression.$SYSTEM.SQL.WEEK(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.
YEAR is a date/time function that returns an integer in the range 1840-9999 that indicates the year in a given date expression.$SYSTEM.SQL.YEAR(dateexp)
- dateexp
- An expression that is a logical %Library.Date value or timestamp literal.