Copyright: ASA Version 7.0.2 Copyright © 2000 Sybase, Inc. All rights reserved.


Getting Started
ASA

Version 7.0.2

Document ID:  MC0056

November 2000

 Copyright page

 About This Manual

 PART 1. Moving to Adaptive Server Anywhere 7.0

 PART 2. Database Concepts

 PART 3. Working With Databases


Copyright page
  ASA Getting Started

 Copyright page

Copyright © 2000 Sybase, Inc. All rights reserved.

No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Sybase, Inc.

Sybase, SYBASE (logo), ADA Workbench, Adaptable Windowing Environment, Adaptive Component Architecture, Adaptive Server, Adaptive Server Anywhere, Adaptive Server Enterprise, Adaptive Server Enterprise Monitor, Adaptive Server Enterprise Replication, Adaptive Server Everywhere, Adaptive Server IQ, Adaptive Warehouse, AnswerBase, Anywhere Studio, Application Manager, AppModeler, APT-Build, APT-Edit, APT-Execute, APT-FORMS, APT-Library, APT-Translator, APT Workbench, ASEP, Backup Server, BayCam, Bit-Wise, Certified PowerBuilder Developer, Certified SYBASE Professional, Certified SYBASE Professional (logo), ClearConnect, Client Services, Client-Library, CodeBank, Cohesion, Column Design, ComponentPack, Connection Manager, CSP, Data Pipeline, Data Workbench, DataArchitect, Database Analyzer, DataExpress, DataServer, DataWindow, DB-Library, dbQueue, Developers Workbench, Direct Connect Anywhere, DirectConnect, Distribution Director, Dynamo, E-Anywhere, E-Whatever, Electronic Case Management, Embedded SQL, EMS, Enterprise Application Server, Enterprise Application Studio, Enterprise Client/Server, Enterprise Connect, Enterprise Data Studio, Enterprise Manager, Enterprise SQL Server Manager, Enterprise Work Architecture, Enterprise Work Designer, Enterprise Work Modeler, EWA, Financial Fusion, First Impression, Formula One, Gateway Manager, GeoPoint, ImpactNow, InfoMaker, Information Anywhere, Information Everywhere, InformationConnect, InstaHelp, Intellidex, InternetBuilder, iScript, Jaguar CTS, jConnect for JDBC, KnowledgeBase, Logical Memory Manager, MainframeConnect, Maintenance Express, MAP, MDI Access Server, MDI Database Gateway, media.splash, MetaWorks, MethodSet, MobiCATS, MySupport, Net-Gateway, Net-Library, NetImpact, Next Generation Learning, Next Generation Learning Studio, O DEVICE, OASiS, OASiS (logo), ObjectConnect, ObjectCycle, OmniConnect, OmniSQL Access Module, OmniSQL Toolkit, Open Client, Open Client/Server, Open Client/Server Interfaces, Open ClientConnect, Open Gateway, Open Server, Open ServerConnect, Open Solutions, Optima++, Partnerships that Work, PB-Gen, PC APT Execute, PC DB-Net, PC Net Library, PhysicalArchitect, Power Through Knowledge, Power++, power.stop, PowerAMC, PowerBuilder, PowerBuilder Foundation Class Library, PowerDesigner, PowerDimensions, PowerDynamo, PowerJ, PowerScript, PowerSite, PowerSocket, Powersoft, Powersoft Portfolio, Powersoft Professional, PowerStage, PowerStudio, PowerTips, PowerWare Desktop, PowerWare Enterprise, ProcessAnalyst, Relational Beans, Replication Agent, Replication Driver, Replication Server, Replication Server Manager, Replication Toolkit, Report Workbench, Report-Execute, Resource Manager, RW-DisplayLib, RW-Library, S-Designor, S Designor, SAFE, SAFE/PRO, SDF, Secure SQL Server, Secure SQL Toolset, Security Guardian, SKILS, smart.partners, smart.parts, smart.script, SQL Advantage, SQL Anywhere, SQL Anywhere Studio, SQL Code Checker, SQL Debug, SQL Edit, SQL Edit/TPU, SQL Everywhere, SQL Modeler, SQL Remote, SQL Server, SQL Server Manager, SQL Server SNMP SubAgent, SQL Server/CFT, SQL Server/DBM, SQL SMART, SQL Station, SQL Toolset, SQLJ, Startup.Com, STEP, SupportNow, Sybase Central, Sybase Client/Server Interfaces, Sybase Development Framework, Sybase Financial Server, Sybase Gateways, Sybase Learning Connection, Sybase SQL Desktop, Sybase SQL Lifecycle, Sybase SQL Workgroup, Sybase Synergy Program, Sybase User Workbench, Sybase Virtual Server Architecture, Sybase MPP, SybaseWare, Syber Financial, SyberAssist, SyBooks, System XI (logo), System 10, System 11, SystemTools, Tabular Data Stream, The Enterprise Client/Server Company, The Extensible Software Platform, The Future Is Wide Open, The Learning Connection, The Model For Client/Server Solutions, The Online Information Center, Transact-SQL, Translation Toolkit, Turning Imagination Into Reality, UltraLite, UNIBOM, Unilib, Uninull, Unisep, Unistring, URK Runtime Kit for UniCode, Viewer, Visual Components, VisualSpeller, VisualWriter, VQL, Warehouse Control Center, Warehouse Studio, Warehouse WORKS, WarehouseArchitect, Watcom, Watcom SQL Server, Watcom SQL, Web.PB, Web.SQL, Web Deployment Kit, WebSights, WebViewer, WorkGroup SQL Server, XA-Library, XA-Server, and XP Server are trademarks of Sybase, Inc. or its subsidiaries.

All other trademarks are property of their respective owners.

Last modified: June 2000. Part Number: MC0056.

 Copyright page

 About This Manual

 PART 1. Moving to Adaptive Server Anywhere 7.0

 PART 2. Database Concepts

 PART 3. Working With Databases


About This Manual
  ASA Getting Started

 About This Manual

 About this Manual

 Using this book

 Related books

 Documentation conventions


About this Manual
  ASA Getting Started

    About This Manual

 About this Manual

Subject

This book describes how to build simple databases and database applications using Adaptive Server Anywhere.

Audience

This manual is for beginning users of Adaptive Server Anywhere, and for users of previous versions who want to find out what is new and different in Adaptive Server Anywhere version 7.0.

Before you begin

This manual assumes some familiarity with basic programming concepts.  It also assumes a working knowledge of the operating system on which you will be using Adaptive Server Anywhere.


Using this book
  ASA Getting Started

    About This Manual

 Using this book

Part I    is for users of previous versions of the software. It describes new features in Adaptive Server Anywhere 7.0, and information you need to know if you are upgrading from an earlier version.

Part II    introduces the Adaptive Server Anywhere relational database management system (RDBMS) and basic relational database concepts. If you are new to databases, this part provides a necessary foundation for your work and for understanding the product. If you have worked with other database systems, but are new to Adaptive Server Anywhere, you may want to read this section to see some of the distinguishing features of Adaptive Server Anywhere.

Part III    describes how to design and build a database—planning the database structure, creating tables, entering data, establishing relationships between tables, and creating views. Many of these tasks can be accomplished using our database administration utility, Sybase Central.

It also describes how to use structured query language (SQL) to access and modify the data in your database.

This book does not exhaust the possibilities of Adaptive Server Anywhere. It is intended for people who need only to develop simple applications, or who are unfamiliar with Adaptive Server Anywhere.


Related books
  ASA Getting Started

    About This Manual

 Related books

This book provides information you need to get started with Adaptive Server Anywhere.  More detailed information is available in other books in the Adaptive Server Anywhere documentation set:

•	Adaptive Server Anywhere User's Guide    This book provides the most thorough and in-depth information on all aspects of Adaptive Server Anywhere. It includes material on designing and building databases, using SQL, administering databases, and other aspects of Adaptive Server Anywhere operation.

•	Adaptive Server Anywhere Programming Interfaces Guide    Some aspects of database programming are specific to the programming interface you work with. The Programming Interfaces Guide provides material on each of the programming interfaces you can use to access Adaptive Server Anywhere databases.

This book is not required for users of Application Development tools with built-in ODBC support, such as Sybase PowerBuilder or PowerJ.

•	Adaptive Server Anywhere Reference Manual    A complete reference to Adaptive Server Anywhere. This book describes the database server, the administration utilities, SQL, and error messages.

The format of these books (printed or online) may depend on the product in which you obtained Adaptive Server Anywhere.

Online documentation is most current
Online documentation is updated with each maintenance release, and always contains the most current information.

Depending on which package you have purchased, you may have additional books describing other components of your product.


Documentation conventions
  ASA Getting Started

    About This Manual

 Documentation conventions

The following icons are used in this documentation:

Icon	Meaning
	A client application. If the icon is used to represent a particular application or kind of application, the name is indicated in the bottom right corner.
	A database server, such as Sybase Adaptive Server Anywhere or Adaptive Server Enterprise. If the icon is used to represent a particular kind of database server, such as Adaptive Server Anywhere, the name is indicated in the bottom  right corner.
	An UltraLite application and database server. In UltraLite, the database server and the application are part of the same process.
	A database. In some high-level diagrams, the icon may be used to represent both the database and the database server that manages it.
	Replication or synchronization middleware. These pieces of software assist in sharing data among databases. Examples are the MobiLink Synchronization Server, the SQL Remote Message Agent, and the Replication Agent (Log Transfer Manager) for use with Replication Server. If the particular kind of middleware is not obvious from the context of the diagram, the name is indicated in the bottom right hand corner.
	A Sybase Replication Server.
 Installed files


Installed files
  ASA Getting Started

    About This Manual

      Documentation conventions

 Installed files

The following terms are used throughout the manual:

•	Installation directory    The directory into which you install Adaptive Server Anywhere. The default installation directory is c:\Program Files\Sybase\SQL Anywhere 7

•	Executable directory    The executables and other files for each operating system are stored in a subdirectory of the installation directory. This subdirectory has the following name:

•	Windows NT and Windows 95/98    win32

•	UNIX    bin

•	NetWare and Windows CE    The executables are held in the Adaptive Server Anywhere installation directory itself on these platforms.

 Copyright page

 About This Manual

 PART 1. Moving to Adaptive Server Anywhere 7.0

 PART 2. Database Concepts

 PART 3. Working With Databases


PART 1. Moving to Adaptive Server Anywhere 7.0
  ASA Getting Started

 PART 1. Moving to Adaptive Server Anywhere 7.0

This part is for users of previous versions of Adaptive   Server Anywhere. It describes new features, behavior changes, and   upgrade issues introduced in Adaptive Server Anywhere 7.0.

If you have not used previous versions of Adaptive Server   Anywhere, you do not need to read this part.

 CHAPTER 1.  Adaptive Server Anywhere Version 7.0.2

 CHAPTER 2.  Adaptive Server Anywhere Version 7.0.1

 CHAPTER 3.  Adaptive Server Anywhere Version 7.0

 CHAPTER 4.  Adaptive Server Anywhere 6.0.3

 CHAPTER 5.  Adaptive Server Anywhere 6.0.2

 CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 CHAPTER 7.  Upgrading Version 5 Applications


CHAPTER 1.  Adaptive Server Anywhere Version 7.0.2
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 1.  Adaptive Server Anywhere Version 7.0.2

About this chapter

This chapter provides an overview of the new features and behavior changes introduced in Adaptive Server Anywhere version 7.0.2.

 New features

 Behavior changes


New features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 1.  Adaptive Server Anywhere Version 7.0.2

 New features

This section introduces the new features in Adaptive Server Anywhere version 7.0.2. It provides an exhaustive listing of major and minor new features, with cross references to locations where each feature is discussed in detail.

•	Dynamic cache sizing    On Windows 95/98, the size of the database server cache increases and decreases depending on the load on the database server and the other demands on system memory. This feature removes the need for choosing an explicit cache size in many circumstances, and can also boost performance.

  For more information, see Using the cache to improve performance.

•	Additional collations    There are three new collations available: one to support Russian and Ukrainian (1251CYR, ANSI Code Page 1251), one to support Turkish (1254TRK, ANSI Code Page 1254), and one to support German (1252DEU, ANSI Code Page 1252).

  For a complete list of available collations, see Supplied collations.

•	Interactive SQL return codes    When run in command-line mode, Interactive SQL now sets a program exit code indicating the success or otherwise of the operations in the session.

  For more information, see The Interactive SQL utility.

•	DELETE_OLD_LOGS enhancement    The DELETE_OLD_LOGS database option is used in management of offline transaction logs in a replication environment. The option has been enhanced to permit more control over when processed transaction logs are deleted.

  For more information, see DELETE_OLD_LOGS option.

•	Connection troubleshooting and enhancements    The following changes have been made to permit better troubleshooting and tuning of client/server communications:

•	The APPINFO string is now added to the client debug log file.

  For more information, see AppInfo connection parameter.

•	Two new connection parameters can be used to tune prefetching of rows.

  For more information, see PrefetchRows connection parameter and PrefetchBuffer connection parameter.

•	The dbping command-line utility has been enhanced to enable testing of ODBC connections as well as embedded SQL.

  For more information, see The Ping utility.

•	A new callback function has been added for the embedded SQL programming interface, that reports debug messages.

  For more information, see db_register_a_callback function.

•	The ConnectionName connection parameter value was previously overridden for ODBC clients. You can now use the ConnectionName parameter from ODBC clients.

  For a list of connection parameters, see Connection parameters.

•	dbspawn enhancement    The dbspawn command-line utility optionally reports the operating system process ID of the database server.

  For more information, see The Spawn utility.

•	Obtain remote table foreign key information    Two new stored procedures, sp_remote_exported_keys and sp_remote_imported_keys, allow you to obtain information about foreign keys and their corresponding primary keys for remote tables.

  For more information, see sp_remote_exported_keys system procedure and sp_remote_imported_keys system procedure.

•	New migration tool    You can migrate (import) remote Oracle, DB2, Microsoft SQL Server, Sybase Adaptive Server Enterprise, Sybase Adaptive Server Anywhere and Microsoft Access databases into Adaptive Server Anywhere using the new sa_migrate set of stored procedures.

  For more information, see Import tasks.

•	Event handlers    Adaptive Server Anywhere can now determine how many instances of a particular event handler is executing at any given time. This is useful for limiting event handlers to only one instance at a time.

  For more information, see EVENT_PARAMETER function.

•	New connection property    A new connection property helps distinguish between internal connections used to run event handlers.

  For more information, see CONNECTION_PROPERTY function .

•	Dbdsn supports user and system specifiers    The dbdsn command-line utility now supports the u (user) and s (system) specifiers.

  For more information, seeThe Data Source utility.

•	Support for comments in @filename files    Adaptive Server Anywhere now supports comment lines in @filename files.

  For more information, see @filename command-line option.

•	Truncate timestamp option    To allow for greater compatibility with non-ASA databases, you can now truncate timestamp values.

  For more information, see TRUNCATE_TIMESTAMP_VALUES option.

•	Obtaining licensing information    Engine properties have been added to help you obtain accurate licensing information about your copy of Adaptive Server Anywhere.

  For more information, see Server-level properties.


Behavior changes
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 1.  Adaptive Server Anywhere Version 7.0.2

 Behavior changes

The following are behavior changes from previous versions of the software.

•	Circular alias references disallowed    If two aliases in a SELECT statement refer to each other, directly or indirectly, the server may hang or crash. This happens when an expression refers to an alias that is defined later in the select list. The error message that is generated is "Definition for alias alias-name must appear before its first reference." This error may be generated for some queries which were previously considered valid.

•	Console Utility now supports both NetWare and UNIX    Previously, only UNIX was supported. The server console utility allows you to monitor the server from a client machine. You can use it to track who is logged on to a database server elsewhere on your network. You can also display both the server and the client statistics on your local client screen, disconnect users and configure the database server.


CHAPTER 2.  Adaptive Server Anywhere Version 7.0.1
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 2.  Adaptive Server Anywhere Version 7.0.1

About this chapter

This chapter provides an overview of the new features and behavior changes introduced in Adaptive Server Anywhere version 7.0.1.

 New features

 Behavior changes


New features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 2.  Adaptive Server Anywhere Version 7.0.1

 New features

This section introduces the new features in Adaptive Server Anywhere version 7.0.1. It provides an exhaustive listing of major and minor new features, with cross references to locations where each feature is discussed in detail.

New features in other components
For new features in other components of SQL Anywhere Studio, see the following:

MobiLink synchronization    What's new in 7.0.1.

UltraLite deployment    What's new in version 7.0.1.

SQL Remote    New features in 7.0.1.

•	New Service Creation utility    Running a database server as a service under NT allows databases to keep running without tying up the machine on which they are running. Previously, you added services using the Create a New Service wizard from Sybase Central. In Version 7 of Adaptive Server Anywhere, you can now also manage ASA services on Windows NT using a new command-line utility. A variety of switches allow you to add or delete a service, list all ASA services, or display the details of a particular service. This feature is particularly useful for embedding the creation of a service in installations.

  For more information about the Service Creation utility, see The Service Creation utility.

•	Windows CE 3.0 support    In addition to Windows CE 2.11, Adaptive Server Anywhere now supports Windows CE 3.0 on the following processors:

•	MIPS

•	Hitachi SH3.

•	ARM.

Support for Windows CE 2.11 is provided on a wider range of platforms. For more information, see Adaptive Server Anywhere supported operating systems.

With support for Windows CE 3.0, the OLE DB driver on CE works without installing any additional software.

•	Embedded SQL enhancements    A new function, db_locate_servers, provides a programmatic way of locating Adaptive Server Anywhere database servers listening on TCP/IP.

  For more information, see db_locate_servers function.

A new callback function, DB_CALLBACK_CONN_DROPPED, provides a way of adding logic when the database server is about to drop a connection.

  For more information, see db_register_a_callback function.

•	Connection-level Debug and Logfile connection parameters    The DBG and LOG client-side connection parameters are now connection-specific, so you can configure debug information separately for different connections, even from the same application.

  For more information, see Debug connection parameter, and Logfile connection parameter.

•	New database property    The LTMGeneration property has been added for users of the Replication Agent, or LTM. This property is primarily for use in technical support cases.

  For more information, see Database-level properties.

•	New deployment feature     Users of InstallShield Professional 5.5 and up can use the new SQL Anywhere Studio InstallShield Template Projects to deploy their own application. This feature allows you to quickly build your application's installation using the entire template project, or just the parts that apply to your install.

  For more information, see Using InstallShield templates for deployment.

•	New backup statement feature    When using the Backup statement, you can specify an empty string as a directory to rename or truncate the log without copying it first. This is particularly useful in a replication environment where space is a concern. You can use this feature with an event handler on transaction log size to rename the log when it reaches a given size, and with the DELETE_OLD_LOGS option to delete the log when it is no longer needed.

  For more information, see the BACKUP statement.

•	Additional Java class    The Java class java.net.PlainDatagramSocketImpl was previously partially supported. It is now fully supported under Windows and UNIX.

  For more information, see Java class data types.


Behavior changes
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 2.  Adaptive Server Anywhere Version 7.0.1

 Behavior changes

The following are behavior changes from previous versions of the software.

•	ODBC autocommit change    A behavior change was introduced in version 7.0.0, tying the ODBC autocommit setting to the CHAINED database option. As a result of customer feedback, this change has been rolled back. in 7.0.1 and later. ODBC autocommit is again independent of the CHAINED option, and therefore setting autocommit to ON does not lead to a COMMIT after each operation in a procedure.


CHAPTER 3.  Adaptive Server Anywhere Version 7.0
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 3.  Adaptive Server Anywhere Version 7.0

About this chapter

This chapter provides an overview of the new features and behavior changes introduced in Adaptive Server Anywhere version 7.0.

 About version 7.0

 New features overview

 Behavior changes

 Upgrade issues


About version 7.0
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

 About version 7.0

Finding out about the new features

The primary format for the documentation is HTML Help. The HTML Help Home Page gives you easy access to the new features, information about how to contact Sybase, and other starting points for this release.

If you do not have Internet Explorer 4.0 or HTML Help installed on your machine, you will install Windows Help instead of HTML Help. The content is the same except for the HTML Help home page, which is not present in Windows Help.

If you are using Windows Help, you should look at Chapter 1 of Getting Started with Adaptive Server Anywhere for information on Adaptive Server Anywhere new features, and at the first chapters of the UltraLite Developer's Guide and the Replication and Synchronization Guide for information on new features in those technologies.


New features overview
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

 New features overview

This section introduces the new features in Adaptive Server Anywhere version 7.0. It provides an exhaustive listing of major and minor new features, with cross references to locations where each feature is discussed in detail.

If you have the printed version of this book, and if you do not have the complete SQL Anywhere Studio documentation set, you should look in the online documentation for the detailed description of each feature. To locate the information in the online documentation, go to the index and enter the specified title.

Administration and ease of use enhancements

•	Task scheduling and event handling in the database    You can now add scheduled operations to the database. This can be useful for automatic backups, periodic reports to fill summary tables, and other tasks.

The database server can also be instructed to execute event handlers when certain events occur, including disk space thresholds on the drives holding the database file or the transaction log file, or failed connection attempts.

Event handlers can be created and altered using Sybase Central, and can be debugged using the Adaptive Server Anywhere debugger.

  For more information see Automating Tasks Using Schedules and Events, and CREATE EVENT statement.

•	Updated Sybase Central    Sybase Central is now available as a Java application. This provides a uniform interface to Adaptive Server Anywhere from any supported platform.

The Windows version of Sybase Central is also included in case you require access to a feature not yet implemented in the new Sybase Central. No new features are being added to the Windows version. If you wish to access the C++ version, you can do so from the Sybase Central directory, using the scview.exe executable.

•	Updated Interactive SQL    The Interactive SQL utility has been enhanced and is now available as a Java application. The new Interactive SQL has multiple windows, with each window opening a separate database connection.

You can start Interactive SQL from the Adaptive Server Anywhere program group, or from Sybase Central, or by entering dbisql from a command prompt. The following command gives a list of command-line options:

dbisql /?

The Windows version of Interactive SQL is also included in case you require access to a feature not yet implemented in the new Interactive SQL.

No new features are being added to the Windows version, which is now named dbisqlc.exe.

•	New validation features    Additional validation of databases is provided by the new VALIDATE INDEX statement and by enhancements to the VALIDATE TABLE statement. This statement is called both by the Validation utility, and by the sa_validate system procedure. The enhancements are available through all these routes.

  For more information, see VALIDATE INDEX statement, and VALIDATE TABLE statement.

•	Lock troubleshooting    A new system procedure, sa_locks, provides information on locks in the database. If lock issues are identified, information on the connection processes involved can be found using the AppInfo connection property.

  For more information, see sa_locks system procedure, and AppInfo connection parameter.

•	Unloading result sets    The new UNLOAD SQL statement allows query result sets to be unloaded into a comma-delimited text file.

  For more information, see UNLOAD statement.

•	Validate backup copies of databases    If you backup a database using the WAIT BEFORE START clause, the backup copy is created in such a fashion that it can be started in read-only mode and validated.

  For more information, see BACKUP statement.

•	Default global autoincrement    This feature provides an easy way to generate integer keys which are unique across all databases in a SQL Remote replication environment.

Integration with distributed computing architectures

•	Distributed transactions and three-tiered computing    Distributed transactions include operations on more than one server in a single transaction. A transaction server controls the commit and rollback behavior of distributed transactions.

In this release, Adaptive Server Anywhere can participate in distributed transactions coordinated by the Microsoft Distributed Transaction Coordinator (DTC). Products such as Sybase Enterprise Application Server and Microsoft Transaction Server can use DTC for transaction coordination, so DTC support enables Adaptive Server Anywhere to participate in three-tiered computing with these products.

  For more information, see Three-tier Computing and Distributed Transactions.

Integration with COM

•	OLE DB provider    OLE DB is a data access model from Microsoft. It uses the Component Object Model (COM) interfaces and, unlike ODBC, OLE DB does not assume that the data source uses a SQL query processor. While it has been possible to access Adaptive Server Anywhere via OLE DB using an OLE DB/ODBC bridge provided by Microsoft, this release of Adaptive Server Anywhere includes an OLE DB provider. This provider brings several benefits:

•	OLE DB is the principal data access option for the forthcoming version of Windows CE.

•	Some features, such as updating through a cursor, are not available using the OLE DB/ODBC bridge.

•	If you use the Adaptive Server Anywhere OLE DB provider, ODBC is not required in your deployment.

  For more information, see Using ADO and OLE DB with Adaptive Server Anywhere.

Connectivity enhancements

•	Java connectivity improvements    If you use jConnect to connect to Adaptive Server Anywhere from a Java application, you can now take advantage of many of the features previously available only to ODBC and Embedded SQL applications, such as autostarting of database servers, and detailed control over network communications using communications parameters.

•	TCP/IP connectivity    Establishing a client/server connection over TCP/IP is now simpler. Clients no longer need to specify the port number when attempting to connect, even if the server is running on a port other than the default port number (2638). If the default port number is in use when a database server is started, the server aquires an unused port number from the operating system.

If you are trying to connect through a firewall (using UseUDP=NO), and if the database server is not running on port 2638, you must still specify a port number. For more information on this scenario, see Connecting across a firewall.

A command-line utility displays all Adaptive Server Anywhere database servers running TCP/IP on a network. For more information, see The Server Location utility.

•	SPX connectivity    You can use the SPX protocol for connecting to databases. This feature is particularly useful in Novell NetWare environments with IPX/SPX as the primary network protocol. SPX is recommended over IPX.

  For more information on SPX at the client, see CommLinks connection parameter. For information on SPX on the server, see -x command-line option. For network communication parameters that you can use with SPX, see Network communications parameters.

Performance enhancements

•	Dynamic cache sizing    On Windows 95/98, the size of the database server cache increases and decreases according to the load on the database server and the other demands on system memory. This feature removes the need for choosing an explicit cache size in many circumstances, and can also boost performance.

  For more information, see Using the cache to improve performance.

•	Indexing enhancements    Additional flexibility has been added to control the amount of information stored in indexes (the hash size) to improve index selectivity. Also, the architecture of primary and foreign key indexes has been altered.

For indexes on multiple columns, or for indexes on columns in which the first set of characters or digits are similar across many rows, control over hash size provides a way of increasing the selectivity of indexes, and so improving performance.

  For more information, see How indexes work, CREATE INDEX statement, and CREATE TABLE statement.

  For information on how to find the number of levels in an index, see sa_index_levels system procedure.

In previous releases, primary and foreign keys have had a single index automatically associated with them, which describes all primary key values and all the related foreign key entries. In some situations, this architecture lead to poor performance. The new index organization separates these indexes, which leads to improved performance in some situations.

  For more information on key indexes, see Using keys to improve query performance.

Your database must be unloaded and reloaded to take advantage of variable hash size indexes, and separate key indexes. Running the upgrade utility is not sufficient. For instructions, see Upgrade issues.

•	Separate storage for string extensions   The physical storage of values longer than 255 characters has been reorganized. The pages allocated for a table are now divided into two disjoint sets. The first set contain only rows. Where a column value in a row contains a string longer than 255 characters, only a prefix of the string (up to 255 characters) and a reference to a string extension are stored in the row. For strings longer than 255 characters, the string extensions are allocated in the second set of table pages. This change improves performance on queries requiring scans of tables storing long values because a sequential scan of a table only needs to traverse the pages in the first set.

Your database must be unloaded and reloaded to take advantage of this feature.

•	New database page-sizes    In addition to 1K, 2K, and 4K page sizes, you can now create databases with page sizes of 8K, 16K or 32K.

Large page sizes can improve performance in some cases, particularly for large databases. However, there are additional memory requirements with large page sizes, and so they should only be used after investigation of the costs and benefits.

  For more information, see The DBINIT command-line utility, and CREATE DATABASE statement.

  For information on the number of indexes per table and how it depends on page size, see Size and number limitations.

•	Optimizer tuning    You can use the OPTIMIZATION_GOAL option to instruct the optimizer to optimize for the time it takes to return the first row of a query, or the overall time it takes to return all rows. The default is to optimize for the first row. If you are using applications such as PowerBuilder DataWindow applications, which require a complete result set, you may wish to change this option setting.

  For more information, see OPTIMIZATION_GOAL option.

•	Optimizer enhancements    Further enhancements to the optimizer have been implemented to assist with performance of queries that use internal temporary tables and that use primary and foreign key indexes. These enhancements require no user action.

Miscellaneous enhancements

•	Larger numbers of users and other identifiers    Many identifiers in the system tables identifying database objects have been changed from SMALLINT to UNSIGNED INTEGER. This change increases the number of objects that can be held in a database without violating an absolute limit.

•	Inserting and exporting images and documents    Two new system external functions allow you to read and write the contents of files. These functions allow direct inserting of images, documents, and so on into tables from environments such as Interactive SQL.

  For more information, see Inserting documents and images into a table, xp_read_file system procedure, and xp_write_file system procedure.

