*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
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
.
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 ENDPlease 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] |