Caché Transact-SQL (TSQL) Migration Guide
Caché TSQL Language Elements
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter describe the following TSQL language elements:

Literals
String Literals
A string literal must be delimited by quote characters. The preferred delimiter characters are single quote characters. You can also use double quote characters as string delimiters if you specify SET DELIMITED_IDENTIFER OFF. Otherwise, double quote characters are parsed as delimiting an identifier.
If you delimit a string literal with single quote characters, you can include literal double quote characters within the string. To include a literal single quote character within the string, double it by typing two single quotes.
A string containing literal single quotes, such as 'this is an ''embedded'' string', is compiled by Caché to single quotes within double quotes: "this is an 'embedded' string".
Empty Strings
When migrating Transact-SQL code to Caché TSQL, it may be necessary to redefine the empty string. You can do this by setting the following Caché system global:
^%SYS("sql","sys","namespace",nspace,"empty string")
All of these specified values are keyword literals, except nspace, which is a namespace name specified as a quoted string.
Caution:
Changing the empty string definition should be done with extreme caution. It can result in data containing different representations for an empty string. It can also cause existing programs to fail when executed in this namespace. After defining the empty string, you must purge all cached queries and recompile all classes and routines for that namespace that use the former empty string definition.
The following Caché ObjectScript example changes the empty string definition for the SAMPLES namespace. It first sets the empty string value to a single blank space. It then sets the empty string value to the non-printing character represented by the ASCII code 0. (This example then immediately resets the empty string value to the Caché default):
  SET ^%SYS("sql","sys","namespace","SAMPLES","empty string")=" "
  WRITE !,"Empty string set to:"
  ZZDUMP ^%SYS("sql","sys","namespace","SAMPLES","empty string")
  SET ^%SYS("sql","sys","namespace","SAMPLES","empty string")=$CHAR(0)
  WRITE !,"Empty string set to:"
  ZZDUMP ^%SYS("sql","sys","namespace","SAMPLES","empty string")
  SET ^%SYS("sql","sys","namespace","SAMPLES","empty string")=""
  WRITE !,"Empty string reset to:"
  ZZDUMP ^%SYS("sql","sys","namespace","SAMPLES","empty string")
  WRITE !,!,"End of sample program"
 