•	New interface for external functions    Stored procedures and user-defined functions that reference external libraries now use a new interface. The new interface provides a wider range of operating systems (including UNIX), a wider range of data types, removes the restriction that returned data fit into 255 bytes, and supports NULL as a valid value for arguments. The older interface is still supported, but should not be used for new development work.

  For more information, see Creating procedures and functions with external calls.

•	START DATABASE, STOP DATABASE and STOP ENGINE statements    These statements were previously available only from Interactive SQL. They are now available from all applications.

  For more information, see START DATABASE statement, STOP DATABASE statement, and STOP ENGINE statement.

•	FIRST and TOP clause in updates and deletes    The FIRST and TOP clauses can be used to update or delete only the first one or more of any set of rows satisfying a WHERE clause.

  For more information, see DELETE statement, and UPDATE statement.

•	Explicit table locking    The LOCK TABLE statement allows direct control over concurrency at a table level, independent of the current isolation level.

  For more information, see LOCK TABLE statement.

•	Expressions in Transact-SQL outer joins    The *= and =* operators in a WHERE clause provide a way of specifying outer joins for users who wish to use the Transact-SQL dialect. In previous releases, only column names could be used in such joins. Now as long as each side of the join operator refers to a single table, any expression can be used in these joins. For example, the following query is now possible:

select *

from customer, sales_order

where substr( customer.id, 1, 1 ) *=

substr( sales_order.cust_id, 1, 1)

•	Cursors in stored procedures can reference variables    In stored procedures and user-defined functions, you can declare a cursor on a variable using the following syntax:

DECLARE cursor-name CURSOR USING variable-name

where variable-name is a string variable containing the SELECT statement for the cursor.

  For more information, see DECLARE CURSOR statement.

•	Additional database and server properties    The following properties have been added:

•	PageSize    The database server uses a single page size from startup until it is closed down. This page size is the maximum page size database that can be mounted by the database server. You can now obtain this page size using the PageSize server-level property function:

select property( 'PageSize' )

•	AppInfo    This function provides identification information for a client application. It is a connection property:

select connection_property( 'AppInfo' )

  For more information, see AppInfo connection parameter.

•	IsRuntimeServer    This function returns YES if the database server is a limited desktop runtime personal database server. Otherwise, it returns NO.

•	Log truncation points    Properties for replication-specific log offsets have been added. The properties LTMTrunc, RemoteTrunc, and SyncTrunc return the minimal confirmed log offset for the Replication Agent, SQL Remote, and MobiLink dbmlsync replication, respectively. These offsets are also known as truncation points because they indicate the point at which the transaction log can be truncated. The property CurrentRedoPos returns the current offset in the log file, where the next database operation is to be logged.

  For a complete list of property functions and information on how to access them, see Database properties.

•	Referential integrity checks before commit    A new system procedure (sa_check_commit) allows you to check for referential integrity conflicts before committing changes to a database.

  For more information, see sa_check_commit system procedure.

•	SQL function enhancements    The following functions have been added or enhanced.

•	REPLACE function    This new function replaces all occurrences of a substring with another substring.

  For more information, see REPLACE function.

•	LIST function enhancement    The LIST function now accepts an optional second value, which is the delimiter string that separates the list items.

  For more information, see LIST function.

•	Output redirection change    The output redirection functionality in Interactive SQL has been extended to include three new Interactive SQL statements and an Export option in the File menu.

You can now use an OUTPUT TO statement to redirect content from the Results pane to a new file. You can add an APPEND clause to append the content to the end of an existing file, or you can add a VERBOSE clause to include the content of the Messages pane with the output.

In earlier versions, output redirection in Interactive SQL could only be done with the symbols >#, >>#, >&, and >>&. You can still use these symbols, but the new Interactive SQL statements allow for more precise output and code that is easier to read.

  For more information, see Exporting query results in the ASA User's Guide.

•	Embedded SQL enhancements    A new function, db_string_ping_server, has been introduced to test that a database server can be located with a specified current connection string.

  For more information, see db_string_ping_server function.

•	New LOAD TABLE / UNLOAD TABLE format    A new format has been added to the UNLOAD TABLE statement to allow data to be output in BCP format and to the LOAD TABLE statement to allow the import of Adaptive Server Enterprise generated BCP out files containing blobs.

  For more information, see LOAD TABLE statement or UNLOAD TABLE statement.

•	Last default timestamp    The new global variable @@dbts returns a TIMESTAMP value that represents the last value generated for a column using DEFAULT TIMESTAMP.

  For more information, see Global variables.

•	Troubleshooting enhancements    On starting the database server, you can log operations executed by the server to a file using the -zr command-line switch. You can use the sa_server_option procedure to control the same behavior while the server is running.

  For more information, see sa_server_option system procedure, and -zr command-line option.

•	Archive backup on NetWare    The archive backup format is now supported on NetWare. Archive backups to tape require NetWare 5.

  For more information, see BACKUP statement.

•	Additional Java class     The java.math.BigInteger class is now fully supported.

  For a list of supported Java classes, see Java class data types.

•	Added filtering for dbtran    The command-line version of the Log Translation utility allows further filtering of the output.

  For more information, see Log translation utility options.

•	Faster table truncation    The TRUNCATE TABLE statement is much faster for version 7.0 databases, for tables with foreign keys.

•	Suppressing event log messages    If you run the database server as a Windows NT service, you can suppress event log messages using a registry entry.

  For more information, see Suppressing Windows NT event log messages.

•	Viewing current license information     The dblic utility now accepts an argument that allows you to view current license information for a server executable without starting the server.

  For more information, see The License utility.

•	SP_REMOTE_TABLES procedure     A new argument, tabletype, has been added to the sp_remote_tables stored procedure. This argument returns the remote table's type.

  For more information about the tabletype argument, see sp_remote_tables system procedure.


Behavior changes
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

 Behavior changes

This section lists deprecated and unsupported features, and behavior changes from previous versions of the software.

•	UltraLite behavior changes    For a list of UltraLite behavior changes, see Behavior changes.

Deprecated and unsupported features

This list includes features that are no longer supported and that impact existing applications.

•	Windows 3.x and Windows CE 2.0 no longer supported    Windows 3.1 and Windows 3.11 are no longer supported. Windows CE 2.0 is no longer supported.

•	DDE protocol no longer supported    The DDE protocol was used to communicate from 16-bit Windows 3.x applications to a Windows 95/98 database server on the same machine. It is no longer required: Windows 3.x applications based on older versions of the software can use TCP/IP to communicate with the version 7.0 database server.

•	IPX protocol deprecated    Although communications using IPX are still supported in the present release, it is highly recommended that you use the SPX protocol instead. The communications parameters are the same as for IPX, and performance is better. Support for IPX will be dropped in a future release.

By default, both the database server and the client software do not start the IPX protocol unless you instruct it to do so explicitly using the -x command-line option or the CommLinks connection parameter. The SPX protocol is started by default.

  For information on using SPX from the client side, see CommLinks connection parameter. For information on using SPX from the server side, see -x command-line option.

•	NUMBER function deprecated    Although it is supported in the current release, the NUMBER function will not be supported in future versions of Adaptive Server Anywhere.

•	Deprecated network communication parameters     The Broadcast and CommAutoStop communication parameters are still allowed, but have no effect. They will not be supported in future versions of Adaptive Server Anywhere.

•	No dbclient compatibility executable    In version 6, the dbcli6.exe command-line utility provided easier compatibility with version 5 client connection methods. There is no comparable utility in version 7.

Behavior changes

This list includes behavior changes in existing features that may impact applications or have an impact during development or database management.

•	Interactive SQL changes    The new version of Interactive SQL has some changes from previous versions. As it is an interactive tool, most do not need documentation.

The supported formats for INPUT and OUTPUT statements have changed, and now include:

•	INPUT    ASCII, DBASE, DBASEII, DBASEIII, EXCEL, FIXED, FOXPRO, LOTUS

•	OUTPUT    ASCII, DBASE, DBASEII, DBASEIII, EXCEL, FIXED, FOXPRO, HTML, LOTUS, SQL

•	Server name space change    It is now disallowed for more than one database server with the same name to be running on TCP/IP anywhere on the network. Previously, multiple servers with the same name were allowed as long as they were on separate ports.

•	No separate network ports library    There is no longer a client-side library named dbport6.dll. The network communication functionality that was in this DLL is now included in the client libraries.

For clients using IPX, a separate DLL is still required (dbipx7.dll). This library is not required if you are using the SPX protocol, which is now recommended over SPX/IPX networks.

•	Path settings    If you use the command-line utilities and you have Adaptive Server Anywhere 6.0 installed as well as Adaptive Server Anywhere version 7.0, you must ensure that the version 7.0 directory occurs ahead of the Adaptive Server Anywhere 6.0 directory in your path. Otherwise, the Version 6 application starts, rather than the version 7.0 application.

•	Connection behavior change    Same-machine connections use a shared-memory communication protocol. In version 7, connections over shared memory are attempted only if CommLinks is not specified in the connection string or DSN. In previous releases, a connection string containing the following parameters:

eng=myengine;links=tcpip

would attempt to find myengine over TCP/IIP, and if this failed would attempt a shared memory connection. In version 7, no shared memory link is attempted.

  For more information on the current behavior, see CommLinks connection parameter.

•	Mirrored logs deleted when DELETE_OLD_LOGS is ON    Previously, any mirror of an old transaction log was not deleted, although the primary copy of the old transaction log was deleted.

•	ODBC SQLDescribeCol behavior    A SQLDescribeCol call on the @@identity field now returns SQL_BIGINT. In earlier versions, it returned SQL_INTEGER.

•	ODBC autocommit uses CHAINED option    In previous releases, the autocommit behavior of the ODBC driver was independent of the CHAINED database option. The driver itself carried out the COMMIT after each operation.

Using autocommit behavior from ODBC now changes the CHAINED option for the connection.

Change rolled back
  As a result of customer feedback, this change has been rolled back. in 7.0.1 and later. ODBC autocommit is again independent of the CHAINED option, and therefore setting autocommit to ON does not lead to a COMMIT after each operation in a procedure.

•	Update constraints    A new ANSI_UPDATE_CONSTRAINTS option has been added. Setting this option to CURSORS, or STRICT, restricts updates to those allowed by the ANSI standard. Setting this option to OFF, which is the historical behavior, allows a greater range of updates.

  For more information, see ANSI_UPDATE_CONSTRAINTS option, and UPDATE statement.

•	Identifier length limit    Long identifiers are treated more consistently than in the past. Identifiers longer than 128 bytes were sometimes accepted and sometimes not, depending on the type of database object being named. Now any attempt to define identifiers longer than 128 bytes reports an error.

  For more information, see Identifiers.

•	Windows CE installation    With the dropping of support for Windows CE 2.0, installation behavior has been changed. The Adaptive Server Anywhere DLLs are no longer placed in the Windows directory; instead they are installed under the Adaptive Server Anywhere installation directory selected by the user.

The ASA_ prefix identifying Adaptive Server Anywhere DLLs no longer serves a purpose, and has been dropped.

•	jConnect connections    If you use the REMOTEPWD field to connect via jConnect to a named database on an Adaptive Server Anywhere database server, you must assign the field in a different manner for jConnect version 4.2 and above, included with this software.

  For more information, see Supplying a URL for the server.

•	User-defined errors    Within procedures and triggers, you can declare exceptions in the range 99000 to 99999 as user-defined errors in compound statements. You can use the SIGNAL statement to handle these errors.

  For more information, see BEGIN... END statement, and SIGNAL statement.

•	LOAD TABLE and UNLOAD TABLE security    A database server command-line option has been added to control the permissions required to execute the LOAD TABLE and UNLOAD TABLE statements.

  For more information, see -gl command-line option.

•	@@identity in triggers    If a table (T1) with an autoincrement column has an insert trigger which causes an insert into a second table (T2) also having an autoincrement column, it was not previously possible to obtain the autoincrement value assigned for T1 after the insert had completed. At that point, the value of @@identity would be the value assigned to T2. The behavior of @@identity has been altered to make the value accessible.

  For the new behavior of @@identity within triggers, see @@identity global variable.

•	Starting the debugger    The command to start the debugger from a command prompt has changed to dbprdbg.

•	Embedded SQL DECL_FIXCHAR    In previous releases, the SQL preprocessor converted a type DECL_FIXCHAR to an array. For example, DECL_FIXCHAR(12) was converted to char name_fixchar[12].

In the current release, the SQL preprocessor converts DECL_FIXCHAR declarations to DECL_FIXCHAR(12) name_fixchar, and references are of the form name_fixchar.array[i].


Upgrade issues
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

 Upgrade issues

Adaptive Server Anywhere includes new features affecting the database server, client/server communications, administration utilities, and synchronization setups using both SQL Remote and MobiLink.

 Standard upgrade precautions

 Database server compatibility

 Client/server compatibility

 Adaptive Server Anywhere debugger

 Command-line tools


Standard upgrade precautions
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

        Upgrade issues

 Standard upgrade precautions

There are several precautions you should take before upgrading any application, and these apply to Adaptive Server Anywhere upgrades just as to any other software.

•	Check the behavior changes    Before upgrading, you should confirm that none of the documented behavior changes in Version 7 affect your application.

•	Test    You should test your application in a Version 7 environment thoroughly before upgrading any applications in production use.

•	Backup    You should back up your existing software and data before upgrading. In addition, as recovery cannot happen across a database upgrade, you should make a backup after upgrading to ensure recoverability going forward.

•	Test your upgrade procedure    If you are upgrading many end users, test your upgrade procedure carefully before carrying it out.

SQL Anywhere is used in so many different configurations that no upgrade guidelines can be guaranteed for all cases.


Database server compatibility
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

        Upgrade issues

 Database server compatibility

Adaptive Server Anywhere database servers work with older databases. There is no need to upgrade your database in order to continue working with existing applications.

New features in version 7.0 are of the following kinds:

•	New features that require no change to your database. These are features such as dynamic cache resizing that have no database options, system table changes, or database storage changes associated with them.

•	New features that rely on new database options or system table changes, such as scheduling and event handling, or using the version 7 debugger. To access these features you must upgrade your database. You can upgrade a database using the Upgrade utility or the ALTER DATABASE statement.

•	New features that rely on a changed file format for the database. To use any of these features you must unload and reload your database.

In version 7.0, these features include the following:

•	Variable hash size indexes

•	Separate primary and foreign key indexes

•	Reorganized storage for blobs.

•	GLOBAL DEFAULT AUTOINCREMENT columns.

The database storage reorganization enhances the performance of some operations.

Upgrading a database

Upgrading a database adds system tables to support new features such as event handling, and also adds new database options.

♦ To upgrade a database (command line):

1	Make a backup of your database.

2	Ensure that you have exclusive access to the database to be upgraded.

3	Execute the dbupgrad command-line utility against the database:

dbupgrad -c "connection-string"

The connection-string must connect to the database to be unloaded, with DBA authority.

4	Shut down the database and archive the transaction log, before using the upgraded database.

Unloading and reloading a database

If you do not want to change the page size or other characteristics of your database, unloading and reloading can be done in a relatively straightforward manner.

♦ To unload and reload a database:

1	Make a backup of your database.

2	Ensure that you have exclusive access to the database to be unloaded and reloaded.

3	Execute the dbunload command-line utility using the -ar command-line switch to create a new database.

dbunload -ar -c "connection-string"

The connection-string must connect to the database to be unloaded, with DBA authority. This command replaces the existing database with an upgraded database.

4	Shut down the database and archive the transaction log, before using the reloaded database.

If you want to change the characteristics of the database, the procedure is more involved. For more information, see Designing rebuild and extract procedures.

Note

To use the -ar command-line switch, you must be connecting to a personal server, or to a network server over shared memory.


Client/server compatibility
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

        Upgrade issues

 Client/server compatibility

Version 6.0.0, 6.0.1, 6.0.2, and 6.0.3 database servers support connections from Version 7 clients. Version 7 database servers support connections from version 6.0.0, 6.0.1, 6.0.2, or 6.0.3 client applications.


Adaptive Server Anywhere debugger
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

        Upgrade issues

 Adaptive Server Anywhere debugger

The Adaptive Server Anywhere debugger included in this software can only debug databases that have been upgraded to version 7.0. Running the Upgrade utility is sufficient: there is no need to unload and reload the database.


Command-line tools
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 3.  Adaptive Server Anywhere Version 7.0

        Upgrade issues

 Command-line tools

If you have both Adaptive Server Anywhere version 6 and version 7.0 on your machine, you must pay attention to your system path when using command-line tools. Setup adds the version 7.0 executable directory to the end of your system path. If your Adaptive Server Anywhere Version 6 executable directory is ahead of it in your path and you enter a dbinit command, for example, you will use the Version 6 tool, and consequently create a Version 6 database.

♦ To use the version 7.0 command-line tools:

•	Change to the version 7.0 executable directory before executing your command,

or

Modify your system path so that the version 7.0 executable directory is ahead of the Adaptive Server Anywhere Version 6 executable directory.


CHAPTER 4.  Adaptive Server Anywhere 6.0.3
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 4.  Adaptive Server Anywhere 6.0.3

About this chapter

This chapter provides an overview to the new features and behavior changes introduced in Adaptive Server Anywhere 6.0.3.

 New features

 Behavior changes

 Upgrade issues


New features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 4.  Adaptive Server Anywhere 6.0.3

 New features

In addition to bug fixes, Adaptive Server Anywhere version 6.0.3 includes new features in both the software and the documentation.

•	SQL Remote enhancements    For a list of SQL Remote enhancements in this release, see New features in version 6.0.3.

•	UltraLite enhancements    For a list of UltraLite enhancements in this release, see What's new in 6.0.3.

•	Combined stored procedure and Java debugger    The Java debugger that was provided in previous releases has been upgraded. The new version of the debugger is able to debug not only Java classes within the database, but also SQL stored procedures and triggers.

  For information on how to use the debugger, see Debugging Logic in the Database.

•	Cache size changes    The default initial cache size for the database server is now computed from the database file sizes and the machine physical memory. A specific initial cache size can be specified as a percentage of the physical memory of the machine rather than as an absolute amount, for easier deployment to a variety of environments. On Windows NT, and Windows 95/98, the cache grows if it is exhausted or if Java requires more memory, or if an internal algorithm suggests a performance benefit. Previously, the server would give a Fatal Error: dynamic memory exhausted error if cache was exhausted.

  For more information, see Cache size.

•	Read-only databases    You can designate a database as read only when you start a database server. This feature makes deployment of databases on read-only media, such as CD-ROMs, more straightforward.

The ReadOnly database property returns ON for read-only databases, and OFF for databases that are not being run in read-only mode.

  For more information on read-only databases, see -r command-line option.

•	Computed column extensions    New flexibility has been added to computed columns. You can now add computed columns to non-empty tables, and change the expression associated with a computed column. Computed columns are recalculated in a number of circumstances to ensure that the values are reliable.

  For more information, see Defining computed columns, and When computed columns are recalculated.

  For information on syntax, see ALTER TABLE statement.

•	Support for the euro    Collations have been added that include the euro currency symbol. These collations are the 1252LATIN1 and ISO9LATIN1 collations.

  For more information, see The 1252LATIN1 collation, and The ISO9LATIN1 collation.

•	Additional collations    Other collations have been added to the list of supplied collations, including 852POL (OEM Code Page 852 (Latin 2), with Polish ordering), 1250POL (Windows Latin2 code page 1250 with Polish ordering), 1250Latin2 (Windows Latin2 Code page 1250). 932JPN (Japanese), 936ZHO (similar to EUC_CHINA), and 950TAI (similar to EUC_TAIWAN).

  For a complete list, see Supplied collations.

•	New Windows CE platforms    The SH4 and ARM processors are now supported under Windows CE 2.1x.

  For a list of supported devices, chips, and operating system versions, see System requirements.

•	ALTER TABLE extensions    The ALTER TABLE statement has been extended to provide SQL/92-compliant clauses to set and drop defaults on columns. These clauses are an alternative to the existing MODIFY clause.

ALTER column-name SET DEFAULT default-value

| ALTER column-name DROP DEFAULT

  For more information, see ALTER TABLE statement.

•	LOAD TABLE extensions    You can now load specific columns of a table using the LOAD TABLE statement. A new CHECK CONSTRAINTS option has been introduced to address rebuild issues.

  For more information, see LOAD TABLE statement.

•	Easier connections across firewalls    A set of communications parameters has been introduced to allow easier connections across firewalls.

  For more information, see Connecting across a firewall.

•	BACKUP statement extended    The MATCH keyword has been introduced to allow renaming of the backup copy of the transaction log to a filename of the form YYMMDDnn.log. If you use this keyword, you can execute the same statement multiple times without writing over data.

  For more information, see BACKUP statement.

•	Fast table truncation    A faster form of the TRUNCATE TABLE statement is provided for tables that meet certain criteria. As this fast truncation carries out a COMMIT as a side effect, a new option has been introduced to disable it. By default, the option is set to ON; it should be set to OFF for maximum compatibility with older versions of the software.

  For more information, see TRUNCATE_WITH_AUTO_COMMIT option, and TRUNCATE TABLE statement.

•	Easier unload and reload    The dbunload utility has been enhanced (-ar command-line option) to allow a single-step unload and reload of a database that can be used whether or not your database is involved in replication.

  For more information, see Unload utility options, and Designing rebuild and extract procedures.

•	Temporary file location    The database server checks for a new environment variable, ASTMP, when deciding on the location of the temporary file. This allows you to use directories other than system temporary directories for the temporary file.

  For more information, see ASTMP environment variable.

•	New system procedures    New system procedures allow DBA users to override some database server command-line options (sa_server_option), and to flush the database server cache (sa_flush_cache).

  For more information, see System and catalog stored procedures.

•	Character set translation tuning    You can control the application locale used in character set translation for an individual connection using the new Charset connection parameter.

  For more information, see CharSet connection parameter.

•	Re-organized Performance Monitor statistics    The statistics made available to the Windows NT Performance Monitor have been organized into areas. Some statistics have been added, and ones of little use have been removed.

  For a list of available statistics, see Monitoring database statistics from the Windows NT 4.0 Performance Monitor.

•	Database properties from the utility database    You can now execute SELECT statements, with no tables, against the utility database. This is primarily of use for retrieving database and connection properties.

  For more information, see Using the utility database.

•	New database properties    The following properties are available using the property function.

•	IsNetworkServer    Returns YES if connected to a network database server, and NO if connected to a personal database server.

  For more information, see Server-level properties.

•	DefaultCollation    You can use the new DefaultCollation property to find the default collation to be used when creating a database.

  For more information, see Finding the default collation, and Server-level properties.

•	MultiByteCharSet    You can use the MultiByteCharSet database property to determine whether a database is using a multi-byte or single-byte collation.

  For information on this property, see Database-level properties.

•	Support for some JDBC 2.0 functions in internal JDBC    The internal server-side JDBC driver now supports functions from the JDBC 2.0 interface. Server-side Java applications can now use features such as such as scrollable, updatable result sets and batch updates. A side effect is that you can now access result sets from Java methods from Interactive SQL.

  For more information, see Server-side JDBC features.

•	Updating Java columns    The restriction that set methods in UPDATE statements must return a non-void data type has now been removed. For example, the following statement now works, even if the method setName() returns void.

UPDATE Product

SET JProd.setName( 'Tank Top' )

WHERE ID = 302

  For more information, see Updating Java objects.

•	Using Java methods that return void as SQL expressions    You can use Java methods that return void as SQL expressions. These methods return this, that is, the object itself, to the SQL calling environment.

  For more information, see Return value of methods returning void.

•	Using the main method in Java classes    You can now execute a main method of a Java class from SQL.

  For more information, see The main method.

•	User-defined functions using Java classes    You can wrap a Java method in a SQL user-defined function.

  For more information, see CREATE FUNCTION statement.

•	Extensions to stored procedures using Java methods    You can use OUT and INOUT parameters in stored procedures that are wrappers for Java methods.

  For more information, see Returning values from Java via stored procedures.

•	Multi-threaded Java classes in the database    Support has been added for the package java.lang.Thread.

  For more information, see Using threads in Java applications.

  For a list of all supported packages, see Supported Java packages.

•	File access from Java    Support has been added for all the classes in the package java.io, including those that enable file access from classes in the database. For security reasons, a new option has been introduced, which must be set by the DBA to enable this feature.

This feature is supported on Windows NT only.

  For more information see JAVA_INPUT_OUTPUT option.

•	CONVERT function extensions    The date and time styles supported by the CONVERT function have been extended.

  For more information, see CONVERT function.

•	Database server startup dialog    On 32-bit Windows operating systems, if you start a database server with no arguments, a window appears where you can specify a database file and additional command-line parameters.

  For more information, see Starting the server.

•	Console utility enhancements    The Console utility has been enhanced to provide more information concerning connections and server performance.

  For more information, see The Console utility.

•	dbtran utility enhancements    The dbtran command-line utility permits filtering of the transaction log operations to isolate subsets of operations.

  For more information, see The DBTRAN command-line utility.

•	dblog utility enhancements    The dblog utility now displays additional summary information, including offset information.

  For more information, see The DBLOG command-line utility.

•	dbspawn utility enhancements    The dbspawn utility has a -f command-line switch to force a server to start even if one is already running. This switch uses a ForceStart connection parameter, used only by the db_start_engine Embedded SQL function.

  For more information, see The Spawn utility, and db_start_engine function.

•	Replication Agent runs as a daemon    On UNIX operating systems, you can run the Replication Agent as a daemon by supplying the -ud command-line option.

  For more information, see The Log Transfer Manager.

•	TCP/IP local machine connections    You can now use TCP/IP to connect from a client application to a database server running on the same machine. This is of most use for Windows 3.x applications connecting to a Windows 95/98 database server on the local machine, where TCP/IP connections are faster than the DDE protocol, which has been the recommended method until now.

 New documentation features


New documentation features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 4.  Adaptive Server Anywhere 6.0.3

        New features

 New documentation features

The printed documentation is not updated with each maintenance release. The online documentation is updated with each maintenance release. The online documentation is more current and more reliable than the printed documentation.

In addition to documenting the new features, several changes have been made to the documentation to improve it, or to provide information that was previously missing.

•	HTML Help format    Online documentation for Windows NT and Windows 95/98 is now available in HTML Help format. HTML Help is the standard online documentation format with new versions of Windows. Sybase Central documentation remains in Windows Help (WinHelp) format.

By default, the HTML Help version of the online documentation is installed if you have Internet Explorer version 4.0 or later installed, or if you have HTML Help already installed. Otherwise, WinHelp format is installed. The content of the online documentation is the same in each format.

If you prefer to use WinHelp, you can select the option to do so from the Adaptive Server Anywhere setup program.

•	Corrections    A major review of the documentation for technical correctness has led to many small changes throughout the manuals.

•	Backup and Recovery rewrite    The Backup and Recovery chapter in the User's Guide has been rewritten.

  See Backup and Data Recovery.

•	International Languages and Character Sets rewrite    Over recent releases, many features have been added for better national language support. The chapter has been rewritten with a focus on using locales to manage language and character set issues.

  See International Languages and Character Sets.

•	Additional deployment information    Material describing how to create and use a silent install of Adaptive Server Anywhere has been added to the chapter on deployment.

  See Using a silent installation for deployment.


Behavior changes
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 4.  Adaptive Server Anywhere 6.0.3

 Behavior changes

The following are behavior changes from previous versions of the software.

•	SQL Remote behavior changes    For a list of SQL Remote behavior changes, see Behavior changes.

•	UltraLite behavior changes    For a list of UltraLite behavior changes, see Behavior changes.

•	Debugger replaced    The Java debugger included in previous versions of this software has been replaced with a more comprehensive and powerful stored procedure and Java debugger.

•	Cache sizing    New cache sizing options on the database server command line introduce behavior changes on existing database server command lines.

For database server command lines with no -c option, the default cache size is now chosen based on database file sizes and machine capabilities, instead of being a fixed value.

For database server command lines with a -c option, the cache size on Windows NT and Windows 95/98 may increase depending on the load on the server and the physical memory of the machine.

•	Adding columns with default values    When an added column has a default value, the entire column is populated with the default. In previous releases, the column was populated with NULL.

•	Permissions of referential integrity actions    When changes are made to a primary table, referential integrity actions such as cascading deletes or updates can take place on a secondary table. These actions are implemented using system triggers. The triggers now execute with the permissions of the owner of the secondary table. Previously, they executed with permissions of the owner of the primary table. The new behavior means that cascaded operations can take place between tables with different owners, without additional permissions having to be granted.

•	datediff, MONTHS, and YEARS functions    The number of months between two dates is now calculated as the number of first-of-the-months between the dates. For example, the difference between January 25 and February 2 is 1; the difference between January 1 and January 31 is 0. The number of years is now calculated as the number of first-of-the-years between the dates.

This changes the results of these functions by one number, in some cases. The change was made for compatibility with Adaptive Server Enterprise.

For the smaller time units there are overflow values to the DATEDIFF function that are now imposed. Previous versions of the software gave incorrect answers if the limit was exceeded.

  For a full description, see DATEDIFF function.

