The following section defines the compatibility of the CIS server classes with the built-in ASE functions.
When a SQL statement such as a select, insert, delete or update contains a built-in function, CIS has to determine whether or not the function can be forwarded to the remote server, or if it must be evaluated within the local server using remote data.
Functions are only sent to a remote server if the statement containing them can be handled by quickpass mode (see the select command).
In the tables shown below, support for function by server class is indicated by a ‘Y’; an ‘N’ indicates no support is provided, and ‘C’ indicates support for it is determined by capabilities of the underlying DBMS (often the case for DirectConnects).
The aggregate functions generate summary values that appear as new columns in the query results. The aggregate functions are:
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
avg |
Y |
Y |
Y |
Y |
C |
Y |
count |
Y |
Y |
Y |
Y |
C |
Y |
max |
Y |
Y |
Y |
Y |
C |
Y |
min |
Y |
Y |
Y |
Y |
C |
Y |
sum |
Y |
Y |
Y |
Y |
C |
Y |
Datatype conversion functions change expressions from one datatype to another and specify new display formats for date/time information. The datatype conversion functions are:
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
convert() |
Y |
Y |
Y |
Y |
C |
N |
inttohex() |
Y |
Y |
N |
Y |
C |
N |
hextoint() |
Y |
Y |
N |
Y |
C |
N |
The date functions manipulate values of the datatype datetime or smalldatetime. Note that the getdate() function is always expanded by the local server; the presence of this builtin function will not cause a query to be eliminated from quickpass mode optimizations, however.
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
dateadd |
Y |
Y |
Y |
Y |
C |
N |
datediff |
Y |
Y |
Y |
Y |
C |
N |
datename |
Y |
Y |
N |
Y |
C |
N |
datepart |
Y |
Y |
Y |
Y |
C |
N |
Mathematical functions return values commonly needed for operations on mathematical data. Mathematical function names are not keywords.
Each function also accepts arguments that can be implicitly converted to the specified type. For example, functions that accept approximate numeric types also accept integer types. Adaptive Server automatically converts the argument to the desired type
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
abs |
Y |
Y |
Y |
Y |
C |
N |
acos |
Y |
Y |
N |
Y |
C |
N |
asin |
Y |
Y |
N |
Y |
C |
N |
atan |
Y |
Y |
N |
Y |
C |
N |
atn2 |
Y |
Y |
N |
Y |
C |
N |
ceiling |
Y |
Y |
Y |
Y |
C |
N |
cos |
Y |
Y |
N |
Y |
C |
N |
cot |
Y |
Y |
N |
Y |
C |
N |
degrees |
Y |
Y |
N |
Y |
C |
N |
exp |
Y |
Y |
N |
Y |
C |
N |
floor |
Y |
Y |
Y |
Y |
C |
N |
log |
Y |
Y |
N |
Y |
C |
N |
log10 |
Y |
Y |
N |
Y |
C |
N |
pi |
Y |
Y |
N |
Y |
C |
N |
power |
Y |
Y |
N |
Y |
C |
N |
radians |
Y |
Y |
N |
Y |
C |
N |
rand |
Y |
Y |
Y |
Y |
C |
N |
round |
Y |
Y |
N |
Y |
C |
N |
sign |
Y |
Y |
N |
Y |
C |
N |
sin |
Y |
Y |
N |
Y |
C |
N |
sqrt |
Y |
Y |
Y |
Y |
C |
N |
tan |
Y |
Y |
N |
Y |
C |
N |
Security functions return security-related information.The security functions are:
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
ic_sec_service_on() |
N |
N |
N |
N |
N |
N |
show_sec_services) |
N |
N |
N |
N |
N |
N |
String function operate on binary data, character strings, and expressions. The string functions are:
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
ascii |
Y |
Y |
N |
Y |
C |
N |
char |
Y |
Y |
N |
Y |
C |
N |
charindex |
Y |
Y |
N |
Y |
C |
N |
char_lengt |
Y |
Y |
N |
Y |
C |
N |
difference |
Y |
Y |
Y |
Y |
C |
N |
lower |
Y |
Y |
Y |
Y |
C |
N |
ltrim |
Y |
Y |
Y |
Y |
C |
N |
patindex |
N |
N |
N |
N |
N |
N |
replicate |
Y |
Y |
N |
Y |
C |
N |
reverse |
Y |
N |
N |
Y |
Y |
N |
right |
Y |
Y |
Y |
Y |
C |
N |
rtrim |
Y |
Y |
Y |
Y |
C |
N |
soundex |
Y |
N |
Y |
Y |
C |
N |
space |
Y |
Y |
N |
Y |
C |
N |
str |
Y |
Y |
N |
Y |
C |
N |
stuff |
Y |
Y |
N |
Y |
C |
N |
substring |
Y |
Y |
Y |
Y |
C |
N |
upper |
Y |
Y |
Y |
Y |
C |
N |
System functions return special information from the database. The system functions are:
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
col_length |
Y |
Y |
N |
Y |
C |
N |
col_name |
Y |
Y |
N |
Y |
C |
N |
curunreservedpgs |
N |
N |
N |
N |
N |
N |
data_pgs |
N |
N |
N |
N |
N |
N |
datalength |
Y |
Y |
N |
Y |
C |
N |
db_id |
N |
N |
N |
N |
N |
N |
db_name |
N |
N |
N |
N |
N |
N |
host_id |
N |
N |
N |
N |
N |
N |
host_name |
N |
N |
N |
N |
N |
N |
index_col |
N |
N |
N |
N |
N |
N |
isnull |
Y |
Y |
N |
Y |
N |
N |
lct_admin |
N |
N |
N |
N |
N |
N |
mut_excl_roles |
N |
N |
N |
N |
N |
N |
object_id |
N |
N |
N |
N |
N |
N |
object_name |
N |
N |
N |
N |
N |
N |
proc_role |
N |
N |
N |
N |
N |
N |
ptn_data_pgs |
N |
N |
N |
N |
N |
N |
reserved_pgs |
N |
N |
N |
N |
N |
N |
role_contain |
N |
N |
N |
N |
N |
N |
role_id |
N |
N |
N |
N |
N |
N |
role_name |
N |
N |
N |
N |
N |
N |
rowcnt |
N |
N |
N |
N |
N |
N |
show_role |
N |
N |
N |
N |
N |
N |
suser_id |
N |
Y |
Y |
N |
N |
N |
suser_name |
N |
Y |
Y |
N |
N |
N |
tsequal |
Y |
Y |
N |
Y |
N |
N |
used_pgs |
N |
N |
N |
N |
N |
N |
user |
Y |
Y |
Y |
N |
N |
N |
user_id |
Y |
Y |
Y |
Y |
N |
N |
user_name |
Y |
Y |
Y |
Y |
N |
N |
valid_name |
N |
N |
N |
N |
N |
N |
valid_user |
N |
N |
N |
N |
N |
N |
Text and image functions operate on text and image data. The text and image functions are:
Function |
ASE |
ASA |
ASIQ |
sql_serv |
dir_con |
db2 |
|---|---|---|---|---|---|---|
textptr() |
Y |
Y |
N |
Y |
C |
N |
textvalid) |
Y |
Y |
N |
Y |
C |
N |
