Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Back to Tip 78] [Forward to Tip 80] [Archives]

Breck Carter
Last modified: October 16, 1998
mail to: bcarter@bcarter.com

Tip 79: From SQL Anywhere To SQL Server

Quick Reference Notes - Converting From Sybase SQL Anywhere 5.5 To Sybase Adaptive Server Enterprise 11.5

Changes since June 11 are marked . Introduction

This is a work in progress. It is not a "how to", just a "where to look" or "idea list". It is also NOT exhaustive.

Tip: ALWAYS LOOK THINGS UP... there are subtle differences as well as the obvious ones.

Tip: Before actually running your code on ASE 11.5, try converting it to "TRANSACT SQL" and testing it with SA 5.5. This is especially helpful if you want to run the same code in both environments.

Tip: Some valid ASE 11.5 statements won't work in the PowerBuilder Database Administration Painter. Before giving up, try running the statement via ISQL. If it works there, it will probably also work via PowerBuilder's DataWindow or EXECUTE IMMEDIATE commands.

Tip: Watch out for the ASE 11.5 TIMESTAMP datatype... it has nothing to do with dates or times. It is intended for concurrency control and the detection of changes.

Tip: SA 5.5's CHAR datatype is varying length but some front-end tools (e.g., JConnect) treat it as fixed length.

If you did this            ...try changing it to
in SA 5.5...                  this in ASE 11.5
Comments
128                           30
The maximum length of identifiers (table names, column names, etc.) is only 30 in ASE 11.5. [Top]
;                             go
Use the "go" separator in SQL Advantage scripts, or just get rid of the semocolons in stored procedures, etc. [Top]
% comment                     -- comment
                              /* comment */
-- and /* */ also work in SA 5.5. However, -- does not work in the PowerBuilder 5 Database Administration painter. [Top]
// comment                    -- comment
                              /* comment */
-- and /* */ also work in SA 5.5.
However, -- does not work in the PowerBuilder 5 Database Administration painter. [Top]
'\x01'                        char ( hextoint
                                 ( '0x01' ) )
char() and hextoint() also work in SA 5.5. [Top]
||                            +
String concatenation via + also works in SA 5.5. [Top]
+ ''                          + ltrim ( '' )
This may be sometimes necessary if '' results in a single space. [Top]
''                            ' '
According to the ASE 11.5 documentation "Inserting an empty string into a variable character type or text column inserts a single space." If this is what's happening to you, don't mislead maintenance programmers by coding ''. Use a single space ' ' instead. [Top]
<>                            !=
Both "not equal" operators can be used in SA 5.5 but only != works in ASE 11.5. [Top]
CHAR ( 1 to 255 )             VARCHAR ( 1 to 255 )
CHAR is truly fixed-length in ASE 11.5. [Top]
CHAR ( over 255 )             TEXT
There are difficulties mixing strings and TEXT values in expressions. Also, the results of string operations are truncated at 255 characters. [Top]
DATE datatype                 DATETIME datatype
There are no DATE or TIME datatypes in ASE 11.5. [Top]
DateFormat                    convert
   ( CURRENT DATE,               ( char ( 26 ),
     'Mmmmmmmmm Dd, yyyy' )        getdate(),
                                   9 )
Different calls to convert() give different results. "Format 9" gives the most detail, 26 characters wide. [Top]
DECLARE x CHAR(1)             DECLARE @x CHAR(1)
[Top]
INSERT INTO tbl               INSERT INTO tbl
   VALUES                        ( column1,
      ( value1,                    column3 ) 
        DEFAULT,                 VALUES
        value3 )                    ( value1, 
                                      value3 )
The DEFAULT keyword cannot be used in the INSERT...VALUES list in ASE 11.5 as a substitute for a missing column value. [Top]
DEFAULT AUTOINCREMENT         IDENTITY
IDENTITY also works with SA 5.5. However, in ASE 11.5 there can be only one IDENTITY column per table, it must be NUMERIC ( p, 0 ), it is not updateable and it cannot hold NULL values.
Do NOT use DEFAULT @@IDENTITY on a non-IDENTITY column. You may want to do this to copy freshly-assigned identity values from an IDENTITY column to a non-IDENTITY column, but this won't work if a single INSERT statement inserts more than one row. [Top]
DEFAULT CURRENT DATE          DEFAULT getdate()
ASE 11.5 only has the DATETIME datatype, not DATE or TIME. [Top]
DEFAULT CURRENT PUBLISHER     n/a
suser_name() will give the current user. [Top]
DEFAULT CURRENT TIME          DEFAULT getdate()
ASE 11.5 only has the DATETIME datatype, not DATE or TIME. [Top]
DEFAULT CURRENT TIMESTAMP     DEFAULT getdate()
getdate() also works in SA 5.5. [Top]
DEFAULT CURRENT USER          DEFAULT suser_name()
suser_name() also works in SA 5.5. [Top]
DEFAULT LAST USER             n/a
DEFAULT suser_name() will at least initialize the column. [Top]
DEFAULT TIMESTAMP             n/a
DEFAULT getdate() will at least initialize the column. [Top]
FROM dummy                    Omit FROM clause
The FROM clause may also be omitted in SA 5.5 (PowerBuilder sometimes requires a FROM clause, though). [Top]
FROM <fancy JOINs and ONs>    n/a
ASE 11.5 expects a simple list of tables in the FROM clause; sorry, no JOIN operators or ON clauses. [Top]
FROM ... LEFT OUTER JOIN      WHERE ... *=
*= may be hugely awkward and limiting but it also works in SA 5.5. [Top]
FROM ... RIGHT OUTER JOIN     WHERE ... =*
=* also works in SA 5.5. [Top]
IF a = b                      CASE a
   THEN c                        WHEN b THEN c
   ELSE d                        ELSE d