•	Starting the database server with no arguments    In previous releases, a command such as the following:

dbeng6

would display the usage text for the database server. On 32-bit Windows operating systems, it now displays a window where you can specify a database file and additional command-line parameters.

•	Default page size    The default page size for databases is now 2048 bytes. This choice is a better choice for many users.

•	Default database collation    The default collation used when creating databases has changed. The default depends on your operating system settings.

  For information on how to find the default collation, see Finding the default collation.

•	SQL Preprocessor default collation    If no collation is explicitly specified, the Embedded SQL Preprocessor now uses locale information to choose a default collation. If the locale information is unavailable, then 850LATIN1 will be used. The collation used is reported following the banner. Previous behavior was to use 850.

  For information on the preprocessor, see The SQL preprocessor.

•	Enforced server name length    The server name is checked on startup, and is truncated to a maximum value of 40 characters. On NetBIOS, it is truncated to 16 characters. From the client side, the value of the EngineName parameter is also truncated to 40 characters.

  For more information, see EngineName connection parameter.

•	Agent connection parameter    The Agent connection parameter behavior has been changed. The meaning of this parameter changed from version 5 to version 6, as the need for the dbclient executable was removed. The parameter meaning has changed to be more useful in a Version 6 environment.

  For more information, see Agent connection parameter.


Upgrade issues
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 4.  Adaptive Server Anywhere 6.0.3

 Upgrade issues

Version 6.0.3 software works with older databases. There is no need to upgrade your database in order to continue working with existing applications. There is no need to upgrade your database in order to use many of the new features, including new SQL statements and statement extensions.

If you wish to use new database options, you must upgrade your database. You can upgrade a database using the Upgrade utility or the ALTER DATABASE statement.

Version 6.0.0, 6.0.1, and 6.0.2 database servers do not support connections from Version 6.0.3 clients that set the new UseUDP communication parameter to OFF (ON is the default). Otherwise, Version 6.0.0, 6.0.1, and 6.0.2 database servers support connections from Version 6.0.3 clients. Version  6.0.3 database servers support connections from version 6.0.0, 6.0.1, or 6.0.2 client applications.

  For more information, see The Upgrade utility, and ALTER DATABASE statement.

  For information on SQL Remote upgrade issues, see New features in version 6.0.3. For information on UltraLite upgrade issues, see Upgrade issues.


CHAPTER 5.  Adaptive Server Anywhere 6.0.2
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 5.  Adaptive Server Anywhere 6.0.2

About this chapter

This chapter provides an overview to the new features and behavior changes introduced in Adaptive Server Anywhere 6.0.2.

 New features

 Behavior changes


New features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 5.  Adaptive Server Anywhere 6.0.2

 New features

In addition to bug fixes, Adaptive Server Anywhere version 6.0.2 includes new features in both the software and the documentation.

Cross references
The printed documentation is not necessarily updated with each maintenance release. Cross references in this section may not be valid in the printed documents. For current information, see the online documentation.

•	UltraLite deployment option    UltraLite databases for small devices such as the PalmPilot and Windows CE computers can be developed with this version of the software.

  For information, see the book UltraLite Developer's Guide.

•	Performance enhancements    Many aspects of Adaptive Server Anywhere performance are improved in this version of the software.

•	Backup and Restore SQL statements    Adding BACKUP and RESTORE as SQL statements provides server side backup and automation of backups using SQL scripts.

The BACKUP statement provides direct backup to tape.

  For more information, see BACKUP statement.

•	Security features    New security features have been added.

•	Auditing    Database administrators can keep track of activity performed on a database by turning on the AUDITING option. The record of activities is kept in the transaction log. By turning on auditing, you increase the amount of data saved in the transaction log to include login attempts, accurate timestamps of all events, all permissions checks, and all actions requiring DBA authority.

  For more information, see Auditing database activity.

•	Minimum password length    Database administrators can specify a minimum password length, to discourage easily discovered passwords.

  For more information, see MIN_PASSWORD_LENGTH option.

•	Locating servers    A command-line utility is provided for troubleshooting connections.

  For more information, see The Ping utility.

•	Starting databases from jConnect connections    Database connections over TDS, including connections from Java applications over jConnect, can start a database on a server.

  For more information, see Supplying a URL for the server.

•	getObject and setObject methods from Java client applications    From Java applications, you can now retrieve objects from a table and insert objects into a table without the explicit serialization and deserialization required in previous releases.

  For more information, see Inserting and retrieving objects.

•	ODBC 3.51    The ODBC driver has been updated to ODBC 3.51. This version of ODBC includes support for Unicode applications.

  For more information, see ODBC conformance.

•	Control of allowed JOIN syntax    In previous releases, some multi-table queries have been allowed that have ambiguous join clauses. In the present release, you can set an option to disallow such queries.

  For more information, see EXTENDED_JOIN_SYNTAX option.

•	Administration utility enhancements    Options have been added to the administration utilities to provide additional features.

•	dbtran    If you use the new -d switch, dbtran puts each operation as it occurs in the transaction log file. This makes transaction log output easier to read. This has been added primarily for auditing purposes.

  For more information, see The DBTRAN command-line utility.

•	dbtran    You can run dbtran against a running database server instead of against a log file. This feature has been added to increase the security of the transaction log—there is now no need to access the transaction log directly.

  For more information, see The DBTRAN command-line utility.

•	LTM logging    New command-line options allow you to tune message logging from these utilities.

  For more information, see The Log Transfer Manager.

•	New LTM options    New command line options enable you to replicate only backed up transactions (backup_only), and to shut down as soon as all data is replicated (continuous).

  For more information, see The LTM configuration file.

•	SQL Remote enhancements    For a list of SQL Remote enhancements in this release, see New features in version 6.0.2.

 New documentation features


New documentation features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 5.  Adaptive Server Anywhere 6.0.2

        New features

 New documentation features

In addition to documenting the new features, some additional material has been added to the online documentation.

•	Security features    A chapter has been added, which collects together information on Adaptive Server Anywhere security features, including auditing.

  This chapter is Keeping Your Data Secure.

•	Using subqueries    A chapter has been added, which describes in more detail how to use subqueries.

  This chapter is Selecting Data using Subqueries.

•	Improved reference material    Each function and data type has been separated into its own topic. This provides quicker access to this material.

Online documentation more recent than printed
The printed documentation may not be updated with each maintenance release. The online documentation is updated with each maintenance release.


Behavior changes
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 5.  Adaptive Server Anywhere 6.0.2

 Behavior changes

The following are behavior changes from previous versions of the software.

•	Permissions required to debug Java    In order to use the Java debugger, you must now either have DBA authority, or be granted membership in the SA_DEBUG group. The SA_DEBUG group does not exist in databases created prior to 6.0.2, and in these older databases any user can use the Java debugger. The SA_DEBUG group was added to close a potential security hole.

  For more information, see Requirements for using the debugger.

•	Default packet size change    The default packets size for client/server communications has been changed from 512 bytes to 1000 bytes. This change improves performance for multi-row fetches and fetches of large rows. It also increases the memory requirements.

  For more information on packet size, see CommBufferSize connection parameter, and -p command-line option.


CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

About this chapter

This chapter provides an overview to the new features introduced in Adaptive Server Anywhere 6.0.1

 Adaptive Server Anywhere for Windows CE

 Remote data access

 Character set translation

 New Java features

 Additional new features

 New features in SQL Remote

 Behavior changes


Adaptive Server Anywhere for Windows CE
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 Adaptive Server Anywhere for Windows CE

The Microsoft Windows CE operating system developed for handheld computing devices and embedded devices custom-built to carry out a specific task.

Starting with Version 6.0.1, Adaptive Server Anywhere is available for Windows CE. Versions 2.0 and up of the CE operating system are supported.

The Windows CE version of Adaptive Server Anywhere has the following characteristics:

•	Full-featured database    All SQL features in other versions of Adaptive Server Anywhere are available in the Windows CE version, including transaction processing, referential integrity actions, procedures and triggers, and so on.

The Java features and the remote data access features are not available in Windows CE.

•	Administer from your desktop    When running Windows CE on a device that can be attached to a network or directly to a PC, you can administer your Windows CE database from a Sybase Central running on the PC.

•	ODBC and Embedded SQL applications    You can use either of these interfaces to develop client applications.

•	SQL Remote replication    The SQL Remote file link is implemented to be compatible with Windows CE ActiveSync synchronization.

 Desktop management of Windows CE databases

 Creating Windows CE databases

 SQL Remote and Windows CE

 Windows CE database features

 Using ODBC with Windows CE


Desktop management of Windows CE databases
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

        Adaptive Server Anywhere for Windows CE

 Desktop management of Windows CE databases

Instead of the personal database server, the network database server (dbsrv6.exe) is the version supplied for Windows CE. The network server supports communications over a TCP/IP network link.

The usual client/server arrangement has the database server running on a machine with more power and resources than the client applications. Clearly, this is not the case with Windows CE; instead, the less powerful machine is running the database server.

The advantage to supplying a network server on Windows CE is that you can run database applications on your desktop computer to carry out tasks on your Windows CE database. For example:

•	You can use Sybase Central on your desktop PC to manage your database

•	You can use Interactive SQL on your desktop to load and unload data, and carry out queries.

•	You can use InfoMaker to produce reports.

The Windows CE database server does not start the TCP/IP network link unless it is explicitly requested:

dbsrv6 -x tcpip ...


Creating Windows CE databases
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

        Adaptive Server Anywhere for Windows CE

 Creating Windows CE databases

If you have Windows CE services installed on your Windows 95 or Windows NT desktop, you get an option to create a Windows CE database when you create a database from Sybase Central (Windows Edition). Sybase Central enforces the requirements for Windows CE databases, and optionally copies the resulting database file to your Windows CE machine.


SQL Remote and Windows CE
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

        Adaptive Server Anywhere for Windows CE

 SQL Remote and Windows CE

If you have Windows CE services installed on your Windows 95 or Windows NT desktop, you get an option to create a Windows CE database when you extract a remote database from Sybase Central (Windows Edition). Sybase Central enforces the requirements for Windows CE databases, and optionally copies the resulting database file to your Windows CE machine.

From within the Sybase Central (Windows Edition) Utilities folder, if you have Windows CE services installed, you have an option to set up SQL Remote for ActiveSync synchronization. This sets your folder for FILE message link messages to be the ActiveSync folder. When you dock your Windows CE machine to your desktop machine, ActiveSync keeps the files in your desktop machine's ActiveSync folder synchronized with those in the Windows CE ActiveSynce folder.


Windows CE database features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

        Adaptive Server Anywhere for Windows CE

 Windows CE database features

The Windows CE version of the database software contains all the features found on other operating systems except for a few features that are not appropriate for Windows CE. The excluded features include the following:

•	Java in the Database

•	External procedures and functions

•	Only the network database server is supplied.

The following SQL statements are not supported on Windows CE:

•	ALTER DATABASE

•	ALTER SERVER

•	ALTER WRITEFILE

•	CREATE COMPRESSED DATABASE

•	CREATE DATABASE

•	CREATE EXPANDED DATABASE

•	CREATE EXTERNLOGIN

•	CREATE EXISTING TABLE and CREATE TABLE using the AT clause.

•	CREATE SERVER

•	CREATE WRITEFILE

•	DROP DATABASE

•	DROP SERVER

•	INSTALL JAVA

•	REMOVE JAVA

•	START JAVA

•	STOP JAVA


Using ODBC with Windows CE
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

        Adaptive Server Anywhere for Windows CE

 Using ODBC with Windows CE

There is no ODBC driver manager for Windows CE. This requires changes to the use of data sources compared to other Windows operating systems.

  For information on how to use ODBC data sources with Windows CE, see Using ODBC data sources on Windows CE.


Remote data access
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 Remote data access

Starting with Adaptive Server Anywhere Version 6.0.1, a single connection can access data from external data sources, as if it were stored on the local server. Supported external data sources include the following:

•	Adaptive Server Anywhere databases

•	Adaptive Server Enterprise databases

•	Oracle databases

•	IBM DB2 databases

•	Microsoft SQL Server Databases

•	Other ODBC data sources

Remote data access capabilities

There are several aspects to the remote data access features:

•	Carry out queries that join tables in external data sources to tables in the local data source, or to other external data sources.

•	Insert, update, and delete data in external data sources.

•	Send statements to a remote server in its native SQL syntax.

•	Migrate data from one server to another.

  For information about remote data access, see the chapter Accessing Remote Data and the chapter Server Classes for Remote Data Access. These chapters contain pointers to the relevant reference sections.


Character set translation
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 Character set translation

Character set translation has been added to translate strings automatically between different character sets as data is passed between client applications and the database server. This enables more flexibility in mixed character-set environments.

Character set translation can be carried out among character sets that represent the same characters, but at different values. There needs to be a degree of compatibility between the character sets for this to be possible. For example, character set translation is possible between EUC-JIS and Shift-JIS character sets, but not between EUC-JIS and OEM code page 850.

To enable character-set translation, you must start the database server using the new -ct command-line option. For example:

dbeng6 -ct asademo.db

Most of the character set translation features occur automatically, with little user intervention required.

  For a description of character set translation features, see Starting a database server using character set translation. For more detailed information, see Understanding character set translation.


New Java features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 New Java features

There are some changes made to the Java support. These include the following:

•	Updating Java fields    You can now use standard UPDATE syntax to update a field in a column of Java data type. The following statement was not supported before 6.0.1, but is now supported:

UPDATE Product

SET JProd>>unit_price = 16.00

WHERE ID = 302

Use of the EVALUATE function is no longer required.

•	Compressed jar files    You can now install compressed jar files and zip files into the database. However, you should not use the jar utility that comes with the Sun JDK. Other zip utilities do produce suitable files.

•	Result sets from Java procedures    You can wrap Java methods in a stored procedure, which can return a result set or multiple result sets to the calling environment.

  For information on this feature, see Returning result sets from Java methods.

•	Default internal connection    When a database connection is established for internal JDBC operations, it is now recommended that you use the following URL:

jdbc:default:connection

In version 6.0.0, an empty string was used to establish this connection. While the empty string does still work, it is deprecated. The new URL corresponds to the SQLJ1 proposed standard.


Additional new features
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 Additional new features

Several other features have been added to Adaptive Server Anywhere 6.0.1. These include the following:

•	jConnect 4.0    The version of jConnect included in this product has been updated to version 4.0.

•	Autostart connection parameter    This parameter prevents a personal server from starting if no network connection is successful.

  For a description, see AutoStart connection parameter.

•	MESSAGE statement    Extensions to the MESSAGE statement allow messages to be directed to the client, the server window, or a log file.

  For a description, see MESSAGE statement.

•	Message callbacks     Windows Embedded SQL applications can handle messages received from the server while a request is being processed by registering a message callback function.

  For more information, see Request management functions.

•	More control over operating system threads    A new database server command-line option (-gx) controls the number of operating system threads that are in use. The existing -gt option controls how many can be in use at one time, effectively controlling the number of CPUs that can be exploited.

  For more information, see The database server.

•	Connection property system procedures    Two additional system procedures provide alternative ways of querying connection information.

  For more information, see sa_conn_properties_by_conn system procedure, and sa_conn_properties_by_name system procedure.

•	NULLIF function    This provides an abbreviated form of the CASE expression. NULLIF compares the values of the two expressions. If the first expression equals the second expression, NULLIF returns NULL. If the first expression does not equal the second expression, NULLIF returns the first expression. The NULLIF function provides a short way to write some CASE expressions.

  For more information, see Miscellaneous functions.


New features in SQL Remote
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 New features in SQL Remote

Several features have been added to SQL Remote.

•	Minimized Message Agent    The Message Agent can be made to start with a minimized window using the -q command-line switch.

•	Message Agent request to resend messages    The point at which the Message Agent requests that a missing message be resent is now user-configurable using the -rp command-line switch.

  For information on these options, see The Message Agent and Tuning incoming message polling.

•	Cleaning the stable queue    For Adaptive Server Enterprise, the new -fq command-line option on the Message Agent assists administration by cleaning confirmed messages from the stable queue.

  For information, see The Message Agent.

•	Windows CE    For information on using SQL Remote with Windows CE, see SQL Remote and Windows CE


Behavior changes
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 6.  New Features in Adaptive Server Anywhere 6.0.1

 Behavior changes

This section describes behavior changes between version 6.0.0 and 6.0.1.

Java system table changes    The system tables used to record Java class information (SYSJAR, SYSJARCOMPONENT, and SYSJAVACLASS ) had SMALLINT primary keys. These data types have been altered to use INTEGER primary keys. This change allows more Java classes to be stored in a database, and more changes to the Java classes in the database.

This change takes effect for new databases and databases upgraded using the Upgrade utility from this or future releases.

