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.