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 87] [Forward to Tip 89] [Archives]
Breck Carter
Last modified: September 29, 1999
mail to: bcarter@bcarter.com



Tip 88: Sybase Adaptive Server Anywhere
Default Values

Adaptive Server Anywhere DEFAULT values are a lot more powerful than many people realize. Not only can you specify ordinary literal values like 1 and 'Hello', and special constants like CURRENT DATE, but you can use global variables like @@spid and even expressions like dateadd ( day, 15, getdate() ).

Not only that, but there are two special values that aren't just defaults: DEFAULT TIMESTAMP and DEFAULT LAST USER columns are automatically filled in whenever the row is updated as well as inserted, with the current date/time and user id respectively.

Here is an ISQL script which demonstrates the different kind of DEFAULT values: 

// Connect as "dba" and insert the 1st row.
CONNECT DBA IDENTIFIED BY SQL;
DROP TABLE auto;
CREATE TABLE auto (
  autoincrement_key     INTEGER         NOT NULL DEFAULT AUTOINCREMENT,
  null_string           VARCHAR ( 10 )  NULL     DEFAULT NULL,
  simple_string         VARCHAR ( 10 )  NOT NULL DEFAULT 'Hello',
  simple_number         INTEGER         NOT NULL DEFAULT 1,
  inserted_by_publisher VARCHAR ( 128 ) NULL     DEFAULT CURRENT PUBLISHER,
  inserted_by_remote    VARCHAR ( 128 ) NULL     DEFAULT CURRENT REMOTE USER,
  inserted_by_user      VARCHAR ( 128 ) NOT NULL DEFAULT CURRENT USER,
  inserted_by_user2     VARCHAR ( 128 ) NOT NULL DEFAULT USER,
  inserted_on_date      DATE            NOT NULL DEFAULT CURRENT DATE,
  inserted_at_time      TIME            NOT NULL DEFAULT CURRENT TIME,
  inserted_at_date_time TIMESTAMP       NOT NULL DEFAULT CURRENT TIMESTAMP,
  updated_by_user       VARCHAR ( 128 ) NOT NULL DEFAULT LAST USER,
  updated_at_date_time  TIMESTAMP       NOT NULL DEFAULT TIMESTAMP,
  complex_date_time     TIMESTAMP       NOT NULL DEFAULT (dateadd(day,15,getdate())),
  version_string        VARCHAR ( 128 ) NOT NULL DEFAULT @@version,
  servername_string     VARCHAR ( 128 ) NOT NULL DEFAULT @@servername,
  spid_number           INTEGER         NOT NULL DEFAULT @@spid,
  procid_number         INTEGER         NOT NULL DEFAULT @@procid,
  db_name_string        VARCHAR ( 128 ) NOT NULL DEFAULT DB_NAME(),
  user_id_number        INTEGER         NOT NULL DEFAULT User_id(),
  user_name_string      VARCHAR ( 128 ) NOT NULL DEFAULT User_name(),
  suser_id_number       INTEGER         NOT NULL DEFAULT Suser_id(),
  suser_name_string     VARCHAR ( 128 ) NOT NULL DEFAULT Suser_name(),
  getdate_date_time     TIMESTAMP       NOT NULL DEFAULT getdate(),
  ordinary              INTEGER         NOT NULL,
  PRIMARY KEY ( autoincrement_key ) );
INSERT INTO auto ( ordinary ) VALUES ( 1 );

// Define "abc" as the publisher. 
REVOKE GROUP FROM dba;
GRANT GROUP TO dba;
REVOKE CONNECT FROM abc;
GRANT CONNECT TO abc IDENTIFIED BY abc;
GRANT PUBLISH TO abc;

// Connect as "xyz" and insert the 2nd row.
REVOKE CONNECT FROM xyz;
GRANT CONNECT TO xyz IDENTIFIED BY xyz;
GRANT DBA TO xyz;
GRANT MEMBERSHIP IN GROUP DBA TO xyz;
CONNECT xyz;
INSERT INTO auto ( ordinary ) VALUES ( 1 );