Parentheses in -x command line    In previous releases, database server command lines using the -x command-line option have used the brace character ({) to surround options. The same applies to the string in the CommLinks connection parameter. For example:

dbsrv6 -x tcpip{Localhost=demo}

Existing command lines that use braces are still supported, but it is now recommended that you use parentheses to surround the options. Use of parentheses provides support for multi-byte character set identifiers in the database server command line and the CommLinks command-line parameter.

dbsrv6 -x tcpip(Localhost=demo)

ANSINULL default for Transact-SQL and jConnect connections    This has been changed to ON, which matches Adaptive Server Enterprise default behavior.

Database server -v command-line option    Prior to Version 6, this option produced verbose output to the transaction logs. This is obsolete, and -v is now used to supply version information.

Database server -gss command-line option    The behavior of the -gs server command-line option, used to set the stack size, was complicated. The -gs command-line option is now deprecated, and -gss provides the same functionality in a clearer way.

  For more information, see The database server.

Character set conversion in Interactive SQL    Formerly, when the CHAR_OEM_TRANSLATION option was set to DETECT, Interactive SQL would fetch the collation label from the database to determine whether or not OEM to ANSI character set translation would be turned on. If the collation label started with a string that indicated an ANSI code page, translation would be turned off. Otherwise it would be turned on. When the option was set to DETECT, Interactive SQL would display a message in the status window indicating the collation label of the database and the display translation setting.

The new behaviour is as follows. If the option is set to DETECT, Interactive SQL will obtain the CharacterSet connection property from the server. This is the character set that the server is using for sending all character strings on this connection. If this character set indicates an ANSI code page, then OEM to ANSI translation is turned off. Otherwise it is turned on. A new message is displayed, indicating the collation label of the database, the character set used for communication over this connection, and the display translation setting.


CHAPTER 7.  Upgrading Version 5 Applications
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

 CHAPTER 7.  Upgrading Version 5 Applications

About this chapter

The client/server communication protocol changed between SQL Anywhere versions 5 and Adaptive Server Anywhere version 6. This chapter provides upgrade instructions for those users upgrading from Version 5 to the Adaptive Server Anywhere 7.0 release.

This chapter describes upgrade procedures for users of SQL Anywhere Versions 5.0 and 5.5, and users of Watcom SQL 4.0.

 Understanding upgrades

 Review of SQL Anywhere Version 5 architecture

 Upgrading Embedded SQL applications

 Upgrading ODBC applications

 Using the compatibility library

 Capturing dbclient command-line information

 Upgrading databases

 Upgrading SQL Remote installations


Understanding upgrades
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Understanding upgrades

A database application and DBMS consists of several components. If your organization has a large SQL Anywhere installation, you may have many client machines, possibly running several applications, connected to more than one server.

When upgrading your system, you need to decide which components to upgrade, and in which order. This chapter guides you in making those choices and carrying out the upgrade.

SQL Anywhere Version 5
In this chapter, SQL Anywhere Version 5 refers to both versions 5.0 and 5.5 of SQL Anywhere.

  In order to upgrade existing applications to Adaptive Server Anywhere Version 7.0, it is helpful to understand how Version 6 and later connections work. For information, see Connecting to a Database.

 The components in your system

 Major upgrading issues

 When to upgrade your database

 Running more than one version of the software


The components in your system
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Understanding upgrades

 The components in your system

If you are currently running SQL Anywhere Version 5, you will have some or all of the following components in your system:

•	Application    Your application, aside from the SQL Anywhere components.

•	Connection parameters    SQL Anywhere Version 5.0 connection parameters. These may be assembled from an ODBC data source, or in some other way.

•	Driver manager    The ODBC driver manager, for ODBC applications.

•	ODBC driver    The SQL Anywhere Version 5 ODBC driver, for ODBC applications. For network applications, the ODBC driver is on the client machine.

•	Interface library    The SQL Anywhere Version 5 interface library is used by ODBC and Embedded SQL applications. For network applications, the interface library is on the client machine.

•	SQL Anywhere client    The dbclient.exe executable and its command line, for network applications. The command line may specify the server name, and a set of network communications parameters. It may be stored in a batch file or an ODBC data source Start Line parameter. For network applications, the SQL Anywhere client is on the client machine.

•	The database server    The SQL Anywhere Version 5 database server. For network applications, this may be on a separate machine from the client components.

•	The database    A SQL Anywhere Version 5 database. This is on the same machine as the database server.

Database upgrades not required
You do not need to upgrade your database in order to use the Version 6 or Version 7.0 database server with an existing application.


Major upgrading issues
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Understanding upgrades

 Major upgrading issues

The major issues in upgrading arise from the change in client/server communication protocol. This change means that the Version 5 interface library is not able to communicate with a Version 7.0 database server.

To help with this issue, Adaptive Server Anywhere Version 7.0 includes a compatibility library. This DLL allows communications to both Version 5 and Version 7.0 database servers.


When to upgrade your database
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Understanding upgrades

 When to upgrade your database

There is no need to upgrade the database itself when you start using Version 7.0. You need to upgrade the database only if you wish to take advantage of Version 7.0 features.

  For more information on upgrading databases, see "Upgrade issues" on page 15.


Running more than one version of the software
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Understanding upgrades

 Running more than one version of the software

The Adaptive Server Anywhere software is designed so that both separate versions can be run if you install them in separate directories. This is the default behavior of the Adaptive Server Anywhere installation program.


Review of SQL Anywhere Version 5 architecture
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Review of SQL Anywhere Version 5 architecture

This section reviews the architecture for SQL Anywhere Version 5 applications connecting to a SQL Anywhere Version 5 database.

This information helps you to understand the changes needed when upgrading to Version 7.0. If you are familiar with SQL Anywhere Version 5 architecture, you do not need to read this section.

 Standalone components for Version 5 (Embedded SQL)

 Standalone components for Version 5 (ODBC)

 Client/server components for Version 5


Standalone components for Version 5 (Embedded SQL)
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Review of SQL Anywhere Version 5 architecture

 Standalone components for Version 5 (Embedded SQL)

If you are using SQL Anywhere Version 5 as a personal server, with an Embedded SQL client application, you are using the following components on your machine:

•	A SQL Anywhere Version 5 database.

•	The SQL Anywhere Version 5 database engine (personal database server).

•	The SQL Anywhere Version 5 interface library.

•	A SQL Anywhere Version 5.0 connection string.

The following figure illustrates how these pieces fit together.

Here, the question mark in dbl50?.dll represents a single character indicating the operating system. The interface library is named dlb50t.dll on Windows 95 and Windows NT, dbl50w.dll on Windows 3.x, and so on.

Embedded SQL client applications make calls to the interface library. The interface library is referenced by name, so that the name dbl50?.dll is a part of the client application.


Standalone components for Version 5 (ODBC)
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Review of SQL Anywhere Version 5 architecture

 Standalone components for Version 5 (ODBC)

If you are using SQL Anywhere Version 5 as a personal server, with an ODBC client application, you are using the following components on your machine:

•	A SQL Anywhere Version 5 database.

•	The SQL Anywhere Version 5 database engine.

•	The SQL Anywhere Version 5 interface library.

•	The SQL Anywhere Version 5 ODBC driver.

•	The ODBC driver manager.

•	A SQL Anywhere Version 5.0 connection description. This may be an ODBC data source, or a connection string from an application.

The following figure illustrates how these pieces fit together. The client application passes a data source name to the ODBC driver manager. The ODBC driver manager looks up the appropriate driver in ODBCINST.INI. The driver looks up the connection information in ODBC.INI and, via the interface library, connects to the SQL Anywhere Version 5 database engine.


Client/server components for Version 5
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Review of SQL Anywhere Version 5 architecture

 Client/server components for Version 5

If you are using SQL Anywhere as a network server, you have the following components on your server machine:

•	A SQL Anywhere Version 5 database

•	The SQL Anywhere Version 5 database server

You have the following components on your client machine:

•	The SQL Anywhere Version 5 Client executable

•	The SQL Anywhere Version 5 interface library.

•	The SQL Anywhere ODBC driver, if your application uses ODBC.

•	A SQL Anywhere Version 5.0 connection description. This may be an ODBC data source, or a connection string from an application.

The data source may contain connection information in the start option, corresponding to a SQL Anywhere Client command line. You may also have connection strings in your application, and batch files that start a client with particular command-line parameters.

The architecture of a Version 5 Embedded SQL client/server connection is illustrated in the figure. For ODBC applications the ODBC driver manager and ODBC driver stand between the application and the interface library.


Upgrading Embedded SQL applications
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Upgrading Embedded SQL applications

You can upgrade a SQL Anywhere Version 5 standalone application to use the Adaptive Server Anywhere Version 7.0 database server by upgrading the database server and the interface library. You do not need to upgrade the database or the client application itself.

The upgrade procedure uses the compatibility library. The compatibility library is a dynamic library (a DLL on PCs, shared library on UNIX) that enables Embedded SQL applications to work with both Version 5 and Version 7.0 database servers.

  For a description of the compatibility library, see Using the compatibility library.

StartLine connection parameter
If your application connection string uses a StartLine parameter that explicitly provides a dbclient.exe or dbeng50.exe command line, and this is hard coded into your application, there are additional upgrade considerations.

For information on upgrading applications that use StartLine connection parameters, see Start parameters and the compatibility library.

Components upgraded

The following figure illustrates the changes you need to make in your setup in order to upgrade:

  For a description of the compatibility library, see Using the compatibility library.

 Upgrade procedures for Embedded SQL applications

 Using the version 5 utilities with Adaptive Server Anywhere 7.0


Upgrade procedures for Embedded SQL applications
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Upgrading Embedded SQL applications

 Upgrade procedures for Embedded SQL applications

This section provides step-by-step procedures for different kinds of Embedded SQL applications:

•	Applications using an embedded database are standalone applications using the personal database server (dbeng50.exe for Version 5, dbeng7.exe for Version 7.0).

•	Client/server applications connect across a network to the network database server. In Version 5, these applications use the dbclient.exe executable.

♦ To upgrade Embedded SQL applications that use an embedded database:

1	Install the Version 7.0 software    The Version 7.0 software contains components that enable Version 5 applications to continue working. You can either install into a separate directory or over the top of your Version 5 software.

The installation places the Version 7.0 executable directory ahead of the Version 5 executable directory in your system path.

2	Ensure that your application is using the compatibility DLL    If necessary, copy the compatibility library dbl50?.dll from your Adaptive Server Anywhere executable directory to a place where your application will locate it.

For example, you could copy the compatibility library to the same directory as the module of your application that loads it. The file dbl50?o.dll is installed into your Adaptive Server Anywhere executable directory. This directory must be in your path as this library is required by the compatibility library.

At this stage, your Version 5 application should continue to work as before. However, it will be connecting to your database through the compatibility library rather than directly through the Version 5 interface library.

  If you have any problems at this stage, you need to check how your application locates the interface library. For information, see File locations and the compatibility library.

3	Create a new connection description    If your application obtains its connection parameters from configuration files, batch files, or the system registry, you should prepare a new description that uses the Version 7.0 database server. For example, Sybase Central stores connection descriptions in the system registry.

•	If you store a connection string that uses the DBF parameter to start the default database server, then the Version 7.0 database server is started automatically by the compatibility library, instead of the Version 5 database server. In this case, no new connection description is needed.

•	If you store a connection string that uses a Start Line parameter specifying dbeng50.exe, you must replace this with one specifying dbeng7.exe. If your application contains a hard-coded connection string, you need to take extra steps at this point.

  For more information on upgrading StartLine parameters, see Start parameters and the compatibility library.

•	If the database server is started in some other way, such as by a batch file or using a Windows NT service, you must reconfigure this so that the Version 7.0 database server is started instead.

4	Use the new connection description    With this step, you are using all Version 7.0 software and have completed your upgrade. The database itself does not need to be upgraded to work with existing applications.

♦ To upgrade Embedded SQL client/server applications:

1	Prepare for the upgrade     This step must be carried out at each client machine. You prepare for the upgrade by installing the Version 7.0 software.

The installation places the Version 7.0 executable directory ahead of the Version 5 executable directory in your system path.

2	Ensure that your application is using the compatibility DLL     This step must be carried out at each client machine. If necessary, copy the compatibility library dbl50?.dll from your Adaptive Server Anywhere executable directory to a place where your application will locate it.

For example, you could copy the compatibility library to the same directory as the module of your application that loads it. The file dbl50?o.dll is installed into your Adaptive Server Anywhere executable directory. This directory must be in your path as this library is required by the compatibility library.

At this stage, your Version 5 application should continue to work as before. However, it will be connecting to your database through the compatibility library rather than directly through the Version 5 interface library.

  If you have any problems at this stage, you need to check how your application locates the interface library. For information, see File locations and the compatibility library.

3	Create a new connection description     This step must be carried out at each client machine. If your application obtains its connection parameters from configuration files, batch files, or the system registry, you should prepare a new description that uses the Version 7.0 database server. This description is for use when the server is upgraded.

If you store a connection string that uses a StartLine connection parameter specifying dbclient.exe, you must replace this with a new one. The new connection description should contain all the dbclient information as a set of parameters.  If your application contains a hard-coded connection string, you need to take extra steps at this point.

  For more information on upgrading StartLine parameters, see Start parameters and the compatibility library.

  For more information on creating connection descriptions that capture the dbclient command-line information, see Capturing dbclient command-line information.

4	Upgrade the database server     This step must be carried out at the server machine.

•	As with any software upgrade, back up your database before upgrading.

•	Install Adaptive Server Anywhere on the server machine.

•	Start the Version 7.0 database server on the database.

5	Use the new connection description    This step must be carried out at each client machine. You need to use the new connection description to connect to the Version 7.0 server. With this step, you are using all Version 7.0 software and have completed your upgrade. The database itself does not need to be upgraded to work with existing applications.


Using the version 5 utilities with Adaptive Server Anywhere 7.0
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Upgrading Embedded SQL applications

 Using the version 5 utilities with Adaptive Server Anywhere 7.0

For the Version 5 database utilities, connection strings are supplied interactively. The Version 5 database utilities such as ISQL are Embedded SQL applications that search for the interface library in the following order:

1	The current directory

2	The Version 5 executable directory

3	The system path

For these applications, even though the compatibility library is ahead of the Version 5 interface library in the system path, the Version 5 compatibility library is located.

♦ To use Version 5 utilities with Adaptive Server Anywhere 7.0:

1	Make a backup copy of your interface library file.

2	Copy the compatibility library from your Version 7.0 executable directory to your Version 5 directory. For example, on Windows 95 and Windows NT, copy the file dbl50t.dll from the win32 subdirectory of your Version 7.0 installation to the win32 subdirectory of your Version 5 installation.

3	You can now run your Version 5 utilities against both Version 5 and Version 7.0 database servers.


Upgrading ODBC applications
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Upgrading ODBC applications

You can upgrade Version 5 ODBC applications in the following ways:

•	Replace the Version 5 ODBC data source with a Version 7.0 ODBC data source. This approach is a complete upgrade, and is described in this section.

•	Use the compatibility library to connect to a Version 7.0 database server. If you choose this route, your application continues to use the Version 5 ODBC driver, so this is not a complete upgrade. The procedure for upgrading in this way is the same as for Embedded SQL applications.

  For information about upgrading in this manner, see Upgrading Embedded SQL applications.

StartLine parameter
If your ODBC data source contains a start line specifying the Version 5 standalone engine (dbeng50.exe) or the SQL Anywhere client (dbclient.exe), you cannot upgrade using the compatibility library.

Components upgraded

The following figure illustrates the changes you must make when upgrading an ODBC standalone application.

Data sources

The ODBC data source specifies which ODBC driver to use. When an ODBC data source is created as an Adaptive Server Anywhere Version 7.0 data source, it uses the Version 7.0 ODBC driver.

Data source must be Version 6
ODBC applications require a Version 6 ODBC data source to work with a Version 7.0 ODBC driver.

 Upgrade procedures for ODBC applications

 Upgrade notes for PowerBuilder and InfoMaker users


Upgrade procedures for ODBC applications
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Upgrading ODBC applications

 Upgrade procedures for ODBC applications

This section provides step-by-step procedures for different kinds of ODBC applications:

•	Applications using an embedded database are standalone applications using the personal database server (dbeng50.exe for Version 5, dbeng7.exe for Version 7.0).

•	Client/server applications connect across a network to the network database server. In Version 5, these applications use the dbclient.exe executable.

Some applications allow you to change the ODBC data source name you use. Other applications use a fixed data source name. You can upgrade either kind of application.

♦ To upgrade ODBC applications that use an embedded database:

1	Install the Version 7.0 software    The Version 7.0 software contains components that enable Version 5 ODBC applications to continue working. You can either install into a separate directory or over the top of your Version 5 software.

Your application should be unaffected by installation of the Version 7.0 software.

2	Create a Version 7.0 ODBC data source    The changes you make depend on the connection parameters you use in the data source.

•	If you start the default database server using the DBF parameter, you can use the same connection parameters in your new data source as your old one.

•	If you store a connection string that uses a Start Line parameter specifying dbeng50.exe, you must replace this with one specifying dbeng7.exe.

  For more information on upgrading StartLine parameters, see Start parameters and the compatibility library.

•	If the database server is started in some other way, such as by a batch file or using a Windows NT service, you must reconfigure this so that the Version 7.0 database server is started instead.

  For information on creating Version 7.0 data sources, see Working with ODBC data sources.

3	Use the new data source    With this step, you are using all Version 7.0 software and have completed your upgrade. The database itself does not need to be upgraded to work with existing applications.

Some applications may have the data source name hard wired. In this case, you need to replace the Version 5 data source with a Version 7.0 data source of the same name. It is recommended that you rename, rather than delete, your Version 5 data source.

♦ To upgrade ODBC client/server applications:

1	Prepare for the upgrade     This step must be carried out at each client machine. You prepare for the upgrade by installing the Version 7.0 software.

Your application should be unaffected by installation of the Version 7.0 software.

2	Create a Version 7.0 ODBC data source    This step must be carried out at each client machine. The changes you make depend on the connection parameters you use in the data source.

•	If you start the default database server using the DBF parameter, you can use the same connection parameters in your new data source as your old one.

•	If you store a connection string that uses a StartLine connection parameter specifying dbclient.exe, you must replace this with a new one. The new connection description should contain all the dbclient information as a set of parameters. If your application contains a hard-coded connection string, you need to take extra steps at this point.

  For more information on upgrading StartLine parameters, see Start parameters and the compatibility library.

  For more information on creating connection descriptions that capture the dbclient command-line information, see Capturing dbclient command-line information.

•	If the database server is started in some other way, such as by a batch file or using a Windows NT service, you must reconfigure this so that the Version 7.0 database server is started instead.

  For information on creating Version 7.0 data sources, see Working with ODBC data sources.

3	Upgrade the database server     This step must be carried out at the server machine.

•	As with any software upgrade, back up your database before upgrading.

•	Install Adaptive Server Anywhere on the server machine.

•	Start the Version 7.0 database server on the database.

4	Use the new data source    This step must be carried out at each client machine. You need to use the new connection description to connect to the Version 7.0 server. With this step, you are using all Version 7.0 software and have completed your upgrade. The database itself does not need to be upgraded to work with existing applications.


Upgrade notes for PowerBuilder and InfoMaker users
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Upgrading ODBC applications

 Upgrade notes for PowerBuilder and InfoMaker users

Users of Sybase PowerBuilder and InfoMaker should make some changes in order to obtain full functionality with Adaptive Server Anywhere Version 7.0.

The pbodb60.ini file

PowerBuilder and InfoMaker use a file named pbodb60.ini  to hold ODBC data source information. The 60 in the file name may be different, depending on the version you have. For each ODBC driver it provides such things as DDL syntax, default DBParm options, valid function names and special datatypes.

If your pbodb60.ini file does not have a Adaptive Server Anywhere section, PowerBuilder and InfoMaker default to a core syntax. This limits the operations you can carry out using these tools. For example you cannot create, alter, or drop primary and foreign keys.

Upgrading your pbodb60.ini file

To obtain complete functionality with PowerBuilder and InfoMaker, you need to upgrade your pbodb60.ini file.

♦ To upgrade your pbodb60.ini file:

1	Make a backup copy of your existing pbodb60.ini file.

2	Add an Adaptive Server Anywhere section to the working copy of the file containing the same information as the existing Sybase SQL Anywhere section:

[Adaptive Server Anywhere]

PBSyntax='WATCOM50_SYNTAX'

PBDateTime='STANDARD_DATETIME'

PBFunctions='WATCOM_FUNCTIONS'

PBDefaultValues='autoincrement,current date,current time,current timestamp,timestamp,null,user'

PBDefaultCreate='YES'

PBDefaultAlter='YES'

PBDefaultExpressions='YES'

DelimitIdentifier='YES'

PBDateTimeInvalidInSearch='NO'

PBTimeInvalidInSearch='YES'

PBQualifierIsOwner='NO'

PBSpecialDataTypes='WATCOM_SPECIALDATATYPES'

IdentifierQuoteChar='"'

PBSystemOwner='sys,dbo,rs_systabgroup'

PBUseProcOwner='YES'

SQLSrvrTSName='YES'

SQLSrvrTSQuote='YES'

SQLSrvrTSDelimit='YES'

ForeignKeyDeleteRule='Disallow if Dependent Rows Exist (RESTRICT),Delete any Dependent Rows (CASCADE),Set Dependent Columns to NULL (SET NULL)'

TableListType='GLOBAL TEMPORARY'


Using the compatibility library
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Using the compatibility library

The compatibility library is a dynamic library (a DLL on PCs, shared library on UNIX) that enables Embedded SQL applications to work with both Version 5 and Version 7.0 database servers. This section describes how the compatibility library works.

Who needs to read this section?
You should read this section if you are upgrading SQL Anywhere Version 5 to Adaptive Server Anywhere Version 7.0, and have existing Embedded SQL applications that you need to work with the Version 7.0 server.

 The Version 5 Embedded SQL interface library

 How Version 5 client applications locate the interface library

 Using the compatibility library

 How the compatibility library works

 File locations and the compatibility library

 Start parameters and the compatibility library


The Version 5 Embedded SQL interface library
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Using the compatibility library

 The Version 5 Embedded SQL interface library

All client machines running SQL Anywhere Version 5 applications, whether connecting over a network or to a personal server, have a SQL Anywhere Version 5 interface library.

For Windows 95 and Windows NT, this library is a DLL named dbl50t.dll.


How Version 5 client applications locate the interface library
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Using the compatibility library

 How Version 5 client applications locate the interface library

Version 5 client applications locate the interface library in one of the following ways:

•	ODBC applications    ODBC applications connect to a SQL Anywhere database using the SQL Anywhere ODBC driver. The SQL Anywhere Version 5 ODBC driver calls functions in the Version 5 Embedded SQL interface library.

•	Embedded SQL applications    Embedded SQL applications for Windows operating systems call into the interface library. In these calls, the interface library is referenced by name—dbl50t.dll for Windows 95 and NT.

The SQL Anywhere Version 5 ODBC driver is an Embedded SQL application.


Using the compatibility library
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Using the compatibility library

 Using the compatibility library

The compatibility library is optionally installed as part of the Version 7.0 client software. It provides support for two interface libraries at the same time. You should check your installation to confirm that it is installed.

The setup program should ensure that your application calls the compatibility library instead of the Version 5 interface library.

The setup program carries out the following steps to ensure that applications call the interface library.

•	The compatibility library has the same name as the SQL Anywhere Version 5 interface library. For example, on Windows NT, the compatibility library is named dbl50t.dll.

•	The compatibility library is installed into the same directory as other Version 7.0 software.

•	The Version 7.0 installation directory is placed ahead of the Version 5 directory in the system path. This ensures that applications locate the compatibility library ahead of the Version 5 interface library.

•	A Version 5 interface library is installed into the same directory as the compatibility library, but with the name dbl50to.dll. When the compatibility library is accessing Version 5 servers, it calls this interface library.

•	The Version 7.0 interface library is installed into the same directory as the compatibility library. It has the name dblib7.dll on Windows NT. When the compatibility library is accessing Version 7.0 servers, it calls this interface library.

If you have problems using the compatibility library, you should check the order of the directories in your path, and ensure that the Version 7.0 location is ahead of the Version 5 location in the path.


How the compatibility library works
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Using the compatibility library

 How the compatibility library works

Using the supplied connection string, the compatibility library attempts to connect to an Adaptive Server Anywhere database using the Adaptive Server Anywhere Version 7.0 interface library. If this attempt fails, it attempts to connect to a SQL Anywhere database using the SQL Anywhere Version 5 library.

The following figure illustrates how the compatibility library enables communications to both a SQL Anywhere Version 5 and an Adaptive Server Anywhere Version 7.0 database server. The number in the lower right hand corner of the boxes indicates the version of the software component.

The following figure illustrates the algorithm used by the compatibility library to connect to a server:


File locations and the compatibility library
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Using the compatibility library

 File locations and the compatibility library

The compatibility library and the Version 5 interface library have the same file name (dbl50.dll). For your application to use the compatibility library, it must locate it ahead of the Version 5 interface library when it searches for DLLs.

To ensure that your application locates the compatibility library ahead of the Version 5 interface library you must understand how your application searches for DLLs.

Searching for DLLs

The Version 7.0 installation program places the Version 7.0 executable directory ahead of the Version 5 directory in the system path, so any application that uses the path to searching for dbl50.dll will find the compatibility library ahead of the Version 5 interface library. If your application searches in a different manner, you must ensure that the compatibility library is located.

The Version 5 ODBC driver is in the same directory as the Version 5 interface library, and so locates this library instead of the Version 7.0 library. To enable Version 5 ODBC applications to connect to Version 7.0 database servers, you can either rename the Version 5 interface library, or copy the Version 7.0 compatibility library along with dbl50to.dll into your Version 5 directory.

Testing to see which library is located

You can test to see which library is located in the following ways:

•	Attempt to connect to a Version 7.0 database server. You cannot connect using the Version 5 interface library.

•	Specify a database file parameter (DBF) and no start line in your connection string. If the Version 5 interface library is located, the SQL Anywhere Version 5 standalone database engine is started. If the compatibility library is located, the Version 7.0 personal database server is started.


Start parameters and the compatibility library
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Using the compatibility library

 Start parameters and the compatibility library

Applications using a connection string that includes a StartLine connection parameter face some additional issues in upgrading.

The StartLine parameter provides explicit instructions for starting the database engine or the SQL Anywhere Client executable. Sample StartLine parameters are as follows:

•	Standalone application    A sample StartLine parameter for a Version 5 standalone application is as follows:

dbeng50.exe -c 8M

•	Network client application     A sample StartLine parameter for a Version 5 network client application is as follows:

dbclient.exe -x tcpip

In Version 6, the dbeng50.exe executable is replaced by the personal database server dbeng7.exe. The dbclient.exe executable is no longer required in Version 7.0.

♦ To upgrade StartLine parameters:

•	The procedure depends on where your connection parameters are stored.

•	If your connection parameters are stored outside the application itself, then you need to alter the connection parameters to use the appropriate dbeng7.exe executable name instead of dbeng50.exe.

All the information on the dbclient command line can be rephrased in terms of other connection parameters. For information, see Connection and Communication Parameters.

•	If your connection parameters are hard-wired into your application, you must relink your application with a new connection string.

There are many possible configurations of client command lines and connection parameters. Be sure you test any solution thoroughly before deploying.


Capturing dbclient command-line information
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Capturing dbclient command-line information

Version 5 client applications that connect to a database server do so via the SQL Anywhere Client, an executable named dbclient.exe. The client executable command line contains information needed to locate a server, including the following:

•	Default server name    The server name on the client command line is the default server name. When a client executable is running, the application does not need to supply a server name in order to connect to the default server.

•	Network communications parameters    A listing of network protocols to use together with a set of communications parameters specifies where the client executable is to look as it attempts to locate a server.

•	Client/Server communication tuning     A set of parameters allows the packet size, buffer size, and so on to be tuned for optimum performance.

In Version 7.0, this information is held in an ODBC data source along with other connection information. As there is no longer a client executable, there is no longer a client command line. In Version 7.0, Embedded SQL applications can use ODBC data sources as a source of connection parameters.

Client command line scope

Only one Version 5 client executable can be run at a time, and it may be used by more than one application and handle connections to more than one server. The command-line information is therefore global to the machine.

 How to capture client command-line information


How to capture client command-line information
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

        Capturing dbclient command-line information

 How to capture client command-line information

During upgrade to Version 7.0, you must ensure that Version 5 dbclient command-line information is captured in such a way that the Version 7.0 ODBC or Embedded SQL applications can use it. You can do this in one of the following ways:

•	Place the information in an ODBC data source    If the information can be placed in a data source, you can use it with Version 7.0 ODBC or Embedded SQL applications.

•	Use the SQLCONNECT environment variable    The SQLCONNECT environment variable contains a connection string. It is searched early in the process of establishing which connection parameters to use. You may be able to use SQLCONNECT settings to override connection parameters.

The way to capture client command-line information depends on where the information is located.

Where command-line information is located

Your existing command line information may be held in one of the following places.

•	ODBC data source    The ODBC data source contains a START parameter that can hold a client executable command-line.

•	As a connection string    Your application may obtain client information (for example from an initialization file), and supply it in a connection string as the START parameter.

•	A batch file    You may have a batch file that includes a client executable command line as part of your startup process.

•	Under an icon    You may have a client executable command line under an icon on your desktop.

How to capture the information

•	From an ODBC data source    The ODBC data source upgrade captures the information in an ODBC data source START parameter.

•	From a batch file or under an icon    You can move the dbclient.exe parameters into your CommLinks connection parameter. The dbcli7.exe executable is provided for compatibility reasons only, and makes the command-line parameters available to Version 6 interface libraries.

•	Hard-wired connection strings    Only if you have a hard-wired connection string in your application (that is, one that cannot be edited), you must alter the source of the application and recompile.


Upgrading databases
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Upgrading databases

If you wish to use the new features of Adaptive Server Anywhere Version 7.0, you need to upgrade your database to Adaptive Server Anywhere 6. The format of the database files is the same as in SQL Anywhere Version 5, but the system tables and available database options have changed.

Caution
Ensure that you back up your database before upgrading.

Supported versions

You can upgrade your database from any of the following versions of the software to Adaptive Server Anywhere Version 6 format:

•	SQL Anywhere Version 5    Including versions 5.0 and 5.5, all patch levels.

•	Watcom SQL Version 4    All patch levels.

•	Watcom SQL Version 3.2    Only patch level e and above.

What the upgrade utility does

The upgrade utility carries out the following tasks:

•	Adds new system tables    For example, the SYSJAR and SYSJAVACLASS system tables are new in Version 6, and must be present for any of the Java capabilities of Adaptive Server Anywhere to be available.

•	Adds new columns to existing tables    For example, with the introduction of user-defined data types in SQL Anywhere Version 5, two system tables had a user_type column added.

•	Adds database options    Database options provide a degree of tuning for overall database behavior or for individual user behavior. The list of available options is kept in the system tables, and needs to be upgraded for the options to become available.

•	  For instructions on upgrading databases, see "Upgrade issues" on page 15.


Upgrading SQL Remote installations
  ASA Getting Started

    PART 1. Moving to Adaptive Server Anywhere 7.0

      CHAPTER 7.  Upgrading Version 5 Applications

 Upgrading SQL Remote installations

SQL Remote installations include a consolidated database and many remote databases, together with a Message Agent at each site.

At each site, the Message Agent handles the sending and receiving of messages. The messages take the form of SQL statements, and the database server handles the actual execution of those SQL statements.

The upgrade requirements for SQL Remote are as follows:

•	No need to upgrade databases    There is no need to upgrade databases for Version 7.0. However, if you do wish to upgrade a database, you can do so using the Upgrade utility at any of the sites. There is no need for all databases to be upgraded at the same time.

•	Software upgrades can be one site at a time     Version 5 Message Agents can exchange messages with Version 7.0 Message Agents as long as the COMPRESSION database option is set to a value of -1 (minus one). There is no need to upgrade software throughout the installation simultaneously.

•	Message Agent and server can be upgraded separately     The Message Agent is an Embedded SQL application. Therefore, the database server can be upgraded before the Message Agent as long as the compatibility library is used. It is, however, recommended that you upgrade your Message Agent at the same time as the database server for performance reasons.

The Message Agent cannot be upgraded before the database server, as a Version 6 client application cannot work with a Version 5 server.

•	Do not unload and reload     There is no need to unload and reload a database when moving from Version 5 to Version 6.

Replication is based on the transaction log, and when a database is unloaded and reloaded, the old transaction log is no longer available. For this reason, good backup practices are especially important when participating in replication.

Example

One approach to upgrading is as follows:

1	Upgrade the consolidated database server and Message Agent. Set the COMPRESSION database option to -1 so that all messages are compatible with the Version 5 software at remote sites.

2	Over time, upgrade remote database servers and Message Agents. You can set the COMPRESSION database option to a value other than -1 to take advantage of compression and encoding on messages being sent to the consolidated database server.

3	When all remote database servers and Message Agents are upgraded, set the COMPRESSION database option a the consolidated site to a value other than -1.


PART 2. Database Concepts
  ASA Getting Started

 PART 2. Database Concepts

This part introduces Adaptive Server Anywhere and describes the   basic concepts you need to understand in order to work with relational   databases and database applications.

 CHAPTER 8.  Databases and Applications

 CHAPTER 9.  Introduction to Adaptive Server Anywhere

 CHAPTER 10.  The Architecture of Database Applications


CHAPTER 8.  Databases and Applications
  ASA Getting Started

    PART 2. Database Concepts

 CHAPTER 8.  Databases and Applications

About this chapter

This chapter introduces basic database concepts. It describes what a relational database is, and what it is used for. It describes how databases and database applications work together.

Other parts of the documentation set assume that you are familiar with the concepts introduced in this chapter.

 Relational database concepts

 SQL and database computing

 The pieces of a database system

 How the pieces fit together


Relational database concepts
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

 Relational database concepts

A relational database management system (RDBMS) is a system for storing and retrieving data, in which the data is organized in tables. A relational database consists of a collection of tables that store interrelated data.

This section introduces some of the terms and concepts that are important in talking about relational databases.

Many of the concepts described here are illustrated in tutorial form in "Designing and Building Your Database".

 Database tables

 Relations between tables

 Other database objects


Database tables
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        Relational database concepts

 Database tables

In a relational database, all data is held in tables, which are made up of rows and columns.

Each table has one or more columns, and each column is assigned a specific data type, such as an integer, a sequence of characters (for text), or a date. Each row in the table has a single value for each column.

For example, a table containing employee information may look as follows:

emp_ID	emp_lname	emp_fname	emp_phone
10057	Huong	Zhang	1096
10693	Donaldson	Anne	7821
Characteristics of relational tables

The tables of a relational database have some important characteristics:

•	There is no significance to the order of the columns or rows.

•	Each row contains one and only one value for each column.

•	All values for a given column have the same data type.

The following table lists some of the formal and informal relational database terms describing tables and their contents, together with their equivalent in nonrelational databases. This manual uses the informal terms.

Formal relational term	Informal relational term	Nonrelational term
Relation	Table	File
Attribute	Column	Field
Tuple	Row	Record
What do you keep in each table?

When you are designing your database, you should make sure that each table in the database holds information about a specific thing, such as employees, products, or customers.

By designing a database this way you can set up a structure that eliminates redundancy and the possible inconsistencies caused by redundancy. For example, both the sales and accounts payable departments might enter and look up information about customers. In a relational database, the information about customers is entered only once, in a table that both departments can access.

  For more information about database design, see Designing Your Database.

  For instructions on how to create a table, see "Lesson 3: Design and create a table".


Relations between tables
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        Relational database concepts

 Relations between tables

A relational database is a set of related tables. You use primary and foreign keys to describe relationships between the information in different tables.

The tables in a relational database are, as the name suggests, related. Primary keys identify each row in a table uniquely, and foreign keys define the relationships between rows in different tables.

If you make proper use of primary keys and foreign keys, you can use relational databases to hold information in an efficient manner, without redundancy.

 Tables have a primary key

 Tables are related by foreign keys


Tables have a primary key
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        Relational database concepts

          Relations between tables

 Tables have a primary key

Each table in a relational database should have a primary key. The primary key is a column, or set of columns, that uniquely identifies each row. No two rows in a table may have the same value of a primary key.

Examples

In the sample database, the employee table stores information about employees. It has a primary key column named emp_id, which holds a unique ID number assigned to each employee. A single column holding an ID number is a common way to assign primary keys, and has advantages over names and other identifiers which, while commonly different, may not be unique.

A more complex primary key can be seen in the sales_order_items table of the sample database, which has the following columns:

•	id    An order number, identifying the order the item is part of.

•	line_id    A line number, identifying each item on any order.

•	prod_id    A product ID, identifying the product being ordered.

•	quantity    A quantity, showing how many items were ordered.

•	ship_date    A ship date, showing when the order was shipped.

A particular sales order item is identified by the order it is part of, and by a line number on the order. These two numbers are stored in the id and line_id columns. Items may share a single id value (corresponding to an order for more than one item) or they may share a line_id number (all first items on different orders have a line_id of 1). No two items share both values, and so the primary key is made up of the two columns.

  For a tutorial example, see "Lesson 4: Identify and create primary keys".


Tables are related by foreign keys
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        Relational database concepts

          Relations between tables

 Tables are related by foreign keys

The information in one table is related to that in other tables by foreign keys.

Example

The sample database has one table holding employee information and one table holding department information. The department table has the following columns:

•	dept_id    An ID number for the department. This is the primary key for the table.

•	dept_name    The name of the department.

•	dept_head_id    The employee ID for the department manager.

To find the name of a particular employee's department, there is no need to put the name of the employee's department into the employee table. Instead, the employee table contains a column holding a number that matches one of the dept_id values in the department column.

This employee column is called a foreign key to the department table. A foreign key references a particular row in the table containing the corresponding primary key.

In this example, the employee table (which contains the foreign key in the relationship) is called the foreign table or referencing table. The department table (which contains the referenced primary key) is called the primary table or the referenced table.

  For a tutorial example, see "Lesson 5: Design column properties".


Other database objects
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        Relational database concepts

 Other database objects

A relational database holds more than a set of related tables. Among the other objects that make up a relational database are the following:

•	Indexes    Indexes allow quick lookup of information. Conceptually, an index in a database is like an index in a book. In a book, the index relates each indexed term to the page or pages on which that word appears. In a database, the index relates each indexed column value to the physical location at which the row of data containing the indexed value is stored.

Indexes are an important design element for high performance. Their use is transparent to the user.

•	Views    Views are computed tables, or virtual tables. They look like tables to client applications, but they do not hold data. Instead, whenever they are accessed, the information in them is computed from the underlying tables.

The tables that actually hold the information are sometimes called base tables to distinguish them from views. A view is defined with a SQL query on base tables or other views.

•	Stored procedures and triggers    These are routines held in the database itself that act on the information in the database.

You can create and name your own stored procedures to execute specific database queries and to perform other database tasks. Stored procedures can take parameters. For example, you might create a stored procedure that returns the names of all customers who have spent more than the amount that you specify as a parameter in the call to the procedure.

A trigger is a special stored procedure that automatically fires whenever a user updates, deletes, or inserts data, depending on how you define the trigger. You associate a trigger with a table or columns within a table. Triggers are useful for automatically maintaining business rules in a database.

•	Users and groups    Each user of a database has a user ID and password. You can set permissions for each user, so that confidential information is kept private and users are prevented from making unauthorized changes. Users can be assigned to groups in order to make the administration of permissions easier.

•	Java objects    You can install Java classes into the database. Java classes provide a powerful way of building logic into your database, and a special class of user-defined data types for holding information.


SQL and database computing
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

 SQL and database computing

When a client application wants to carry out a database task, such as retrieving information using a query or inserting a new row into a table, it does so using Structured Query Language (SQL) statements. SQL, pronounced "sequel", is a relational database language that has been standardized by the ANSI and ISO standards bodies.

Depending on how you develop a client application, SQL statements could be supplied in function calls from the programming language. Some application development tools provide user interfaces for building and generating SQL statements.

The programming interface delivers the SQL statement to the database server. The database server receives the statement and executes it, returning the required information (such as query results) back to the application.

Client/server communication protocols carry information between the client application and the database server, and programming interfaces define how an application sends the information. No matter what interface you use, and what network protocol you use, it is SQL statements that are sent to a server, and the results of SQL statements that are returned to the client application.

Example

This SQL statement extracts the last names of all employees from the employee table in the sample database.

SELECT emp_lname

FROM employee

Example

This SQL statement creates a table called food that lists types of food and the amount in stock in the employee cafeteria:

CREATE TABLE food (

food_id integer primary key,

food_type char(20)

quantity integer

)

  For an introduction to SQL, see the chapters beginning with Selecting Data from Database Tables.

 Queries

 Other SQL statements

 The system tables


Queries
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        SQL and database computing

 Queries

The "Q" in "SQL" stands for query. You query (or retrieve) data from a database with the SELECT statement. The basic query operations in a relational system are projection, restriction, and join. The SELECT statement implements all of them.

Projections and restrictions

A projection is a subset of the columns in a table. A restriction (also called selection) is a subset of the rows in a table, based on some conditions.

For example, the following SELECT statement retrieves the names and prices of all products that cost more than $15:

SELECT name, unit_price

FROM product

WHERE unit_price > 15

This query uses both a projection (SELECT name, unit_price) and a restriction (WHERE unit_price > 15).

  For more information, see Chapter 12, "Selecting rows from a table".

Joins

A join links the rows in two or more tables by comparing the values in key columns and returning rows that have matching values. For example, you might want to select the order item identification numbers and product names for all order items that were shipped more than a dozen items:

SELECT sales_order_items.id, product.name

FROM product JOIN sales_order_items

WHERE sales_order_items.quantity > 12

The product table and the sales_order_items table are joined together based on the foreign key relationship between them.

  For more information, see the chapter, Joining Tables.


Other SQL statements
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        SQL and database computing

 Other SQL statements

You can do more with SQL than just query. SQL includes statements that create tables, views, and other database objects. It also includes statements that modify tables (the INSERT, UPDATE, and DELETE statements), and commands that perform many other database tasks discussed in this manual.


The system tables
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        SQL and database computing

 The system tables

Every database contains a set of system tables. These are special tables that the system uses to manage data and the system. These tables are also sometimes called the data dictionary or the system catalog.

System tables contain information about the database. You never alter the system tables directly in the way that you can alter other tables. The system tables hold information about the tables in a database, the users of a database, the columns in each table, and so on. This information is data about data, or metadata.


The pieces of a database system
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

 The pieces of a database system

This section describes how database applications and the database server work together to manage databases.

Any information system contains the following pieces:

•	A database    Data is stored in a database. In diagrams in the documentation, a database is indicated by a cylinder:

An Adaptive Server Anywhere database is a file, usually with an extension of .db. Adaptive Server Anywhere includes a sample database for you to work with: this is the file asademo.db in your Adaptive Server Anywhere installation directory.

UltraLite databases are stored in a device-dependent manner. On Windows CE, the UltraLite database is a file, but on the Palm Computing Platform it is stored in the Palm database.

  For instructions on how to create a database, see Designing and Building Your Database

•	A database server    The database server manages the database. No other application addresses the database file directly; they all communicate with the database server.

  For instructions on starting database servers, see Connecting Your Application to its Database

In diagrams in the documentation, a database server is indicated as follows:

Adaptive Server Anywhere provides two versions of its database server: the personal database server and the network database server. In addition to the features of the personal server, the network server supports client/server communications across a network. The request-processing engine is identical in the two servers.

•	A programming interface    Applications communicate with the database server using a programming interface. You can use ODBC, JDBC, OLE DB, Sybase Open Client, or Embedded SQL.

Many application development tools provide their own programming environment that hides the details of the underlying interface. For example, if you develop an application using Sybase PowerBuilder you never have to make an ODBC function call. Nevertheless, behind the scenes each of these tools is using one of the programming interfaces.

The programming interface provides a library of function calls for communicating with the database. For ODBC and JDBC, the library is commonly called a driver. The library is typically provided as a shared library on UNIX operating systems or a dynamic link library (DLL) on PC operating systems. The JDBC interface uses the Sybase jConnect driver, which is a zip file of compiled Java classes.

In diagrams in the documentation, a programming interface is indicated as follows:

•	A client application    Client applications use one of the programming interfaces to communicate with the database server.

If you develop an application using a rapid application development (RAD) tool such as Sybase PowerBuilder, you may find that the tool provides its own methods for communicating with database servers, and hides the details of the language interface. Nevertheless, all applications do use one of the supported interfaces.

In diagrams in the documentation, a client application is indicated by the following:

UltraLite database servers are custom-generated for each UltraLite application, and are part of the application itself. An UltraLite application together with its database server are indicated as follows:


How the pieces fit together
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

 How the pieces fit together

Database applications can connect to a database server located on the same machine as the application itself, or on a different machine. In addition, with Adaptive Server Anywhere you can build distributed databases, with physically-distinct databases on different machines sharing data.

 Personal applications and embedded databases

 Client/server applications and multi-user databases


Personal applications and embedded databases
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        How the pieces fit together

 Personal applications and embedded databases

You can use SQL Anywhere to build a complete application and database on a single computer. In the simplest arrangement, this is a standalone application or personal application: it is self-contained, with no connection to other databases. In this case, the database server, and the database, can be started by the client application, and it is common to refer to the database as an embedded database: as far as the end user is concerned, the database is a part of the application.

Standalone applications have the following architecture, with a client application connecting through a programming interface to a database server running on the same machine:

The Adaptive Server Anywhere personal database server is generally used for standalone applications, although you can also use applications on the same machine as the network server. When a database server is used to run embedded databases, it is sometimes called a database engine.


Client/server applications and multi-user databases
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 8.  Databases and Applications

        How the pieces fit together

 Client/server applications and multi-user databases

You can use Adaptive Server Anywhere to build an installation with many applications running on different machines, connected over a network to a single database server running on a separate machine. This is a client/server environment, and has the following architecture. The interface library is located on each client machine.

In this case the database server is the Adaptive Server Anywhere network database server, which supports network communications. The database is also called a multi-user database.

No changes are needed to a client application for it to work in a client/server environment, except to identify the server to which it should connect.


CHAPTER 9.  Introduction to Adaptive Server Anywhere
  ASA Getting Started

    PART 2. Database Concepts

 CHAPTER 9.  Introduction to Adaptive Server Anywhere

About this chapter

This chapter introduces the Adaptive Server Anywhere relational database system. It describes the uses and features of Adaptive Server Anywhere.

 Introduction to Adaptive Server Anywhere

 Adaptive Server Anywhere intended uses

 Adaptive Server Anywhere hallmarks

 Adaptive Server Anywhere system requirements

 The Adaptive Server Anywhere database server

 Adaptive Server Anywhere applications


Introduction to Adaptive Server Anywhere
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

 Introduction to Adaptive Server Anywhere

The previous chapter, Databases and Applications, introduced databases in a general manner. This chapter describes the particular hallmarks of Adaptive Server Anywhere and describes the uses to which it can be put.

Adaptive Server Anywhere provides a series of tools for storing and managing data. You can use these tools to enter data into your database, to change your database's structure, and to alter your data.

The Adaptive Server Anywhere relational database management system is the heart of SQL Anywhere Studio. This section describes the kinds of task for which Adaptive Server Anywhere is designed and describes the technical hallmarks of the product.

Adaptive Server Anywhere is designed for tasks that require a full-featured SQL database. It is designed to operate in varied environments. It takes advantage of available memory and CPU resources, providing good performance in environments with ample resources. It also operates well in environments with more limited physical and database administration resources. Restricted resource environments include mobile computing environments, embedded database use, and workgroup servers.


Adaptive Server Anywhere intended uses
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

 Adaptive Server Anywhere intended uses

Roles for which Adaptive Server Anywhere is ideally suited include the following:

•	An embedded database    Many applications require a database "behind the scenes". Personal Information Managers, document management systems —just about any application that stores information. Adaptive Server Anywhere is intended to be the database for these applications. The UltraLite deployment option is intended for embedded environments that have very restricted resources.

•	Mobile computing    Laptop and notebook computers are now common in many workplaces. Adaptive Server Anywhere is intended to be the SQL database for these computers. With SQL Remote replication, Adaptive Server Anywhere extends transaction-based computing throughout the enterprise. The UltraLite deployment option and MobiLink synchronization technology provide full database functionality on devices with limited resources, such as hand-held computers.

•	A workgroup server    Workgroups ranging in size from a few people to a few hundred people may require a database server that they can share. Adaptive Server Anywhere is a multi-user server that can provide a high-performance database for workgroups, well-suited for (but not limited to) environments where administration and hardware resources are limited.


Adaptive Server Anywhere hallmarks
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

 Adaptive Server Anywhere hallmarks

Adaptive Server Anywhere 7.0 builds on the technological hallmarks of the previous version of this product:

•	Full SQL relational database management system    Adaptive Server Anywhere is a transaction-processing relational database management system (RDBMS), with full recovery capabilities, online backup, referential integrity actions, stored procedures, triggers, row-level concurrency control, a rich SQL language, and all the features you expect in a full SQL RDBMS.

•	Economical hardware requirements    Adaptive Server Anywhere requires fewer memory and disk resources than other database management systems.

•	Easy to use    You can use Adaptive Server Anywhere without the extensive database administration efforts usually associated with relational database management systems.

•	Standalone and network use    Adaptive Server Anywhere can be used in a standalone manner, for example as an embedded database in a data-centric application, or as a network server in a multi-user client/server or three-tier environment. As an embedded database system, it can be started automatically by an application when required.

•	High performance    While Adaptive Server Anywhere is designed with simple administration and modest resource requirements in mind, it is a scalable, high-performance DBMS. Adaptive Server Anywhere can run on multiple CPUs, has an advanced query optimizer, and provides performance monitoring and tuning tools.

•	Industry standard interfaces    Adaptive Server Anywhere provides a native ODBC 3.5 driver for high performance from ODBC applications, and an OLE DB driver for use from ActiveX Data Object (ADO) programming environments. It comes with Sybase jConnect for JDBC, and supports Embedded SQL and Sybase Open Client interfaces.

•	A cross-platform solution    Adaptive Server Anywhere can be run on many operating systems, including Windows NT, Windows 95/98, Windows CE, Novell NetWare, Sun Solaris, Hewlett Packard HP-UX, IBM AIX, and Linux.

  The components available on each platform may differ. For information, see Availability of components.


Adaptive Server Anywhere system requirements
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

 Adaptive Server Anywhere system requirements

Adaptive Server Anywhere requires the following:

•	Operating system and CPU    You must have one of the following in order to run the Adaptive Server Anywhere database server:

•	Windows NT    Version 3.51 or later, or Windows 2000.

•	Windows    Windows 95 and Windows 98 are supported.

•	Windows CE    Version 2.11 is supported. Handheld PC (HPC), HPC Pro, and Palm-sized PC (PPC) devices using the Hitachi SH3 or SH4 processor, the MIPS processor, or the ARM processor is recommended. The OLE DB driver is available on SH3, MIPS, and ARM processors.

•	Novell NetWare    Version 3.2 or later.

•	UNIX, including Linux    For a detailed list of supported UNIX operating systems, see the SQL Anywhere Studio Read Me First for UNIX.

For PC operating systems, an Intel 486 or higher CPU, or compatible CPU, is required.

•	Memory    Adaptive Server Anywhere can run with as little as 3 MB of memory. If you use Java in the database, Adaptive Server Anywhere requires 8 MB of memory. Your computer must have this much memory in addition to the requirements for the operating system.

  For more information on supported operating systems for components in SQL Anywhere Studio, see Supported Platforms.

 Network software requirements


Network software requirements
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

        Adaptive Server Anywhere system requirements

 Network software requirements

If you are running an Adaptive Server Anywhere network database server, you must have appropriate networking software installed and running.

The Adaptive Server Anywhere network server is available for Windows NT, Windows 95/98, Windows CE, Novell NetWare, Linux, and UNIX operating systems.

Adaptive Server Anywhere supports the following network protocols:

•	TCP/IP

•	SPX (not UNIX operating systems or Windows CE)

•	NetBIOS (not UNIX operating systems or Windows CE)


The Adaptive Server Anywhere database server
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

 The Adaptive Server Anywhere database server

There are two versions of the Adaptive Server Anywhere database server included in the product:

•	The personal database server    This server is provided for single-user, same-machine use: for example, as an embedded database server. It is also useful for development work.

The name of the personal server executable is as follows:

•	On UNIX operating systems, it is dbeng7.

•	On Windows 95/98 and Windows NT, it is dbeng7.exe.

•	The network database server    In addition to the features of the personal server, the network server supports client/server communications across a network. It is provided for multi-user use.

The name of the network server executable is as follows:

•	On UNIX operating systems, it is dbsrv7.

•	On Windows 95/98 and Windows NT, it is dbsrv7.exe.

•	On Novell NetWare, the server is a NetWare Loadable Module (NLM) called dbsrv7.nlm.

Same SQL features in each version

The request-processing engine is identical in the two versions of the server. They support exactly the same SQL language, and exactly the same database features. The personal server does not support communications across a network, more than ten concurrent connections, or the use of more than two CPUs. Applications developed against a personal server work unchanged against a network server.


Adaptive Server Anywhere applications
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

 Adaptive Server Anywhere applications

This section introduces some of the database applications that are supplied with Adaptive Server Anywhere in the SQL Anywhere Studio product. These applications help you design, build, and administer your databases.

 Sybase Central

 Interactive SQL

 Command-line utilities

 Availability of components

 The SQL Anywhere program group


Sybase Central
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

        Adaptive Server Anywhere applications

 Sybase Central

Sybase Central provides a graphical user interface for creating and modifying databases and database objects, for inspecting the structure of databases, and for database server administration.

Sybase Central lets you perform such tasks as creating a new database, adding a table, or adding a column to a table.

  For information on using Sybase Central, see Connecting to a database from Sybase Central.

The Sybase Central window is similar to Windows Explorer. The main window is split into two vertically-aligned panes. The left pane displays a hierarchical view of database objects or containers in a tree-like structure. A container is a database object that can hold other database objects, including other containers.

The right pane displays the contents of the container that has been selected in the left pane. For example, to create a database, click the Utilities folder in the left pane, and then double-click Create Database in the right pane.


Interactive SQL
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

        Adaptive Server Anywhere applications

 Interactive SQL

Interactive SQL is an application for typing and sending SQL statements to a database. Interactive SQL allows you to query and alter data in your database as well as modify the structure of your database.

Everything that can be done in Sybase Central can be done in Interactive SQL, but administration tasks are easier in Sybase Central. For this reason, this book uses Sybase Central whenever possible, but when a task is important, or is simple to accomplish in Interactive SQL, the Interactive SQL instructions are also included.

  For information on working with Interactive SQL, see Using Interactive SQL.

Each Interactive SQL window is divided into three sections: the SQL Statements pane at the top; the Messages pane in the middle; and the Results pane at the bottom.

The Interactive SQL windows

•	The SQL Statements pane    This is where you type SQL statements to access and modify your data.

•	The Messages pane     This information displays information from the database server, such as how much time was required to process the request, how many rows were examined in the process, and how many rows appear in the result set.

•	The Results pane     This is where the result sets of your data appear. For example, if you enter a query asking how many customers ordered five or more different types of products, that number appears in the Data window.


Command-line utilities
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

        Adaptive Server Anywhere applications

 Command-line utilities

A set of command-line utilities is provided for carrying out administration tasks such as backing up a database. Command-line utilities are useful for including in batch files for repeated use.

  For more information about administration utilities, see Database Administration Utilities.


Availability of components
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

        Adaptive Server Anywhere applications

 Availability of components

What components you have installed depends on which operating system you are using, what choices you made when installing the software, and whether you received the Adaptive Server Anywhere product or installed Adaptive Server Anywhere as part of another product.

For example, if you have received Adaptive Server Anywhere as part of another product, you may not have both versions of the database server.

Not all components are available on all operating systems. For example, there is no personal server for NetWare, only a network server.


The SQL Anywhere program group
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 9.  Introduction to Adaptive Server Anywhere

        Adaptive Server Anywhere applications

 The SQL Anywhere program group

This section does not apply to users of UNIX operating systems. Installing SQL Anywhere Studio under UNIX does not provide a program group.

For Windows operating systems, after the software is installed you will have a SQL Anywhere program group. You can reach the program group by clicking the Start button and choosing Programs [image] Sybase SQL Anywhere 7.

Program group items

The program group contains some or all of the following items. The items you see depend on the choices you made when installing the software.

•	Adaptive Server Anywhere 7    Contains the items:

•	Debug Database Objects    Starts the Adaptive Server Anywhere Procedure Debugger.

•	Interactive SQL    Starts the Interactive SQL utility for sending SQL statements to a database.

•	Manage Adaptive Server Anywhere    Starts Sybase Central.

•	Network Server Sample     Starts the network database server and loads the sample database.

•	ODBC Administrator    Starts the ODBC Administrator program for setting up and editing ODBC data sources.

•	Personal Server Sample    Starts the personal server and loads the sample database.

•	Synchronization program group    To access MobiLink Synchronization programs and samples.

•	UltraLite program group    To access UltraLite programs and samples.

•	SQL Anywhere documentation    Opens the online documentation for Adaptive Server Anywhere.

•	Sybase Central 4.0    Starts Sybase Central 4.0.

•	SybaseWeb    Opens a Web page with information about Sybase products.

In addition, you may have items for InfoMaker, PowerDesigner and PowerDynamo.


CHAPTER 10.  The Architecture of Database Applications
  ASA Getting Started

    PART 2. Database Concepts

 CHAPTER 10.  The Architecture of Database Applications

About this chapter

This chapter describes how database applications communicate with the Adaptive Server Anywhere database server, and provides a glimpse into the architecture of Adaptive Server Anywhere.

 Application programming interfaces

 Inside Adaptive Server Anywhere


Application programming interfaces
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

 Application programming interfaces

An overview of database application architecture was given in Chapter 6, "How the pieces fit together". The current section describes the architecture in more detail.

Adaptive Server Anywhere supports a wide set of programming interfaces to provide flexibility in the kinds of applications and application development environments you can use.

 Supported interfaces and protocols

 Communications protocols

 ODBC applications

 Embedded SQL applications

 Open Client applications

 JDBC applications

 OLE DB applications


Supported interfaces and protocols
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 Supported interfaces and protocols

The following diagram displays the supported interfaces, and the interface libraries used. In most cases the interface library is the same name as the interface itself.

The applications supplied with SQL Anywhere Studio use several of these interfaces:


Communications protocols
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 Communications protocols

Each interface library communicates with the database server using a communication protocol. Adaptive Server Anywhere supports two communication protocols, Tabular Data Stream (TDS) and Command Sequence. These protocols are internal, and for most purposes it does not matter which one you are using. Your choice of development environment will be governed by your available tools.

The major differences you will see are upon connecting to the database. The Command Sequence applications and TDS applications use different methods to identify a database and database server, and so connection dialogs are different.

TDS    This protocol is also used by Sybase Adaptive Server Enterprise, Open Client applications and Java applications that use the jConnect JDBC driver connect using TDS.

Command Sequence    This protocol is used by Adaptive Server Anywhere, Adaptive Server IQ, and is used by Embedded SQL, ODBC, and OLE DB applications.


ODBC applications
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 ODBC applications

You can develop ODBC applications using a variety of development tools and programming languages, as displayed below.

For example, of the applications supplied with SQL Anywhere Studio, InfoMaker, SQL Modeler, and PowerDynamo use ODBC to connect to the database.


Embedded SQL applications
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 Embedded SQL applications

You can develop C or C++ applications using the Embedded SQL programming interface. The command line database tools are examples of applications developed in this manner.

Embedded SQL is also a programming interface for UltraLite applications.


Open Client applications
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 Open Client applications

Open Client is an interface that is also supported by Sybase Adaptive Server Enterprise. You can develop Open Client applications in C or C++. Other Sybase applications, such as OmniConnect or Replication Server, use Open Client.


JDBC applications
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 JDBC applications

You can develop applications that use JDBC to connect to Adaptive Server Anywhere using Java. Several of the applications supplied with SQL Anywhere Studio use JDBC: the stored procedure debugger, Sybase Central, and Interactive SQL.

Java and JDBC are also important programming languages for developing UltraLite applications.


OLE DB applications
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Application programming interfaces

 OLE DB applications

Adaptive Server Anywhere includes an OLE DB driver. You can develop applications using Microsoft's OLE DB interface directly, or using the ActiveX Data Objects (ADO) interface. The ADO interface is included in Visual Basic and other Microsoft programming tools.


Inside Adaptive Server Anywhere
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

 Inside Adaptive Server Anywhere

While you never need to deal with the internals of the database server, a glimpse behind the scenes may help you understand the process better.

 Inside the database server

 Inside the database


Inside the database server
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Inside Adaptive Server Anywhere

 Inside the database server

The Adaptive Server Anywhere database server has an internal structure that allows many requests to be handled efficiently.

•	A communications layer handles the actual exchange of data with client applications. This layer receives requests from client applications, and returns results. The timing of these actions is governed by a negotiation between client and server, to make sure that the network traffic is kept to a minimum, but that the data is made available as soon as possible on the client side.

•	The parser checks each SQL statement sent to the database server, and transforms it into an internal form for processing.

•	If the request is a query, or an update or delete statement, there may be many different ways of accessing the data, which may take massively different times. The job of the optimizer is to select from among all these possibilities the best route to getting the required data quickly.

•	A Java Virtual Machine is built into the database server, and any Java operations sent by client applications, or invoked internally by the database server, are handled by the Java VM.

•	The efficient access of data is a complex task, and the lowest level of the database server is concerned with reading and writing data from the disk, caching data in memory to avoid unnecessary disk access, and balancing the demands of different users.


Inside the database
  ASA Getting Started

    PART 2. Database Concepts

      CHAPTER 10.  The Architecture of Database Applications

        Inside Adaptive Server Anywhere

 Inside the database

All the information in an Adaptive Server Anywhere database is stored in a single database file, which can be copied from one machine to another. It is possible to make databases of several files, but this is generally only required for very large databases.

In addition to the database file, Adaptive Server Anywhere uses two other files when it is running a database. These are the transaction log and the temporary file.

•	The database file    Internally, the database file is composed of pages: fixed size areas of disk. The data access layer reads and writes data one page at a time. Many pages hold the data that is in the database tables, but other pages hold index information, information about the distribution of data within the database, and so on.

•	The transaction log    The transaction log is a separate file, which contains a record of all the operations performed on the database. Normally, it has the same name as the database file, except that it ends with the suffix .log instead of .db. It has three important functions.

•	Record operations on your data to enable recovery    You can recreate your database from a backup together with the transaction log if the database file is damaged.

•	Enable database replication    SQL Remote uses this file to replicate the changes to your database on portable computers which are sometimes, but not always, connected to the network.

•	Improve performance    By writing information to the transaction log, the database server can safely process your commands without writing to the database file as frequently.

•	The temporary file    The temporary file is opened when the database server starts, and is closed down when the server stops. As its name suggests, the temporary file is used while the server is running to hold temporary information. The temporary file does not hold information that needs to be kept between sessions.

The temporary file is stored in your temporary directory. The location of this directory is generally identified by your TEMP environment variable.


PART 3. Working With Databases
  ASA Getting Started

 PART 3. Working With Databases

The first chapter takes you on a tour of the Adaptive Server Anywhere sample   database to illustrate how databases are put together. It then describes how   to design and create your own database.

This remaining chapters introduce SQL, the language for accessing and manipulating   data. It also describes how to incorporate SQL into database applications.

 CHAPTER 11.  Designing and Building Your Database

 CHAPTER 12.  Connecting Your Application to its Database

 CHAPTER 13.  Using Interactive SQL

 CHAPTER 14.  Selecting Data from Database Tables

 CHAPTER 15.  Selecting Data from Multiple Tables

 CHAPTER 16.  Selecting Aggregate Data

 CHAPTER 17.  Selecting Data Using Subqueries

 CHAPTER 18.  Updating the Database

 CHAPTER 19.  System Tables


CHAPTER 11.  Designing and Building Your Database
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 11.  Designing and Building Your Database

About this chapter

This chapter introduces some principles of database design, and describes how to create a database using Sybase Central. It uses the Adaptive Server Anywhere sample database to illustrate the principles involved.

 Introduction

 The sample database

 Tutorial: design and build a simple database


Introduction
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

 Introduction

It is worth spending some time designing even the simplest database: what tables you will have, the keys that relate these tables, and so on.

Designing a database is not a difficult task for small and medium sized databases, but it is an important one. Bad database design can lead to an inefficient and possibly unreliable database system. Database applications are built to work on specific parts of a database, and rely on the database design, so a bad design can be difficult to revise at a later date.

Designing a large database is a complex task. There are formal approaches, such as conceptual data modeling, that help you to create efficient designs. Powerful tools such as Sybase PowerDesigner and DataArchitect enable you to apply these techniques to design and maintain large database designs.

This chapter does not attempt to tackle design issues for large databases. For an elementary look at the principles of database design, see Designing Your Database. For more advanced treatments, see the Sybase PowerDesigner documentation or a book devoted to database design.

Instead, this chapter helps you decide the kind of information you group together in a single table, and the way in which to think about and classify relationships between tables.

This chapter assumes that you are familiar with the concepts of database tables, primary keys, and foreign keys. For information, see Databases and Applications.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Introduction

 About this chapter

If you want to know...	Then see...
Where to find information about the sample database	The sample database
How to connect to the sample database	Connect to the sample database
How to view tables	View a list of tables in the sample database
How to view columns	View the columns of a table
How to create a database file	Lesson 1: Create a database file
How to connect to a database	Lesson 2: Connect to your database
How to create a table	Lesson 3: Design and create a table
How to create a primary key	Lesson 4: Identify and create primary keys
How to set column properties	Lesson 5: Design column properties
How to create relationships between tables	Lesson 6: Design and create relationships between tables


The sample database
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

 The sample database

There is a sample database included with Adaptive Server Anywhere. Many of the examples throughout the documentation use this sample database.

The sample database represents a small company that makes a limited range of sports clothing. It contains internal information about the company (employees, departments, and financial data) as well as product information (products), and sales information (sales orders, customers, and contacts).

The following figure shows the tables in the sample database and how they are related to each other. The boxes represent tables, and the arrows represent foreign key relationships. Primary key columns are underlined.

We will use this database to illustrate the concepts described in this chapter.

 Viewing the structure of the sample database using Sybase Central


Viewing the structure of the sample database using Sybase Central
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        The sample database

 Viewing the structure of the sample database using Sybase Central

This section describes how to use Sybase Central to view the contents of the sample database.

 Connect to the sample database

 View a list of tables in the sample database

 View the columns of a table


Connect to the sample database
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        The sample database

          Viewing the structure of the sample database using Sybase Central

 Connect to the sample database

You need to connect to the sample database from Sybase Central in order to view the tables and other objects in the database.

♦ To connect to the sample database from Sybase Central:

1	Start Sybase Central

Choose Start [image] Programs [image] Sybase SQL Anywhere 7 [image] Sybase Central 4.0.

2	Open the Connect dialog

•	Click Tools [image] Connect.

•	If you are presented with a list of plugins, choose Adaptive Server Anywhere from the list.

The Connect dialog appears:

3	Select the ODBC Data Source Name option and click Browse. In the resulting list, choose ASA 7.0 Sample.

4	Click OK to establish the connection.


View a list of tables in the sample database
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        The sample database

          Viewing the structure of the sample database using Sybase Central

 View a list of tables in the sample database

Once you are connected to the sample database, you can open the folders in the left pane to view the tables and other objects that make up the database.

  For information on connecting to a database from Sybase Central, see Connect to the sample database.

♦ To view a list of tables in the database:

1	In Sybase Central, open Adaptive Server Anywhere.

The Sybase Central left pane contains a list of plugins. Each one is used to manage a different Sybase product. The Adaptive Server Anywhere plugin is used to manage all aspects of Adaptive Server Anywhere.

2	Open the sample database.

Under the Adaptive Server Anywhere plugin is a list of database servers to which you have connections. When the sample database is started, the database server is named asademo.

Each database server may be running one or more databases. In this case, the sample database is also named asademo. The database also has the user name for the current connection in parentheses next to it. In this case, you are connected as a user named DBA.

3	Open the tables folder.

Each database contains a set of folders, describing the different types of database objects. Here, we are interested only in the tables in the database. Open the tables folder to list the available tables.

Notes

Each table has a creator, or owner, which is listed in the right pane. If you see more tables than are shown in the figure, right click the asademo database, and choose Filter Objects By Owner from the popup menu. Check DBA, and uncheck the other user IDs in the list. Click OK to restrict the objects shown to those owned by DBA.


View the columns of a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        The sample database

          Viewing the structure of the sample database using Sybase Central

 View the columns of a table

Sybase Central displays tables as containers. Each table has folders for columns, foreign keys to and from the table, indexes, and triggers. Here, we look only at the columns of the product table in the sample database.

  For information on viewing tables, see View a list of tables in the sample database.

♦ To view information about the columns of the product table:

1	In Sybase Central, open the product table.

2	Open the Columns folder.

The name, data type, and other information about each column is displayed in the right pane.

3	View the properties of the color column.

Right click the color column, and choose Properties from the popup menu. The color property sheet is displayed. This property sheet contains a detailed description of the column. Some of the properties are useful for advanced users only, and are not described in this book.

You can use Sybase Central to browse other aspects of the sample database, including foreign keys and primary keys. You are encouraged to do so in order to be familiar with the sample database for examples in the remainder of the documentation.


Tutorial: design and build a simple database
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

 Tutorial: design and build a simple database

When designing a database, you plan what items you want to store information about, and what information you will keep about each. You also determine how these items are related. You classify the things in your database as entities; the links between these entities are called relationships.

In this tutorial, you design and build a very simple database, modeled on the product, sales_order_items, sales_order, and customer tables of the sample database, but simplified. The database you create is not used in other parts of the documentation, so going through the activities is not required. However, it is highly recommended to gain familiarity with the software and concepts.

 Lesson 1: Create a database file

 Lesson 2: Connect to your database

 Lesson 3: Design and create a table

 Lesson 4: Identify and create primary keys

 Lesson 5: Design column properties

 Lesson 6: Design and create relationships between tables


Lesson 1: Create a database file
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Tutorial: design and build a simple database

 Lesson 1: Create a database file

In this lesson, you create a database file to hold your database.

  For more information, see "The pieces of a database system".

Concepts

A database file, is a container, ready to hold your database. The database file contains system tables and other system objects that are common to all databases, but you must add all the tables and the data they hold.

The collection of tables, indexes, and so on within the database, and all the relationships between them, make up the database schema. The schema is the database, without the data. This tutorial describes how to design and create a very simple database schema.

For a conceptual introduction to some of these pieces, see Chapter 6, "Relational database concepts".

The name of each object within the database, including tables, columns, and indexes, is an identifier. There are rules governing what you can use as identifiers. You can use any set of letters, numbers or symbols. However, you must enclose a column name in double quotes if it contains characters other than letters, numbers, or underscores, if it does not begin with a letter, or if it is the same as a keyword.

  For more information about identifiers, see Identifiers.

Exercise

♦ To create a new database file:

1	Start Sybase Central.

You do not need to connect to any database in this tutorial.

2	In the left pane, open the Adaptive Server Anywhere container.  Then open the Utilities folder.  In the right pane, double-click Create Database.

3	On the Choose a Connection page, choose Connection Created By a Local Engine, and click Next.

A personal database server starts, with the name SybCentral. This server will create the database file.

4	Choose a location and name for your database file.

On the Create an Adaptive Server Anywhere Database page, enter a filename of c:\temp\mysample. If your temporary directory is somewhere other than c:\temp, specify a path of your own choice.

5	Click Finish to create the database.

Other options are available when creating a database, which you can view by clicking Next, but the default choices are good for many purposes.

The Create Database Message Console displays the progress of the task, and disappears when the file is created.


Lesson 2: Connect to your database
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Tutorial: design and build a simple database

 Lesson 2: Connect to your database

In this lesson, you connect to the database file you created.

  For more information, see "How the pieces fit together".

Exercise

Once your database is created, you can connect to it in order to create tables and other database objects.

♦ To connect to your database:

1	Start Sybase Central.

2	Choose Tools [image] Connect to open the Connect dialog. If you have multiple plug-ins loaded, you may need to choose a plug-in before the Connect dialog opens.

3	Specify the user ID and password.

On the first page of the Connect dialog (the Identification page), enter a user ID of DBA and a password of SQL. These are the values created for new databases, and so grant access to your new database.

Choose None in the profile options at the bottom of the page.

4	Specify your database file.

Click the Database tab. Enter the full path of your database file in the Database File field. For example, if you followed the suggestion in the previous lesson, you should enter the following:

c:\temp\mysample.db

5	Connect to the database.

Click OK. The SybCentral database server loads your sample database. Open the SybCentral container in the left pane to see mysample.

For now, ignore the utility_db databases that you may see under the SybCentral database server.


Lesson 3: Design and create a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Tutorial: design and build a simple database

 Lesson 3: Design and create a table

In this lesson, you add a table to your database.

Concepts

Each table in your database should contain information about a single subject. In the language of database design, you are identifying an entity. For example, the sample database holds information about employees in one table, and information about products in another table: employees and products are entities within the database design.

  For an introduction to tables, see Database tables.

Each column in a table describes a particular characteristic of the thing that you would like to store information about. For example, in the sample database, the employee table has columns that hold an employee ID number, the first and last names, an address, and other particular information that pertains to a particular employee.

In database diagrams, each table is depicted by a rectangular box. The name of the table is at the top of the box, and the column names are listed inside the box.

In the product table from the sample database, above, each product is an item of sports clothing.

Exercise

Create a simplified version of the product table, containing only the identifier (id) and name columns.

♦ To create the product table:

1	In Sybase Central, connect to your database.

2	Open the Tables folder in your database.

First open the server and database containers, then open the Tables folder.

3	Double-click Add Table in the right panel. The Table Editor appears.

The Sybase Central Table Editor is where you add tables to your database.

4	Name your table.

Type product in the Name box in the upper left corner of the table editor.

5	Create the columns.

The next lesson tells you more about columns and what to put in them. Here, we create two columns for our simplified version of the product table.

Add an identifier column with the following properties:

•	Name    Give the column a name of id.

•	Primary key     Click the Key heading until an icon with a key and the letter P appears, indicating that the column is a primary key.

•	Data type    Give the column the integer data type.

•	Add a column with the following properties:

•	Name    Give the column a name of name. This column holds the product name.

•	Data type    Give the column the char data type, which holds character strings, and enter a maximum length of 15 in the Size/Precision column.

1	To finish, click the Save and Close button.

You have now created a table in your database. The table data is held in the database file.  At present, the table is empty.

The next two lessons have more to say about columns and data types.


Lesson 4: Identify and create primary keys
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Tutorial: design and build a simple database

 Lesson 4: Identify and create primary keys

In this lesson, you learn more about defining primary keys for your tables. There is no exercise associated with this lesson.

  For more information, see "Tables have a primary key".

Concepts

The primary key is a special column or columns used to uniquely identify a row in a table. In the product table, the id column uniquely identifies each product.

Each row has a unique value for the id column, and the values in the other columns pertain only to that one product. Two products might have the same name or the same size, but not the same id number. In the diagram, the id column is underlined to show that it is a primary key.

Creating a column specifically to hold an identifier, which has no other meaning, is common practice in database design. You will know from any bank, utility, or credit card statements you get that each account has a unique identifier.

Using an autoincrement primary key

You can make entering primary keys simple by assigning a primary key column a default value of AUTOINCREMENT. The value for this column is entered automatically each time a new row is added, and its value is one more than the field's value for the last row added.

♦ To create an AUTOINCREMENT primary key:

1	Open the Sybase Central Table Editor.

2	Select the column in which you wish to have AUTOINCREMENT data. Click the Property sheet button on the toolbar to open the property sheet for the column.

3	Choose the Default tab.

4	Click Select.

5	Click on System-Defined, and select Autoincrement from the drop-down list.

6	To finish, click the Save and Close button.


Lesson 5: Design column properties
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Tutorial: design and build a simple database

 Lesson 5: Design column properties

In this lesson, you learn more about choosing data types and other attributes for the columns of your tables.

Concepts

Each column has a data type associated with it, that defines the type of information it holds. Choose a data type of the column that is appropriate for the data in the column. For example, identifier columns may have an integer data type; columns holding names or addresses are character data types.

Data types are organized into the following categories:

•	Numeric data types    There are several numeric data types. Some are exact (not affected by round-off errors during operations) and some are approximate.

The data type of the column affects the maximum size of the column. For example, if you specify SMALLINT, a column can contain a maximum value of 32,767. If you specify INTEGER, the maximum value is 2,147,483,647.

  For a complete list, see Numeric data types.

•	Character data types    These are used to hold strings of text, such as names, addresses, and so on. These data types have a length, indicating the maximum length of string that can be stored in them.

  For a list, see Character data types.

•	Binary data types    These can be useful to hold information that may be meaningful to an application, but is encoded in a binary format.

  For a list, see Binary data types.

•	Date/time data types    These hold times of the day, as well as dates.

  For a list, see Date and time data types.

•	Long data types    These are sometimes called blobs (binary large objects). They can be used to hold long strings of text (called memo fields in some databases), or images, or other application-specific information.

  For more information, see LONG BINARY data type, and LONG VARCHAR data type.

In addition, Adaptive Server Anywhere supports user-defined data types and special Java data types. These are not discussed in this introductory book.

NULL and NOT NULL

If the column value is mandatory for each row, you define the column as being NOT NULL. Otherwise, the column is allowed to contain NULL, which represents a missing value. The default is to allow NULL, but you should explicitly declare columns NOT NULL unless there is a good reason to allow NULL.

  For a complete description of the NULL value, see NULL value. For information on its use in comparisons, see Search conditions.

♦ To specify a data type for a column:

1	In the Tables folder, open the table for whose column you wish to specify a data type.

2	Open the Columns folder.

3	Select the column for which you wish to specify a data type.

4	Click on the Data Type tab.

5	Select a basic data type from the first drop-down box.

Exercise

This lesson and the last lesson have introduced the basic concepts you need to know in order to create database tables. You can put these to work by adding some more tables to your database. These tables will be used in the subsequent lessons in this chapter.

Using the Sybase Central Table Editor, add the following tables to your database:

•	customer    Add a table named customer, with the following columns:

•	id    An identification number for each customer. This column has integer data type, and is the primary key.

•	company_name    The company name. This column is a character data type, with a maximum length of 35 characters.

•	sales_order    Add a table named sales_order, with the following columns:

•	id    An identification number for each sales order. This column has integer data type, and is the primary key.

•	order_date    The date on which the order was placed. This column has date data type.

•	cust_id    The identification number of the customer who placed the sales order. This column has integer data type.

•	sales_order_items    Add a table named sales_order_items to hold line item information, with the following columns:

•	id    The identification number of the sales order of which the line item is a part. This column has integer data type, and should be identified as a primary key column.

•	line_id    An identification number for each sales order. This column has integer data type, and should be identified as a primary key column.

•	prod_id    The identification number for the product being ordered. This column has integer data type.

You have now created four tables in your database.  The tables are not yet related in any way.  In the next lesson, you define foreign keys to relate the tables to one another.


Lesson 6: Design and create relationships between tables
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 11.  Designing and Building Your Database

        Tutorial: design and build a simple database

 Lesson 6: Design and create relationships between tables

In this lesson, you learn about designing and creating relationships between tables, using foreign keys.

  For more information, see "Tables are related by foreign keys".

Concepts

Although each table contains information about a single subject, two or more tables may contain related information. For example, an employee is a member of a department, or a sales order is for a set of products. Relationships in a database may appear as foreign key relationships between tables, or may appear as separate tables themselves. You will see examples of each in this chapter.

You create relationships in your database to encode rules or practices that govern the data in your tables. Once a relationship is built into the structure of the database, there is no provision for exceptions.

Relationships among tables are classified according to cardinality. In this section the different relationships are listed.

•	One-to-one relationships    Each item in one entity corresponds to either zero or one entity in another. For example, in the sample database, one employee manages one department. There is nowhere to put a second department manager. Duplicating the department entry would involve duplicating the department ID, which is the primary key.

It is often appropriate to combine the items in a one-to-one relationship into a single table. There is a column in the department table for a manager, rather than having a separate table named manager.

  For cases where it is appropriate to keep the items separate, see  Designing Your Database.

•	Many-to-one relationships    A many-to-one relationship becomes a foreign key relationship between tables. In a many-to-one relationship, the primary key in the one entity appears as a new foreign key column in the many table.

For example, in the database you just created, one customer can place many orders, but only one customer places each order. To represent the one-to-many relationship, you need a foreign key column in the sales_order table (cust_id) that maps to the primary key column in the customer table (id). It is often convenient to give the two columns the same name.

Each entry in the cust_id column of the sales_order table must match one of the entries in the id column of the customer table. The sales_order table (which contains the foreign key in the relationship) is called the foreign table or referencing table. The customer table (which contains the referenced primary key) is called the primary table or the referenced table.

•	Many to many relationships    A many-to-many relationship is represented by an intermediate table, and there is a foreign key relationship from the intermediate table to each of the related entities.

For example, in the sample database, there is a many-to-many relationship between products and sales orders. One sales order can be for many products, and one product can appear on many sales orders.

In some cases, the intermediate table (sales_order_items) contains additional information, such as the number of items of the product that were ordered and the date they were shipped. In this case, the intermediate table holds no additional information.

Exercise

Add foreign keys to relate the tables in your database.

Add the following foreign keys:

•	A foreign key from the id column in sales_order_items, referencing the id column in sales_order. This key builds the many-to-one relationship between sales orders and sales order items into the database.

•	A foreign key from the prod_id column in sales_order_items, referencing the id column in product. This key builds the many-to-one relationship between sales order items and products into the database.

•	A foreign key from the cust_id column in sales_order, referencing the id column in customer. This key builds the many-to-one relationship between sales orders and customers into the database.

The first two foreign keys, taken together build the many-to-many relationship between sales orders and products into the database.

♦ To create a foreign key:

1	Click the table for which you wish to create a foreign key.

2	Open the Foreign Keys folder.

3	Double click Add Foreign Key to invoke the Foreign Key Wizard.

4	Follow the steps of the wizard.

This completes this introductory section on designing and building relational databases. Remaining chapters in the book describe how to add and retrieve data from databases.  These chapters use the Adaptive Server Anywhere sample database, which is a bigger database than the one you have just created.


CHAPTER 12.  Connecting Your Application to its Database
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 12.  Connecting Your Application to its Database

About this chapter

This chapter shows you how to establish a connection from your application to the database it is working with.

 Introduction to connections

 Referencing data sources

 Creating an ODBC Data Source


Introduction to connections
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 12.  Connecting Your Application to its Database

 Introduction to connections

Any client application that uses a database must establish a connection to that database before any work can be done. The connection forms a channel through which all activity from the client application takes place. For example, your user ID determines permissions to carry out actions on the database—and the database server has your user ID because it is part of the request to establish a connection.

Many client applications, including application development systems, use the Open Database Connectivity (ODBC) interface to access Adaptive Server Anywhere. An ODBC data source is a set of connection parameters that are stored in the registry or in a file.

You can use ODBC data sources to connect to Adaptive Server Anywhere databases from any of the following applications:

•	Sybase Central and Interactive SQL

•	all the Adaptive Server Anywhere utilities

•	PowerDynamo, PowerDesigner, and InfoMaker

•	any application that supports ODBC, such as Visual Basic and PowerDynamo

Adaptive Server Anywhere client applications on UNIX can use ODBC data sources. On UNIX, the data source is stored as a file.

  Adaptive Server Anywhere supports several programming interfaces in addition to ODBC. For more information, see Introduction to connections.


Referencing data sources
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 12.  Connecting Your Application to its Database

 Referencing data sources

You need an ODBC data source on the client computer for each database you want to connect to.

The ODBC data source contains a set of connection parameters. You can store sets of Adaptive Server Anywhere connection parameters as an ODBC data source, in either the system registry or as files.

Once you have a data source, your connection string can simply name the data source to use:

•	Data source     Use the DSN connection parameter to reference a data source in the registry:

DSN=my data source

•	File data source     Use the FileDSN connection parameter to reference a data source held in a file:

FileDSN=mysource.dsn

The Connect dialog in Sybase Central and Interactive SQL has fields for entering an ODBC Data Source Name or ODBC Data Source File.


Creating an ODBC Data Source
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 12.  Connecting Your Application to its Database

 Creating an ODBC Data Source

This section describes how to create an ODBC data source in Windows. The process is almost identical for creating system data sources and file data sources—just go to the System DSN tab or File DSN tab on the ODBC Data Source Administrator dialog (see below).

  For information about setting up ODBC data sources on UNIX, see Using ODBC data sources on UNIX.

♦ To create an ODBC data source:

1	From the Windows Start menu, choose Programs [image] Sybase SQL Anywhere 7 [image] Adaptive Server Anywhere 7 [image] ODBC Administrator.

or
From Sybase Central, choose Tools [image] Adaptive Server Anywhere [image] ODBC Administrator.

The ODBC Data Source Administrator appears, showing a list of the data sources you currently have installed on your computer. For example,

2	On the User DSN tab, click Add.

The Create New Data Source wizard appears.

3	Select Adaptive Server Anywhere 7.0 from the list of drivers, and click Finish.

The Adaptive Server Anywhere ODBC Configuration dialog appears.

Many of the fields in this dialog box are optional. Click the question mark at the top right of the window and click an entry field to find more information about that field. You probably only need to use the following parameters:

•	Data Source Name (ODBC tab)    Type a name that will appear in the Connect dialog. It can contain spaces, but should be short.

•	User ID (Login tab)    The database user ID you will use to connect. If you omit the user ID, you will be prompted for it when you attempt to connect.

•	Password (Login tab)    You should omit or encrypt the password if there are security concerns with having passwords stored on your machine. If you omit the password, you will be prompted for it when you attempt to connect.

•	Database File (Database tab)    You can select a database file by browsing your machine.

4	When you have specified the parameters you need, click OK to create the data source and close the dialog box.

  For a full description of database connections, see Connecting to a Database.

  For more information about ODBC data sources, see Working with ODBC data sources.


CHAPTER 13.  Using Interactive SQL
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 13.  Using Interactive SQL

About this chapter

This chapter discusses how to run and use Interactive SQL. Interactive SQL is a utility shipped with Adaptive Server Anywhere; it lets you execute SQL statements, build scripts, and display database data.

 Introduction to Interactive SQL

 Starting Interactive SQL

 Using Interactive SQL to display data

 Working with commands in Interactive SQL

 Configuring Interactive SQL


Introduction to Interactive SQL
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

 Introduction to Interactive SQL

Interactive SQL is a utility for sending SQL statements to the database server. You can use it for the following purposes:

•	Browsing the information in a database.

•	Trying out SQL statements that you plan to include in an application.

•	Loading data into a database and carrying out administrative tasks.

In addition, Interactive SQL can run command files or script files. For example, you can build repeatable scripts to run against a database and then use Interactive SQL to execute these scripts in a batch fashion.

Main window description

When you open an Interactive SQL window, it appears with the following three main panes:

•	SQL Statements    Provides a place for you to type SQL statements. If the amount of code you type exceeds the size of the pane, scroll bars automatically appear for the pane.

•	Messages    Displays information about an execution. You can specify options for this pane in the Options dialog.

•	Results    Displays the results of commands that you execute. For example, if you use SQL statements to search for specific data in the database, this pane displays the columns and rows that match the search criteria. If the information exceeds the size of the pane, scroll bars automatically appear for the pane.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Introduction to Interactive SQL

 About this chapter

The following table lists the most common information how to run and use Interactive SQL.

If you want to know...	Then see...
How to start Interactive SQL	Starting Interactive SQL
How to connect to a database	Connecting Your Application to its Database
How to use the Interactive SQL toolbar	Using the Interactive SQL toolbar
How to open a new Interactive SQL window	Opening multiple windows
Where to find information about keyboard short cuts	Keyboard shortcuts
How to display data	Using Interactive SQL to display data
How to execute SQL commands in Interactive SQL	Working with commands in Interactive SQL
How to set Interactive SQL viewer options	Configuring Interactive SQL
Where to find more detailed information on selecting data	Selecting Data from Database Tables
Where to find more information on loading and unloading data	Introduction to import and export
How to automate common tasks	Saving, loading, and running command files


Starting Interactive SQL
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

 Starting Interactive SQL

You can start Interactive SQL in two ways: from Sybase Central or on its own. The way that you start Interactive SQL on its own depends on your operating system.

If you start Interactive SQL on its own, the Connect dialog appears, which lets you connect to a database just as you would in Sybase Central.

  For detailed information on connecting to databases, see Connecting to a Database.

♦ To start Interactive SQL from Sybase Central:

•	To start Interactive SQL, right-click the database in Sybase Central and choose File [image] Open Interactive SQL.

or

To start Interactive SQL without a connection to a database, choose Tools [image] Adaptive Server Anywhere [image] Open Interactive SQL. The Connect dialog automatically appears.

♦ To start Interactive SQL on its own:

1	Select Interactive SQL from the Adaptive Server Anywhere program group in the Start menu

or

2	Type the following command at a system prompt:

dbisql

 Using the Interactive SQL toolbar

 Opening multiple windows

 Keyboard shortcuts


Using the Interactive SQL toolbar
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Starting Interactive SQL

 Using the Interactive SQL toolbar

The Interactive SQL toolbar (shown below) appears at the top of the Interactive SQL window. It provides you with buttons for executing common commands.

With the buttons on this toolbar, you can:

•	Recall the executed SQL statement immediately before your current position in the history list.

•	View a list of up to 50 previously executed SQL statements.

•	Recall the executed SQL statement immediately after your current position in the history list.

•	Execute the SQL statement currently showing in the SQL Statements pane.

•	Interrupt the execution of the current SQL statement.

As an easy reminder of what these buttons do, you can hold your cursor over each button to see a popup description.


Opening multiple windows
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Starting Interactive SQL

 Opening multiple windows

In Interactive SQL, you can open multiple windows. Each window corresponds to a separate database connection. Using this feature, you can connect simultaneously to two (or more) different databases on different servers, or you can open concurrent connections to a single database.

♦ To open a new Interactive SQL window:

1	Choose File [image] New Window.

2	In the resulting Connect dialog, enter connection options, and click OK to connect.

You can also connect to or disconnect from a database with the Connect and Disconnect commands in the SQL menu, or by executing a CONNECT or DISCONNECT statement.


Keyboard shortcuts
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Starting Interactive SQL

 Keyboard shortcuts

Interactive SQL provides the following keyboard shortcuts.

Function key	Description
F1	Help
F5	Executes all text in the SQL Statements pane You can also perform this operation by clicking the Execute SQL Statement button on the toolbar
F9	Executes whatever text is selected in the SQL Statements pane If no text is selected, all of the statements are executed.
Pgup	Moves up in the current pane
Pgdn	Moves down in the current pane
Ctrl+Home	Moves to the top of the current pane
Ctrl+End	Moves to the bottom of the current pane
Ctrl+N	Opens a new window. Each window corresponds with a separate database connection
Esc	Clears the SQL Statements pane
Alt+Left cursor	Shows the previous SQL statement in the history list
Alt+Right cursor	Shows the next SQL statement in the history list
Ctrl+H	Shows the history of your executed SQL statements during the current session
F7	Displays the Lookup Table Name dialog In this dialog, you can find and select a table and then press Enter to insert the table name into the SQL Statements pane at the cursor position. Or, with a table selected in the list, press F7 again to show the columns in that table. You can then select a column and press Enter to insert the column name into the SQL Statements pane at the cursor position.
F8	Displays the Lookup Procedure Name dialog In this dialog, you can find and select a procedure and then press Enter to insert the procedure name into the SQL Statements pane at the cursor position.
Ctrl+S	Saves the contents of the SQL Statements pane
Alt+F4	Exits Interactive SQL


Using Interactive SQL to display data
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

 Using Interactive SQL to display data

One of the principal uses of Interactive SQL is to browse the information in databases. This section shows how to query the information in the sample database.

You can display database information using the SELECT statement in Interactive SQL. The following example shows the command to type in the SQL Statements pane. Once you have typed the command, you must click the Execute SQL Statement button on the toolbar to carry out the command.

After you execute the statement, the data (called a result set) appears in the Results pane. You can use the scroll bars to see areas of the table that are outside your current view of the pane.

♦ To list all the columns and rows of the employee table:

1	Start Interactive SQL and connect to the sample database.

2	Type the following in the SQL Statements pane:

SELECT *

FROM employee

3	On the toolbar, click the Execute SQL Statement button.

Emp_id	Manager_id	Emp_lname	emp_fname	...
102	501	Fran	Whitney	...
105	501	Matthew	Cobb	...
129	902	Philip	Chin	...
148	1293	Julie	Jordan	...
160	501	Robert	Breault	...
...
  For more information on Select statements, see Selecting Data from Database Tables.


Working with commands in Interactive SQL
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

 Working with commands in Interactive SQL

The following chapters describe some of the commands you can use in Interactive SQL.  This section describes general tasks for working with commands in Interactive SQL.

All SQL statements can be entered as commands in the top pane of the Interactive SQL viewer. When you are finished typing, you need to execute the statement to run it.

♦ To enter a command:

•	In the SQL Statements pane, type the command.

♦ To execute a command, do one of the following:

•	Press the Execute SQL Statement button or choose SQL [image] Execute or press F5.

♦ To clear the SQL Statements pane:

•	Choose Edit [image] Clear SQL or press Escape.

Tips
You can execute only a selection of text in the SQL Statements button by pressing F9.

Any messages about an executed statement, such as syntax errors, appear in the Messages pane.

 Canceling an Interactive SQL command

 Combining multiple statements

 Looking up tables, columns, and procedures

 Recalling commands

 Saving, loading, and running command files

 Logging commands

 Interrupting commands


Canceling an Interactive SQL command
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Canceling an Interactive SQL command

The Stop button cancels a command.

A Stop operation stops current processing and prompts for the next command. If a command file was being processed, you are prompted for an action to take (Stop command file, Continue, or Exit Interactive SQL). These actions can be controlled with the Interactive SQL ON_ERROR option (see ON_ERROR option).

Reported errors

When an abort is detected, one of three different errors will be reported depending upon when the abort is detected.

1	If the abort is detected when Interactive SQL is processing the request (as opposed to the database engine), then the following message is displayed:

ISQL command terminated by user

Interactive SQL stops processing immediately and the database transaction is left alone.

2	If the cancel is detected by the database engine while processing a standard data manipulation command (SELECT, INSERT, DELETE, and UPDATE) and the engine is not running in bulk operations mode, then the following message is displayed.

Statement interrupted by user.

The effects of the current command are undone, but the rest of the transaction is left intact.

3	If the abort is detected while the database engine is processing a data definition command (CREATE, DROP, ALTER, etc.), the following message appears:

Terminated by user -- transaction rolled back

Since data definition commands all perform a COMMIT automatically before the command starts, the effect of the ROLLBACK is to just cancel the current command.

This message also occurs when the database engine is running in bulk operations mode executing a command that modifies the database (INSERT, UPDATE, and DELETE). In this case, ROLLBACK cancels not only the current command, but everything that has been done since the last COMMIT. In some cases, it may take a considerable amount of time for the database engine to perform the automatic ROLLBACK.


Combining multiple statements
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Combining multiple statements

The Interactive SQL environment allows multiple statements to be entered at the same time. This can be done by ending each statement with a semi-colon (;).

♦ To enter multiple statements in SQL Statements pane:

1	Try entering the following three commands into the SQL Statements pane.

UPDATE employee

SET dept_id = 400,

    manager_id = 1576

WHERE emp_id = 467;

UPDATE employee

SET dept_id = 400,

    manager_id = 1576

WHERE emp_id = 195;

SELECT *

FROM employee

WHERE emp_id IN ( 195, 467 );

2	On the toolbar, click the Execute SQL Statement button. All three statements are executed. After execution, the commands remain in the SQL Statements pane. If you want to clear this pane, press the Esc key.


Looking up tables, columns, and procedures
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Looking up tables, columns, and procedures

While you are entering commands in Interactive SQL, you can look up the names of tables, columns, or procedures stored in the current database and insert them at your cursor position.

♦ To look up the names of tables in the database:

1	Choose Tools [image] Lookup Table Name.

2	Find and select the table.

3	Click OK to insert the table name into the SQL Statements pane.

♦ To look up column names in the database:

1	Choose Tools [image] Lookup Table Name.

2	Find and select the table containing the column.

3	Click Show Columns.

4	Select the column and click OK to insert the column name into the SQL Statements pane.

♦ To look up the names of procedures in the database:

1	Choose Tools [image] Lookup Procedure Name.

2	Find and select the procedure.

3	Click OK to insert the procedure name into the SQL Statements pane.

In the tables and procedures lookup dialogs, you can enter the first few characters of the table or procedure you are looking for. After you type something in the field, the dialog waits a short time, then narrows the list to include only those items that start with the text you entered.

You can use the standard SQL wild card metacharacter % to mean "match anything". For example, to list only those tables ending in order, type %order into the look up field. To list all items containing the word java, type %java%. Clearing the search criteria from the look up field resets the filtering to display all the items.


Recalling commands
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Recalling commands

When you execute a command, Interactive SQL automatically saves it in a history list that lasts for the duration of the current session. Interactive SQL maintains a record of  up to 50 of the most recent commands.

You can view the entire list of commands in the Command History dialog. To access the Command History dialog, press CTRL +H, or click the book icon in the toolbar.

The most recent commands appear at the bottom of the list. To recall a command, highlight it and click OK. It will appear in the SQL Statements pane of Interactive SQL.

You can also recall commands without the Command History dialog. Use the arrows in the toolbar to scroll back and forward through your commands, or press ALT + Right Arrow and ALT + Left Arrow.

You can also save commands in text files, so that you can use them in a subsequent Interactive SQL session. For more information, see Saving, loading, and running command files.


Saving, loading, and running command files
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Saving, loading, and running command files

You can save the commands currently present in the SQL Statements pane so that they are available for future Interactive SQL sessions. The file in which you save them is called a command file (also known as a script).

When you begin a new session, you can load the contents of a command file into the SQL Statements pane, or you can run the contents immediately.

♦ To save the commands in the SQL Statements pane:

1	Choose File [image] Save As.

2	In the Save dialog, specify a location, name and format for the file. Click Save when finished.

♦ To load commands from a file into the SQL Statements pane:

1	Choose File [image] Open.

2	In the Open dialog, find and select the file. Click Open when finished.

♦ To run a command file immediately:

1	Choose File [image] Run Script.

2	In the Open dialog, find and select the file. Click Open when finished.

You can supply a command file as a command line argument for Interactive SQL.

The Run Script menu item is the functional equivalent of a READ statement. For example, in the SQL Statements pane, you can also run a command file by typing:

READ 'c:\filename.sql'

where c:\filename.sql is the path, name, and extension of the file. You can enclose the string in single quotation marks (as shown) if you want, but they are only required if the path contains embedded spaces


Logging commands
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Logging commands

With the Interactive SQL logging feature, you can record commands as you execute them. Interactive SQL continues to record until you stop the logging process, or until you end the current session. The recorded commands are stored in a log file.

♦ To begin logging Interactive SQL commands:

1	Choose SQL [image] Start Logging.

2	In the Save dialog, specify a location and name for the log file.

3	Click Save when finished.

♦ To stop logging Interactive SQL commands:

•	Choose SQL [image] Stop Logging.

Tips
You can also start and stop logging by typing in the SQL Statements pane. To start logging, type and execute START LOGGING 'c:\filename.sql', where c:\filename.sql is the path, name, and extension of the log file. A log file must have the .SQL extension. You only need to include the single quotation marks if the path contains embedded spaces. To stop logging, type and execute STOP LOGGING.

Once you start logging, all commands that you try to execute are logged, including ones that do not execute properly.


Interrupting commands
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Working with commands in Interactive SQL

 Interrupting commands

♦ To interrupt an Interactive SQL command:

•	On the toolbar, click the Interrupt the SQL statement button.

or

Choose SQL [image] Stop.

This operation stops current processing and prompts for the next command. If a command file was being processed, you are prompted for an action to take.


Configuring Interactive SQL
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

 Configuring Interactive SQL

You can configure different Interactive SQL viewer options in the Options dialog. This dialog provides settings for commands, viewer appearance, import/export features and messages.

Each option can be set from the interface, or using the SET OPTION statement.

♦ To access the Options dialog:

•	Choose Tools [image] Options.

The tabs of the Options dialog are described in the following sections.

 Commands dialog tab

 Appearance dialog tab

 Import/Export dialog tab

 Messages dialog tab


Commands dialog tab
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Configuring Interactive SQL

 Commands dialog tab

The Commands tab of the Options dialog has the following components.

Commit options    Lets you choose when transactions are committed. You can choose to commit automatically after each transaction is executed or only when you exit your Interactive SQL session. You can also commit manually by entering an explicit COMMIT command whenever appropriate.

  For more information, see AUTO_COMMIT option in the ASA Reference.

When an error occurs    Lets you configure how Interactive SQL responds to errors.

  For more information, see ON_ERROR option in the ASA Reference.

Echo Command Files To Log option    When you start logging, this option causes SQL statements executed from script files (or command files) to be copied to the log along with the SQL statements entered interactively. If this option is disabled, only SQL statements entered interactively are copied to the log when you start logging.

  For more information, see ECHO option in the ASA Reference.

Show multiple result sets    Enables or disables the display of multiple result sets. For example, you can use this feature when you create a procedure containing multiple SELECT statements. If this option is enabled, you can see each result set when you call the procedure.

If you are using the jConnect driver, choosing to show multiple result sets requires Interactive SQL to wait for an entire result set to be retrieved before any rows are displayed. This may result in longer waits for large result sets.

Automatically refetch results    Enables or disables the ability of Interactive SQL to automatically regenerate the most recent result set after you execute an INSERT, UPDATE, or DELETE statement. For example, if you are updating a table with the Results pane showing the rows about to be affected, this option causes Interactive SQL to automatically refresh the Results pane to reflect the executed changes.

  For more information, see AUTO_REFETCH option in the ASA Reference.


Appearance dialog tab
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Configuring Interactive SQL

 Appearance dialog tab

The Appearance tab of the Options dialog has the following components.

•	Display null values as    Lets you specify how you want nulls displayed in the table columns when you browse data.

  For more information, see NULLS option in the ASA Reference.

•	Maximum number of rows to display    Lets you limit the number of rows displayed.

•	Application look and feel    Lets you choose the viewer appearance. You have three options:

•	Metal provides the standard Java look

•	CDE/Motif provides the standard Motif look

•	Windows provides the standard Windows look


Import/Export dialog tab
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Configuring Interactive SQL

 Import/Export dialog tab

The Import/Export tab of the Options dialog has the following components.

•	Default export format    Lets you choose the default file format for exporting. This format is automatically selected in the Files of Type field in the Save dialog, but you can still choose other formats. The default is also used when Interactive SQL interprets an OUTPUT statement if no format is specified.

•	Default import format    Lets you choose the default file format for importing. This format is automatically selected in the Files of Type field in the Open dialog, but you can still choose other formats. The default is also used when Interactive SQL interprets an INPUT statement if no format is specified.

•	ASCII options    Let you specify the default symbols that are used for the field separator, quote string and escape character when you import or export data in the ASCII format.

  For more information on importing and exporting see Importing and Exporting Data in the ASA User's Guide.


Messages dialog tab
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 13.  Using Interactive SQL

        Configuring Interactive SQL

 Messages dialog tab

The Messages tab of the Options dialog has the following components.

•	Execution plan options    Lets you determine the level of detail given about the way your query is optimized. You have the following options:

•	None    No information about an execution appears in the Messages pane.

•	Short plan    Basic information about an execution appears in one line in the Messages pane. This line can show the table(s) accessed and whether the rows are to be read sequentially or accessed through an index. This plan is the default.

•	Long plan    Detailed information about an execution appears in multiple lines in the Messages pane.

•	Measure execution time for SQL statements    Enables or disables the ability of Interactive SQL to measure the time it takes for a statement to execute. When this option is enabled, the time appears in the Messages pane.

•	Default number of lines in Messages pane    Lets you specify the initial height (in lines) of the Messages pane. The default is 7 lines.

  For more information, see Reading access plans in the ASA User's Guide.


CHAPTER 14.  Selecting Data from Database Tables
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 14.  Selecting Data from Database Tables

About this chapter

This chapter introduces SQL queries, which retrieve data from a database.  Queries are built using the SELECT statement.

  This chapter describes exclusively how to select data from a single table at a time. For information about selecting data from multiple related tables, see Joining Tables. In addition, Obtaining Aggregate Data describes how to group your data and perform calculations on the data in one or more columns.

 Introduction

 Looking at the information in a table

 Ordering query results

 Selecting columns from a table

 Selecting rows from a table

 Comparing dates in queries

 Pattern matching in search conditions

 Matching rows by sound

 Compound search conditions in the WHERE clause

 Short cuts for typing search conditions

 Running command files


Introduction
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Introduction

The SELECT statement is a versatile command. SELECT statements can become highly complex in applications retrieving very specific information from large databases. This tutorial uses only simple SELECT statements: more advanced queries are described in later tutorials.

  For more information about the full syntax of the select statement, see SELECT statement.

This tutorial uses the Adaptive Server Anywhere 7.0 Sample database. The sample database contains information about employees, departments, sales orders, and so on.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

        Introduction

 About this chapter

If you want to know...	Then see...
How to view information in a table with Interactive SQL	Viewing information in a table with Interactive SQL
How to enter query results	Ordering query results
How to select columns from a table	Selecting columns from a table
How to select rows from a table	Selecting rows from a table
How to compare dates in queries	Comparing dates in queries
How to use Pattern matching	Pattern matching in search conditions
How to match rows by sound	Matching rows by sound
How to use compound search conditions	Compound search conditions in the WHERE clause
Where to find information about short cuts for search conditions	Short cuts for typing search conditions
Where to find information about command files	Running command files
Notes

Ideally, you should be running the Adaptive Server Anywhere software on your computer while you read and work through the examples in this chapter.

♦ To start Interactive SQL from Sybase Central:

•	To start Interactive SQL, right-click the database in Sybase Central and choose File [image] Open Interactive SQL.

or

To start Interactive SQL without a connection to a database, choose Tools [image] Adaptive Server Anywhere [image] Open Interactive SQL. The Connect dialog automatically appears.

♦ To start Interactive SQL on its own:

1	Select Interactive SQL from the Adaptive Server Anywhere program group in the Start menu. You can also type dbisql at a command prompt.

or

2	Type the following command at a system prompt:

dbisql

♦ To connect to the sample database from Sybase Central:

1	Start Sybase Central

Choose Start [image] Programs [image] Sybase SQL Anywhere 7 [image] Sybase Central 4.0.

2	Open the Connect dialog.

•	Click Tools [image] Connect.

•	If you are presented with a list of plugins, choose Adaptive Server Anywhere from the list.

The Connect dialog appears:

3	Select the ODBC Data Source Name option and click Browse. In the resulting list, choose ASA 7.0 Sample.

4	Click OK to establish the connection.

Each example instructs you to type commands into Interactive SQL and describes what you will see on your computer screen. If you cannot run the software as you read the tutorials, you will still be able to learn about SQL but you will not have the opportunity to experiment on your own.


Looking at the information in a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Looking at the information in a table

To view the information in a database's tables, you use commands.  You can enter multiple commands by separating them with semicolons.

 Viewing information in a table with Interactive SQL


Viewing information in a table with Interactive SQL
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

        Looking at the information in a table

 Viewing information in a table with Interactive SQL

You use the SELECT statement to view data from one or more tables. In this lesson, you look at one of the tables in the database. The command used will look at everything in a table called employee.

♦ To view the employee table:

•	Type the following in the Interactive SQL command window:

SELECT * FROM Employee

The SELECT statement retrieves all the rows and columns of the employee table, and the Interactive SQL Data window lists those that will fit:

Emp_id	Manager_id	Emp_fname	emp_lname	dept_id
102	501	Fran	Whitney	100
105	501	Matthew	Cobb	100
129	902	Philip	Chin	200
148	1293	Julie	Jordan	300
160	501	Robert	Breault	100
You will also see some information in the Interactive SQL statistics window. This information is explained later.

Case sensitivity

The table name employee is shown starting with an upper case E, even though the real table name is all lower case. Adaptive Server Anywhere databases can be created as case-sensitive or case-insensitive in their string comparisons, but are always case insensitive in their use of identifiers.

  For information on creating databases, see Initializing a database, or The Initialization utility.

You can type select or Select instead of SELECT. You can enter SQL keywords in upper case, lower case, or any combination of the two. In the manuals, upper case letters are generally used for SQL keywords.

A SELECT statement returns rows of data

The employee table contains a number of rows organized into columns. Each column has a name, such as emp_lname or emp_id. There is a row for each employee of the company, and each row has a value in each column. For example, the employee with employee ID 102 is Fran Whitney. Her manager has employee ID 501.

Tips
SQL keywords are case insensitive, meaning it doesn't matter how you capitalize them. For example, SELECT is the same as Select, which is the same as select. Some objects in the database (such as table and column names) may be case sensitive if the database is case sensitive, meaning you must capitalize these objects correctly.

You can type the statements all on one line, or break them up by pressing enter at the end of each line. Some SQL statements, such as the SELECT statement, consist of several parts, called clauses. In many examples, each clause is placed on a separate line for easier reading. You do not have to type them this way for them to process correctly.


Ordering query results
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Ordering query results

Unless otherwise requested, the database server returns the rows of a table in an order that has no meaning. Often it is useful to look at the rows in a table in a more meaningful sequence. For example, you might like to see employees in alphabetical order.

The following example shows how adding an ORDER BY clause to the SELECT statement causes the results to be retrieved in alphabetical order.

♦ List the employees in alphabetical order:

•	Type the following in the Interactive SQL command window:

SELECT * FROM employee ORDER BY emp_lname

Emp_id	manager_id	emp_fname	emp_lname	dept_id
1751	1576	Alex	Ahmed	400
1013	703	Joseph	Barker	500
591	1576	Irene	Barletta	400
191	703	Jeannette	Bertrand	500
1336	1293	Janet	Bigelow	300
Notes

The order of the clauses is important. The ORDER BY clause must follow the FROM clause and the SELECT clause.

 Ordering query results to improve performance


Ordering query results to improve performance
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

        Ordering query results

 Ordering query results to improve performance

Sometimes there is more than one possible way for Adaptive Server Anywhere to execute a query with an ORDER BY clause. You can use indexes to enable the database server to search the tables more efficiently.

Queries with WHERE and ORDER BY clauses

An example of a query that can be executed in more than one possible way is one that has both a WHERE clause and an ORDER BY clause.

SELECT *

FROM customer

WHERE id > 300

ORDER BY company_name

In this example, the server must decide between two strategies:

1	Go through the entire customer table in order by company name, checking each row to see if the customer id is greater than 300.

2	Use the key on the id column to read only the companies with id greater than 300. The results would then need to be sorted by company name.

If there are very few id values greater than 300, the second strategy is better because only a few rows are scanned and quickly sorted. If most of the id values are greater than 300, the first strategy is much better because no sorting is necessary.

Solving the problem

The example above could be solved by creating a two-column index on id and company_name. (The order of the two columns is important.) The server could then use this index to select rows from the table and have them in the correct order. However, keep in mind that indexes take up space in the database file and involve some overhead to keep up to date. Do not create indexes indiscriminately.


Selecting columns from a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Selecting columns from a table

Often, you are only interested in some of the columns in a table. For example, to make up birthday cards for employees you might want to see the emp_lname, dept_id, and birth_date columns.

♦ List the last name, department, and birthdate of each employee in:

•	Type the following:

SELECT emp_lname, dept_id, birth_date

FROM employee

emp_lname	dept_id	birth_date
Whitney	100	1958-06-05
Cobb	100	1960-12-04
Chin	200	1966-10-30
Jordan	300	1951-12-13
Breault	100	1947-05-13
Rearranging columns

The three columns appear in the order in which you typed them in the SELECT statement. If you want to rearrange the columns, simply change the order of the column names in the statement. For example, to put the birth_date column on the left, use the following statement:

SELECT birth_date, emp_lname , dept_id

FROM employee

Ordering rows

You can order rows and look at only certain columns at the same time as follows:

SELECT birth_date, emp_lname , dept_id

FROM employee

ORDER BY emp_lname

As you might have guessed, the asterisk in

SELECT * FROM employee

is a short form for all columns in the table.


Selecting rows from a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Selecting rows from a table

Sometimes you will not want to see information on all the employees in the employee table. Adding a WHERE clause to the SELECT statement allows only some rows to be selected from a table.

For example, suppose you would like to look at the employees with first name John.

♦ List all employees named John:

•	Type the following:

SELECT *

FROM employee

WHERE emp_fname = 'John'

emp_id	manager_id	emp_fname	emp_lname	dept_id
318	1576	John	Crow	400
862	501	John	Sheffield	100
1483	1293	John	Letiecq	300
Notes

•	The apostrophes (single quotes) around the name 'John' are required. They indicate that John is a character string. Quotation marks (double quotes) have a different meaning. Quotation marks can be used to make otherwise invalid strings valid for column names and other identifiers.

•	The sample database is not case sensitive, so you would get the same results whether you searched for ' 'JOHN', 'john', or 'John'.

•	How you order clauses is important. The FROM clause comes first, followed by the WHERE clause, and then the ORDER BY clause. If you type the clauses in a different order, you will get a syntax error.

•	You do not need to split the statement into several lines. You can enter the statement into the command window in any format. If you use more than the number of lines that fit on the screen, the text scrolls in the Command window.

Again, you can combine what you have learned:

SELECT emp_fname, emp_lname, birth_date

FROM employee

WHERE emp_fname = 'John'

ORDER BY birth_date

The WHERE clause uses a search condition to select rows.  In this case the search condition is emp_fname = 'John'.


Comparing dates in queries
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Comparing dates in queries

Sometimes, you will not know exactly what value you are looking for, or you would like to see a set of values. You can use comparisons, other than equals, in the WHERE clause to select a set of rows that satisfy the search condition. The following example shows the use of a date inequality search condition.

♦ List all employees born before March 3, 1964:

•	Type the following:

SELECT emp_lname, birth_date

FROM employee

WHERE birth_date < 'March 3, 1964'

emp_lname	birth_date
Whitney	1958-06-05
Cobb	1960-12-04
Jordan	1951-12-13
Breault	1947-05-13
Espinoza	1939-12-14
Dill	1963-07-19
The database server knows that the birth_date column contains dates, and automatically converts the string 'March 3, 1964' to a date.


Pattern matching in search conditions
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Pattern matching in search conditions

Another useful type of search condition is a pattern match. In SQL, the word LIKE is used to search for patterns. The use of LIKE can be explained by example.

♦ List all employees whose last name begins with BR:

•	Type the following:

SELECT emp_lname, emp_fname

FROM employee

WHERE emp_lname LIKE 'br%'

emp_lname	emp_fname
Breault	Robert
Braun	Jane
The % in the search condition indicates that any number of other characters may follow the letters BR.

♦ List all employees whose surname begins with BR, followed by zero or more letters and a T, followed by zero or more letters:

•	Type the following:

SELECT emp_lname, emp_fname

FROM employee

WHERE emp_lname LIKE 'BR%T%'

emp_lname	emp_fname
Breault	Robert
The first % sign matches the string eaul, while the second % sign matches the empty string (no characters).

Another special character that can be used with LIKE is the _ (underscore) character, which matches exactly one character.

The pattern BR_U% matches all names starting with BR and having U as the fourth letter. In Braun the matches the letter A and the % matches N.


Matching rows by sound
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Matching rows by sound

With the SOUNDEX function, you can match rows by sound, as well as by spelling. For example, suppose a phone message was left for a name that sounded like "Ms. Brown". Which employees in the company have names that sound like Brown?

♦ List employees with surnames that sound like Brown:

•	Type the following:

SELECT emp_lname, emp_fname

FROM employee

WHERE SOUNDEX( emp_lname ) = SOUNDEX( 'Brown' )

emp_lname	emp_fname
Braun	Jane
 Jane Braun is the only employee matching the search condition.


Compound search conditions in the WHERE clause
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Compound search conditions in the WHERE clause

We have seen how equal (=) and less than (<) work as comparison operators. Adaptive Server Anywhere also supports other comparison operators, such as greater than (>), greater than or equal (>=), less than or equal (<=), and not equal (<>), as well as the functions LIKE and SOUNDEX.

These conditions can be combined using AND and OR to make more complicated search conditions.

♦ List all employees born before March 3, 1964, but exclude the employee named Whitney:

•	Type the following:

SELECT emp_lname, birth_date

FROM employee

WHERE birth_date < '1964-3-3'

AND emp_lname <> 'whitney'

emp_lname	birth_date
Cobb	1960-12-04
Jordan	1951-12-13
Breault	1947-05-13
Espinoza	1939-12-14
Dill	1963-07-19
Francis	1954-09-12


Short cuts for typing search conditions
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Short cuts for typing search conditions

Using the shortform BETWEEN

SQL has two short forms for typing in search conditions. The first, BETWEEN, is used when you are looking for a range of values. For example the following query:

SELECT emp_lname, birth_date

FROM employee

WHERE birth_date BETWEEN '1963-1-1'

AND '1965-3-31'

is equivalent to the following:

SELECT emp_lname, birth_date

FROM employee

WHERE birth_date >= '1963-1-1'

AND birth_date <= '1965-3-31'

Using the short form IN

The second short form, IN, may be used when looking for one of a number of values. The command

SELECT emp_lname, emp_id

FROM employee

WHERE emp_lname IN ('yeung','bucceri','charlton')

means the same as:

SELECT emp_lname, emp_id

FROM employee

WHERE emp_lname = 'yeung'

OR emp_lname = 'bucceri'

OR emp_lname = 'charlton'


Running command files
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

 Running command files

This section describes how to use the Interactive SQL command window to enter multiple commands at a time and how to process files consisting of a set of commands.

 Entering multiple statements

 Saving statements as command files

 Executing command files


Entering multiple statements
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

        Running command files

 Entering multiple statements

SQL statements can get quite large. You have already seen how to use the editor to enter statements on several lines. The Interactive SQL environment also allows multiple commands to be entered at the same time. This is done by ending each statement with a semi-colon (;).

Example: entering multiple statements

♦ To enter multiple statement in the Interactive SQL Command window

1	Try entering the following three commands into the Command window.

UPDATE employee

SET dept_id = 400,

    manager_id = 1576

WHERE emp_id = 467;

UPDATE employee

SET dept_id = 400,

    manager_id = 1576

WHERE emp_id = 195;

SELECT *

FROM employee

WHERE emp_id IN ( 195, 467 )

2	Press the execute key (F9). All three statements are executed. After execution, the commands are left in the Command window.

Outputting data to a file

Notice that the data for each command remains in the Data window only until the next command is executed. To keep a record of your data, you can save the output of each statement to a separate file. If statement1 and statement2 are two SELECT statements, then you can output them to file1 and file2, respectively, as follows:

Statement1; OUTPUT TO file1;
statement2; OUTPUT TO file2;


Saving statements as command files
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

        Running command files

 Saving statements as command files

You can save commands you enter in Interactive SQL to a command file. This keeps a permanent record of the SQL commands so they can be used later if you wish.

♦ To save statements as a command file:

1	Choose File [image] Save As from the menu bar. You are then prompted for a filename.

2	Type a file name (for example, transfer.sql) and press enter.

3	The command file can be run using the Interactive SQL READ command, but you should rollback the changes first. Press the escape key to clear the editor and then execute the ROLLBACK WORK command.

4	Now enter the following command:

READ transfer.sql

This command executes the command file transfer.sql which contains the three commands that we saved previously. As each command is executed, it flashes up in the Command window.

What are command files?

Command files are just ASCII files containing SQL statements as you see them in the editor. You can use any editor you like to create command files. You can include comment lines along with the SQL statements to be executed.  Command files are also commonly called scripts.


Executing command files
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 14.  Selecting Data from Database Tables

        Running command files

 Executing command files

You can execute command files in the following ways:

•	You can use the Interactive SQL READ command to execute command files.

The following statement executes the file temp.sql:

READ temp.sql

•	You can load a command file into the Interactive SQL Command window and execute it directly from there.

You load command files back into the Command window by choosing File [image] [image] Open. Enter transfer.sql when prompted for the file name.

•	You can supply a command file as a command line argument for Interactive SQL.

The Command window in Interactive SQL has a limit of 500 lines. For command files larger than this, you should use a generic editor capable of handling large files. The READ command has no limit on the number of lines that can be read.


CHAPTER 15.  Selecting Data from Multiple Tables
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 15.  Selecting Data from Multiple Tables

About this chapter

This chapter describes database queries that look at information in more than one table. To do this, SQL provides the JOIN operator. There are several different ways to join tables together in queries, and this chapter describes some of the more important ones.

 Viewing information from several tables

 Joining tables with the cross product

 Restricting a join

 Joining tables using key joins

 Joining tables using natural joins

 Joining tables using outer joins


Viewing information from several tables
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

 Viewing information from several tables

Sometimes it is necessary to view data from multiple related tables. To do this, you use a join to view the data in a useful and meaningful way.

 About this chapter

 Displaying a list of tables


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

        Viewing information from several tables

 About this chapter

If you want to know...	Then see...
How to display a list of tables	Displaying a list of tables
How to display all combinations of data from two tables	Joining tables with the cross product
How to make a join useful	Restricting a join
How to join two tables in which the join's foreign keys have the same name	Joining tables using key joins
How to join tables on columns with the same names	Joining tables using natural joins


Displaying a list of tables
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

        Viewing information from several tables

 Displaying a list of tables

In Interactive SQL, you can display a list of tables by pressing the f7 key.

Highlight a table and click Show Columns to see the columns for that table. The escape key takes you back to the table list, and pressing it again will take you back to the command window. Pressing enter instead of escape copies the highlighted table or column name into the command window at the current cursor position.

Press escape to leave the list.


Joining tables with the cross product
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

 Joining tables with the cross product

One of the tables in the sample database is sales_order, which lists the orders placed to the company. Each order has a sales_rep column, containing the employee ID of the sales representative responsible for the order. There are 648 rows in the sales_order table.

You can get information from two tables at the same time by listing both tables in the FROM clause of a SELECT query.

Example

The following example lists all the data in the employee table and the sales_order table:

SELECT *

FROM sales_order

CROSS JOIN employee

The results of this query, displayed in the Interactive SQL data window, match every row in the employee table with every row in the sales_order table. Since there are 75 rows in the employee table and 648 rows in the sales_order table, there are 75 ( 648 = 48,600 rows in the result of the join. Each row consists of all columns from the sales_order table followed by all columns from the employee table. This join is called a full cross product.

The cross product join is a simple starting point for understanding joins, but not very useful in itself.


Restricting a join
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

 Restricting a join

Consider the sales_order and employee tables. The most natural way to make a join between these tables useful is to insist that the sales_rep in the sales_order table be the same as the one in the employee table in every row of the result. Then each row contains information about an order and the sales representative responsible for it.

To do this, add an ON phrase to the previous query to show the list of employees and their course registrations.

Example 1

♦ Restrict the query:

•	Type the following in the Interactive SQL command window:

SELECT *

FROM sales_order JOIN employee

ON sales_order.sales_rep = employee.emp_id

The table name is given as a prefix to identify the columns. Although not strictly required in this case, using the table name prefix clarifies the statement, and is required when two tables have a column with the same name. A table name used in this context is called a qualifier.

The results of this query contain only 648 rows (one for each row in the sales_order table). Of the original 48,600 rows in the join, only 648 of them have the employee number equal in the two tables.

Example 2

The following query is a modified version that fetches only some of the columns and orders the results.

♦ Modify the query:

•	Type the following in the Interactive SQL command window:

SELECT employee.emp_lname, sales_order.id,

sales_order.order_date

FROM sales_order JOIN employee

ON sales_order.sales_rep = employee.emp_id

ORDER BY employee.emp_lname

You can also create joins in Visual Basic. In this example, you will create a form to display all of the sales orders, grouped by the employee who dealt with them.


Joining tables using key joins
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

 Joining tables using key joins

Many common joins are between two tables related by a foreign key. The most common join restricts foreign key values to be equal to primary key values. The example you have already seen restricts foreign key values in the sales_order table to be equal to the primary key values in the employee table.

SELECT emp_lname, id, order_date

FROM sales_order JOIN employee

ON sales_order.sales_rep = employee.emp_id

The query can be more simply expressed using a KEY JOIN:

SELECT emp_lname, id, order_date

FROM sales_order

KEY JOIN employee

The join operator KEY JOIN is just a short cut for typing the ON phrase; the two queries are identical.

If you look at the diagram of the employee database, foreign keys are represented by lines between tables. You can use the KEY JOIN operator anywhere two tables are joined by a line in the diagram.

  To view the employee database diagram, see The sample database.

Joining more than two tables

Two or more tables can be joined using join operators. The following query uses four tables to list the total value of the orders placed by each customer. It connects the four tables customer, sales_order, sales_order_items and product using the single foreign-key relationships between each pair of these tables.

SELECT company_name,

SUM(sales_order_items.quantity *

product.unit_price) AS value

FROM customer

KEY JOIN sales_order

KEY JOIN sales_order_items

KEY JOIN product

GROUP BY company_name

Company_name	value
Able Inc.	6120
AMF Corp.	3624
Amo & Sons	3216
Amy's Silk Screening	2028
Avco Ent	1752
...	...
Note

The example above uses the SUM operator, which is an example of an aggregate function. Aggregate functions work with GROUP BY clauses to return values for each row group. In this example, the sum of sales_order_items.quantity * product.unit_price - that is, the total amount of money paid per product type - is calculated for each company_name, thereby returning each company's sales.

  For more information on aggregate functions, see A first look at aggregate functions.


Joining tables using natural joins
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

 Joining tables using natural joins

The NATURAL JOIN operator joins two tables based on common column names. In other words, Adaptive Server Anywhere generates a ON phrase that equates the common columns from each table.

Example

For example, for the following query:

SELECT emp_lname, dept_name

FROM employee

NATURAL JOIN department

the database server looks at the two tables and determines that the only column name they have in common is dept_id. The following ON phrase is internally generated and used to perform the join:

FROM employee JOIN department

ON employee.dept_id = department.dept_id

Errors using NATURAL JOIN

This join operator can cause problems by equating columns you may not intend to be equated. For example, the following query generates unwanted results:

SELECT *

FROM sales_order

NATURAL JOIN customer

The result of this query has no rows.

The database server internally generates the following ON phrase:

FROM sales_order JOIN customer

ON sales_order.id = customer.id

The id column in the sales_order table is an ID number for the order. The ID column in the customer table is an ID number for the customer. None of them matched. Of course, even if a match were found, it would be a meaningless one.


Joining tables using outer joins
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

 Joining tables using outer joins

In the previous examples, you created joins to display all data from two or more tables with matching key values. These joins are called inner joins. Sometimes, you may wish to display all data from a table containing a primary key, as well as the data from a second table whose foreign key values match the primary key values of the first table. To do this, you use an outer join.

Example

This SQL statement displays all of the rows of the customer table, along with all of the rows from the sales_order table where the customer ID field agrees:

Select *

from customer, sales_order

left outer join sales_order

on customer_id = sales_order.cust_id

The result set of this SQL statement will be all of the rows from the customer table, and whenever a customer has placed one or more orders, then that customer's orders from the sales_order table will be displayed alongside his personal information from the customer table.

 Creating outer joins on three or more tables


Creating outer joins on three or more tables
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 15.  Selecting Data from Multiple Tables

        Joining tables using outer joins

 Creating outer joins on three or more tables

You can create multiple outer joins in a SQL statement. However, outer joins can sometimes be ambiguous. For instance, consider a SELECT statement of the following form:

Select *

from tableA, tableB, tableC

left outer join tableA

on tableA.colA = tableB.colB

left outer join tableB

on tableB.colB = tableC.colC

In this case, it is unclear how the user wants SQL to evaluate this statement. If the first outer join is performed first, then all of the rows of tableA will be extracted, as well as those of tableB which match rows in tableA, and those in tableC which match rows in tableB. If, however, the second outer join is performed first, the all of the rows of tableB will be extracted, along those in tableC and tableA that match tableB's rows. You can remove this ambiguity by using parentheses to instruct SQL how to evaluate the statements.

This example illustrates some of the complexity that you can build into queries.  For more information on joins, see Joins: Retrieving Data from Several Tables in the ASA User's Guide.


CHAPTER 16.  Selecting Aggregate Data
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 16.  Selecting Aggregate Data

About this chapter

This chapter describes how to construct queries that tell you aggregate information. Examples of aggregate information are as follows:

•	The total of all values in a column

•	The number of distinct entries in a column

•	The average value of entries in a column

 Summarizing data

 A first look at aggregate functions

 Using aggregate functions to obtain grouped data

 Restricting groups


Summarizing data
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 16.  Selecting Aggregate Data

 Summarizing data

Sometimes it is worthwhile to examine aspects of the data in your table that you can't see with simple queries. For example, you may wish to find the average amount of money that a customer pays for an order, or to see how many employees work for each department. For these types of tasks you use aggregate functions.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 16.  Selecting Aggregate Data

        Summarizing data

 About this chapter

If you want to know...	Then see...
How to view summary information about an entire table	A first look at aggregate functions
How to view summary information about groups in a table	Using aggregate functions to obtain grouped data
How to view summary data about a restricted set of groups in a table	Restricting groups


A first look at aggregate functions
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 16.  Selecting Aggregate Data

 A first look at aggregate functions

Suppose you want to know how many employees there are. The following statement retrieves the number of rows in the employee table:

SELECT count( * )

FROM employee

count( * )

75

The result returned from this query is a table with only one column (with title count( * )) and one row, which contains the number of employees.

The following command is a slightly more complicated aggregate query:

SELECT   count( * ),

min( birth_date ),

max( birth_date )

FROM employee

count( * )	min( birth_date )	max( birth_date )
75	1936-01-02	1973-01-18
The result set from this query has three columns and only one row. The three columns contain the number of employees, the birthdate of the oldest employee, and the birthdate of the youngest employee.

COUNT, MIN and MAX are called aggregate functions. Each of these functions summarizes information for an entire table. In total, there are six aggregate functions: MIN, MAX, COUNT, AVG, SUM, and LIST. All but COUNT have the name of a column as a parameter. As you have seen, COUNT has an asterisk as its parameter.


Using aggregate functions to obtain grouped data
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 16.  Selecting Aggregate Data

 Using aggregate functions to obtain grouped data

In addition to providing information about an entire table, aggregate functions can be used on groups of rows.

♦ To list the number of orders each sales representative is responsible for:

•	Type the following:

SELECT sales_rep, count( * )

FROM sales_order

GROUP BY sales_rep

sales_rep	count( * )
129	57
195	50
299	114
467	56
667	54
The results of this query consist of one row for each sales rep ID number, containing the sales rep ID, and the number of rows in the sales_order table with that number.

Whenever GROUP BY is used, the resulting table has one row for each different value found in the GROUP BY column or columns.

You can also view grouped data in Visual Basic.

A common error with GROUP BY

A common error with GROUP BY is to try to get information that cannot properly be put in a group. For example,

SELECT sales_rep, emp_lname, count( * )

FROM sales_order

KEY JOIN employee

GROUP BY sales_rep

gives the following error:

Function or column reference to 'emp_lname' in the select list must also appear in a GROUP BY

SQL does not realize that each of the result rows for an employee with a given ID have the same value of emp_lname. An error is reported since SQL does not know which of the names to display.

However, the following is valid:

SELECT sales_rep, max( emp_lname ), count( * )

FROM sales_order

KEY JOIN employee

GROUP BY sales_rep

The max function chooses the maximum (last alphabetically) surname from the detail rows for each group. The surname is the same on every detail row within a group so the max is just a trick to bypass a limitation of SQL.

Note

These examples both use joins. Joins are a way of displaying data from multiple tables in a meaningful way. For more information on joins, see Joining Tables.


Restricting groups
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 16.  Selecting Aggregate Data

 Restricting groups

You have already seen how to restrict rows in a query using the WHERE clause. You can restrict GROUP BY clauses by using the HAVING keyword.

♦ To list all sales reps with more than 55 orders:

•	Type the following:

SELECT sales_rep, count( * )

FROM  sales_order

KEY JOIN employee

GROUP BY sales_rep

HAVING count( * ) > 55

sales_rep	count( * )
129	57
299	114
467	56
1142	57
Order of clauses
  GROUP BY must always appear before HAVING. In the same manner, WHERE must appear before GROUP BY.

HAVING clauses and WHERE clauses can be combined. When combining these clauses, the efficiency of the query can depend on whether criteria are placed in the HAVING clause or in the WHERE clause. Criteria in the HAVING clause restrict the rows of the result only after the groups have been constructed. Criteria in the WHERE clause are evaluated before the groups are constructed, and save time.

♦ To list all sales reps with more than 55 orders and an ID of more than 1000:

•	Type the following:

SELECT sales_rep, count( * )

FROM sales_order

KEY JOIN employee

WHERE sales_rep > 1000

GROUP BY sales_rep

HAVING count( * ) > 55

The following statement produces the same results.

♦ To list all sales reps with more than 55 orders and an ID of more than 1000:

•	Type the following:

SELECT sales_rep, count( * )

FROM sales_order

KEY JOIN employee

GROUP BY sales_rep

HAVING count( * ) > 55

AND sales_rep > 1000

The first statement is faster because it can eliminate making up groups for some of the employees. The second statement builds a group for each sales rep and then eliminates the groups with wrong employee numbers. For example, in the first statement, the database engine would not have to make up a group for the sales rep with employee ID 129. In the second command, the database engine would make up a group for employee 129 and eliminate that group with the HAVING clause.

Fortunately, Adaptive Server Anywhere detects this particular problem and changes the second query to be the same as the first. Adaptive Server Anywhere performs this optimization with simple conditions (nothing involving OR or IN). For this reason, when constructing queries with both a WHERE clause and a HAVING clause, you should be careful to put as many of the conditions as possible in the WHERE clause.


CHAPTER 17.  Selecting Data Using Subqueries
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 17.  Selecting Data Using Subqueries

About this chapter

This chapter shows how to use the results of one query as part of another SELECT statement. This is a useful tool in building more complex and informative queries.

 Introducing subqueries

 Preparing to use subqueries

 A simple subquery

 Comparisons using subqueries

 Using subqueries instead of joins


Introducing subqueries
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 17.  Selecting Data Using Subqueries

 Introducing subqueries

A relational database allows you to store related data in more than one table. The section Joining Tables outlines one way of extracting data from related tables. A second method involves subqueries - queries that appear in another query's WHERE clause or HAVING clause.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 17.  Selecting Data Using Subqueries

        Introducing subqueries

 About this chapter

If you want to know...	Then see...
When subqueries are used	Preparing to use subqueries
An example of a simple subquery	A simple subquery
How to compare column values to a single value returned by a subquery	Comparisons using subqueries
How to use a subquery instead of a join	Using subqueries instead of joins


Preparing to use subqueries
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 17.  Selecting Data Using Subqueries

 Preparing to use subqueries

Sometimes it is useful to use the results of one statement as part of another statement.

Example 1

For example, suppose that you need a list of order items for products that are low in stock.

You can look up the products for which there are less than 20 items in stock in the product table.

♦ To list all products for which there are less than 20 items in stock:

•	Type the following:

SELECT id, description, quantity

FROM product

WHERE quantity < 20

Id	description	quantity
401	Wool cap	12
This query shows that only wool caps are low in stock.

Example 2

You can list all the order items for wool caps with the following query:

♦ To list all orders for wool caps, most recent first:

•	Type the following:

SELECT *

FROM sales_order_items

WHERE prod_id = 401

ORDER BY ship_date DESC

Id	line_id	prod_id	quantity	ship_date
2082	1	401	48	1994-07-09
2053	1	401	60	1994-06-30
2125	2	401	36	1994-06-28
2027	1	401	12	1994-06-17
2062	1	401	36	1994-06-17
This two-step process of identifying items low in stock and identifying orders for those items can be combined into a single query using subqueries.


A simple subquery
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 17.  Selecting Data Using Subqueries

 A simple subquery

In the previous sections, you had to obtain a result from one SQL statement before you used it in another. SQL provides you with a way to use the results of one query in another in just one step. The following query incorporates a subquery.

Example 1

♦ To list order items for products low in stock:

•	Type the following:

SELECT *

FROM sales_order_items

WHERE prod_id IN

(	SELECT id

FROM product

WHERE quantity < 20 )

ORDER BY ship_date DESC

id	line_id	prod_id	quantity	ship_date
2082	1	401	48	1994-07-09
2053	1	401	60	1994-06-30
2125	2	401	36	1994-06-28
2027	1	401	12	1994-06-17
2062	1	401	36	1994-06-17
By using a subquery, the search can be carried out in just one query, instead of using one query to find the list of low-stock products and a second to find orders for those products.

The subquery in the statement is the phrase enclosed in parentheses:

(	SELECT id

FROM product

WHERE quantity < 20 )

The subquery makes a list of all values in the id column in the product table satisfying the WHERE clause search condition.

Example 2

Consider what would happen if an order for ten tank tops were shipped so that the quantity column for tank tops contained the value 18. The query using the subquery, would list all orders for both wool caps and tank tops. On the other hand, the first statement you used would have to be changed to the following:

SELECT *

FROM sales_order_items

WHERE prod_id IN ( 401, 300 )

ORDER BY ship_date DESC

The command using the subquery is an improvement because it still works even if data in the database is changed.


Comparisons using subqueries
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 17.  Selecting Data Using Subqueries

 Comparisons using subqueries

Two tables in the sample database are concerned with financial results. The fin_code table is a small table holding the different codes for financial data and their meanings:

♦ To list the revenue items from the fin_data table:

•	Type the following:

SELECT *

FROM fin_data

WHERE fin_data.code IN

 (	SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )

year	quarter	code	amount
1992	Q1	r1	1023
1992	Q2	r1	2033
1992	Q3	r1	2998
1992	Q4	r1	3014
1993	Q1	r1	3114
This example has used qualifiers to clearly identify the table to which the code column in each reference belongs. In this particular example, the qualifiers could have been omitted.

Notes about subqueries

In general, subqueries are restricted to one column name listed between SELECT and FROM: one select-list item. The following example does not make sense, since SQL would not know which column from fin_code to compare to the fin_data.code column.

SELECT *

FROM fin_data

WHERE fin_data.code IN

(	SELECT fin_code.code, fin_code.type

FROM fin_code

WHERE type = 'revenue' )

Further, while subqueries used with an IN condition may return several rows, a subquery used with a comparison operator must return only one row. For example the following command results in an error since the subquery returns two rows:

SELECT *

FROM fin_data

WHERE fin_data.code =

(	SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )

The IN comparison allows several rows. Two other keywords can be used as qualifiers for operators to allow them to work with multiple rows: ANY and ALL.

The following query is identical to the successful query above:

SELECT *

FROM fin_data

WHERE fin_data.code = ANY

(	SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )

While the = ANY condition is identical to the IN condition, ANY can also be used with inequalities such as, or, to give more flexible use of subqueries.

The word ALL is similar to the word ANY. For example, the following query lists financial data that is not revenues:

SELECT *

FROM fin_data

WHERE fin_data.code <> ALL

(	SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )

This is equivalent to the following command using NOT IN:

SELECT *

FROM fin_data

WHERE fin_data.code NOT IN

(	SELECT fin_code.code

FROM fin_code

WHERE type = 'revenue' )


Using subqueries instead of joins
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 17.  Selecting Data Using Subqueries

 Using subqueries instead of joins

Suppose you need a chronological list of orders and the company that placed them, but would like the company name instead of their customer ID. You can get this result using a join as follows:

Using a join

♦ To list the order id, date, and company name for each order since the beginning of 1994:

•	Type the following:

SELECT	 sales_order.id,

sales_order.order_date,

customer.company_name

FROM sales_order

KEY JOIN customer

WHERE order_date > '1994/01/01'

ORDER BY order_date

id	order_date	company_name
2473	1994-01-04	Peachtree Active Wear
2474	1994-01-04	Sampson & Sons
2036	1994-01-05	Hermanns
2106	1994-01-05	Salt & Pepper's
2475	1994-01-05	Cinnamon Rainbow's
For more on joins, see Joining Tables.

Using a subquery

The following statement obtains the same results using a subquery instead of a join:

SELECT sales_order.id,

sales_order.order_date,

(	SELECT company_name FROM customer

WHERE customer.id = sales_order.cust_id )

FROM sales_order

WHERE order_date > '1994/01/01'

ORDER BY order_date

The subquery refers to the cust_id column in the sales_order table even though the sales_order table is not part of the subquery. Instead, the sales_order.cust_id column refers to the sales_order table in the main body of the statement. This is called an outer reference. Any subquery that contains an outer reference is called a correlated subquery.

A subquery can be used instead of a join whenever only one column is required from the other table. (Recall that subqueries can only return one column.) In this example, you only needed the company_name column so the join could be changed into a subquery.

If the subquery might have no result, this method is called an outer join. The join in previous sections of the tutorial is more fully called an inner join.

Using an outer join

♦ To list all customers in Washington state together with their most recent order ID:

•	Type the following:

SELECT	 company_name, state,

(	SELECT MAX( id )

FROM sales_order

WHERE sales_order.cust_id = customer.id )

FROM customer

WHERE state = 'WA'

company_name	state	MAX(id)
Custom Designs	WA	2547
It's a Hit!	WA	(NULL)
The It's a Hit! company placed no orders, and the subquery returns NULL for this customer. Companies who have not placed an order are not listed when inner joins are used.

You could also specify an outer join explicitly. In this case a GROUP BY clause is also required.

SELECT company_name, state,

MAX( sales_order.id )

FROM customer

KEY LEFT OUTER JOIN sales_order

WHERE state = 'WA'

GROUP BY company_name, state


CHAPTER 18.  Updating the Database
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 18.  Updating the Database

About this chapter

This chapter describes how to make changes to the contents of your database. It includes descriptions of how to add rows, remove rows, and change the contents of rows, as well as how to make changes permanent or correct changes you have made.

 Introduction

 Adding rows to a table

 Modifying rows in a table

 Transactions in SQL

 Deleting rows

 Integrity checking


Introduction
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

 Introduction

To keep data current in your database, you need to know how to make changes to it. You can add rows to your table to include new data, or you can delete rows in your table to exclude outdated data. To update your existing records, you can modify rows in your table. Finally, to ensure the integrity of your database, you can check for errors.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Introduction

 About this chapter

If you want to know...	Then see...
How to add rows to your table	Adding rows to a table
How to update rows in your table	Modifying rows in a table
How to impliment transactions	Transactions in SQL
How to delete rows in your table	Deleting rows
How to check for errors in your data	Integrity checking


Adding rows to a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

 Adding rows to a table

Suppose that the company decides to sell a new product, a brown acrylic vest. This action requires you to add data to the product table of the sample database.

♦ To add a brown acrylic vest to the product table in Interactive SQL:

•	You can add this information to the database using the following INSERT statement:

INSERT

INTO product ( id, name, description, size, color, quantity, unit_price )

VALUES ( 800, 'Vest', 'Acrylic Vest', 'Extra Large', 'Brown', 42, 20.00 )

If you make a mistake and forget to specify one of the columns, Adaptive Server Anywhere reports an error.

NULL values in columns

The NULL value is a special value used to indicate that something is either not known or not applicable. Some columns are allowed to contain the NULL value, and others are not.


Modifying rows in a table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

 Modifying rows in a table

In most databases, you need to update records that are already stored in the database. For example, the manager ID should change when employees are transferred between departments, as well as the department ID.

♦ To transfer employee #195 to department 400 in Interactive SQL:

•	The following statement carries out both updates at the same time for employee Marc Dill (employee ID 195):

UPDATE employee

SET dept_id = 400, manager_id = 1576

WHERE emp_id = 195

The UPDATE and INSERT commands

Since SQL updates all rows that satisfy the conditions of the WHERE clause, sometimes more than one row is updated at one time. For example, if a group of sales employees are transferred into marketing and have their dept_id column updated, the following statement sets the manager_id for all employees in the marketing department to 1576.

UPDATE employee

SET manager_id = 1576

WHERE dept_id = 400

For employees already in the marketing department, no change is made.


Transactions in SQL
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

 Transactions in SQL

Adaptive Server Anywhere expects you to group your commands into transactions. You commit a transaction to make changes to your database permanent. When you alter your data, your alterations are not made permanent right away. Instead, they are stored in your transaction log and made are permanent when you enter the COMMIT command.

Knowing which commands or actions signify the start or end of a transaction lets you take full advantage of transactions.

Starting transactions

Transactions start with one of the following events:

•	The first statement following a connection to a database

•	The first statement following the end of a transaction

Completing transactions

Transactions complete with one of the following events:

•	A COMMIT statement makes the changes to the database permanent.

•	A ROLLBACK statement undoes all the changes made by the transaction.

•	A statement with a side effect of an automatic commit is executed:  Database definition commands, such as ALTER, CREATE, COMMENT, and DROP all have the side effect of an automatic commit.

•	A disconnection from a database performs an implicit rollback.

Options in Interactive SQL

Interactive SQL provides you with two options which let you control when and how transactions end:

•	If you set the option AUTO_COMMIT to ON, Interactive SQL automatically commits your results following every successful statement and automatically perform a ROLLBACK after each failed statement.

•	The setting of the option COMMIT_ON_EXIT controls what happens to uncommitted changes when you exit Interactive SQL. If this option is set to ON (the default), Interactive SQL does a COMMIT; otherwise it undoes your uncommitted changes with a ROLLBACK statement.

If you are using a data source
By default, ODBC operates in autocommit mode. Even if you have set the AUTO_COMMIT option to OFF in Interactive SQL, ODBC's setting will override Interactive SQL's. You can change ODBC's setting by using the SQL_ATTR_AUTOCOMMIT connection attribute. ODBC autocommit is independent of the CHAINED option.

Adaptive Server Anywhere also supports Transact-SQL commands, such as BEGIN TRANSACTION, for compatibility with Sybase Adaptive Server Enterprise. For further information, see An overview of Transact-SQL support in the ASA User's Guide.

 Making changes permanent

 Canceling changes

 Transactions and data recovery


Making changes permanent
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Transactions in SQL

 Making changes permanent

The SQL statement

COMMIT

makes all changes permanent.

You should use the COMMIT command after groups of statements that make sense together. For example, if you want to transfer money from one customer's account to another, you should add money to one customer's account, then delete it from the other's, and then commit, since in this case it does not make sense to leave your database with less or more money than it started with.

You can instruct Adaptive Server Anywhere to commit your changes automatically by setting the AUTOCOMMIT option to ON. When AUTOCOMMIT is set to ON, Adaptive Server Anywhere must update the database after every insert, update, and delete statement you make to it. This can slow down performace considerably, especially if your database is large. Therefore, it is a good idea to leave the AUTOCOMMIT option set to OFF.

Use COMMIT with care
When trying the examples in this tutorial, be careful not to COMMIT any changes until you are sure that you want to change the database permanently.

  For more information on Interactive SQL options, see Interactive SQL options.


Canceling changes
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Transactions in SQL

 Canceling changes

Any uncommitted change you make can be cancelled. SQL allows you to undo all of the changes you made since your last commit with one command:

ROLLBACK

The ROLLBACK statement

The ROLLBACK statement undoes all changes you have made to the database since the last time you made changes permanent (see Making changes permanent).

  For more information on Interactive SQL options, see Interactive SQL options.


Transactions and data recovery
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Transactions in SQL

 Transactions and data recovery

Suppose that a system failure or power outage suddenly takes your database engine down. Adaptive Server Anywhere is carefully designed to protect the integrity of your database in such circumstances. It provides you with a number of independent means of restoring your database. For example, it provides you with a log file which you may store on a separate drive so that should the system failure damage one drive, you still have a means of restoring your data. In addition, when you use a log file, Adaptive Server Anywhere does not have to update your database between COMMIT statements, which improves your database's performance.

Transaction processing allows the database server to identify states in which your data is in a consistent state. Transaction processing ensures that if, for any reason, a transaction is not successfully completed, then the entire transaction is undone, or rolled back. The database is left entirely unaffected by failed transactions.

Adaptive Server Anywhere's transaction processing ensures that the contents of a transaction are processed securely, even in the event of a system failure in the middle of a transaction.

  For a detailed description of data recovery mechanisms, see Backup and Data Recovery in the ASA User's Guide.


Deleting rows
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

 Deleting rows

Sometimes you will want to remove rows from a table. Suppose Rodrigo Guevara (employee ID 249) leaves the company. The following statement deletes Rodrigo Guevara from the employee table.

DELETE

FROM employee

WHERE emp_id = 249

With UPDATE and DELETE, the search condition can be as complicated as you need. For example, if the employee table is being reorganized, the following statement removes from the employee table all male employees hired between March 3, 1989 and March 3, 1990.

DELETE

FROM employee

WHERE sex = 'm'

    AND start_date between '1988-03-03'

    AND '1989-03-03'

Since you have made changes to the database that you do not want to keep, you should undo the changes as follows:

ROLLBACK


Integrity checking
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

 Integrity checking

Adaptive Server Anywhere automatically checks for some common errors in your data.

 Inserting duplicate data

 Inserting incorrect values

 Errors on DELETE or UPDATE


Inserting duplicate data
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Integrity checking

 Inserting duplicate data

For example, suppose you attempt to create a department but supply a dept_id value that is already in use:

To do this, enter the command:

INSERT

INTO department ( dept_id, dept_name, dept_head_id )

VALUES ( 200, 'Eastern Sales', 902 )

The INSERT is rejected, as it would make the primary key for the table not unique. Since dept_id field is a primary key duplicate values are not permitted.


Inserting incorrect values
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Integrity checking

 Inserting incorrect values

Another mistake is to type an incorrect value. The following statement inserts a new row in the sales_order table, but incorrectly supplies a sales_rep ID that does not exist in the employee table.

INSERT

INTO sales_order ( id, cust_id, order_date,

sales_rep)

VALUES ( 2700, 186, '1995-10-19', 284 )

There is a one-to-many relationship between the employee table and the sales_order table, with a join between the sales_rep field of the sales_order table and the emp_id field of the employee table. Only after a record in the table containing the primary key for the join (the employee table) has been entered can a corresponding record on table containing the foreign key (the sales_order table) be inserted.

Foreign key

The primary key for the employee table is the employee ID number. The sales rep ID number in the sales_rep table is a foreign key for the employee table, meaning that each sales rep number in the sales_order table must match the employee ID number for some employee in the employee table.

When you try to add an order for sales rep 284 you get an error message:

No primary key value for foreign key 'ky_so_employee_id' in table 'sales_order'

There isn't an employee in the employee table with that ID number. This prevents you from inserting orders without a valid sales rep ID. This kind of validity checking is called referential integrity checking, as it maintains the integrity of references among the tables in the database.

  For more information on primary and foreign keys, see Relations between tables.


Errors on DELETE or UPDATE
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 18.  Updating the Database

        Integrity checking

 Errors on DELETE or UPDATE

Foreign key errors can also arise when doing update or delete operations. For example, suppose you try to remove the R&D department from the department table. The dept_id field, being the primary key of the department table, constitutes the ONE side of a one-to-many relationship (the dept_id field of the employee table is the corresponding foreign key, and hence forms the MANY side). A record on the "one" side of a relationship may not be deleted until all corresponding records on the MANY side are deleted.

DELETE

FROM department

WHERE dept_id = 100

Example: DELETE errors

An error is reported indicating that there are other records in the database that reference the R&D department, and the delete operation is not carried out.

primary key for row in table 'department' is referenced in another table

In order to remove the R&D department, you need to first get rid of all employees in that department:

DELETE

FROM employee

WHERE dept_id = 100

You can now perform the deletion of the R&D department.

You should cancel these changes to the database (for future use) by entering a ROLLBACK statement:

ROLLBACK WORK

All changes made since the last successful COMMIT WORK will be undone. If you have not done a COMMIT, then all changes since you started Interactive SQL will be undone.

Example: UPDATE errors

The same error message is generated if you perform an update operation that makes the database inconsistent.

For example, the following UPDATE statement causes an integrity error:

UPDATE department

SET dept_id = 600

WHERE dept_id = 100

In all of the above examples, the integrity of the database was checked as each command was executed. Any operation that would result in an inconsistent database is not performed.

Example: checking the integrity after the COMMIT WORK is complete

It is possible to configure the database so that the integrity is not checked until the COMMIT WORK is done. This is important if you want to change the value of a referenced primary key; for example, changing the R&D department's ID from 100 to 600 in the department and employee tables. In order to make these changes, the database has to be inconsistent in between the changes. In this case, you must configure the database to check only on commits.

  For more information, see WAIT_FOR_COMMIT option.

You can also define foreign keys in such a way that they are automatically fixed. In the above example, if the foreign key from employee to department were defined with ON UPDATE CASCADE, then updating the department ID would automatically update the employee table.

In the above cases, there is no way to have an inconsistent database committed as permanent. Adaptive Server Anywhere also supports alternative actions if changes would render the database inconsistent.

  For more information, see the chapter Ensuring Data Integrity.


CHAPTER 19.  System Tables
  ASA Getting Started

    PART 3. Working With Databases

 CHAPTER 19.  System Tables

About this chapter

This chapter describes the system tables, several special tables found in every Adaptive Server Anywhere database. These system tables describe all the tables and columns in the database. The database server automatically updates the system table as the database structure is changed.

 The system tables

 The SYSCATALOG table

 The SYSCOLUMNS table

 Other system tables


The system tables
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 19.  System Tables

 The system tables

Adaptive Server Anywhere stores important information about your database in system tables. The data from tables can be viewed in the same way that the data from other tables can be viewed, but you can not update data from the system tables.

 About this chapter


About this chapter
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 19.  System Tables

        The system tables

 About this chapter

If you want to know...	Then see...
Where to find a listing of all the tables in the database	The SYSCATALOG table
Where to find information about the columns in a table	The SYSCOLUMNS table
How to find other information about the tables in your database	Other system tables


The SYSCATALOG table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 19.  System Tables

 The SYSCATALOG table

The first system table, syscatalog, lists all the tables in the database. You can view the data from the syscatalog table in the same way that you view the data from any other table in your database. For more information on viewing data, see Looking at the information in a table.

Alternately, you can display a lit of all the tables in the database by pressing the F7 key. This shows a list of the database's tables, each with a prefix of DBA, representing the owner of the tables. To leave the list, press escape.

The owner of the system tables

The owner of the system tables is the special user ID, SYS and the owner of the company tables is dba. In addition, there is a set of views owned by the special user ID DBO, which provide an emulation of the Sybase SQL Server system catalog; these tables are not discussed in this section.

Recall that dba is the user ID you used when connecting to the database from Interactive SQL. So far, you have simply typed the table names employee and department; SQL looked in SYSCATALOG for tables with those names created by dba. In this example, by typing SYS.SYSCATALOG you specified that SYSCATALOG was created by the user ID SYS. Note the similarity to the way column names are qualified, such as employee.emp_id.

Other columns in the system table

The other columns in this table contain other important information. For example, the column named Ncols is the number of columns in each table, and the column named tabletype identifies the table as a permanent table (also called a base table) or a view.


The SYSCOLUMNS table
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 19.  System Tables

 The SYSCOLUMNS table

Another important system table is called SYSCOLUMNS describing all the columns in all the tables in the database. To see the contents of a table, type the command, in which tablename represents the name of the table whose columns you wish to list:

SELECT *

FROM sys.syscolumns

WHERE tname = tablename

This command lists all the columns in the employee table. If you look at the columns to the right, you can see from the Coltype column that some columns in the employee table contain character information; others contain integer and date information.

Alternately, you may view a table's columns as follows:

1	Invoke the list of tables by pressing F7.

2	Select the table whose columns you wish to view.

3	Click on the columns button.


Other system tables
  ASA Getting Started

    PART 3. Working With Databases

      CHAPTER 19.  System Tables

 Other system tables

There are several other system tables in the database that will not be described in the tutorial. You can find out their names by examining SYS.SYSCATALOG and look at them if you want.

  For a full description of each of the system tables, see System Tables.