ENDIF                         END
Please note: This discussion applies to
the IF expression which does not exist in ASE 11.5.
The IF statement does exist, and works just fine.
This CASE expression also works in SA 5.5. The problem with ASE 11.5 is there is no IF expression, and the CASE expression does not support comparison operators like >= or EXISTS. [Top]
IF a > b                      CASE sign ( a - b )
   THEN c                        WHEN 1 THEN c
   ELSE d                        ELSE d
ENDIF                         END
This CASE expression is convoluted, but it also works in SA 5.5.
The sign() function works on numeric arguments, and is helpful because it returns only 3 values: +1 for positive values, 0 for zero and -1 for negative. With CASE expressions, that's what want: just a few known values you can put in WHEN clauses. [Top]
IF a >= b                     CASE sign ( a - b )
   THEN c                        WHEN 1 THEN c
   ELSE d                        WHEN 0 THEN c
ENDIF                            ELSE d
                              END
This CASE expression is a bit more convoluted, but it also works in SA 5.5. [Top]
IF a >= b                     CASE sign ( sign
   THEN c                            ( a - b ) + 1 )
   ELSE d                        WHEN 1 THEN c
ENDIF                            ELSE d
                              END
This CASE expression is horrendously convoluted, but it also works in SA 5.5.
The original SA 5.5 code is the same for this example and the previous one, but the ASE 11.5 code is different: it doesn't repeat the "THEN c". This is helpful if "c" is in fact a big complicated nested expression such as a subselect. [Top]
IF EXISTS                     CASE sign
   ( SELECT *                    ( ( SELECT count(*)
       FROM ...                        FROM ...
      WHERE ... )                     WHERE ... ) )
   THEN a                        WHEN 1 THEN a
   ELSE b                        ELSE b
ENDIF                         END
This CASE expression is convoluted AND (probably) inefficient, but it also works in SA 5.5. [Top]
IF a THEN                     IF a
   xxx                           xxx
ELSEIF b THEN                 ELSE IF b
   yyy                           yyy
ELSE                          ELSE
   zzz                           zzz
END IF
Drop the THEN and END IF keywords, and change the ELSEIF to ELSE IF. [Top]
ifnull ( a, b, a )            isnull ( a, b )
This simple form of ifnull() can be replaced with an even simpler call to isnull() which works in both SA 5.5 and ASE 11.5: "If a is NULL then return b, else return a." [Top]
INSERT INTO TBL               INSERT INTO TBL
   ( VARCHAR_COL )               ( VARCHAR_COL )
   VALUES ( '' )                 VALUES ( '' )

                              - is the same as -

                                 VALUES ( ' ' )
You can insert an empty or zero-length string into a varchar column but what you actually get in the database is a single space. In other words, char_length ( varchar_col ) will return 1 in ASE 11.5 whereas length ( varchar_col ) returned 0 in SA 5.5. [Top]
INTEGER DEFAULT               NUMERIC ( 10, 0 )
   AUTOINCREMENT                 IDENTITY
The IDENTITY keyword also works in SA 5.5 but is implemented as DEFAULT AUTOINCREMENT. [Top]
lcase ( s )                   lower ( s )
lower() also works in SA 5.5. [Top]
left ( s, 5 )                 substring ( s, 1, 5 )
substring() also works in SA 5.5. [Top]
length ( s )                  char_length ( s )
char_length() does NOT work in SA 5.5. [Top]
locate ( s, find_this )       charindex ( find_this, s )
[Top]
LONG BINARY datatype          IMAGE datatype
IMAGE also works in SA 5.5. [Top]
LONG VARCHAR datatype         TEXT datatype
TEXT also works in SA 5.5. [Top]
ltrim ( x )                   isnull ( ltrim
                                 ( x ), '' )
If x is filled with blanks, ltrim() will return '' (zero-length string) in SA 5.5. This behaviour makes some string handling functions easy to code.

Unfortunately, ltrim ( ' ' ) returns NULL in ASE 11.5. This can cause havoc in many situations, including INSERT statements being used to fill NOT NULL columns. Calling isnull() helps, but please note that it returns ' ' (one space) even though '' (zero-length string) is specified. [Top]