// Connect as "pqr" and update the 2nd row.
REVOKE CONNECT FROM pqr;
GRANT CONNECT TO pqr IDENTIFIED BY pqr;
GRANT DBA TO pqr;
GRANT MEMBERSHIP IN GROUP DBA TO pqr;
CONNECT pqr;
UPDATE auto 
   SET ordinary = ordinary + 1
 WHERE autoincrement_key = 2;

1. DEFAULT AUTOINCREMENT

DEFAULT ValueRow 1Row 2
AUTOINCREMENT
 
1
 
2

Autoincrement is cool: it works, it's reliable, it's well-described in the Help, and it only has two minor quirks. First of all, for performance reasons every autoincrement column should be the first column in a primary key or unique index. And second, if you insert, delete and then re-insert rows, gaps will appear in the numbering scheme... unless you shut down and restart the engine between the delete and re-insert.

2. DEFAULT NULL

DEFAULT ValueRow 1Row 2
NULL
 
NULL
 
NULL

The NULL default is the default; it's what you get if don't specify DEFAULT at all.

3. DEFAULT 'Hello'

DEFAULT ValueRow 1Row 2
'Hello'
 
'Hello'
 
'Hello'

A string literal: one of the simplest default values. Beware of front-end tools that don't understand special constants like LAST USER, however; sometimes they put your value inside quotes. For example, if you want DEFAULT LAST USER but you get DEFAULT 'LAST USER', that's wrong. 'LAST USER' a string literal, not a special constant.

4. DEFAULT 1

DEFAULT ValueRow 1Row 2
1
 
1
 
1

A numeric literal: the other simple kind of default.

5. DEFAULT CURRENT PUBLISHER

DEFAULT ValueRow 1Row 2
CURRENT PUBLISHER
 
'abc'
 
'abc'

If you use publish-subscribe replication, one single user id is identified as the "publisher" of each database. This user id is unique across all databases that participate in the replication scheme and can be used as a "database identifier".

Together with DEFAULT AUTOINCREMENT you can use DEFAULT CURRENT PUBLISHER to define a two-column primary key that is unique across all databases that are being replicated.

6. DEFAULT CURRENT REMOTE USER

DEFAULT ValueRow 1Row 2
CURRENT REMOTE USER
 
NULL
 
NULL

The special constant DEFAULT CURRENT REMOTE USER is always NULL except:

  • inside a RESOLVE UPDATE replication conflict trigger fired by DBREMOTE.EXE on the consolidated database in a publish-subscribe replication setup, and

  • inside a SET OPTION REPLICATION_ERROR stored procedure when, for example, there is a primary key error.

As a DEFAULT value CURRENT REMOTE USER is valuable when you're keeping a diagnostic trace in a table you've created.

7. DEFAULT CURRENT USER

DEFAULT ValueRow 1Row 2
CURRENT USER
 
'dba'
 
'xyz'

You can use CURRENT USER to set up column which answers the question, "Who inserted this row?"

8. DEFAULT USER

DEFAULT ValueRow 1Row 2
USER
 
'dba'
 
'xyz'

This is the same as DEFAULT CURRENT USER.

9. DEFAULT CURRENT DATE

DEFAULT ValueRow 1Row 2
CURRENT DATE
 
'1999-05-02'
 
'1999-05-02'

DEFAULT CURRENT DATE answers the question, "When was this row inserted?"

10. DEFAULT CURRENT TIME

DEFAULT ValueRow 1Row 2
CURRENT TIME
 
'11:10:26.880'
 
'11:10:27.320'

DEFAULT CURRENT TIME also answers the question, "When was this row inserted?" but gives the time instead of the date.

11. DEFAULT CURRENT TIMESTAMP

DEFAULT ValueRow 1Row 2
CURRENT TIMESTAMP
 
'1999-05-02 11:10:26.880'
 
