SQL Anywhere® Server SQL Reference

1038 Pages·2006·5.7 MB·English

SQL Anywhere® Server
SQL Reference
Published: October 2006
Copyright and trademarks
Copyright © 2006 iAnywhere Solutions, Inc. Portions copyright © 2006 Sybase, Inc. All rights reserved.
iAnywhere Solutions, Inc. is a subsidiary of Sybase, Inc.
iAnywhere grants you permission to use this document for your own informational, educational, and other non-commercial purposes; provided
that (1) you include this and all other copyright and proprietary notices in the document in all copies; (2) you do not attempt to "pass-off" the
document as your own; and (3) you do not modify the document. You may not publish or distribute the document or any portion thereof without
the express prior written consent of iAnywhere.
This document is not a commitment on the part of iAnywhere to do or refrain from any activity, and iAnywhere may change the content of
this document at its sole discretion without notice. Except as otherwise provided in a written agreement between you and iAnywhere, this
document is provided “as is”, and iAnywhere assumes no liability for its use or any inaccuracies it may contain.
iAnywhere®, Sybase®, and the marks listed at http://www.ianywhere.com/trademarks are trademarks of Sybase, Inc. or its subsidiaries. ®
indicates registration in the United States of America.
Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.
All other company and product names mentioned may be trademarks of the respective companies with which they are associated.
Contents
About This Manual ...................................................................................... xi
SQL Anywhere documentation ................................................................................ xii
Documentation conventions .................................................................................... xv
Finding out more and providing feedback ............................................................. xix
I. Using SQL .................................................................................................. 1
SQL Language Elements ............................................................................................ 3
Keywords ............................................................................................................... 4
Identifiers ............................................................................................................... 7
Strings .................................................................................................................... 8
Constants ............................................................................................................... 9
Operators ............................................................................................................. 11
Expressions ......................................................................................................... 15
Search conditions ................................................................................................ 20
Special values ...................................................................................................... 30
Variables .............................................................................................................. 37
Comments ........................................................................................................... 43
NULL value .......................................................................................................... 44
SQL Data Types ......................................................................................................... 47
Character data types ........................................................................................... 48
Numeric data types .............................................................................................. 56
Money data types ................................................................................................ 65
Bit array data types .............................................................................................. 66
Date and time data types ..................................................................................... 68
Binary data types ................................................................................................. 75
Domains ............................................................................................................... 79
Data type conversions ......................................................................................... 81
Java and SQL data type conversion .................................................................... 87
SQL Functions ........................................................................................................... 91
Function types ..................................................................................................... 92
Alphabetical list of functions .............................................................................. 102
SQL Statements ....................................................................................................... 287
Copyright © 2006, iAnywhere Solutions, Inc. iii
SQL Anywhere® Server - SQL Reference
Using the SQL statement reference .................................................................. 293
ALLOCATE DESCRIPTOR statement [ESQL] .................................................. 297
ALTER DATABASE statement .......................................................................... 299
ALTER DBSPACE statement ............................................................................ 302
ALTER DOMAIN statement ............................................................................... 304
ALTER EVENT statement ................................................................................. 305
ALTER FUNCTION statement ........................................................................... 307
ALTER INDEX statement .................................................................................. 308
ALTER MATERIALIZED VIEW statement ......................................................... 310
ALTER PROCEDURE statement ...................................................................... 312
ALTER PUBLICATION statement [MobiLink] [SQL Remote] ............................ 314
ALTER REMOTE MESSAGE TYPE statement [SQL Remote] ......................... 316
ALTER SERVER statement ............................................................................... 318
ALTER SERVICE statement .............................................................................. 320
ALTER STATISTICS statement ......................................................................... 324
ALTER SYNCHRONIZATION SUBSCRIPTION statement [MobiLink] ............. 326
ALTER SYNCHRONIZATION USER statement [MobiLink] .............................. 328
ALTER TABLE statement .................................................................................. 330
ALTER TRIGGER statement ............................................................................. 339
ALTER VIEW statement .................................................................................... 340
ATTACH TRACING statement .......................................................................... 342
BACKUP statement ........................................................................................... 344
BEGIN statement ............................................................................................... 349
BEGIN TRANSACTION statement [T-SQL] ...................................................... 352
BREAK statement [T-SQL] ................................................................................ 354
CALL statement ................................................................................................. 355
CASE statement ................................................................................................ 357
CHECKPOINT statement .................................................................................. 359
CLEAR statement [Interactive SQL] .................................................................. 360
CLOSE statement [ESQL] [SP] ......................................................................... 361
COMMENT statement ....................................................................................... 363
COMMIT statement ........................................................................................... 365
CONFIGURE statement [Interactive SQL] ......................................................... 367
CONNECT statement [ESQL] [Interactive SQL] ................................................ 368
CONTINUE statement [T-SQL] .......................................................................... 371
iv Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Reference
CREATE DATABASE statement ....................................................................... 372
CREATE DBSPACE statement ......................................................................... 378
CREATE DECRYPTED FILE statement ............................................................ 380
CREATE DOMAIN statement ............................................................................ 382
CREATE ENCRYPTED FILE statement ............................................................ 384
CREATE EVENT statement .............................................................................. 387
CREATE EXISTING TABLE statement ............................................................. 392
CREATE EXTERNLOGIN statement ................................................................. 394
CREATE FUNCTION statement ........................................................................ 396
CREATE INDEX statement ............................................................................... 402
CREATE LOCAL TEMPORARY TABLE statement .......................................... 406
CREATE MATERIALIZED VIEW statement ...................................................... 408
CREATE MESSAGE statement [T-SQL] ........................................................... 410
CREATE PROCEDURE statement ................................................................... 412
CREATE PROCEDURE statement [T-SQL] ...................................................... 421
CREATE PUBLICATION statement [MobiLink] [SQL Remote] ......................... 423
CREATE REMOTE MESSAGE TYPE statement [SQL Remote] ...................... 427
CREATE SCHEMA statement ........................................................................... 429
CREATE SERVER statement ............................................................................ 431
CREATE SERVICE statement ........................................................................... 434
CREATE STATISTICS statement ...................................................................... 438
CREATE SUBSCRIPTION statement [SQL Remote] ........................................ 439
CREATE SYNCHRONIZATION SUBSCRIPTION statement [MobiLink] .......... 441
CREATE SYNCHRONIZATION USER statement [MobiLink] ........................... 444
CREATE TABLE statement ............................................................................... 446
CREATE TRIGGER statement .......................................................................... 458
CREATE TRIGGER statement [T-SQL] ............................................................ 462
CREATE VARIABLE statement ......................................................................... 463
CREATE VIEW statement ................................................................................. 465
DEALLOCATE statement .................................................................................. 468
DEALLOCATE DESCRIPTOR statement [ESQL] ............................................. 469
Declaration section [ESQL] ................................................................................ 470
DECLARE statement ......................................................................................... 471
DECLARE CURSOR statement [ESQL] [SP] .................................................... 472
DECLARE CURSOR statement [T-SQL] ........................................................... 476
Copyright © 2006, iAnywhere Solutions, Inc. v
SQL Anywhere® Server - SQL Reference
DECLARE LOCAL TEMPORARY TABLE statement ........................................ 477
DELETE statement ............................................................................................ 479
DELETE (positioned) statement [ESQL] [SP] .................................................... 481
DESCRIBE statement [ESQL] ........................................................................... 483
DESCRIBE statement [Interactive SQL] ............................................................ 487
DETACH TRACING statement .......................................................................... 489
DISCONNECT statement [ESQL] [Interactive SQL] .......................................... 490
DROP statement ................................................................................................ 491
DROP CONNECTION statement ...................................................................... 493
DROP DATABASE statement ........................................................................... 494
DROP EXTERNLOGIN statement ..................................................................... 495
DROP PUBLICATION statement [MobiLink] [SQL Remote] ............................. 496
DROP REMOTE MESSAGE TYPE statement [SQL Remote] .......................... 497
DROP SERVER statement ................................................................................ 498
DROP SERVICE statement ............................................................................... 499
DROP STATEMENT statement [ESQL] ............................................................ 500
DROP STATISTICS statement .......................................................................... 501
DROP SUBSCRIPTION statement [SQL Remote] ............................................ 502
DROP SYNCHRONIZATION SUBSCRIPTION statement [MobiLink] .............. 504
DROP SYNCHRONIZATION USER statement [MobiLink] ............................... 505
DROP VARIABLE statement ............................................................................. 506
EXCEPT operation ............................................................................................ 507
EXECUTE statement [ESQL] ............................................................................ 508
EXECUTE statement [T-SQL] ........................................................................... 510
EXECUTE IMMEDIATE statement [SP] ............................................................ 512
EXIT statement [Interactive SQL] ...................................................................... 515
EXPLAIN statement [ESQL] .............................................................................. 517
FETCH statement [ESQL] [SP] .......................................................................... 519
FOR statement .................................................................................................. 523
FORWARD TO statement ................................................................................. 526
FROM clause ..................................................................................................... 528
GET DATA statement [ESQL] ........................................................................... 535
GET DESCRIPTOR statement [ESQL] ............................................................. 537
GET OPTION statement [ESQL] ....................................................................... 539
GOTO statement [T-SQL] .................................................................................. 540
vi Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Reference
GRANT statement ............................................................................................. 541
GRANT CONSOLIDATE statement [SQL Remote] ........................................... 546
GRANT PUBLISH statement [SQL Remote] ..................................................... 548
GRANT REMOTE statement [SQL Remote] ..................................................... 549
GRANT REMOTE DBA statement [MobiLink] [SQL Remote] ........................... 551
GROUP BY clause ............................................................................................ 552
HELP statement [Interactive SQL] ..................................................................... 555
IF statement ....................................................................................................... 556
IF statement [T-SQL] ......................................................................................... 558
INCLUDE statement [ESQL] .............................................................................. 560
INPUT statement [Interactive SQL] ................................................................... 561
INSERT statement ............................................................................................. 566
INSTALL JAVA statement ................................................................................. 570
INTERSECT operation ...................................................................................... 572
LEAVE statement .............................................................................................. 573
LOAD STATISTICS statement .......................................................................... 575
LOAD TABLE statement .................................................................................... 576
LOCK TABLE statement .................................................................................... 584
LOOP statement ................................................................................................ 586
MESSAGE statement ........................................................................................ 588
OPEN statement [ESQL] [SP] ........................................................................... 592
OUTPUT statement [Interactive SQL] ............................................................... 595
PARAMETERS statement [Interactive SQL] ..................................................... 599
PASSTHROUGH statement [SQL Remote] ...................................................... 600
PREPARE statement [ESQL] ............................................................................ 601
PREPARE TO COMMIT statement ................................................................... 603
PRINT statement [T-SQL] .................................................................................. 604
PUT statement [ESQL] ...................................................................................... 605
RAISERROR statement [T-SQL] ....................................................................... 607
READ statement [Interactive SQL] .................................................................... 609
READTEXT statement [T-SQL] ......................................................................... 611
REFRESH MATERIALIZED VIEW statement ................................................... 612
REFRESH TRACING LEVEL statement ........................................................... 614
RELEASE SAVEPOINT statement .................................................................... 616
REMOTE RESET statement [SQL Remote] ...................................................... 617
Copyright © 2006, iAnywhere Solutions, Inc. vii
SQL Anywhere® Server - SQL Reference
REMOVE JAVA statement ................................................................................ 618
REORGANIZE TABLE statement ...................................................................... 619
RESIGNAL statement ........................................................................................ 621
RESTORE DATABASE statement .................................................................... 622
RESUME statement ........................................................................................... 624
RETURN statement ........................................................................................... 625
REVOKE statement ........................................................................................... 627
REVOKE CONSOLIDATE statement [SQL Remote] ........................................ 629
REVOKE PUBLISH statement [SQL Remote] ................................................... 630
REVOKE REMOTE statement [SQL Remote] ................................................... 631
REVOKE REMOTE DBA statement [SQL Remote] .......................................... 632
ROLLBACK statement ....................................................................................... 633
ROLLBACK TO SAVEPOINT statement ........................................................... 634
ROLLBACK TRANSACTION statement [T-SQL] .............................................. 635
ROLLBACK TRIGGER statement ..................................................................... 636
SAVE TRANSACTION statement [T-SQL] ........................................................ 637
SAVEPOINT statement ..................................................................................... 638
SELECT statement ............................................................................................ 639
SET statement ................................................................................................... 646
SET statement [T-SQL] ..................................................................................... 648
SET CONNECTION statement [Interactive SQL] [ESQL] ................................. 651
SET DESCRIPTOR statement [ESQL] .............................................................. 652
SET OPTION statement .................................................................................... 654
SET OPTION statement [Interactive SQL] ........................................................ 657
SET REMOTE OPTION statement [SQL Remote] ............................................ 658
SET SQLCA statement [ESQL] ......................................................................... 660
SETUSER statement ......................................................................................... 661
SIGNAL statement ............................................................................................. 663
START DATABASE statement .......................................................................... 664
START ENGINE statement [Interactive SQL] .................................................... 666
START JAVA statement .................................................................................... 667
START LOGGING statement [Interactive SQL] ................................................. 668
START SUBSCRIPTION statement [SQL Remote] .......................................... 669
START SYNCHRONIZATION DELETE statement [MobiLink] .......................... 671
STOP DATABASE statement ............................................................................ 673
viii Copyright © 2006, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Reference
STOP ENGINE statement ................................................................................. 674
STOP JAVA statement ...................................................................................... 675
STOP LOGGING statement [Interactive SQL] ................................................... 676
STOP SUBSCRIPTION statement [SQL Remote] ............................................ 677
STOP SYNCHRONIZATION DELETE statement [MobiLink] ............................ 678
SYNCHRONIZE SUBSCRIPTION statement [SQL Remote] ............................ 679
SYSTEM statement [Interactive SQL] ............................................................... 681
TRIGGER EVENT statement ............................................................................. 682
TRUNCATE TABLE statement .......................................................................... 683
UNION operation ............................................................................................... 685
UNLOAD statement ........................................................................................... 687
UNLOAD TABLE statement ............................................................................... 689
UPDATE statement ........................................................................................... 692
UPDATE (positioned) statement [ESQL] [SP] ................................................... 696
UPDATE statement [SQL Remote] .................................................................... 699
VALIDATE statement ......................................................................................... 702
WAITFOR statement ......................................................................................... 704
WHENEVER statement [ESQL] ......................................................................... 706
WHILE statement [T-SQL] ................................................................................. 707
WINDOW clause ................................................................................................ 708
WRITETEXT statement [T-SQL] ........................................................................ 711
II. System Objects .................................................................................... 713
Tables ....................................................................................................................... 715
System tables .................................................................................................... 716
Diagnostic tracing tables .................................................................................... 727
Other tables ....................................................................................................... 743
Views ........................................................................................................................ 745
System view definitions in Sybase Central ........................................................ 746
System views ..................................................................................................... 747
Consolidated views ............................................................................................ 804
Compatibility views ............................................................................................ 819
System Procedures ................................................................................................. 829
System procedure overview .............................................................................. 830
System procedures ............................................................................................ 831
Copyright © 2006, iAnywhere Solutions, Inc. ix
SQL Anywhere® Server - SQL Reference
System extended procedures ............................................................................ 948
Adaptive Server Enterprise system and catalog procedures ............................. 959
Index .................................................................................................................................. 961
x Copyright © 2006, iAnywhere Solutions, Inc.