mod ( 21, 11 )                21 % 11
% starts a comment in SA 5.5. [Top]
NULL IDENTITY                 n/a
In ASE 11.5, IDENTITY is an alternative to NULL/NOT NULL, and it implies NOT NULL. [Top]
NULL DEFAULT AUTOINCREMENT    n/a
IDENTITY may be used but it is implicitly NOT NULL. [Top]
ORDER BY s                    ORDER BY upper ( s )
See WHERE s = t. [Top]
repeat ( s, n )               replicate ( s, n )
replicate() also works in SA 5.5. [Top]
SET x = 'Hello';              SELECT @x = 'Hello'
Change SET to SELECT but don't get carried away: UPDATE ... SET doesn't change to UPDATE ... SELECT. [Top]
string ( i )                  ltrim ( str ( i, n ) )
str() does NOT work in SA 5.5. [Top]
substr ( s, 4 )               substring ( s, 4, 255 )
substring() also works in SA 5.5. Note that in ASE 11.5 the length parameter is required. [Top]
substr ( s, 4, 5 )            substring ( s, 4, 5 )
substring() also works in SA 5.5. [Top]
substr ( 'xxx', 4, 1 )        isnull ( substring
                                 ( 'xxx', 4, 1 ),
                                   '' )
This is a simple example of an out-of-bounds substring call which returns '' (zero-length string) in SA 5.5. This behaviour makes some string handling operations easy to code.

Unfortunately, substring ( 'xxx', 4, 1 ) returns NULL in ASE 11.5. This can cause havoc in many situations, including INSERT statements being used to fill NOT NULL columns. Calling isnull() helps, but please note that it returns ' ' (one space) even though '' (zero-length string) is specified. [Top]

TIME datatype                 DATETIME datatype
There are no DATE or TIME datatypes in ASE 11.5. [Top]
TIMESTAMP datatype            DATETIME datatype
There are no DATE or TIME datatypes in ASE 11.5. Watch out for the ASE 11.5 TIMESTAMP datatype: it has nothing to do with dates or times, and is intended for concurrency control and the detection of changes. [Top]
trim ( s )                    ltrim ( rtrim ( s ) )
ltrim() and rtrim() also work in SA 5.5. [Top]
ucase ( s )                   upper ( s )
upper() also works in SA 5.5. [Top]
VARCHAR ( over 255 )          TEXT
There are difficulties mixing strings and TEXT values in expressions. Also, the results of string operations are truncated at 255 characters. [Top]
WHERE s = t                   WHERE upper ( s )
                                  = upper ( t )

  Strings Compare Equal?    SA 5.5  ASE 11.5
  ======================    ======  ========
     "Ab" = "Ab"              YES     YES
     "Ab" = "ab"              YES      no
     "Ab" = "AB"              YES      no
     "Ab" = "Ab "              no     YES
     "Ab" = "ab "              no      no
     "Ab" = "AB "              no      no
     "Ab" = " Ab "             no      no
     "Ab" = " ab "             no      no
     "Ab" = " AB "             no      no
     "Ab" = " Ab"              no      no
     "Ab" = " ab"              no      no
     "Ab" = " AB"              no      no

  upper ( ltrim ( "Ab" ) )
= upper ( ltrim ( " ab " ) )   no     YES

  upper ( trim ( "Ab" ) )
= upper ( trim ( " ab " ) )   YES    (n/a)

  upper ( ltrim
    ( rtrim ( "Ab" ) ) )
= upper ( ltrim
    ( rtrim ( " ab " ) ) )    YES     YES
String comparisons and sort orders depend on case sensitivity. SA 5.5 is case insensitive by default whereas ASE 11.5 is case sensitive.

Many Wise Elders have said that changing the case sensitivity in either environment is not a good idea. In fact, the Voodoo Gods have a saying,

"DBA who change default sort order had better stock up on dead chickens to wave over keyboard when things go wrong."
String comparisons also depend on how trailing blanks are handled. ASE 11.5 ignores trailing blanks for comparison purposes. By default SA 5.5 does NOT ignore trailing blanks.

When switching from SA 5.5 to ASE 11.5 the safest approach is to look at every single string comparison and ORDER BY clause to see if it's necessary to call upper(), ltrim() and/or rtrim(). [Top]

WHERE tbl.long_varchar_col    WHERE textvalid
   IS NOT NULL                   ( "tbl.text_col",
                                   textptr
                                      ( tbl.text_col ) )
                                 = 1
Text columns can't be tested for IS [NOT] NULL in ASE 11.5. Instead, you have to call textptr() to get a pointer to the data page, and then textvalid() to see if the textptr() call worked. [Top]
WHILE xxx LOOP                WHILE xxx BEGIN
   yyy                           yyy
   zzz                           zzz
END LOOP                      END
Change the LOOP to BEGIN, and the END LOOP to END. [Top]

[Home] [Back to Tip 78] [Forward to Tip 80] [Archives] [mail to: bcarter@bcarter.com]