NULL
In TSQL a NULL supplied to a boolean operation returns as FALSE, as shown in the following example:
DECLARE @var BINARY(1)
SELECT @var=NULL
IF @var PRINT "true" ELSE PRINT "false"
In Sybase dialect, NULL is equal to NULL. A NULL=NULL comparison returns TRUE, and a NULL != NULL comparison returns FALSE.
In MSSQL dialect, a comparison of NULL with any value returns FALSE. Thus NULL=NULL and NULL != NULL comparisons both return FALSE.
DECLARE @var BINARY(1)
SELECT @var=NULL
IF @var=NULL PRINT "true" ELSE PRINT "false"
In Sybase dialect, NULL is not equal to any value. Therefore, Not Equals (!=) comparison involving NULL and any boolean, numeric, or string value (including the empty string ("")) returns TRUE. All Equals (=), Greater Than (>) or Less Than (<) comparisons return FALSE.
In MSSQL dialect, NULL cannot be compared to a value. Thus all Equals (=), Not Equals (!=), Greater Than (>) or Less Than (<) comparisons return FALSE.
In a TSQL string concatenation operation, NULL is equivalent to an empty string. In a TSQL arithmetic operation, NULL is equivalent to 0.
Hexadecimal
Caché TSQL automatically converts hexadecimal numeric literals in TSQL source code to the corresponding decimal (base-10) numeric literals.
Reserved Words
Caché TSQL cannot use as identifiers the SQL Server reserved words. Caché TSQL can use Caché SQL reserved words (that are not also SQL Server reserved words) if the QUOTED_IDENTIFIER SQL configuration setting is set to Yes.
Comments, Blank Lines, and Semicolons
Caché TSQL supports both single-line and multi-line comments.
PRINT 'these are comments'
-- this is a single-line comment
// this is a single-line comment
/* This is a multi-line comment
The command
PRINT 'do not print'
is part of the comment and is not executed */
TSQL-only Statements
Caché TSQL provides the means to include executable statements within Caché TSQL code which are parsed as nonexecutable comments in Transact-SQL. A statement prefixed with two hyphens and a vertical bar is parsed by Caché as an executable statement. Sybase Adaptive Server and Microsoft SQL Server consider this to be a Transact-SQL comment.
PRINT 'any context'
-- PRINT 'commented out'
--| PRINT 'InterSystems only'
Semicolons
You can specify a blank line by using either two hyphens or a semicolon.
A semicolon either before or after a TSQL statement is ignored. They are supported for compatibility with Transact-SQL code, such as stored procedures, that ends statements with a semicolon.
PRINT 'no semicolon'
--
PRINT 'trailing semicolon';
;
;PRINT 'leading semicolon'
Identifiers
An identifier is a name for a TSQL object, such as a table, column, view, key, index, trigger, or stored procedure. Naming conventions for identifiers are as follows:
Some identifiers are qualified with a schema name. For example, schema.tablename or schema.storedprocedure. If the schema name is omitted, the identifier is unqualified. TSQL resolves unqualified identifiers by using the schemaPath property, which provides a search path of schemas to check for the specified table name or stored procedure name.
Delimited and Quoted Identifiers
A delimited identifier is not restricted by the naming conventions of ordinary identifiers. For example, a delimited identifier can be the same word as an SQL reserved word; a delimited identifier can contain space characters.
By default, both square brackets and double quotation marks can be used to delimit an identifier. These delimiters are interchangeable; you can define a delimited identifier by enclosing it with square brackets, and invoke the same delimited identifier by specifying it enclosed with double quotation marks.
You can specify a quoted identifier if the QUOTED_IDENTIFIER SQL configuration setting is set to Yes. You specify a quoted identifier by enclosing it in double quotation marks. When QUOTED_IDENTIFIER is on, double quotes are parsed as delimiting an identifier. When QUOTED_IDENTIFIER is off, double quotes are parsed as alternative delimiters for string literals. The preferable delimiters for string literals are single quotes. A quoted identifier can contain any characters, including blank spaces.
Data Types
The following data types are supported for local variables and table columns. These data types are supported in that they are parsed as valid data types; however, no range or value validation is performed.
BINARY(n) and VARBINARY(n). The (n) size specification is mandatory.
BIT
BOOLEAN
CHAR and VARCHAR
CHAR(n), NCHAR(n), VARCHAR(n), and NVARCHAR(n)
VARCHAR(MAX), and NVARCHAR(MAX). By default, these map to %Stream.GlobalCharacter.
DATETIME and SMALLDATETIME
DECIMAL, DECIMAL(p), and DECIMAL(p,s). Where p and s are integers specifying precision (total digits) and scale (decimal digits).
DOUBLE and DOUBLE PRECISION
FLOAT and FLOAT(n)
INT, BIGINT, SMALLINT, and TINYINT
MONEY and SMALLMONEY
NATIONAL
NUMERIC, NUMERIC(p), and NUMERIC(p,s). Where p and s are integers specifying precision (total digits) and scale (decimal digits).
REAL
TIMESTAMP
Note:
The Microsoft SQL Server TIMESTAMP data type is not used for date or time information. It is an integer counter of the number of times a record is inserted or updated in a table. It should not be confused with the Caché SQL and ODBC TIMESTAMP data type, which represents a date and time in YYYY-MM-DD HH:MM:SS.nnnnnnnnn format. In TSQL, use DATETIME and SMALLDATETIME for date and time values.
ROWVERSION
SQL_VARIANT
The following SQL Server data types are supported in a specific context:
CURSOR
NTEXT, TEXT By default, these map to %Stream.GlobalCharacter.
IMAGE
TABLE
The following are not implemented:
Operators
Arithmetic and Equality Operators
Caché TSQL supports + (addition), – (subtraction), * multiplication, / division, and % modulo arithmetic operators.
Caché TSQL supports the following equality and comparison operators:
When performing equality comparisons (= or <>) between date values with different data types, all date and time values are compared using the TIMESTAMP data type. Thus two dates in different formats can be meaningfully compared. A date value declared as a STRING data type can be compared to a date value declared as a DATETIME data type.
Concatenation Operator
Caché TSQL supports the + (plus sign) as both a concatenation operator and the addition operator. The plus sign functions as a concatenation operator with strings. You can concatenate several strings together using this operator. If all item are strings, TSQL performs concatenation; however, if one of the items is a number, TSQL performs addition, treating non-numeric strings as 0.
'world'+'wide'+'web' concatenates to 'worldwideweb'
'world'+'33'+'web' concatenates to 'world33web'
'world'+33+'web' performs addition (0+33+0=33)
In a TSQL string concatenation operation, NULL is equivalent to an empty string. In a TSQL arithmetic operation, NULL is equivalent to 0. Note that because the plus sign (+) is used for both concatenation and addition, the data type declaration of the NULL variable is critical. The following examples all return “bigdeal”:
DECLARE @var1 BINARY(1)
DECLARE @var2 VARCHAR(10)
SELECT @var1=NULL,@var2=NULL
PRINT "big"+NULL+"deal"
PRINT "big"+@var1+"deal"
PRINT "big"+@var2+"deal"
The following example returns 0; it treats the + as an arithmetic operator and interprets the argument as 0 + 0 + 0 = 0:
DECLARE @var1 INT
SELECT @var1=NULL
PRINT "big"+@var1+"deal"
Caché TSQL also supports || as a concatenation operator.
Comparison Operators
BETWEEN
Caché TSQL supports the BETWEEN range check operator of the form: BETWEEN num1 AND num2. BETWEEN is inclusive of the specified range limits.
IS NULL
Caché TSQL supports the IS NULL match operator. A variable is NULL if it has been declared but not assigned a value, or if it has been explicitly specified as NULL. The empty string is not NULL.
LIKE
Caché TSQL supports the LIKE pattern match operator. LIKE performs not case-sensitive matching of letters. Caché TSQL also supports NOT LIKE.
NOT Logical Operator
The NOT logical operator inverts the truth value of the statement that follows it. For example, IF NOT EXISTS(...). NOT is not case-sensitive.
Bitwise Logical Operators
Caché TSQL supports the AND (&), OR (|), XOR (^), and NOT (~) bitwise operators for the integer data type. The decimal integers are converted to binary, the logical operation is performed, and the resulting binary is converted to a decimal integer value. The NOT (~) operator is a unary operator that inverts bits.