SQL Anywhere® Server SQL Reference - SyBooks Online

1046 Pages·2007·5.41 MB·English

SQL Anywhere® Server
SQL Reference
Published: March 2007
Copyright and trademarks
Copyright © 2007 iAnywhere Solutions, Inc. Portions copyright © 2007 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 .............................................................................................................. 36
Comments ........................................................................................................... 42
NULL value .......................................................................................................... 43
SQL Data Types ......................................................................................................... 47
Character data types ........................................................................................... 48
Numeric data types .............................................................................................. 56
Money data types ................................................................................................ 64
Bit array data types .............................................................................................. 65
Date and time data types ..................................................................................... 67
Binary data types ................................................................................................. 74
Domains ............................................................................................................... 78
Data type conversions ......................................................................................... 80
Java and SQL data type conversion .................................................................... 88
SQL Functions ........................................................................................................... 91
Introduction to SQL functions .............................................................................. 92
Function types ..................................................................................................... 93
Alphabetical list of functions .............................................................................. 103
Copyright © 2007, iAnywhere Solutions, Inc. iii
SQL Anywhere® Server - SQL Reference
SQL Statements ....................................................................................................... 289
Using the SQL statement reference .................................................................. 295
ALLOCATE DESCRIPTOR statement [ESQL] .................................................. 299
ALTER DATABASE statement .......................................................................... 301
ALTER DBSPACE statement ............................................................................ 305
ALTER DOMAIN statement ............................................................................... 307
ALTER EVENT statement ................................................................................. 308
ALTER FUNCTION statement ........................................................................... 310
ALTER INDEX statement .................................................................................. 311
ALTER MATERIALIZED VIEW statement ......................................................... 313
ALTER PROCEDURE statement ...................................................................... 315
ALTER PUBLICATION statement [MobiLink] [SQL Remote] ............................ 317
ALTER REMOTE MESSAGE TYPE statement [SQL Remote] ......................... 319
ALTER SERVER statement ............................................................................... 321
ALTER SERVICE statement .............................................................................. 323
ALTER STATISTICS statement ......................................................................... 327
ALTER SYNCHRONIZATION SUBSCRIPTION statement [MobiLink] ............. 328
ALTER SYNCHRONIZATION USER statement [MobiLink] .............................. 330
ALTER TABLE statement .................................................................................. 332
ALTER TRIGGER statement ............................................................................. 341
ALTER VIEW statement .................................................................................... 342
ATTACH TRACING statement .......................................................................... 344
BACKUP statement ........................................................................................... 346
BEGIN statement ............................................................................................... 351
BEGIN TRANSACTION statement [T-SQL] ...................................................... 354
BREAK statement [T-SQL] ................................................................................ 356
CALL statement ................................................................................................. 357
CASE statement ................................................................................................ 359
CHECKPOINT statement .................................................................................. 361
CLEAR statement [Interactive SQL] .................................................................. 362
CLOSE statement [ESQL] [SP] ......................................................................... 363
COMMENT statement ....................................................................................... 365
COMMIT statement ........................................................................................... 367
CONFIGURE statement [Interactive SQL] ......................................................... 369
CONNECT statement [ESQL] [Interactive SQL] ................................................ 370
iv Copyright © 2007, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Reference
CONTINUE statement [T-SQL] .......................................................................... 373
CREATE DATABASE statement ....................................................................... 374
CREATE DBSPACE statement ......................................................................... 382
CREATE DECRYPTED FILE statement ............................................................ 384
CREATE DOMAIN statement ............................................................................ 386
CREATE ENCRYPTED FILE statement ............................................................ 388
CREATE EVENT statement .............................................................................. 390
CREATE EXISTING TABLE statement ............................................................. 395
CREATE EXTERNLOGIN statement ................................................................. 397
CREATE FUNCTION statement ........................................................................ 399
CREATE INDEX statement ............................................................................... 405
CREATE LOCAL TEMPORARY TABLE statement .......................................... 409
CREATE MATERIALIZED VIEW statement ...................................................... 411
CREATE MESSAGE statement [T-SQL] ........................................................... 413
CREATE PROCEDURE statement ................................................................... 414
CREATE PROCEDURE statement [T-SQL] ...................................................... 425
CREATE PUBLICATION statement [MobiLink] [SQL Remote] ......................... 427
CREATE REMOTE MESSAGE TYPE statement [SQL Remote] ...................... 431
CREATE SCHEMA statement ........................................................................... 433
CREATE SERVER statement ............................................................................ 435
CREATE SERVICE statement ........................................................................... 438
CREATE STATISTICS statement ...................................................................... 442
CREATE SUBSCRIPTION statement [SQL Remote] ........................................ 443
CREATE SYNCHRONIZATION SUBSCRIPTION statement [MobiLink] .......... 445
CREATE SYNCHRONIZATION USER statement [MobiLink] ........................... 448
CREATE TABLE statement ............................................................................... 450
CREATE TRIGGER statement .......................................................................... 462
CREATE TRIGGER statement [T-SQL] ............................................................ 468
CREATE VARIABLE statement ......................................................................... 469
CREATE VIEW statement ................................................................................. 471
DEALLOCATE statement .................................................................................. 474
DEALLOCATE DESCRIPTOR statement [ESQL] ............................................. 475
Declaration section [ESQL] ................................................................................ 476
DECLARE statement ......................................................................................... 477
DECLARE CURSOR statement [ESQL] [SP] .................................................... 478
Copyright © 2007, iAnywhere Solutions, Inc. v
SQL Anywhere® Server - SQL Reference
DECLARE CURSOR statement [T-SQL] ........................................................... 482
DECLARE LOCAL TEMPORARY TABLE statement ........................................ 483
DELETE statement ............................................................................................ 485
DELETE (positioned) statement [ESQL] [SP] .................................................... 488
DESCRIBE statement [ESQL] ........................................................................... 490
DESCRIBE statement [Interactive SQL] ............................................................ 494
DETACH TRACING statement .......................................................................... 496
DISCONNECT statement [ESQL] [Interactive SQL] .......................................... 497
DROP statement ................................................................................................ 498
DROP CONNECTION statement ...................................................................... 500
DROP DATABASE statement ........................................................................... 501
DROP EXTERNLOGIN statement ..................................................................... 502
DROP PUBLICATION statement [MobiLink] [SQL Remote] ............................. 503
DROP REMOTE MESSAGE TYPE statement [SQL Remote] .......................... 504
DROP SERVER statement ................................................................................ 505
DROP SERVICE statement ............................................................................... 506
DROP STATEMENT statement [ESQL] ............................................................ 507
DROP STATISTICS statement .......................................................................... 508
DROP SUBSCRIPTION statement [SQL Remote] ............................................ 509
DROP SYNCHRONIZATION SUBSCRIPTION statement [MobiLink] .............. 510
DROP SYNCHRONIZATION USER statement [MobiLink] ............................... 511
DROP VARIABLE statement ............................................................................. 512
EXCEPT statement ............................................................................................ 513
EXECUTE statement [ESQL] ............................................................................ 515
EXECUTE statement [T-SQL] ........................................................................... 517
EXECUTE IMMEDIATE statement [SP] ............................................................ 519
EXIT statement [Interactive SQL] ...................................................................... 522
EXPLAIN statement [ESQL] .............................................................................. 524
FETCH statement [ESQL] [SP] .......................................................................... 526
FOR statement .................................................................................................. 530
FORWARD TO statement ................................................................................. 533
FROM clause ..................................................................................................... 535
GET DATA statement [ESQL] ........................................................................... 542
GET DESCRIPTOR statement [ESQL] ............................................................. 544
GET OPTION statement [ESQL] ....................................................................... 546
vi Copyright © 2007, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Reference
GOTO statement [T-SQL] .................................................................................. 547
GRANT statement ............................................................................................. 548
GRANT CONSOLIDATE statement [SQL Remote] ........................................... 553
GRANT PUBLISH statement [SQL Remote] ..................................................... 555
GRANT REMOTE statement [SQL Remote] ..................................................... 556
GRANT REMOTE DBA statement [MobiLink] [SQL Remote] ........................... 558
GROUP BY clause ............................................................................................ 559
HELP statement [Interactive SQL] ..................................................................... 562
IF statement ....................................................................................................... 563
IF statement [T-SQL] ......................................................................................... 565
INCLUDE statement [ESQL] .............................................................................. 567
INPUT statement [Interactive SQL] ................................................................... 568
INSERT statement ............................................................................................. 573
INSTALL JAVA statement ................................................................................. 578
INTERSECT statement ...................................................................................... 580
LEAVE statement .............................................................................................. 582
LOAD STATISTICS statement .......................................................................... 584
LOAD TABLE statement .................................................................................... 585
LOCK TABLE statement .................................................................................... 593
LOOP statement ................................................................................................ 595
MESSAGE statement ........................................................................................ 597
OPEN statement [ESQL] [SP] ........................................................................... 601
OUTPUT statement [Interactive SQL] ............................................................... 604
PARAMETERS statement [Interactive SQL] ..................................................... 608
PASSTHROUGH statement [SQL Remote] ...................................................... 609
PREPARE statement [ESQL] ............................................................................ 610
PREPARE TO COMMIT statement ................................................................... 612
PRINT statement [T-SQL] .................................................................................. 613
PUT statement [ESQL] ...................................................................................... 614
RAISERROR statement [T-SQL] ....................................................................... 616
READ statement [Interactive SQL] .................................................................... 618
READTEXT statement [T-SQL] ......................................................................... 620
REFRESH MATERIALIZED VIEW statement ................................................... 621
REFRESH TRACING LEVEL statement ........................................................... 623
RELEASE SAVEPOINT statement .................................................................... 625
Copyright © 2007, iAnywhere Solutions, Inc. vii
SQL Anywhere® Server - SQL Reference
REMOTE RESET statement [SQL Remote] ...................................................... 626
REMOVE JAVA statement ................................................................................ 627
REORGANIZE TABLE statement ...................................................................... 628
RESIGNAL statement ........................................................................................ 630
RESTORE DATABASE statement .................................................................... 631
RESUME statement ........................................................................................... 633
RETURN statement ........................................................................................... 634
REVOKE statement ........................................................................................... 636
REVOKE CONSOLIDATE statement [SQL Remote] ........................................ 638
REVOKE PUBLISH statement [SQL Remote] ................................................... 639
REVOKE REMOTE statement [SQL Remote] ................................................... 640
REVOKE REMOTE DBA statement [SQL Remote] .......................................... 641
ROLLBACK statement ....................................................................................... 642
ROLLBACK TO SAVEPOINT statement ........................................................... 643
ROLLBACK TRANSACTION statement [T-SQL] .............................................. 644
ROLLBACK TRIGGER statement ..................................................................... 645
SAVE TRANSACTION statement [T-SQL] ........................................................ 646
SAVEPOINT statement ..................................................................................... 647
SELECT statement ............................................................................................ 648
SET statement ................................................................................................... 656
SET statement [T-SQL] ..................................................................................... 658
SET CONNECTION statement [Interactive SQL] [ESQL] ................................. 661
SET DESCRIPTOR statement [ESQL] .............................................................. 662
SET OPTION statement .................................................................................... 664
SET OPTION statement [Interactive SQL] ........................................................ 667
SET REMOTE OPTION statement [SQL Remote] ............................................ 668
SET SQLCA statement [ESQL] ......................................................................... 670
SETUSER statement ......................................................................................... 671
SIGNAL statement ............................................................................................. 673
START DATABASE statement .......................................................................... 674
START ENGINE statement [Interactive SQL] .................................................... 676
START JAVA statement .................................................................................... 677
START LOGGING statement [Interactive SQL] ................................................. 678
START SUBSCRIPTION statement [SQL Remote] .......................................... 679
START SYNCHRONIZATION DELETE statement [MobiLink] .......................... 681
viii Copyright © 2007, iAnywhere Solutions, Inc.
SQL Anywhere® Server - SQL Reference
STOP DATABASE statement ............................................................................ 683
STOP ENGINE statement ................................................................................. 684
STOP JAVA statement ...................................................................................... 685
STOP LOGGING statement [Interactive SQL] ................................................... 686
STOP SUBSCRIPTION statement [SQL Remote] ............................................ 687
STOP SYNCHRONIZATION DELETE statement [MobiLink] ............................ 688
SYNCHRONIZE SUBSCRIPTION statement [SQL Remote] ............................ 689
SYSTEM statement [Interactive SQL] ............................................................... 691
TRIGGER EVENT statement ............................................................................. 692
TRUNCATE TABLE statement .......................................................................... 693
UNION statement .............................................................................................. 695
UNLOAD statement ........................................................................................... 698
UNLOAD TABLE statement ............................................................................... 700
UPDATE statement ........................................................................................... 703
UPDATE (positioned) statement [ESQL] [SP] ................................................... 708
UPDATE statement [SQL Remote] .................................................................... 710
VALIDATE statement ......................................................................................... 713
WAITFOR statement ......................................................................................... 715
WHENEVER statement [ESQL] ......................................................................... 717
WHILE statement [T-SQL] ................................................................................. 718
WINDOW clause ................................................................................................ 719
WRITETEXT statement [T-SQL] ........................................................................ 722
II. System Objects .................................................................................... 723
Tables ....................................................................................................................... 725
System tables .................................................................................................... 726
Diagnostic tracing tables .................................................................................... 735
Other tables ....................................................................................................... 751
Views ........................................................................................................................ 753
System views in Sybase Central ....................................................................... 754
Consolidated views ............................................................................................ 809
Compatibility views ............................................................................................ 824
System Procedures ................................................................................................. 833
Introduction to system procedures ..................................................................... 834
System procedures ............................................................................................ 835
Copyright © 2007, iAnywhere Solutions, Inc. ix
SQL Anywhere® Server - SQL Reference
System extended procedures ............................................................................ 951
Adaptive Server Enterprise system and catalog procedures ............................. 962
Index .................................................................................................................................. 965
x Copyright © 2007, iAnywhere Solutions, Inc.