Developing Portable Applications : B. Function Mapping : Mapping OpenSQL Functions to Host Functions : Microsoft SQL Server Gateway
 
Share this page                  
Microsoft SQL Server Gateway
The following table contains a list of the OpenSQL functions and the support level for the Microsoft SQL Server gateway:
Function Name
Support
Comments
_DATE
RESTRICT
Parameter must be a literal.
_DATE4
RESTRICT
Parameter must be a literal.
_TIME
RESTRICT
Parameter must be a literal.
ABS
COMPAT
None
ATAN
COMPAT
None
AVG
COMPAT
None
BIGINT
RESTRICT
None
BIT_ADD
NO
None
BIT_AND
NO
None
BIT_NOT
NO
None
BIT_OR
NO
None
BIT_XOR
NO
None
BYTE
NO
None
C
NO
None
CHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
CHAREXTRACT
TRANS
None
CONCAT
TRANS
None
CORR
NO
None
COS
COMPAT
None
COUNT
TRANS
None
COVAR_POP
NO
None
COVAR_SAMP
NO
None
DATE
TRANS
None
DATE_GMT
RESTRICT
Parameter must be a literal. Return value contains both date and time.
DATE_PART
RESTRICT
Units must be a literal. The unit 'week' is not supported. If the date is not in a host-acceptable format, it must be inside a DATE typecast.
DATE_TRUNC
RESTRICT
Only supported units are: 'day', 'month', and 'year'.
DBMSINFO
RESTRICT
Parameter must be a literal. May only be used as the inner most scalar(). Nesting dbmsinfo() is not supported. Valid request_name values are:
_bintim
_bio_cnt
_cpu_ms
_et_sec
_version
autocommit_state
charset
database
dba
query_language
server_class
terminal
transaction_state
username
DECIMAL
RESTRICT
If the scale of the converted number is larger than the third parameter (optional output scale), Microsoft SQL rounds the number based on the scale, while Ingres truncates the number to fit the scale.
DOW
TRANS
None
EXP
COMPAT
None
FLOAT4
TRANS
None
FLOAT8
TRANS
None
GMT_TIMESTAMP
RESTRICT
Parameter must be a literal.
HEX
RESTRICT
The number of bytes processed by the hexadecimal function is dependent on the HEX_LEN setting in CBF. The default value for HEX_LEN is 30.
IFNULL
TRANS
None
INT1
RESTRICT
Microsoft SQL does not issue an error if the parameter is greater than 127 and less than 256. Values greater than 127 are returned as negative numbers (in twos complement). Parameter value should be between 0 and 127 (to stay in range of both OpenSQL int1 type and mssql TINYINT type).
INT2
TRANS
None
INT4
RESTRICT
None
INT8
RESTRICT
None
INTERVAL
NO
None
INTEXTRACT
RESTRICT
Results may not match Ingres if the second parameter is less than 1 or greater than the length of the first parameter. The first parameter cannot be a literal NULL, but can be an expression that yields NULL.
LEFT
COMPAT
None
LENGTH
TRANS
Cannot be used on image (long byte) or text (long varchar) parameters.
LN
TRANS
None
LOCATE
RESTRICT
Returns 0 when the search character is not found. Ingres returns string length + 1.
LOG
COMPAT
None
LONG_BYTE
RESTRICT
Microsoft SQL Server ignores the optional length parameter.
LONG_VARCHAR
RESTRICT
Parameter must be a string.
LOWER
COMPAT
None
LOWERCASE
TRANS
None
MAX
COMPAT
None
MIN
COMPAT
None
MOD
TRANS
None
MONEY
RESTRICT
Translation is RESTRICT when money mapping is set to FLOAT. Translation is COMPAT when money mapping is set to MONEY. For more information, see the section Gateway Catalog Setup in the Administrator Guide.
NCHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
NOTRIM
NO
None
NVARCHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.
OBJECT_KEY
NO
None
PAD
NO
None
POWER
RESTRICT
Result data type is the same as the first argument. Use a non-integer number in the first argument to avoid the result overflow for a large power.
RANDOM
TRANS
None
RANDOM(L,H)
RESTRICT
The first parameter must be a literal.
RANDOMF
TRANS
None
RANDOMF(L,H)
RESTRICT
The first parameter must be a literal. It always returns float, whereas the Ingres return type varies depending on the type of the input parameters.
REGR_AVGX
NO
None
REGR_AVGY
NO
None
REGR_COUNT
NO
None
REGR_INTERCEPT
NO
None
REGR_R2
NO
None
REGR_SLOPE
NO
None
REGR_SXX
NO
None
REGR_SXY
NO
None
REGR_SYY
NO
None
RIGHT
COMPAT
None
SHIFT
RESTRICT
The first parameter cannot be a host variable. The second parameter must be a literal. Results will not match Ingres when LEN(PARM1) + PARM2 exceed the original storage length of PARM1.
SIN
COMPAT
None
SIZE
NO
None
SOUNDEX
COMPAT
None
SQRT
COMPAT
None
SQUEEZE
RESTRICT
The result string length is dependent on the SQUEEZE_LEN setting in CBF. The default value for SQUEEZE_LEN is 30.
STDDEV_POP
TRANS
None
STDDEV_SAMP
TRANS
None
SUBSTRING
RESTRICT
The third parameter, which is optional in Ingres, is mandatory in Microsoft SQL.
SUM
COMPAT
None
TABLE_KEY
NO
None
TEXT
NO
None
TRIM
TRANS
None
UNHEX
NO
None
UPPER
COMPAT
None
UPPERCASE
TRANS
None
VAR_POP
TRANS
None
VAR_SAMP
TRANS
None
VARBYTE
NO
None
VARCHAR
RESTRICT
If PARM2 is omitted, typecast_strlen is used instead, which is configured in CBF. For more information on typecast_strlen, see the Administrator Guide.