'1999-05-02 11:10:27.320'

DEFAULT CURRENT TIMESTAMP gives both date and time: "When was this row inserted?"

12. DEFAULT LAST USER

DEFAULT ValueRow 1Row 2
LAST USER
 
'dba'
 
'pqr'

DEFAULT LAST USER isn't a just a default, it gets filled in by UPDATE operations as well as INSERTs. In other words it answers the question, "Who inserted or last updated this row?" and can be used instead of a trigger to maintain a last_updated_by_user_id column.

13. DEFAULT TIMESTAMP

DEFAULT ValueRow 1Row 2
TIMESTAMP
 
'1999-05-02 11:10:26.880'
 
'1999-05-02 11:10:27.700'

DEFAULT TIMESTAMP is the other more-than-just-a-default value; it answers the question, "When was this row inserted or last updated?" It can be used instead of a trigger to maintain a last_updated_on_datetime column.

14. DEFAULT (dateadd(day,15,getdate()))

DEFAULT ValueRow 1Row 2
(dateadd(day,15,getdate()))
 
'1999-05-17 11:10:26.880'
 
'1999-05-17 11:10:27.320'

You can use expressions as DEFAULT values. If they are fancy expressions, you have to enclose them in (brackets) like this one, which initializes a date column to a date that is 15 days in the future.

If the expression is TOO fancy, it won't work, and you'll get a cryptic error message like "syntax error near ','". That's because you can't use expressions involving database objects. And that means you can't use CREATE FUNCTION to set up really cool DEFAULT values... sigh.

15. DEFAULT @@version

DEFAULT ValueRow 1Row 2
@@version
 
'6.0.1.1165'
 
'6.0.1.1165'

@@version is a global variable, and it's OK as a DEFAULT value.

16. DEFAULT @@servername

DEFAULT ValueRow 1Row 2
@@servername
 
'asajava'
 
'asajava'

The @@servername global variable is also OK as a DEFAULT value. So is @@identity but BE CAREFUL; using DEFAULT @@identity in some situations (like before-row-insert triggers) may result in logical conundrums. Also note that INTEGER IDENTITY is the same as INTEGER DEFAULT AUTOINCREMENT but not INTEGER DEFAULT @@identity.

17. DEFAULT @@spid

DEFAULT ValueRow 1Row 2
@@spid
 
1537670226
 
1489255977

The @@spid global variable is cool: it uniquely identifies the connection.

18. DEFAULT procid

DEFAULT ValueRow 1Row 2
@@procid
 
0
 
0

@@procid is another global variable.

19. DEFAULT DB_NAME()

DEFAULT ValueRow 1Row 2
DB_NAME()
 
'asajava'
 
'asajava'

DB_NAME() is a system function that's OK to use as a DEFAULT value.

20. DEFAULT User_id()

DEFAULT ValueRow 1Row 2
User_id()
 
1
 
14

The User_id() system function gives the current user id in numeric form; i.e., the SYS.SYSUSERPERM.user_id value as opposed to the more useful SYS.SYSUSERPERM.user_name.

21. DEFAULT User_name()

DEFAULT ValueRow 1Row 2
User_name()
 
'dba'
 
'xyz'

The User_name() function is the same as CURRENT USER.

22. DEFAULT Suser_id()

DEFAULT ValueRow 1Row 2
Suser_id()
 
1
 
14

The Suser_id() system function is the same as User_id().

23. DEFAULT Suser_name()

DEFAULT ValueRow 1Row 2
Suser_name()
 
'dba'
 
'xyz'

The Suser_name() system function is the same as CURRENT USER.

24. DEFAULT getdate()

DEFAULT ValueRow 1Row 2
getdate()
 
'1999-05-02 11:10:26.880'
 
'1999-05-02 11:10:27.320'

The getdate() system function is the same as CURRENT TIMESTAMP.

[Home] [Back to Tip 87] [Forward to Tip 89] [Archives] [mail to: bcarter@bcarter.com]