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


Introducing SQL Anywhere Studio
Version 7.0.2

Document ID:  MC0055

November 2000

 Copyright page

 About This Manual

 PART 1. SQL Anywhere Studio Overview

 PART 2. Getting Results with SQL Anywhere Studio

 PART 3. Appendixes


Copyright page
  Introducing SQL Anywhere Studio

 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: MC0055.

 Copyright page

 About This Manual

 PART 1. SQL Anywhere Studio Overview

 PART 2. Getting Results with SQL Anywhere Studio

 PART 3. Appendixes


About This Manual
  Introducing SQL Anywhere Studio

 About This Manual

 About this Manual

 Using this book

 Related books

 Documentation conventions


About this Manual
  Introducing SQL Anywhere Studio

    About This Manual

 About this Manual

Subject

This book introduces Sybase SQL Anywhere Studio, the complete relational database management system for mobile, embedded, and workgroup computing.

SQL Anywhere Studio is a set of software components for working with relational data. It includes the Adaptive Server Anywhere relational database management system, as well as data synchronization and replication technology. It also includes applications for designing and deploying databases, and for creating custom reports and data entry forms.

This book uses hands-on tutorials to introduce you to the components of SQL Anywhere Studio, showing you how they fit together to build everything from single-user database applications to distributed computing systems synchronizing data across thousands of databases.

Audience

This book is for all application developers and database administrators using SQL Anywhere Studio.

Before you begin

This book assumes an elementary familiarity with databases and SQL. If you do not have such a familiarity, you should read Getting Started with Adaptive Server Anywhere.


Using this book
  Introducing SQL Anywhere Studio

    About This Manual

 Using this book

Part I    describes the components of SQL Anywhere Studio, illustrates some of the computing architectures you can build from these components, and provides information about connecting to the database server and sample database.

Part II    introduces each of the components separately, with hands-on tutorials illustrating how to use them. More detail on each of the components is given in the other documents in the documentation set.


Related books
  Introducing SQL Anywhere Studio

    About This Manual

 Related books

Online documentation is most current and complete
The complete documentation set for SQL Anywhere Studio is available online. Online documentation is updated with each maintenance release. Printed documentation is not necessarily updated with each maintenance release. For this reason, the online documentation is the recommended source for the most current and complete information.

If you are unfamiliar with relational databases, you may wish to read Getting Started with Adaptive Server Anywhere, which introduces basic concepts of relational databases, and introduces the Adaptive Server Anywhere database management system.

A complete description of Adaptive Server Anywhere is provided in the other Adaptive Server Anywhere manuals:

•	Adaptive Server Anywhere Getting Started

•	Adaptive Server Anywhere User's Guide

•	Adaptive Server Anywhere Reference

•	Adaptive Server Anywhere Programming Interfaces Guide

A complete description of SQL Remote and MobiLink technologies is provided in the 	Replication and Synchronization Guide.


Documentation conventions
  Introducing SQL Anywhere Studio

    About This Manual

 Documentation conventions

This section lists the typographic and graphical conventions used in this documentation.

 Syntax conventions

 Graphic icons


Syntax conventions
  Introducing SQL Anywhere Studio

    About This Manual

      Documentation conventions

 Syntax conventions

The following conventions are used in the SQL syntax descriptions:

•	Keywords    All SQL keywords are shown in UPPER CASE. However, SQL keywords are case insensitive, so you can enter keywords in any case you wish; SELECT is the same as Select which is the same as select.

•	Placeholders    Items that must be replaced with appropriate identifiers or expressions are shown in italics.

•	Continuation    Lines beginning with ... are a continuation of the statements from the previous line.

•	Repeating items    Lists of repeating items are shown with an element of the list followed by an ellipsis (three dots). One or more list elements are allowed. If more than one is specified, they must be separated by commas.

•	Optional portions    Optional portions of a statement are enclosed by square brackets. For example, ...

RELEASE SAVEPOINT [ savepoint-name ]

... indicates that savepoint-name is optional. The square brackets should not be typed.

•	Options    When none or only one of a list of items must be chosen, the items are separated by vertical bars and the list enclosed in square brackets.

For example, ...

[ ASC | DESC ]

... indicates that you can choose one of ASC, DESC, or neither. The square brackets should not be typed.

•	Alternatives    When precisely one of the options must be chosen, the alternatives are enclosed in curly braces. For example ...

QUOTES { ON | OFF }

... indicates that exactly one of ON or OFF must be provided. The braces should not be typed.


Graphic icons
  Introducing SQL Anywhere Studio

    About This Manual

      Documentation conventions

 Graphic icons

The following icons are used in this documentation:

Icon	Meaning
	A client application.
	A database server, such as Sybase Adaptive Server Anywhere or Adaptive Server Enterprise.
	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 assist in sharing data among databases. Examples are the MobiLink Synchronization Server, SQL Remote Message Agent, and the Replication Agent (Log Transfer Manager) for use with Replication Server.
	A Sybase Replication Server.
	A programming interface.
 Copyright page

 About This Manual

 PART 1. SQL Anywhere Studio Overview

 PART 2. Getting Results with SQL Anywhere Studio

 PART 3. Appendixes


PART 1. SQL Anywhere Studio Overview
  Introducing SQL Anywhere Studio

 PART 1. SQL Anywhere Studio Overview

This part introduces the SQL Anywhere Studio database management and synchronization system.

 CHAPTER 1.  Introducing SQL Anywhere Studio

 CHAPTER 2.  SQL Anywhere Architecture

 CHAPTER 3.  Running and Connecting to a Database Server


CHAPTER 1.  Introducing SQL Anywhere Studio
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

 CHAPTER 1.  Introducing SQL Anywhere Studio

About this chapter

This chapter introduces the pieces that make up SQL Anywhere Studio. It also describes how to install it, and how to use the documentation.

 Welcome to SQL Anywhere Studio

 Installing SQL Anywhere Studio

 SQL Anywhere Studio documentation


Welcome to SQL Anywhere Studio
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 1.  Introducing SQL Anywhere Studio

 Welcome to SQL Anywhere Studio

With SQL Anywhere Studio you can deliver information to workgroup, mobile, and embedded database systems throughout your organization.

SQL Anywhere Studio includes the following components. More information on each component can be found later in this book.

Relational database systems

•	Adaptive Server Anywhere    The relational database at the core of the product is a transaction-based SQL database designed for personal and workgroup use. Adaptive Server Anywhere runs on a wide range of operating systems, including many flavors of Windows and UNIX, as well as on Novell NetWare. It runs on hardware ranging from multiple-CPU workgroup servers to the most modest PCs, as well as on Windows CE devices.

•	UltraLite    For building and deploying relational database applications on small devices, including the Palm Computing Platform and Windows CE. UltraLite has built-in support for MobiLink synchronization.

UltraLite lets you build relational databases with less than 50 KB of disk space, and is specifically intended for small devices.

Data synchronization technologies

•	MobiLink    For two-way synchronization of data between a central database and many remote UltraLite or Adaptive Server Anywhere databases. The central database can be Adaptive Server Anywhere, Adaptive Server Enterprise, or other DBMS such as Oracle, Microsoft SQL Server, or IBM DB2.

•	SQL Remote    For two-way message-based replication of data between a central database and many remote databases. With SQL Remote you can replicate data between laptop computers and a central database, using e-mail or dial-up access.

•	Replication Agent    For replicating data from Adaptive Server Anywhere databases to other databases via Sybase Replication Server.

Web deployment tools

•	PowerDynamo    For building and managing Web applications linked to your database. It allows you to store, manage, and access both static HTML and dynamic database data locally or over a corporate Intranet or the Internet.

Development, design, and administration tools

•	InfoMaker    For querying databases and creating sophisticated and effective custom reports of data. InfoMaker is also a personal data assistant that lets you work with data in many ways.

•	PowerDesigner    For designing, generating, documenting, and maintaining databases.

•	Sybase Central    The utility for managing Adaptive Server Anywhere databases, PowerDynamo Web sites, MobiLink synchronization, and SQL Remote replication.

InfoMaker, PowerDesigner, and the PowerDynamo development tools are available only on Windows operating systems. However, they can be used as clients of a database server running on any supported operating system.


Installing SQL Anywhere Studio
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 1.  Introducing SQL Anywhere Studio

 Installing SQL Anywhere Studio

How you install SQL Anywhere Studio depends on the operating system you are using. You must ensure that you are properly licensed before installing the software.

♦ On Windows NT or Windows 95/98:

•	Start the installation by running the setup.exe program in the root directory of the SQL Anywhere Studio CD-ROM. Follow the instructions in the setup wizard.

The setup program allows you to choose which of the components you wish to install.

♦ On Novell NetWare or Windows CE:

•	You must install Adaptive Server Anywhere for NetWare from a machine connected to the NetWare server and running Windows 95/98 or Windows NT. Run the SQL Anywhere Studio setup program and choose NetWare or Windows CE installation.

♦ On UNIX:

•	The installation instructions depend on which UNIX operating system you are using. For more information, consult the separate Read Me First booklet, which is included in SQL Anywhere Studio for UNIX.


SQL Anywhere Studio documentation
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 1.  Introducing SQL Anywhere Studio

 SQL Anywhere Studio documentation

The documentation for SQL Anywhere Studio includes the following:

•	Introducing SQL Anywhere Studio (this book).

•	Documentation for each of the components of the product.

•	Supporting documentation for Java.

Documentation is provided in several formats, so that you can choose the format that suits you best.

•	Online Help    You can see a complete set of documentation for SQL Anywhere Studio and its associated tools by installing the online Help.

HTML Help is the primary documentation format for Windows operating systems. To be able to install HTML Help, you must have Internet Explorer 4.0 or later, or have an HTML Help system installed. Windows 98 and later service releases of Windows 95 come with HTML Help already installed.

Windows Help is also provided as an alternative format for many of the components.

Documentation is provided in JavaHelp format on UNIX platforms.

•	Printed books    Depending on the product you purchase, you may have printed versions of some of the books. SQL Anywhere Studio documentation sets can be ordered separately.

The documentation can be accessed in several locations:

•	From the Start menu, click Programs [image] Sybase SQL Anywhere 7 [image] SQL Anywhere Documentation.

•	In each component of SQL Anywhere Studio, click Help on the toolbar. Depending on the component, there may be online books as well as online Help.


CHAPTER 2.  SQL Anywhere Architecture
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

 CHAPTER 2.  SQL Anywhere Architecture

About this chapter

Adaptive Server Anywhere is a relational database system with a multitude of uses, from a network database server hosting many clients to a compact embedded database. The UltraLite deployment technology allows you to use Adaptive Server Anywhere features on even the smallest of devices.

This chapter describes single database server architecture. For information about distributed database systems involving many databases sharing data through SQL Anywhere replication and synchronization technologies, see Sybase Replication Technologies.

 The pieces of a database system

 Embedded database architecture

 Client/server architecture

 Three tier computing architecture

 Using multiple databases

 Comparing Adaptive Server Anywhere and UltraLite


The pieces of a database system
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

 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. For example, on Windows CE, the UltraLite database is a file, but on the Palm Computing Platform it is stored in the Palm database.

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

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, Sybase Open Client, or Embedded SQL.

The programming interface provides a set of function calls for communicating with the database. For ODBC and JDBC, the library is commonly called a driver. The interface 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 one of the Sybase Internet Development tools like PowerBuilder tools, you may find that the tool provides its own methods for communicating with database servers, and hide 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:


Embedded database architecture
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

 Embedded database architecture

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: it is self-contained, with no connection to other databases. In this case it is common to refer to the database as an embedded database, because as far as the end user is concerned, the database is a part of the application. When a database server is used as an embedded database, it is sometimes called a database engine.

The Adaptive Server Anywhere personal database server is generally used for standalone applications. A client application connects through a programming interface to a database server running on the same machine:

UltraLite architecture

If you want to provide a database application for a small device such as a handheld organizer, you may want to use the UltraLite deployment technology. In UltraLite, the database server and the application are part of the same process, and the database server is specific to the application.

In this case, the database may not be a file on disk. The storage method for the database depends on the deployment platform.


Client/server architecture
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

 Client/server architecture

You can use SQL 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.


Three tier computing architecture
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

 Three tier computing architecture

In three-tier computing, application logic is held in an application server, such as Sybase Enterprise Application Server, which sits between the database server and the client applications. In many situations, a single application server may access multiple databases in addition to non-relational data stores. In the Internet case, client applications are browser-based, and the application server is generally a Web server extension.

Sybase Enterprise Application Server stores application logic in the form of components, and makes these components available to client applications. The components may be PowerBuilder components, Java beans, or COM components.

Application servers can also provide transaction logic to their client applications—guaranteeing that sets of operations are executed atomically across multiple databases. Adaptive Server Anywhere is well-suited to three-tier computing, and can participate in distributed transactions coordinated by Microsoft Distributed Transaction Coordinator. Both Sybase Enterprise Application Server and Microsoft Transaction Server use DTC to provide transaction services to their client applications.

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


Using multiple databases
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

 Using multiple databases

This section describes extensions to the Adaptive Server Anywhere architecture described above for the case where you want to use more than one database.

 Running multiple databases on a single database server

 Accessing data in other databases


Running multiple databases on a single database server
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

        Using multiple databases

 Running multiple databases on a single database server

Although the architecture diagrams above show a single database, the Adaptive Server Anywhere personal and network database servers can both mount several databases simultaneously. Each connection from an application must be to a single database, but an application can use separate connections to different databases, or a set of applications can work on different databases, all through the same database server.


Accessing data in other databases
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

        Using multiple databases

 Accessing data in other databases

You can access databases on multiple database servers, or even on the same server, using the Adaptive Server Anywhere Remote Data Access features. The application is still connected to a single database as in the architecture diagrams above, but by defining remote servers, you can use proxy tables that exist on the remote database as if they were in the database to which you are connected.


Comparing Adaptive Server Anywhere and UltraLite
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

 Comparing Adaptive Server Anywhere and UltraLite

This section highlights the differences between Adaptive Server Anywhere and UltraLite, to help you decide which technology is suited to your needs.

 Adaptive Server Anywhere

 UltraLite: the "small fingerprint" database

 Choosing a database


Adaptive Server Anywhere
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

        Comparing Adaptive Server Anywhere and UltraLite

 Adaptive Server Anywhere

For many years, Sybase SQL Anywhere has provided relational database technology designed specifically for the needs of mobile and embedded computing. The relational database at the heart of SQL Anywhere, Adaptive Server Anywhere, has been designed from the ground up with this market in mind:

•	Adaptive Server Anywhere is designed to operate efficiently with limited memory, CPU power, and disk space. Core features such as the query optimizer and the data caching mechanism are designed specifically to operate without extravagant use of resources. At the same time, Adaptive Server Anywhere contains the features needed to take advantage of workgroup servers, including support for many users, scalability over multiple CPUs, and advanced concurrency features.

•	Adaptive Server Anywhere is a cross-platform solution. The same database runs on Windows (95, 98, NT, and CE), UNIX, Novell NetWare, and Linux. You can move a database file from one operating system to another.

•	Adaptive Server Anywhere is designed to operate without administration. A self-tuning query optimizer, autostart, and autostop mechanisms make it ideal for use in embedded systems.

•	Ten years of experience working with successful customers have led to a rich set of field-tested features. Not only the standard checklist features of stored procedures, triggers, declarative referential integrity, full transaction processing and recovery, but all the little extras that can make the difference between a successful project and a failure.

•	SQL Anywhere's synchronization technologies (SQL Remote, MobiLink) mean that you can integrate Adaptive Server Anywhere databases into your organization's infrastructure.

•	With this release, more features designed specifically for mobile computing make Adaptive Server Anywhere even more compelling. Built-in scheduling and event handling mean that administrative tasks can be built into the database itself. Dynamic cache sizing means that the database uses the right amount of your system resources, with no tuning. An OLE DB driver makes Windows CE databases straightforward.


UltraLite: the "small fingerprint" database
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

        Comparing Adaptive Server Anywhere and UltraLite

 UltraLite: the "small fingerprint" database

With the appearance of small computing devices such as handheld computers, pagers, and mobile phones, it became clear that there was a demand for a database with even more modest memory requirements than Adaptive Server Anywhere. An obvious option is to deem some features of Adaptive Server Anywhere "inessential" and produce a trimmed down relational database engine, but Sybase's experience has shown that each application and each customer has a distinct set of features that are, for them, essential. Further, such an approach would mean that customers would have to learn two different databases, inevitably different in some ways.

Instead, Sybase developed UltraLite, a novel technology that uses a reference database and your application source code to generate a relational database engine containing only those features of Adaptive Server Anywhere used by your application. Each query is stored with a complete access plan for fast execution; the code needed to execute just those tasks you need are built into your UltraLite database engine. Each UltraLite database engine is different, but many are only a few tens of kilobytes, and can easily be run in a device as small as a pager.

Adaptive Server Anywhere serves as a reference database when you build your UltraLite application, and so your SQL statements, data types, and so on are exactly those of Adaptive Server Anywhere. UltraLite is a deployment technology for Adaptive Server Anywhere, not a different database system. The tasks that each UltraLite database engine is built to perform are carried out in a manner completely compatible with Adaptive Server Anywhere.

UltraLite has built-in MobiLink synchronization technology so that your application is linked into the information network.

UltraLite supports the Palm Computing Platform, Windows CE, and other operating systems used in small devices, such as VxWorks and Symbian EPOC. There is now also a Java version of UltraLite.


Choosing a database
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 2.  SQL Anywhere Architecture

        Comparing Adaptive Server Anywhere and UltraLite

 Choosing a database

This section describes the complementary features of Adaptive Server Anywhere and UltraLite databases, to help you choose the most appropriate technology for your needs.

If you are deploying mainly to PCs, Adaptive Server Anywhere is built to meet your needs. If you are deploying to small devices such as the Palm Computing Platform, UltraLite will fit the bill. If you are planning to deploy on a platform supported by both technologies, such as Windows CE, you should consider the following issues.

•	Application type    UltraLite database engines include only the code necessary to execute the tasks specified when your application is compiled, so you cannot use dynamic SQL to execute ad hoc queries against an UltraLite database. Further, each UltraLite database and database engine is for use by only a single application: if you want to use more than one application against a single database, you should choose Adaptive Server Anywhere.

•	Programming interface    If you are happier using an interface other than Embedded SQL or Java, such as ODBC or OLE DB, and your target platform is not so resource-constrained as to require UltraLite, you can use Adaptive Server Anywhere.

•	Deploying across a variety of devices    The programming model for UltraLite enables platform-independent database access code, so that you can port the user interface to new platforms and devices without having to alter the underlying data access layer.


CHAPTER 3.  Running and Connecting to a Database Server
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

 CHAPTER 3.  Running and Connecting to a Database Server

About this chapter

This chapter provides basic information about starting, running, and stopping a personal server running the sample database.

For complete instructions on connecting to servers and databases, see Running the Database Server and Connecting to a Database.

 About the database server

 Starting the sample database

 Connecting to the sample database from Interactive SQL

 Using ODBC data sources to connect

 Shutting down a database server


About the database server
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

 About the database server

The database server forms a communications channel and a manipulation device for the database. The database itself is a file on disk. The database server is the most important piece of Adaptive Server Anywhere, because it lets everything else happen.

Adaptive Server Anywhere comes with two versions of its database server. The first is called the personal database server. It can accept connections from applications or users running on the same machine. By contrast, the network database server also accepts connections from applications or users on other computers by means of the network which links the two machines. Apart from this difference, these two versions of the database server perform the same tasks and are virtually identical.

The database server allows access to databases and processes commands in a secure and efficient manner. All access to your database must be made through a database server. Only one database server at a time can manage a database.

The part of the server that performs all these functions is commonly called the engine. It does all the work in your database, much as the engine in your automobile makes everything go.


Starting the sample database
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

 Starting the sample database

This section describes how to start the database server running the sample database. In the documentation, "starting a database" is often used as a convenient way of saying "starting a database server running a database." How you do this depends on the operating system you are using.

 Starting the sample database on Windows operating systems

 Starting the sample database on UNIX operating systems

 The database server window


Starting the sample database on Windows operating systems
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

        Starting the sample database

 Starting the sample database on Windows operating systems

♦ To start the sample database in Windows:

•	From the Start menu, choose Programs [image] Sybase SQL Anywhere 7 [image] Adaptive Server Anywhere 7 [image] Personal Server Sample.

This starts a personal server running the sample database. The server displays as an icon in the system tray, at the opposite end of the Taskbar from the Start button.

♦ To start the sample database at a command prompt:

•	At a command prompt, type

dbeng7 -c 8M path\asademo.db

where the switch -c 8M makes a cache of 8 megabytes available for the server, the dbeng7 command starts the personal database server, and asademo.db is the sample database file.


Starting the sample database on UNIX operating systems
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

        Starting the sample database

 Starting the sample database on UNIX operating systems

♦ To start the sample database on UNIX:

•	At a command prompt, type

dbeng7 path/asademo.db

where path is the Adaptive Server Anywhere installation path.

Do not use the ampersand character to run the database server in the background. An alternative is to run the database as a daemon; to do this, use the -gu command line option.


The database server window
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

        Starting the sample database

 The database server window

You can display the server window in Windows by double-clicking the Adaptive Server Anywhere icon in the system tray at the bottom of your screen.

The server window displays the following information:

•	The server name    The name in the title bar (in this case asademo) is the server name. This name is used by applications when they connect to a server.

•	Startup information    When a database server starts, it sets aside some memory that it uses when processing database requests. This is called the cache. The amount of cache memory is displayed in the window. The cache is organized in fixed-size pages, and the page size is also displayed in the window.

•	Database information    The names of the database files and its transaction log file are displayed in the window.

The startup cache size and page size are the default values. For many purposes, default startup options are fine. For more advanced use, you can provide explicit database server startup options.


Connecting to the sample database from Interactive SQL
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

 Connecting to the sample database from Interactive SQL

Once the database server is running, you can connect to it from an application. Here we connect to it from Interactive SQL, and then test that the connection is working.

♦ To connect to the sample database from Interactive SQL:

1	Ensure that the sample database is loaded on a running server, as described in Starting the sample database .

2	Start Interactive SQL:

•	In Windows, choose Start [image] Programs [image] Sybase SQL Anywhere 7 [image] Adaptive Server Anywhere 7 [image] Interactive SQL.

The Connect dialog appears.

•	Enter the user ID DBA and the password SQL. This is the default user ID and password for Adaptive Server Anywhere databases when they are created. It is also the user ID and password for the sample database.

•	Click ODBC Data Source Name and select ASA 7.0 Sample from the dropdown list.

•	Click OK to connect to the sample database.

•	In UNIX operating systems, type

dbisql -c "uid=dba;pwd=sql;dbn=asademo"

After connecting to the database, the Interactive SQL window appears.

The database name, user ID, and server name are displayed in the title bar.

Sending commands to the database

1	In the SQL Statements pane, type

SELECT * FROM employee

The query displays all the columns of the table named employee. This table contains information about the employees of a fictitious merchandising company.

2	From the SQL menu, click Execute. The following appears:

Interactive SQL retrieves the information by sending a request to your database server. The server, in turn, looks up the information in the employee table and returns it to Interactive SQL.

For more information about Interactive SQL, see Using Interactive SQL.


Using ODBC data sources to connect
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

 Using ODBC data sources to connect

The Open Database Connectivity (ODBC) interface is defined by Microsoft Corporation, and is a standard interface for connecting client applications to database management systems in the Windows and Windows NT environments. Connections are made by specifying connection parameters. It is often convenient to collect a set of connection parameters together and store them in an ODBC data source.

You can connect to Adaptive Server Anywhere databases using ODBC data sources from PowerDynamo, PowerDesigner, InfoMaker, and all the Adaptive Server Anywhere utilities.

Adaptive Server Anywhere supports several programming interfaces in addition to ODBC. You can use ODBC data sources to connect to Adaptive Server Anywhere—not only from ODBC applications, but also from Embedded SQL applications, such as the command-line administration utilities, and from Interactive SQL and Sybase Central—even though these applications do not use ODBC. The functionality to process ODBC data sources is built into the Embedded SQL client library, and into Sybase Central and Interactive SQL.

Connecting to the sample database

When you install Adaptive Server Anywhere, a sample ODBC data source is provided. You can use this to connect to the sample database. Here we show how to use it from Interactive SQL.

♦ To connect to the sample database using an ODBC data source:

1	Start Interactive SQL. For instructions, see Connecting to the sample database from Interactive SQL.

2	In the Connect dialog, select ODBC Data Source Name, and click Browse.

3	From the Data Source Names list, select ASA 7.0 Sample and click OK.

4	Click OK to connect.

The ASA 7.0 Sample data source contains enough information to connect to the sample database whether or not the database server is running. You can try it both ways.

Creating data sources

You can create data sources for your own database connections.

♦ 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 appears, showing a list of the data sources you currently have installed on your machine:

2	On the User DSN tab, click Add.

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.

  For information about setting up ODBC data sources in UNIX, see Using ODBC data sources on UNIX.


Shutting down a database server
  Introducing SQL Anywhere Studio

    PART 1. SQL Anywhere Studio Overview

      CHAPTER 3.  Running and Connecting to a Database Server

 Shutting down a database server

You can now shut down the database server you started in previous sections.

♦ To shut down the database server running the sample database:

1	Double-click the Adaptive Server Anywhere icon in the Windows task bar.

2	Click Shutdown.

Normally, you would not try to shut down a server while it is still connected to one or more clients. If you attempt to do so, you get a warning when you click the Shutdown button. If you ignore the warning and proceed, you will lose any pending transactions.

On UNIX, shut down the database server by pressing q in the console where you started the server.


PART 2. Getting Results with SQL Anywhere Studio
  Introducing SQL Anywhere Studio

 PART 2. Getting Results with SQL Anywhere Studio

This part contains tutorials and other material to help you get started with Anywhere Studio

 CHAPTER 4.  Managing Databases with Sybase Central

 CHAPTER 5.  Designing Databases with PowerDesigner

 CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 CHAPTER 7.  Replicating Data with SQL Remote

 CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

 CHAPTER 9.  Creating Reports with InfoMaker


CHAPTER 4.  Managing Databases with Sybase Central
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

 CHAPTER 4.  Managing Databases with Sybase Central

About this chapter

This chapter introduces Sybase Central, the Sybase database management tool. It provides a brief introduction to using Sybase Central for managing database properties.

 About Sybase Central

 Lesson 1: Getting started

 Lesson 2: Create and edit tables

 Lesson 3: Add and delete columns

 Lesson 4: View and edit procedures

 Lesson 5: Manage users and groups

 Lesson 6: Back up your database

 Restore the sample database

 Summary


About Sybase Central
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 About Sybase Central

Sybase Central is a database management tool that provides Adaptive Server Anywhere database settings, properties, and utilities in a graphical user interface. Sybase Central can also be used for managing other Sybase products, including Adaptive Server Enterprise and PowerDynamo. This chapter describes how to use Sybase Central with Adaptive Server Anywhere databases.

Sybase Central helps administer two types of tasks:

•	Tasks carried out by sending SQL statements to the database server.

•	Tasks carried out by Adaptive Server Anywhere utilities.

Sybase Central provides an easy-to-use interface for both kinds of tasks.

For more information about Adaptive Server Anywhere utilities, see Database Administration Utilities.


Lesson 1: Getting started
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Lesson 1: Getting started

This tutorial introduces the Sybase Central user interface. Lesson 1 describes how to start Sybase Central, how to connect to a database, and how to view a database schema in Sybase Central.

The following lessons show you how to create, delete, and view tables, columns, and procedures; how to manage users and user groups; and how to back up your database.

But first, make a copy of the sample database so that you can restore it after you have finished the tutorial.

♦ To copy the sample database:

1	Navigate to the directory that contains the sample database file, asademo.db. The default installation directory for it is Program Files\Sybase\Adaptive Server Anywhere 7.

2	Create a sub-directory to hold a copy of asademo.db. For example, call it demoback.

3	Create a copy of asademo.db in the backup directory. You will not touch this copy, but work with the original version.

 Start Sybase Central

 Connect to the sample database from Sybase Central

 Explore the database schema


Start Sybase Central
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 1: Getting started

 Start Sybase Central

•	To start Sybase Central in Windows 95/8 or Windows NT, select Start [image] Programs [image] Sybase SQL Anywhere 7 [image] Sybase Central 4.0.

•	To start Sybase Central in UNIX, at a command prompt type scjview.

The Sybase Central main window appears:

The Sybase Central dialog is modeled after 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. For example, the asademo server shown above is a container that holds the Utilities folder, the Statistics folder, and the asademo database (also a container). Sybase Central is at the root of the tree. Plug-ins for Sybase Central, such as the Sybase Adaptive Server Anywhere database management system, occupy the first level after the root level.

The right pane displays the contents of the container that has been selected in the left pane. You can change the appearance of both panes in the Options dialog (accessed through the Tools menu).

Toolbar

The main dialog's toolbar (shown below) provides you with graphic buttons for common commands. To show or hide the toolbar, choose View [image] Toolbar.

With the main toolbar, you can:

•	Navigate through the object tree.

•	Connect to or disconnect from a database, server, or product module.

•	Access the Connection Profiles dialog (also accessible through the Tools menu).

•	Refresh the view of the current folder. You can also press F5 to refresh the folder.

•	Cut, copy, paste, or delete objects.

•	View the property sheet of a selected object.

Status bar

The status bar, which appears at the bottom of the main window, shows a brief summary of menu commands as you navigate through the menus. To show or hide the status bar, choose View [image] Status Bar.


Connect to the sample database from Sybase Central
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 1: Getting started

 Connect to the sample database from Sybase Central

This section describes how to connect to the sample database.

By default, all newly-created Adaptive Server Anywhere databases contain a user ID of DBA and a password of SQL. It is the responsibility of the database administrator to provide the desired level of security by changing passwords and creating other user IDs.

The user ID and password for the sample database are also DBA and SQL. However, when you connect using the ASA 7.0 Sample data source (as the procedure below describes), you do not need to enter this information because the data source already contains it.

♦ To connect to the sample database:

1	Start Sybase Central, as described above.

2	Choose Tools [image] Connect.

3	If a dialog prompts you to choose a plug-in, choose Adaptive Server Anywhere.

The Connect dialog opens.

4	Select the ODBC Data Source Name option.

5	In the ODBC Data Source Name box, type ASA 7.0 Sample, or select it from the dropdown list if it is already there, and click OK.

6	Click OK to connect to the sample database.


Explore the database schema
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 1: Getting started

 Explore the database schema

The database schema is a collection of all the objects in a database. Sybase Central displays a database schema as a hierarchy of containers and their contents. This section describes how to view the schema of a database.

Expanding a database container

Just as in Windows Explorer, there are a variety of methods for viewing the objects in a database, including the following.

•	Select a container in the left pane to select that container. The right pane then shows the contents of the selected container.

•	Click once on the plus or minus icon next to a container in the left pane. This action expands or collapses the container. If no plus or minus icons appear next to a container, it means that this container holds no objects extending beyond the level of that container.

•	Double-click a container in either pane. This action expands the tree in the left pane and displays the contents of the container in the right pane.

The left pane displays container objects only. The right pane displays the contents of the container object selected in the left pane. For example, when you select the Tables folder in the left pane, all of the tables within that folder are displayed in the right pane.

Viewing the tables in a database

The following illustrates the steps taken to examine the contents of a table folder in a database container.

♦ To examine the tables in a database:

1	Expand the Tables folder. You may have to expand asademo first.

2	Each table in the Tables folder is itself a container. Select a table in the left pane to reveal the contents of that table in the right pane. Each table object contains folders for columns, foreign keys, references, indexes, and triggers.

3	Expand the table object in the left pane to reveal its contents in the hierarchical tree. Select each object in the table container. The right pane then displays the contents of that object.

Viewing database object properties

The properties of database objects, such as a database or a stored procedure, can be viewed using any of the following methods.

•	Right-click a database object and choose Properties from the popup menu.

•	Select a database object and click File [image] Properties.

•	While holding the Alt key, double-click a database object in the right pane.

•	With a database object selected in the right pane, press the Alt and Enter keys simultaneously.

You can navigate through a database schema by clicking or double-clicking items in either pane. Explore the contents of the other folders in the database.

Database folders

Every Adaptive Server Anywhere database contains the following folders:

•	Tables    Base tables and global temporary tables stored in the database.

•	Views    Computed tables, stored in the database as a query and evaluated when accessed.

•	Procedures & Functions    for using a module-based language consisting of SQL procedures.

•	Users & Groups    for administering who is permitted to use the database.

•	Integrated Logins    for enabling users to connect to a database using their Windows NT user name and password.

•	Java Objects    for building logic and rich data types into your database.

•	Domains    for creating non-standard data types.

•	Events    for creating and editing events.

•	SQL Remote    for administering SQL Remote replication of data in the database.

•	Connected Users    for monitoring current connections to a database. The Sybase Central connection does not appear under this container.

•	DB Spaces    for creating more than one .db file for the database.

•	Remote Servers    for identifying remote servers so that local users can execute remote procedure calls or retrieve information from the remote server.

•	Synchronization    for managing MobiLink data synchronization.

You should explore the sample database until you are comfortable locating database objects in the Sybase Central main window.


Lesson 2: Create and edit tables
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Lesson 2: Create and edit tables

This lesson takes you through the steps required to add a table to the sample database. This task includes adding columns to an existing table.

 Create a table and column in Sybase Central

 Edit existing tables using the Table Editor


Create a table and column in Sybase Central
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 2: Create and edit tables

 Create a table and column in Sybase Central

In this lesson we use the Table Editor to create a table of offices in the sample database.

♦ To create a table and columns:

1	In the left pane of Sybase Central, open the Tables folder.

2	In the right pane, double-click Add Table.

The Table Editor appears.

3	Type a name for the new table in the Name field. In this case, use the name office.

4	In the first row, under Column Name, type a name for the first column of your new table. Use the name office_id.

5	Click in the first row under the Data Type column. Click the dropdown arrow that appears, and choose smallint from the list of data types.

6	Do not make any entries under any of the other columns. Ensure that the entry under the Allow Nulls column is cleared. This column will later be used as a primary key.

7	On the toolbar, click the Advanced Table Properties button.

8	In the Advanced Table Properties dialog, enter the comment Company offices in the Comment text box. Comments are an optional property. Click OK.

9	Finish creating the table by clicking Save and Close.


Edit existing tables using the Table Editor
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 2: Create and edit tables

 Edit existing tables using the Table Editor

This section describes how to edit an existing table. The steps show you how to add a primary key to the table created in the previous section by turning a column named office_id into a primary key.

♦ To edit an existing table using the Table Editor:

1	Open the Tables folder and select the office table.

2	Click File [image] Edit Columns.

The Table Editor appears, displaying one row for every column in the table.

3	Click once under the Key column in the office_id row. The icon changes to a key, indicating that the column named office_id is now the primary key for the table named office.

Primary key conditions
If a check appears in the Allow Nulls column, or if duplicate values are stored in the office_id column, the column cannot be changed to a primary key.

4	On the toolbar, click Save Table.

5	On the toolbar, click Add Column.

6	Edit the new column so that its name is office_name, its type is char, and its size is 20. Select Allow Nulls so that office_name is allowed to be blank.

7	Click Save and Close.

The column is now in the database, although it has no data. Sybase Central does not include facilities for data entry.


Lesson 3: Add and delete columns
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Lesson 3: Add and delete columns

This lesson describes how to add an address column to the office table created in a previous section.

 Add a column

 Delete tables


Add a column
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 3: Add and delete columns

 Add a column

The customer table already has an address column, and this can be copied and added to the office table. These steps copy the attributes, including the name, of a column in one table to another table, but do not copy data from one table to another.

♦ To copy a column to a table:

1	In the left pane, open the customer table to reveal the Columns folder. Select this folder.

2	In the right pane, right-click the address column and click Copy from the popup menu.

3	Right-click the office table and click Paste from the popup menu.

The Copy Column Definition dialog appears.

4	In the New Name box, type address, and click OK.

The new column has the same attributes as the address column of the customer table but contains no data.

5	To see the new column, select and expand the office table in the left pane, and then select the Columns folder. The right pane displays the columns of the office table, including the new address column.

6	To see the attributes of the address column, double-click it in the right pane.

The address property sheet appears.

7	Change the name of the column to office_address by typing this new name in the text box.

8	Click Apply to accept the change; this modification applies to the address column of the office table only. Once a column has been copied, there is no longer any connection between the original column and the new column.

Notes

•	Most objects in Sybase Central have property sheets, including tables, users, stored procedures, and individual columns.

•	Many folders have Add Object icons. When you double-click these icons, you can create new objects for the database.

•	Whenever you select an object in Sybase Central, commands related to that object appear in two places: in the File menu and in a popup menu that you can access by right-clicking the object.

•	Once you open a container, its contents are cached to improve performance. You can access two refresh commands in the View menu to update the view of either the current container or the entire window.


Delete tables
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 3: Add and delete columns

 Delete tables

Tables can be deleted, or dropped, from a database. For example, after creating and editing the office table in the previous tutorials, you can delete this table to restore the sample database to its original state.

♦ To delete the office table from the sample database:

1	Open the Tables folder.

2	Right-click the office table and choose Delete from the popup menu.


Lesson 4: View and edit procedures
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Lesson 4: View and edit procedures

The Procedures & Functions folder holds stored procedures for a database. This lesson shows how to view and alter the contents of a procedure with the Sybase Central Code Editor, as well as how to modify the procedure's properties.

The Sybase Central Code Editor is a separate window for displaying and editing the code of triggers, procedures, and views.

Beyond text-editing functions, it provides:

•	Automatic syntax highlighting.

•	Unlimited Undo and Redo.

•	Ability to find and replace text, and to jump to specific line numbers.

 Use the Sybase Central Code Editor

 Edit a procedure


Use the Sybase Central Code Editor
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 4: View and edit procedures

 Use the Sybase Central Code Editor

There are several stored procedures included in the sample database. The following steps explain how to view and edit stored procedures.

♦ To view the contents of a procedure:

1	In the left pane of Sybase Central, open the Procedures & Functions folder in the sample database.

2	Right-click sp_customer_list and choose one of the following commands from the popup menu:

•	Choose Open as Watcom-SQL to edit the procedure in the Watcom-SQL dialect. Watcom-SQL is the dialect of SQL developed for Adaptive Server Anywhere.

•	Choose Open as Transact-SQL to edit the procedure in the Transact-SQL dialect. Transact-SQL is the dialect of SQL developed for Sybase Adaptive Server Enterprise. Adaptive Server Anywhere allows you to use either dialect of SQL.

The Sybase Central Code Editor appears, displaying the text of the procedure called sp_customer_list:

This procedure is designed to return a set of customer IDs and company names from the Customer table.

Tip
You can also create, display and edit views and triggers with the Sybase Central Code Editor.


Edit a procedure
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 4: View and edit procedures

 Edit a procedure

You can edit other properties of procedures using the procedure's property sheet. In this section, we alter the permissions for the procedure.

Stored procedures have permissions associated with them. In order to execute a procedure, you either need to be granted permission to execute it, or you need to be a member of a user group that has permission to execute it.

♦ To view and alter the permissions on a procedure:

1	In the Procedures & Functions folder, select sp_customer_list.

2	Click File [image] Properties.

3	Click the Permissions tab of the property sheet to see which user IDs have been granted permissions for this procedure. Currently none have, since the only user for the sample database is DBA, who is the owner of the procedure and so automatically has execute permission.

4	Click Grant.

The Grant Permission dialog appears:

5	In the Grant Permission dialog, select the PUBLIC group and click OK to grant this group permission to execute the sp_customer_list procedure.

The PUBLIC group is added to the Permissions tab of the property sheet with a check mark in the Execute column:

You can revoke this group's permission by clicking the check mark to make it disappear, or by selecting the group and clicking Revoke at the bottom of the dialog.

6	Click OK to accept the changes to the sp_customer_list permissions or Cancel to undo the changes.


Lesson 5: Manage users and groups
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Lesson 5: Manage users and groups

In Adaptive Server Anywhere, both users and groups are objects within the database. However, groups are also containers, capable of containing users and other groups. When users are contained within a group, they are members of that group.

In this structure, permissions granted to a group are inherited by all users and groups contained within. Adaptive Server Anywhere allows you to create users and groups with permission to use a database and grant membership to other groups. Also, users and groups can be members of multiple groups.

This tutorial demonstrates how to create a group for the database, create an individual user, and make the user a member of the group.

 Add a group to the sample database

 Add a user to the database

 Add a user to a group


Add a group to the sample database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 5: Manage users and groups

 Add a group to the sample database

This section describes how to add a group to the sample database.

♦ To add a group to the sample database:

1	Connect to the sample database.

2	Select the Users & Groups folder in the left pane.

3	In the right pane, double-click Add User or Group. The User Creation wizard appears.

4	Choose Create a Group.

5	Type the name Sales as the name of the new group and click Next.

6	Ensure that the group is allowed to connect, and enter a password. Confirm it by entering it again.

7	Select the Resource option, clear the Remote DBA option, and click Next.

8	Click Finish to create the new group.

The sales group now appears in both panes. Groups are container objects, and so they appear in the left pane.


Add a user to the database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 5: Manage users and groups

 Add a user to the database

This section describes how to add a user to the sample database.

♦ To add a user to the sample database:

1	Connect to the sample database.

2	Select the Users & Groups folder in the left pane.

3	In the right pane, double-click Add User or Group. The User Creation wizard appears.

4	Choose to create a user.

5	Type the name Sandy as the name of the new user. This name becomes the user ID for the new user. Click Next when finished.

6	Ensure that the user is allowed to connect, and enter a password. Confirm it by entering it again.

7	Select the Resource option, clear the Remote DBA option, and click Next.

8	Click Finish to create the new user.

An icon for the new user appears in the right pane. There is no icon in the left pane, since users are not containers.


Add a user to a group
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Lesson 5: Manage users and groups

 Add a user to a group

Sybase Central provides two ways to add a user to a group:

•	On the Membership tab of an individual's property sheet

•	With a copy-and-paste method

This section describes how to add two users to a group by copying them.

♦ To add users to a group:

1	Double-click the Users & Groups folder so that the Sales group is showing in the left pane and all the users and groups are showing in the right pane.

2	Right-click the user Sandy and select Copy from the popup menu.

3	Right-click the Sales group in the left pane and select Paste from the popup menu.

4	Repeat by copying dba to Sales.


Lesson 6: Back up your database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Lesson 6: Back up your database

Sybase Central includes a set of database utilities for carrying out common database administration tasks. Wizards walk you through the steps involved.

Utilities

To see all the database utilities provided with Sybase Central, select the Utilities folder. The list of utilities appears in the right pane:

Some of the utilities are used on database files, while others can be used with running databases. Those utilities that can be used on a running database are shown on a popup menu by right-clicking on the database icon.

In this section, we use a wizard back up the sample database. This can be done on a running database.

♦ To back up a running database:

1	In the left pane, right-click the asademo (DBA) icon and choose Backup from the popup menu. The Backup wizard appears.

2	Make sure that Image backup is selected on the first page of the wizard. Click Next.

3	Type a directory in the text box indicating where you want to back up the database to. As this is a tutorial, you may wish to choose a temporary file directory such as c:\temp.

4	Select the All Database Files option. Then click Next.

5	Choose to continue to use the original transaction log. Click Next.

6	Review the choices you have made, and click Finish to back up the database. A window displays the progress of the backup.

Wizards are available for several other database administration tasks. You may wish to try creating a database by opening the Utilities folder in the left pane and then double-clicking Create Database in the right pane.


Restore the sample database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Restore the sample database

Now that you have completed this tutorial, you should restore the sample database so that it can be used again. In Lesson 1: Getting started, you created a copy of asademo.db. You can now replace the version of asademo.db that you just changed with the copy. Delete asademo.log.

If you did not create a copy of asademo.db, you can restore the sample database by deleting the Sales group and the user Sandy.

♦ To delete users and groups:

1	Select the Users & Groups folder in the left pane.

2	Right-click the icon in the right pane.

3	Choose Delete from the popup menu and acknowledge the deletion.


Summary
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

 Summary

In this tutorial, you learned how to

•	Start Sybase Central and connect to a database.

•	Create, modify and view tables and columns.

•	View procedures.

•	Manage users and user groups.

•	Back up your database.

 Where do I go from here?


Where do I go from here?
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 4.  Managing Databases with Sybase Central

        Summary

 Where do I go from here?

  For more information, see Working with Database Objects.


CHAPTER 5.  Designing Databases with PowerDesigner
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

 CHAPTER 5.  Designing Databases with PowerDesigner

About this chapter

SQL Anywhere Studio includes PowerDesigner Physical Architect, a module of Sybase's powerful database design tool, PowerDesigner. This module provides ways to generate and modify databases using a graphical representation of the database structure. You can optimize your database by customizing tables, columns, indexes, referential integrity, views, physical storage, triggers, and stored procedures.

 About PowerDesigner

 Lesson 1: Getting Started

 Lesson 2: Add a column

 Lesson 3: Check your work

 Lesson 4: Save changes and generate database

 Summary


About PowerDesigner
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

 About PowerDesigner

The structure of your database, such as the tables, relationships, views and triggers, is called the database schema. You use SQL statements to create and arrange these elements to your liking, but doing so without a graphical tool can be confusing.

PowerDesigner gives you a graphical representation of the structure of your database. Better, you can modify the structure of the database or create an entirely new one simply by drawing new tables or entering information. Once your design is complete, PowerDesigner can generate a SQL script to generate your new database.

The following diagram, which shows the structure of the sample database, is easily created using PowerDesigner.

The performance of your database depends heavily on your design. In general, you should store information about different distinct types of objects, such as employees or products, in separate tables.

You can identify relationships between these tables using references, meaning that foreign keys in one table identify particular rows in another table. Many-to-one and one-to-many relationships can be represented by a reference. Many-to-many relationships require two references and another table.

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

  For more information about PowerDesigner, click Help in the toolbar of the PowerDesigner main window to access three books: PowerDesigner General Features Guide, PowerDesigner PDM User's Guide, and PowerDesigner Report User's Guide.

  For more PowerDesigner tutorials, click Start [image] Programs [image] Sybase SQL Anywhere 7 [image] PowerDesigner 7 [image] PDM Getting Started. This accesses the book Physical Data Model Getting Started.


Lesson 1: Getting Started
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

 Lesson 1: Getting Started

PowerDesigner can read the structure of a database from a script file that creates the database. However, it is generally easier to just connect to your database from PowerDesigner and let it extract the design directly with the reverse engineering feature.

The following tutorial uses the sample database as a starting point. The tutorial illustrates PowerDesigner by implementing a modifications that improves the design of the sample database.

Currently, the price of each product is always read from the product table. As a result, updating the price effectively changes the sale price of that item on all previous orders. Adding a unit_price column to the sales_order_items table will correct this problem. The actual selling price to each customer can now be stored separately. The price in the product table records the current list price.

♦ To start PowerDesigner:

1	From the Start menu, choose Programs [image] Sybase SQL Anywhere 7 [image] PowerDesigner 7 [image] PowerDesigner.

The PowerDesigner main window appears:

The PowerDesigner main window includes an object browser docked to the left, and an output window docked at the bottom.

2	Choose File [image] New.

3	In the New Dialog, choose Physical Data Model and click OK.

The Choose DBMS dialog appears.

4	From the dropdown list, choose Sybase AS Anywhere 7.

5	Ensure Link is selected and click OK.

The model name MODL_1 appears in the browser and in the title bar.

 Reverse engineer the database


Reverse engineer the database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

        Lesson 1: Getting Started

 Reverse engineer the database

In this section, you generate a Physical Data Model (PDM) of the sample database by reverse engineering it.

♦ Reverse engineer the database:

1	Click in the diagram window (the large central pane of PowerDesigner).

2	From the Database menu in the toolbar, choose Reverse Engineering Database.

The Reverse Engineering a Database dialog appears:

3	Ensure that Using an ODBC Data Source is selected, and that the data source is ASA 7.0 Sample.

If ASA 7.0 Sample does not appear, click the icon to the right of the data source. The Connect to an ODBC Data Source dialog appears. Select Machine Data Source and select ASA 7.0 Sample from the dropdown list. Enter user ID DBA and password SQL. Click Connect to return to the Reverse Engineering a Database dialog.

4	In the Reverse Engineering a Database dialog, click OK.

The ODBC Reverse Engineering dialog appears:

In the lower half of the dialog, there are six check boxes for selecting keys, indexes, and so on. These are the reverse engineering options. Ensure all are selected (the default). You should also ensure that all tables are selected (also the default).

5	Click OK to reverse engineer the database.

A graphic representation of the sample database appears in the diagram window, and the model objects appear in the browser:

6	Examine the diagram. You can use F6 to zoom in, and F7 to zoom out, to make the tables more readable.

•	Experiment with these toolbar buttons:

•	Choose Tools [image] Display Preferences to see ways to modify the display. Click each option on the left to see pages of display preference parameters. Click the Help button at the bottom of each page for complete information.

Each table in the database is represented by a box. The name of the table appears at the top of the box. Below is a list of the columns. Column names that are underlined are part of the primary key for that table. The data type of each column appears on the right. Some of the tables may overlap after reverse engineering, but you can use the Auto-layout tool in the Symbol menu to reorganize them.

References between tables are represented with arrows. The arrows point toward the parent table, which is the table that contains the primary key. An equation appears next to each arrow that identifies the reference.

7	Rearrange the tables so that the diagram is easier to read. To do this, drag them with your mouse. The reference arrows follow automatically. You can select multiple objects by holding down the Shift key.

One possible arrangement appears below:


Lesson 2: Add a column
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

 Lesson 2: Add a column

You are now ready to add the unit_price column to the sales_order_items table. You can accomplish this task by accessing the list of columns through the Table property sheet.

♦ To add a column:

1	Display the column properties:

•	Right-click the sales_order_items table and select Properties from the popup menu.

The Table property sheet appears:

•	Click the Columns tab.

The list of columns appears:

2	Add a new column to hold the unit price.

•	Click the Insert a Row tool.

An arrow appears at the beginning of the line and  a column with a default name (Column_6) appears.

•	Type unit_price in the name column. The name is automatically duplicated as the code.

•	From the Data dropdown list, choose Numeric. (The Data field may be too narrow to read. You can pull the sides of the column to expand it.)

•	The column properties P, F, and M stand for Primary Key, Foreign Key, and Mandatory, as follows:

•	Primary key designates a column whose values uniquely identify a row in the table.

•	Foreign key designates a column that depends on and migrates from a primary key column in another table.

•	Mandatory indicates a column that must be assigned a value.

•	Select Mandatory and click OK.

3	Examine the effect of your changes on the diagram of the database. The sales_order_items table now includes a new column called unit_price.


Lesson 3: Check your work
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

 Lesson 3: Check your work

PowerDesigner lets you quickly detect database design errors in your new model.

♦ To check your new schema:

1	Choose Tools [image] Check Model.

The Check Model Parameters dialog appears. You can use the default parameters.

2	Click OK.

The results of Check Model are shown in the Results List.


Lesson 4: Save changes and generate database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

 Lesson 4: Save changes and generate database

In PowerDesigner, models that depict the physical components of your database design, including tables and columns, are called Physical Data Models (PDM). PowerDesigner stores these in files with the extension .PDM.

♦ To save the physical data model (PDM):

1	Choose File [image] Save As.

2	Type a file name with the extension .pdm and select a location.

3	Click OK.

You can use PowerDesigner to generate a SQL script that implements all the components of your model. You can then use the SQL script to generate a database.

♦ To generate a SQL script to create your new database:

1	Choose Database [image] Generate Database.

The Database Generation dialog appears:

2	Type NewDemo.sql in the File Name box and choose a convenient directory.

3	Ensure that Script Generation is selected.

4	Click the Database tab, and ensure that Create Database is selected.

Explore the other tabs to observe options that give you control over many other properties of the generated script.

5	Click OK.

When the script is created, the Result dialog appears.

6	Click Edit to view the script.

Check that your changes are reflected in the script. For example, the definition of the new office table is shown below.

/* ========================================= */

/*   Table: office                           */

/* ========================================= */

create table office

(

id            integer         not null

default autoincrement

check (

id >= 100),

name          char(15)        not null,

street        char(30)        not null,

city          char(20)        not null,

state         char(2)         not null,

zip           char(5)         not null,

phone         char(10)                ,

fax           char(10)                ,

primary key (id)

);

7	When you are finished, close the dialog: in the Result dialog, click Close.

You can now create your new database from Interactive SQL.

♦ To create the new database:

1	Start Interactive SQL.

2	Connect to the sample database. You can use the same ODBC connection.

3	Create an empty database:

•	Execute the following SQL statement, substituting any convenient directory.

CREATE DATABASE 'c:\\My Scripts\\newdemo.db'

Tip
To execute a SQL statement in Interactive SQL, type or copy the statement in the SQL Statements pane, and then either press F5, or choose SQL [image] Execute.

4	Close the connection to the sample database by clicking SQL [image] Disconnect.

5	Connect to the new database by clicking SQL [image] Connect.

•	Enter DBA as the User ID

•	Enter SQL as the Password

•	Click the Database tab, and in the Database File box, enter the full path and file name of the new database file.

•	Click OK.

6	Use the read statement. Remember that this command demands that you enclose the file name in double quotes.

•	Execute the SQL statement:

READ "c:\\My Scripts\\newdemo.sql"

You can use these basic steps to modify other databases.


Summary
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

 Summary

This tutorial introduced only some of the basic features of PowerDesigner. In fact, it is capable of handling the complete design or modification of your database schema, including all tables, views, indexes, references, triggers and procedures.

Domains

Other features greatly simplify the task of designing larger databases. For example, you can specify specific domains. A domain holds a particular type of data, such as a phone number. It has a data type associated with it, but is more specific. For example, you can create a domain of identification numbers. Whenever you need a identification number in a table, you can associate that column with the identification number domain. All properties and checks associated with that domain are attached automatically.

Domains reduce repetitive definitions. In doing so, they not only reduce your work, but also reduce the chance that you will erroneously use a different type definition or check procedure. Rather than identify a column as simply an integer, you specify what specific type of data that column contains. All instances of that data type share a common definition.

Business rules

A business rule is a written expression of the way a business operates. For example, "the order shipped date must be greater than or equal to the order date" is a business rule.

Business rules fall into four categories:

•	Definition    Expresses inherent properties of an object. Definitions typically describe entities.

•	Fact    Expresses certainty or existence. Facts typically describe relationships.

•	Validation    A constraint on a value.

•	Formula    Calculation used to produce values.

Business rules are particularly handy because they relate directly to the task that a customer requires that a database perform. By recording business rules and attaching them to particular objects, you can ensure that a database performs the required tasks.

 Where do I go from here?


Where do I go from here?
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 5.  Designing Databases with PowerDesigner

        Summary

 Where do I go from here?

  For more information about PowerDesigner, click Help in the toolbar of the PowerDesigner main window to access three books: PowerDesigner General Features Guide, PowerDesigner PDM User's Guide, and PowerDesigner Report User's Guide.

  For more PowerDesigner tutorials, click Start [image] Programs [image] Sybase SQL Anywhere 7 [image] PowerDesigner 7 [image] PDM Getting Started. This accesses the book Physical Data Model Getting Started.

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


CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

 CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

About this chapter

PowerDynamo brings dynamic content to your Web site. With PowerDynamo you can store, manage, and access both static HTML and dynamic database data, and you can make information from your database available over the Web for both information delivery and web-hosted client/server applications.

This chapter introduces you to the most commonly used features of PowerDynamo. In the first section you will learn about the administrative requirements of creating and accessing a database hosted Web site. Further sections walk you through the creation of dynamic templates and forms that access data from the sample database.

 About PowerDynamo

 Lesson 1: Getting started

 Lesson 2: Create a PowerDynamo Personal Web Server mapping

 Lesson 3: Create a template

 Lesson 4: Improve the template

 Lesson 5: Working with DynaScript

 Lesson 6: Working with forms

 Summary


About PowerDynamo
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 About PowerDynamo

This section discusses some concepts that are useful for understanding how PowerDynamo works as an application server in an Adaptive Server Anywhere environment.

PowerDynamo is a client application that acts as a middle-application server, handling all connections to Adaptive Server Anywhere through the ODBC interface and providing the application logic.

PowerDynamo includes Web site management features and mobile Intranet capabilities. The Web site management utilities provide easy management of all aspects of Dynamic Web sites:

•	Authoring through Sybase Central.

•	Site management through Sybase Central.

•	PowerDynamo Personal Web Server for offline access to data and Web documents, as well as for developing Web site content. To use the PowerDynamo Personal Web Server, you must have TCP/IP installed.

•	Application Servers for deployment on a Web site managed by a Web server other than the PowerDynamo Personal Web Server.

  For information on using PowerDynamo's mobile Intranet features, see the PowerDynamo User's Guide.

With PowerDynamo you can embed instructions into any document, and have the output displayed with the document's static HTML. At the core of PowerDynamo is an interpreter which processes specially tagged instructions embedded in the HTML pages or other documents, and servers the resulting output together with static HTML to the Web browser. PowerDynamo supports the following kinds of embedded instructions:

•	Tags     you can embed SQL statements and manipulate returned data within a document with HTML tags and Dynamo's own tags.

•	SQL Statements     you can place up-to-date database query results into a Web page when it is requested with SQL statements.

•	Text replacement and host variable macros     you can respond to HTML forms and other variables supplied by a Web client.

•	Scripts     you can include programmatic control (conditional execution, looping and so on) in your Web pages with DynaScript and JavaScript.

JavaScript vs DynaScript
DynaScript is a server-side scripting language. JavaScript is a client-side scripting language.

For further information on the DynaScript scripting language see the PowerDynamo User's Guide.

 Beyond HTML: Dynamic content


Beyond HTML: Dynamic content
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        About PowerDynamo

 Beyond HTML: Dynamic content

Ordinary Web sites consist of static HTML files. HTML, or HyperText Markup Language, is the language with which World Wide Web text documents are formatted. It defines the basic details of a document such as paragraph tags, graphics, and hypertext links. HTML files typically end with .html or .htm.

The documents that make up a PowerDynamo Web site are called scripts, which end in .ssc, and templates, which end in .stm. These documents work quite a bit differently than the standard HTML document. Templates and scripts can include anything from static HTML to dynamic HTML with embedded scripts and SQL queries. During the server-side processing of these documents, the results are generated and all output is converted to HTML for display purposes. The document, containing only HTML at this point, is then read by the browser.

  For more information about PowerDynamo, click Start [image] Programs [image] Sybase SQL Anywhere 7 [image] PowerDynamo 3.5 to access the books PowerDynamo Reference and PowerDynamo User's Guide. From the version of Sybase Central that comes with PowerDynamo, you can click Help to access those two books and also PowerDynamo 3.5 Plug-In Help.


Lesson 1: Getting started
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Lesson 1: Getting started

Before you start working with PowerDynamo, you need to prepare an ODBC data source and a connection profile, which you will do in this tutorial. Each database-hosted Web site requires a data source and a connection profile. Once this has been accomplished you are able to connect to a database and generate a Web site.

  For information on creating file-hosted Web sites, see the PowerDynamo User's Guide.

Connection profile and Data Source
When you install Adaptive Server Anywhere and PowerDynamo, an ODBC Data Source and a Connection Profile to the asademo.db sample database are automatically provided. For this tutorial we will create new ones so that you will understand how to create your own connection profiles and data sources later on.

This lesson involves the following tasks:

•	Configure an ODBC Data Source for the Web Site

•	Create a Connection Profile

•	Connect to asademo

•	Create a URL mapping

 Create an ODBC data source for the Web site

 Create a connection profile


Create an ODBC data source for the Web site
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 1: Getting started

 Create an ODBC data source for the Web site

Creating an ODBC System DSN is the same as creating an ODBC User DSN, except that the System DSN is available to all users on a machine, including services.

♦ To create an ODBC System Data Source Name:

1	From the Start menu, click Programs [image] Sybase [image] SQL Anywhere 7 [image] Adaptive Server Anywhere 7 [image] ODBC Administrator.

2	Select the System DSN tab.

3	Click Add.

The Create New Data Source dialog appears.

4	Select Adaptive Server Anywhere 7.0 and click Finish.

The ODBC Configuration for Adaptive Server Anywhere dialog appears.

5	Enter the appropriate information in the ODBC, Login, and Database tabs:

•	Data source name: Anywhere

•	User Id: DBA

•	Password: SQL

•	Database name: asademo

•	Database file: path\asademo.db

The other tabs are not used in this tutorial.

6	Click OK.


Create a connection profile
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 1: Getting started

 Create a connection profile

To create a Web site, you need to define a PowerDynamo connection profile for the specific database you want to connect to. A connection profile is associated with an ODBC data source. You create connection profiles and do most Web site generation and administration in Sybase Central.

♦ To create a connection profile:

1	From the Start menu, choose Programs [image] Sybase SQL Anywhere 7 [image] PowerDynamo 3.5 [image] Manage PowerDynamo. This opens the version of Sybase Central that you should use with PowerDynamo.

2	From the Tools menu, choose Connection Profiles.

The Connection Profiles dialog appears.

3	Click New.

The Create New Profile dialog appears.

4	Type ASASite as the profile name, and select PowerDynamo 3.5 from the list of available types. Click OK.

The Connection Profile dialog appears.

5	Ensure that the Connection type is set to ODBC.

6	From the ODBC data source dropdown list, choose Anywhere.

7	Enter the user ID DBA and password SQL. Click OK.

♦ To connect to asademo.db:

1

2	If the Connection Profiles dialog is not already open, click Tools [image] Connection Profiles.

3	Select ASASite.

4	Click Connect.

If you have not already connected to this database with PowerDynamo, Sybase Central will ask if you want to create a Web site on this connection.

5	Click Yes.

PowerDynamo asks you for the name of the root folder for the Web site.

6	Type ASASite as the Web site name and click OK.

After the site is generated, two folders appear in Sybase Central, ASASite and Connections.

What next?

In the next lesson you configure your Web server. The Web server used in this tutorial is PowerDynamo Personal Web Server, which is installed with PowerDynamo.


Lesson 2: Create a PowerDynamo Personal Web Server mapping
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Lesson 2: Create a PowerDynamo Personal Web Server mapping

To connect to your new Web site from a local Web browser, you must create a mapping. This mapping associates a URL prefix with the Dynamo connection profile.

♦ To create a PowerDynamo Personal Web Server mapping:

1	In the left pane of Sybase Central, open the folders Utilities, and then Configuration, and then Mappings.

2	Double-click Add Mapping.

3	Enter /ASASite as the URL prefix that the Web browsers will reference. Don't forget the slash character.

4	For type of site, select Database site (the default).

5	Click Browse and select ASASite as the connection profile to which this mapping will refer.

6	Click OK.

7	The new mapping, called /ASASite, appears in the right pane of Sybase Central:

You have now completed all of the administration details that enable you to create and work with a PowerDynamo Web site. Repeat these steps each time you create a new Web site.

What next?

In the next lesson you learn how to use PowerDynamo to create PowerDynamo Web pages in the form of scripts and templates.


Lesson 3: Create a template
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Lesson 3: Create a template

The basic Dynamo template includes queries as well as formatting and static HTML. PowerDynamo sends embedded queries to Adaptive Server Anywhere, formats the results as defined by the formatting, and sends the formatted results along with any static HTML and text to the Web server, which sends it to the Web client that requested it.

Templates vs Scripts
Templates and scripts are very similar in PowerDynamo. The main difference is that templates consist of HTML as well as DynaScript and SQL queries. Scripts contain a scripting language such as DynaScript without any surrounding HTML.  If you would like the framework (template) provided for you when creating a Web page with a SQL query and HTML, it is recommended that you use the Add Template wizard. If you wish to create a fairly empty Web page that you can edit yourself, it is recommended that you use the Add Script wizard.

In this project you will build a template that includes a query and HTML formatting. It involves the following tasks:

•	Create the Employee template with an embedded query.

•	Start the PowerDynamo Personal Web Server and a Web browser.

•	View the output.

 About embedded queries and formatting

 Create the Employees template with an embedded query

 View the output


About embedded queries and formatting
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 3: Create a template

 About embedded queries and formatting

Embedded queries are stored in the template and are delimited by special HTML comment tags, as seen in the following graphic. These Dynamo tags are extensions to the regular HTML tags that are used for creating Web documents. The main difference between Dynamo tags and HTML tags is the location from which the tags are processed. Dynamo tags are processed at the Web server while HTML tags are processed at the browser. Dynamo tags also provide extended Web site capabilities that are not available through the regular set of HTML tags. Normally, a scripting language such as DynaScript or JavaScript is required to gain the functionality that is provided with the Dynamo tags.

Dynamo provides wizards to help you create simple templates containing queries and HTML. It is a good idea to familiarize yourself with the tags commonly used in PowerDynamo so that you may later modify your own scripts and customize them to your own needs.

  For more information about using Dynamo tags and the DynaScript scripting language see the PowerDynamo User's Guide.


Create the Employees template with an embedded query
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 3: Create a template

 Create the Employees template with an embedded query

PowerDynamo provides an easy way to build a query and to define formatting for data output with the template wizard.

In this task, you will create a template called Employees that displays basic employee data from the sample database.

♦ To create the Employees template:

1	Open the PowerDynamo folder in the left pane of Sybase Central.

2	Open the ASASite folder.

3	Click the sub-folder that is also called ASASite.

4	Double-click Add Template in the right pane of Sybase Central.

The New Template wizard appears.

5	Enter Employees as the name of the Template, and click Next.

6	Enter a description of the template, such as Employee contact information, and click Next.

The description will appear as a level one heading in the Web page you are creating.

7	You may be asked which connection you wish to use for this template. Select Inherited. This indicates that you wish to use the same connection as was used for the root /Site. Click Next.

You are now asked to enter a SQL statement that will be used to generate a default template. You can either type a SQL statement or use the SQL Query Editor to build one. In this task, you will use the SQL Query Editor.

8	Click Select.

The Edit SQL Query dialog appears.

9	On the Tables tab, select the Employee table and click Add.

10	Add the Department table in the same manner.

11	On the Columns tab, click the plus sign next to the employee table name to display the available columns.

12	Add the following columns by selecting the column and clicking the Add button:

•	emp_lname

•	emp_fname

•	street

•	city

•	state

•	zip_code

•	phone

13	Open the department table by clicking on the plus sign, select the dept_name column, and then click Add.

The columns appear in the Selected Columns list.

14	On the Sort tab, select the following columns to sort by:

•	dept_name from the department table

•	emp_lname from the employee table

•	emp_fname from the employee table

15	Select the Test tab.

16	Click Test to see if the query returns data.

Data appears in the data window, verifying the validity of the query.

17	Click OK.

18	Click Next.

19	Choose HTML as the type of document you wish to create, and then click Next.

20	Choose Table with Headings as the default HTML format for the data.

21	Click Finish.

A new template, Employees.stm, appears in the Sybase Central workspace. Next, you will view the resulting document from a Web browser.

♦ To view the output of Employees.stm:

1	Start the PowerDynamo Personal Web Server:

•	Click the Utilities folder in Sybase Central.

•	In the right pane of Sybase Central, double-click Personal Web Server.

When the PowerDynamo Personal Web Server is started, an icon appears in the Windows system tray.

2	Start a Web browser such as Netscape or Microsoft Internet Explorer.

3	Open the following URL in your Web browser:

http://localhost/ASASite/Employees.stm

or

http://machine-name/ASASite/Employees.stm

or

http://machine-IP-number/ASASite/Employees.stm

Notes

If you get an error message in the browser window, double-check the Dynamo configuration.

Another reason for an error may be that you have another Web server loaded on your computer that uses the default port number, 80. You may have to map the ASASite to a port number other than the default. If necessary, see the PowerDynamo User's Guide for information on how to do this. Many users have Web servers on their computers without knowing it.

♦ To change the default port number:

1	In the left pane of Sybase Central, open the Configuration folder and then the Utilities folder.

2	Double-click Default PWS Settings.

3	Double-click Port Number.

4	Type in a new number.

5	Shut down the PowerDynamo Personal Web Server by double-clicking the icon in the system tray to open the Personal Web Server dialog, and then clicking File [image] Exit.

6	Restart the PowerDynamo Personal Web Server by clicking the Utilities folder in Sybase Central and then double-clicking Personal Web Server.


View the output
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 3: Create a template

 View the output

The document appears in the Web browser window. It displays data in a table format, since you chose Table with Headings as the HTML display format in Task 1. The table should look something like this:

To view the template that produced the table, click the sub-folder called ASASite in the left pane of Sybase Central, and then double-click Employees.stm in the right pane. The template appears:

What next?

Next you'll learn how to modify the template.


Lesson 4: Improve the template
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Lesson 4: Improve the template

You can make changes in the template from within Sybase Central and view the results from the open Web browser. When you change the template, you will save the changes to the database. Then, you will reload or refresh the document in the Web browser to view the changed document.

This project involves the following tasks:

•	Refine the formatting and the query.

•	Save changes to the database and reload the URL.

 Refine the HTML and the formatting

 Save changes to the database and reload the URL


Refine the HTML and the formatting
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 4: Improve the template

 Refine the HTML and the formatting

You may not be happy with the default HTML formatting inserted by PowerDynamo for the table. To change formatting, you can refine the code as desired. In this task, you will delete the border from the table and change the heading.

PowerDynamo's code editor displays coding of different types in specific colors. You can use this feature to avoid typographical errors that cause problems in script interpretation.

♦ To delete the border and change the heading:

1	Double-click Employees.stm in the Sybase Central window.

The text of the template appears in the code editor.

2	The text delimited by the HTML tags <H1> and </H1> is a top-level, or level-1, heading. Change this text to "Employee Contact Information by Department".

3	Place the cursor after the </H1> tag and press Enter.

4	Add the following lines:

<P>For changes to the Employee information please contact the Human Resources departments.</P>

5	Delete the word BORDER and the space preceding it from the <TABLE BORDER> tag so that it reads <TABLE>.

6	The Employees.stm template should now look like this:


Save changes to the database and reload the URL
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 4: Improve the template

 Save changes to the database and reload the URL

Once you have made changes to a template or script you need to save it so that you can view the changes in your Web browser. Once the template or script has been saved you can view the changes in a Web browser by reloading the document.

♦ To save changes and reload the URL:

1	Choose File [image] Save to Database to save the Employees.stm template.

2	Close Employees.stm.

3	Reload or refresh the document in the Web browser.

•	In Netscape Navigator, you reload a document by clicking the Reload button or choosing View [image] Reload.

•	In Internet Explorer, you refresh a document by clicking the Refresh button.

The document appears with the changes you just made. You may modify any content within a template or scripts in this way, including:

•	DynaScript

•	JavaScript

•	SQL

•	tags (HTML tags and PowerDynamo tags)

•	Comments

If you get a script or SQL error message, fix the error in the template, save the changes to the database, and reload the URL in the Web browser.

What next?

The next lesson shows you how to use DynaScript to further improve your Web site.


Lesson 5: Working with DynaScript
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Lesson 5: Working with DynaScript

In addition to embedded queries, formatting, and static HTML, you can add application logic to templates with the DynaScript scripting language. You can also equip templates to process parameters passed from a Web browser appended to the URL or submitted via an HTML form.

This project involves the following tasks:

•	Add DynaScript to the template

•	Pass an argument to a template

 About DynaScript

 Add DynaScript to the template

 Pass an argument to a template


About DynaScript
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 5: Working with DynaScript

 About DynaScript

You can embed scripts in HTML templates to control the information that the template provides to the user. The Application server interprets DynaScript scripts and the results of that interpretation are passed on to the Web server which are then passed on to the browser.

The DynaScript scripting language uses a syntax much like JavaScript. It has the following characteristics:

•	Scripts are usually embedded in an HTML template.

You can also maintain a script as a separate file in the site, and include it in other scripts or templates, allowing for code reuse throughout the site.

•	The Application Server interprets the DynaScript language when a Web client requests the document containing the script. Since the script is processed before the content is passed back to the client, no client support for scripting is required to use scripts in your Web site.

•	Script statements and variables are case-sensitive.

•	Variables do not need to be declared.

•	Scripts can use the following data types:

•	numeric

•	logical (boolean) true or false

•	string

•	null

•	function

•	object (methods and properties)

•	Variables do not have a fixed data type. Data-type assignment is carried out as needed when expressions are evaluated.

•	Control statements such as if, while, do-while and for have a syntax similar to C and Java.

•	You can create objects and define their properties and methods.

•	DynaScript includes several built-in object types for working with databases, SQL, and HTML documents.

For more information on the DynaScript and DynaScript objects, see the PowerDynamo User's Guide.


Add DynaScript to the template
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 5: Working with DynaScript

 Add DynaScript to the template

Now that our template is querying the database we want to check that the query was completed without error. We can check for errors during processing by adding some DynaScript to our template.

♦ To add Dynascript to the template:

1	Open the Employees.stm template in Sybase Central.

2	Place the cursor immediately following the </TABLE> tag. (This tag is near the bottom of the file.)

3	Insert a carriage return, and type these two lines of code, each followed by a carriage return:

<!--SCRIPT

-->

These tags delimit DynaScript.

4	Type or copy the following lines of code within the script delimiters, each followed by a carriage return:

document.WriteLn("The query");

    if (SQL.GetErrorCode()==0) {

            document.WriteLn( " succeeded." );

    } else {

        document.WriteLn( " failed." );

    }

The above code checks for an error code and if 0 is returned outputs "The query succeeded" to the browser. If an error code of anything other than 0 is returned, the output to the browser will be "the query failed".

5	Choose File [image] Save to Database, and reload or refresh the document in the Web browser.

About script error messages

If you receive an error warning, return to the open template and fix the error. Save the changes to the database, as above, and reload or refresh the document in the Web browser.


Pass an argument to a template
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Lesson 5: Working with DynaScript

 Pass an argument to a template

Users can supply an argument when they request a document, which will be customized for the value they supply. The usual way to supply an argument is to fill in a form (you will work with forms in the next task).

The simplest way to supply an argument is to add it at the end of the URL, as in a string with the following syntax:

?argumentname=value

In this task, you create an argument called name, reference it from within HTML and DynaScript, and supply the value appended to the URL that you request.

♦ To pass an argument to a template:

1	Return to the open Employee.stm template.

2	Place the cursor above the script element that you added in the previous step. The cursor should be immediately after the </TABLE> tag.

3	Add a carriage return, and type the following:

Hello, $name!<P>

You can refer to an argument directly in the HTML and query portions of a template, where its name is preceded with the $ (dollar sign). This is called a text replacement macro. PowerDynamo substitutes the text replacement macro with the value passed in as an argument before sending the query.

Choose File [image] Save To Database.

1	Request the following URL in the Web browser, substituting your name for yourname:

http://localhost/ASASite/Employees.stm?name=yourname

Scroll to the bottom of the table to see your name.

1	Close the Employees.stm template.

What next?

The next section introduces forms.


Lesson 6: Working with forms
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Lesson 6: Working with forms

Instead of using the formatting repeated for each row of output, which is delimited by the <!--FORMATTING--> tags, you can access and program the results of queries from within DynaScript. DynaScript is an object oriented scripting language. DynaScript objects can have properties (data) associated with them as well as methods (functions that act on the properties).

The query object provides a scriptable way to work with a SQL query embedded in a Dynamo template.

In this project you will:

•	Create a form that asks a user to select which department they would like to display employee information for.

•	Create a template, containing DynaScript that queries the sample database as a result of the input provided to the form.

♦ To create a form:

1	In Sybase Central, start the Add Template wizard.

2	Call the new template Department.stm and click Next.

3	Enter the description Department employee list and click next.

4	Select inherited as the connection type and click Next.

5	Click Next and Finish until the wizard is closed.

6	Open the Department.stm file and replace the contents with the following HTML code.

<HTML>

<TITLE>department.stm</TITLE>

<BODY>

<H1>Department employee list</H1>

<HTML>

<P>Select the department that you would like employee information for</P>

<FORM METHOD=POST ACTION="mulchoice.stm" MULTIPLE SIZE="5">

<OL>

<INPUT TYPE="radio" NAME="choice" value="R & D">Research and Development<BR>

<INPUT TYPE="radio" NAME="choice" value="Sales">Sales<BR>

<INPUT TYPE="radio" NAME="choice" value="Finance">Finance<BR>

<INPUT TYPE="radio" NAME="choice" value="Marketing">Marketing<BR>

<INPUT TYPE="radio" NAME="choice" value="Shipping">Shipping<BR>

</OL>

<P><INPUT TYPE="submit" VALUE="Submit List"></P>

<P><INPUT TYPE="RESET" VALUE="Clear Form"></P>

</FORM>

</BODY>

</HTML>

The above HTML creates a form for which a user is required to select a category for which he would like data displayed. Once a value has been selected it will be used in the next template, mulchoice.stm, to generate results.

7	From the File menu, click Save to Database, and close the template.

♦ To create a template that contains a query and output manipulation:

1	Start the Add Template wizard again and create a new template called mulchoice.stm. Create this template in the same manner that you created Department.stm.

2	Open the mulchoice.stm template and replace the contents with the following HTML code.

<HTML>

<TITLE>Selected Department</TITLE>

<BODY>

<!--SCRIPT

    document.WriteLn("<H1> The ")

    document.WriteLn(document.value.choice)

    document.WriteLn("Group </H1>")

var selectStr = "select emp_lname, emp_fname, phone from employee, department where department.dept_id = employee.dept_id";

if(exists(document.value.choice)){

    selectStr += " and department.dept_name = " + "'" + document.value.choice + "'" ;

}

myQuery=connection.CreateQuery(selectStr);

colCount=myQuery.GetColumnCount();

document.WriteLn("<TABLE BORDER>");

for (i=1; i <= colCount; i++){

    document.WriteLn("<TH>");

    document.WriteLn(myQuery.GetColumnLabel(i));

    document.WriteLn("</TH>");

}

while (myQuery.MoveNext()){

    document.WriteLn("<TR>");

    for (i=1; i <= colCount; i++) {

        document.WriteLn("<TD>");

        document.Write(myQuery.GetValue(i));

        document.WriteLn("</TD>");

    }

    document.WriteLn("</TR>");

}

document.WriteLn("</TABLE>");

-->

</BODY>

</HTML>

Explanation of HTML

The following DynaScript methods and properties are used in the mulchoice.stm template:

•	document.value.variable    used to pass in the argument from Department.stm

•	document.WriteLn(outputString)    used to append the outputString to the output of the document. In the case of mulchoice.stm, document.WriteLn is being used to add HTML tags to the document and to display the query output.

•	connection.CreateQuery( )    executes the query and returns the query object.

•	query.GetColumnCount( )    returns the number of columns in the query result set.

•	From the File menu, click Save to Database, and close the template.

•	Open Department.stm in your browser.

http://localhost/ASASite/department.stm

•	Select a department and click Submit List.

•	Open and examine the templates Department.stm and mulchoice.stm. Look at each line of code individually so that you understand the relationship between what you see in your browser and the two templates that you created.


Summary
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

 Summary

In this tutorial you have learned how to

•	Connect to PowerDynamo.

•	Create a PowerDynamo Personal Web Server mapping.

•	Create and enhance a template.

•	Use DynaScript.

•	Work with forms.

 Where do I go from here?


Where do I go from here?
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 6.  Building a Dynamic Web Site with PowerDynamo

        Summary

 Where do I go from here?

  For more information about PowerDynamo, click Start [image] Programs [image] Sybase SQL Anywhere 7 [image] PowerDynamo 3.5 to access the books PowerDynamo Reference and PowerDynamo User's Guide. From the version of Sybase Central that comes with PowerDynamo, you can click Help to access those two books and also PowerDynamo 3.5 Plug-In Help.


CHAPTER 7.  Replicating Data with SQL Remote
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

 CHAPTER 7.  Replicating Data with SQL Remote

About this chapter

This chapter shows you how to set up a simple replication system using SQL Remote.

 About SQL Remote

 Lesson 1: Getting Started

 Lesson 2: Set up the consolidated database

 Lesson 3: Set up the remote database

 Lesson 4: Replicate data

 Lesson 5: Restore the database and database settings

 Summary


About SQL Remote
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 About SQL Remote

Data replication is the sharing of data among physically distinct databases. Changes made to shared data in any one database are replicated in the other databases. Sybase provides three distinct strategies for data replication:

•	SQL Remote

•	MobiLink

•	Replication Server

SQL Remote is designed for two-way replication involving a consolidated data server and a very large number of remote databases, typically including many mobile databases. Administration and resource requirements at the remote sites are minimal. This system is message-based.

In a SQL Remote installation, the central database must be either Sybase Adaptive Server Anywhere or Sybase Adaptive Server Enterprise.

SQL Remote replicates data by scanning the transaction log and preparing messages, as appropriate, for each transaction. It orders these messages and sends them to the remote or consolidated site. When processing received messages, SQL Remote always processes them in the same order as they were applied to the other database. When necessary, it automatically delays processing a message until all earlier messages have been applied.

For more information about synchronization strategies, including complete SQL Remote documentation, see the ASA Replication and Synchronization Guide.

For an introduction to MobiLink synchronization, see Adaptive Server Anywhere Synchronization using MobiLink.


Lesson 1: Getting Started
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 Lesson 1: Getting Started

This tutorial describes how to set up a simple SQL Remote replication system.

With SQL Remote you can carry out two-way replication between a central database (called the consolidated database) and a set of remote databases. These remote databases may be on laptop computers, while the consolidated database may be on a network in an office. All the setup and administration is carried out at the consolidated database.

This tutorial describes a very simple case, replicating one table from the sample database to a single remote database on the same machine.

In the tutorial you act as the system administrator of a consolidated Adaptive Server Anywhere database. The replication system consists of a simple table from the sample database. The table we replicate is the department table, which is one of the simplest in the database.

The tutorial takes you through the following steps:

1	Setting up the consolidated database.

2	Creating a file-sharing replication system with a single remote database.

3	Replicating data between the two databases.

 Create directories for the tutorial


Create directories for the tutorial
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 1: Getting Started

 Create directories for the tutorial

Before you start, you need to create directories to hold the databases and other files you create during the tutorial. You should also create a copy of the sample database, in case you need to recreate it in its original form.

♦ To prepare for the tutorial:

1	Create a directory to hold the files you make during this tutorial; for example c:\tutorial.

2	Create two subdirectories of tutorial called dba and field; for example, c:\tutorial\dba and c:\tutorial\field. These subdirectories hold messages for each of the two user IDs in the replication system.

3	Create a copy of the sample database. Create a new folder for the copy, called something like demoback, and copy asademo.db into it.

What next?

Now you're ready to create the consolidated database.


Lesson 2: Set up the consolidated database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 Lesson 2: Set up the consolidated database

This section shows you how to prepare the consolidated database of a simple replication system. You require DBA authority to do this.

To prepare a consolidated database for replication, this lesson takes you through the following steps:

1	Create a message type to use for replication.

2	Grant PUBLISH permissions to a user ID to identify the source of outgoing messages.

3	Grant REMOTE permissions to all user IDs that are to receive messages.

4	Create a publication describing the data to be replicated.

5	Create subscriptions describing who is to receive the publication.

 Add a SQL Remote message type

 Add the publisher to the database

 Add a remote user to the database

 Add publications and subscriptions


Add a SQL Remote message type
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 2: Set up the consolidated database

 Add a SQL Remote message type

All messages sent as part of replication use a message type. A message type description has two parts:

•	A message link supported by SQL Remote. In this tutorial, we use the FILE link. This is a file sharing method, where the messages are left in a file on disk, and read by the intended recipient. SQL Remote also supports ftp and e-mail protocols.

•	An address for this message link, to identify the source of outgoing messages. In this tutorial we use a file path to specify where the messages will be left.

Message types are created in all new databases, but you do need to supply an address for the message type you will use.

♦ To add an address to a message type:

1	From Sybase Central, connect to the sample database as user ID DBA using the password SQL, and open the sample database container (asademo) in the left pane.

2	Click the SQL Remote folder in the left pane.

3	Double-click the Message Types folder in the right pane.

4	Right-click the FILE message type, and select Properties.

5	Enter a publisher address to provide a return address for remote users. The publisher address is the directory you created in Lesson 1 to hold messages for the consolidated database (dba). For example, c:\tutorial\dba.

6	Click OK to save the message type.


Add the publisher to the database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 2: Set up the consolidated database

 Add the publisher to the database

Each database in a SQL Remote replication system needs a single user ID that identifies the publisher of the data. Here, we make the dba user ID the publisher.

♦ To set the publisher:

1	Click the Users & Groups folder in the left pane.

2	Right-click dba in the right pane, and select Set As Publisher from the popup menu.

Publisher appears in the Type column beside dba.

A database can have only one publisher. You can find out who the publisher is at any time by opening the SQL Remote folder.


Add a remote user to the database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 2: Set up the consolidated database

 Add a remote user to the database

Each remote database is identified in the consolidated database by a user ID with REMOTE permissions.

When a remote user is added to a database, the message system they use and their address under that message system need to be stored along with their database user ID.

♦ To add a remote user:

1	Open the SQL Remote folder in the left pane, and then click the Remote Users folder.

2	Double-click Add Remote User in the right pane.

The Create a New Remote User wizard appears.

3	Create a remote user with user name field, password field, message type file, and address field. You should select the defaults for all other entries in the wizard, except that you should grant DBA permissions as well as REMOTE DBA permissions to the user on the Indicate Authorities page. Click Finish to create the remote user.

The remote user field appears in the Remote Users folder.

Notes

If you forget to set DBA permission in the wizard, you can set it by double-clicking the user, and checking DBA in the Authorities tab.

You have now created the users who will use this system.


Add publications and subscriptions
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 2: Set up the consolidated database

 Add publications and subscriptions

This section describes how to add a publication to a database, and how to add a subscription to that publication for a user. The publication replicates all rows of the table department.

♦ To add a publication:

1	Click the Publications folder in the SQL Remote folder of the sample database.

2	In the right pane, double-click Add Publication.

The Create a New Publication wizard appears.

3	Name the publication DepartmentPub on the first page of the wizard.

4	On the next page, click Add Article and select department from the list of tables. Leave All Columns selected, and press OK to add the table.

5	Click Finish to create the publication.

Add a subscription

Each user ID that is to receive changes to a publication must have a subscription to that publication. Subscriptions can only be created for a valid remote user. You need to add a subscription to the DepartmentPub publication for the remote database user field.

♦ To add a subscription:

1	Open the Publications folder, which is in the SQL Remote folder.

2	Right-click DepartmentPub, and click Subscribe For in the popup menu.

The Subscribe For User dialog appears, showing the field user in the list of remote users.

3	Select field and click Subscribe to subscribe the field user to the DepartmentPub publication.

4	Close the Subscribe For User dialog.

You have now set up the consolidated database.

What next?

You can now create the remote database.


Lesson 3: Set up the remote database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 Lesson 3: Set up the remote database

The remote database needs to be created and configured in order to send and receive messages and participate in a SQL Remote setup.

The database extraction utility enables you to carry out all the steps needed to create a remote database complete with subscriptions and required user IDs.

 Create the remote database

 Verify that the database is created properly


Create the remote database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 3: Set up the remote database

 Create the remote database

You need to extract a database from the consolidated database for remote user field.

♦ To extract a database:

1	Right-click the sample database asademo (dba), and select Extract Database from the popup menu.

The Extract & Synchronize a Database wizard appears.

2	On the Remote User page, choose field as the remote user.

3	Choose Start Subscriptions Automatically.

4	On the next page, enter a location for the SQL file that will build the database. For example, choose c:\tutorial\field.sql.

5	Choose Reload Into a New Database, and enter a path and filename for the database. For example, c:\tutorial\field.db.

6	Choose to extract the structure and data (the default setting).

7	Leave the other options at their default settings, and create the remote database.

The Unload/Extract Database Message Window appears, showing you the progress of the extraction. When completed, close the window.

Note

In a proper SQL Remote setup, the remote database field would be loaded on to the computer, together with a database server and any client applications required. For this tutorial, we leave the database where it is and use Interactive SQL to input and replicate data.


Verify that the database is created properly
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 3: Set up the remote database

 Verify that the database is created properly

To see what the extraction utility has done, connect to the field database and confirm that all the database objects are created.

♦ To browse through the remote database:

1	In Sybase Central, click the Connect button.

The Connect dialog appears.

2	On the Identification tab, enter the user ID field and password field.

3	On the Database tab, enter the database path (for example, c:\tutorial\field.db). Click OK to connect to the database.

The database field appears in the left pane of Sybase Central.

4	Open the Tables folder. The department table, owned by user dba, is in the list.

5	Right-click the Department table, and select View Data from the popup menu. Interactive SQL opens, showing that all the rows of the department table are filled (five rows). Close Interactive SQL.

6	Open the SQL Remote folder. The field user appears in the right pane, showing that field is the publisher for this database. Any data sent from this database comes from the user field, just as any data from the consolidated database comes from the user dba.

7	Open the Publications folder. You will see that the DepartmentPub publication is present.

8	Open the Remote Users folder. You will see that user ID dba is present, and is designated as a consolidated user. This means dba is the publisher of the consolidated database (above the field database in the hierarchy).

What next?

The system is now ready for replication.


Lesson 4: Replicate data
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 Lesson 4: Replicate data

You now have a replication system in place. In this section, data is replicated from the consolidated database to the remote database, and from the remote to the consolidated database.

 Add data to the consolidated database

 Verify that the data has arrived

 Replicate from the remote database to the consolidated database


Add data to the consolidated database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 4: Replicate data

 Add data to the consolidated database

First, enter a row in the consolidated database.

♦ To enter data in the consolidated database:

1	Connect to the sample database in Interactive SQL as user ID DBA, with password SQL.

2	To enter and commit a row into the department table, execute the following SQL statement:

INSERT INTO department (dept_id, dept_name)

VALUES (202, 'Eastern Sales') ;

COMMIT ;

Tip
To execute an SQL statement in Interactive SQL, type or copy the statement in the SQL Statements pane, and then either press F5, or choose SQL [image] Execute.

3	To confirm that the data is entered by viewing the data in the tables, execute the following statement:

SELECT *

FROM Department

The next step is to send the relevant rows to the remote database.

To send the rows to the remote database, you must run the Message Agent at the consolidated database. The dbremote program is the Message Agent for Adaptive Server Anywhere.

♦ To send the data to the remote database:

1	At a command prompt, navigate to your tutorial directory. For example, navigate to c:\tutorial.

2	To run the Message Agent against the consolidated database, enter the following statement at the command line:

dbremote -c "dbn=asademo;uid=dba;pwd=sql"

The SQL Remote window appears, informing you of the status of your command.

3	When SQL Remote displays the message Execution completed, click Shutdown.

To receive the insert statement at the remote database, you must run the Message Agent, dbremote, at the remote database.

♦ To receive data at the remote database:

1	At a command prompt, change to your tutorial directory. For example, navigate to c:\tutorial.

2	Enter the following statement at the command line to run the Message Agent against the field database:

dbremote -c "dbf=c:\tutorial\field.db;uid=field;pwd=field"

The SQL Remote window appears, informing you of the status of your command.

3	When SQL Remote displays the message Execution completed, click Shutdown.

Notes

•	The SQL Remote window displays status information while running. This information can be output to a log file for record keeping in a real setup. You will see that the Message Agent first receives a message from asademo, and then sends a message. This return message contains confirmation of successful receipt of the replication update; such confirmations are part of the SQL Remote message tracking system that ensures message delivery even in the event of message system errors.

•	Depending on the current status of the database, there are three different connectivity parameters you can use to connect to a database:

•	dbf    Connect to the database using the database file. This parameter requires you to specify the database file itself. If no server is currently running, a server will be started and the database will be loaded onto it. If a server is already running, the database will be loaded onto the default server.

•	dbn    Connect to the database using the database name. This parameter requires you to specify the name of the database. You can only use this parameter when the database is already running.

•	dbs    Connect to the database using the database source. A data source is a collection of parameters stored in the system registry or in a set of files. The source is referenced simply by its name.

  For more information about data sources, see DataSourceName connection parameter.

•	As an alternative to navigating to the tutorials directory, you can specify a path relative to your server. For example, if your server is in the c:\tutorial\dba directory, you can use the statement:

dbremote -c "dbn=..\asademo.db;uid=dba;pwd=sql"


Verify that the data has arrived
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 4: Replicate data

 Verify that the data has arrived

Connect to the field database using Interactive SQL, and inspect the department table to verify that the row has been received.

♦ To verify that the data has arrived:

1	Connect to the field database using Interactive SQL:

•	In Interactive SQL, click SQL [image] Connect.

You may have to disconnect first.

•	On the Identification tab, enter user ID field and password field.

•	On the Database tab, enter or browse to field.db. For example, enter c:\tutorial\field.db.

•	Click OK to connect.

2	Inspect the department table by typing the following statement:

SELECT *

FROM dba.department

You will see that the department table contains the row entered at the consolidated database.


Replicate from the remote database to the consolidated database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 4: Replicate data

 Replicate from the remote database to the consolidated database

Now try entering data at the remote database and sending it to the consolidated database. Only the outlines are presented here.

♦ To replicate data from the remote database to the consolidated database:

1	Connect to the field database from Interactive SQL.

2	Insert a row at the remote database. In the SQL Statements pane, type

INSERT

INTO dba.Department (dept_id, dept_name)

VALUES (203, 'Western Sales')

3	Commit the row:

COMMIT;

4	With the field.db database running, run dbremote to send the message to the consolidated database:

dbremote -c "dbn=field;uid=field;pwd=field"

5	With the asademo.db database running, run dbremote to receive the message at the consolidated database:

dbremote -c "dbn=asademo;uid=dba;pwd=sql"

6	Connect to the consolidated database and display the department table:

SELECT *

FROM department

What next?

The tutorial is now complete, but you should continue to the next section to restore the sample database to its original form.


Lesson 5: Restore the database and database settings
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 Lesson 5: Restore the database and database settings

Once you have completed the tutorial it is important to undo any changes you have made to the sample database. Make sure that the following steps are completed in order to ensure that the settings are reset properly.

♦ To delete the remote user:

1	In the left pane of Sybase Central, click the Remote Users folder for the sample database.

2	Right-click the field user, and select Revoke Remote from the popup menu. Click Yes to remove the user from the list.

♦ To delete the publication:

1	Click the Publications folder in the SQL Remote folder.

2	Right-click DepartmentPub in the right pane and select Delete from the popup menu. Click Yes to confirm the deletion.

♦ To revoke the publishing status from the sample database:

1	Click the Users and Groups folder in the left pane.

2	Right-click the DBA user in the right pane, and select Revoke Publisher from the popup menu.

Revoking a status while running the database
The status of a user cannot be modified while the user is running the database. Make sure that the user is idle before revoking or invoking a status.

♦ To restore the original message type settings:

1	Click the Message Types folder in the SQL Remote folder.

2	Right-click the File message type in the right pane and select Properties.

3	Delete the publisher address and click OK to restore the settings.

♦ To delete the remote database:

1	Click the Utilities folder in the left pane.

2	Double-click Erase Database in the right pane.

The Erase An Adaptive Server Anywhere Database wizard appears.

3	Enter the name of the database you want to erase or click Browse to search for the database. For example, enter c:\tutorial\field.db.

4	On the next page, choose to delete a database file and click next to finish erasing the file.

Notes

Now that the tutorial is complete, it might be a good idea to delete the tutorial directory (c:\tutorial) in order to save space.

 Restore the data in the database


Restore the data in the database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Lesson 5: Restore the database and database settings

 Restore the data in the database

The most important part of the cleanup process is to ensure that the changes to the sample database are reversed. The integrity of the sample database is very important in order to carry out other tutorials in later chapters of the manual.

♦ To delete the inserted data from the sample database:

1	Connect to the sample database (dba) using Interactive SQL.

2	Type the following statements in the command window and click Execute:

DELETE

FROM department

WHERE dept_id = 202

   OR dept_id = 203

   OR dept_name = 'Eastern Sales'

   OR dept_name = 'Western Sales';

COMMIT;

3	Confirm that the data has been deleted by viewing the data in the tables:

SELECT *

FROM department

The inserted data has been deleted and the database has been restored to its original state.

Notes

The previous statements delete only the data that was added specifically in the tutorial. If you have modified the database in any other way, you must reverse the changes you made. Alternatively, select the copy you made of the sample database and copy it over the one you changed.


Summary
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

 Summary

In this tutorial you learned how to

•	Prepare the consolidated database of a simple replication system.

•	Create and configure a remote database.

•	Replicate data in both directions between the two databases.

•	Restore your database and database settings.

•	Verify all the steps.

 Where do I go from here?


Where do I go from here?
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 7.  Replicating Data with SQL Remote

        Summary

 Where do I go from here?

  For more information about SQL Remote, see the ASA Replication and Synchronization Guide.

  For an introduction to MobiLink synchronization, see Adaptive Server Anywhere Synchronization using MobiLink.


CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

 CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

About this chapter

This chapter provides a tutorial that guides you through the process of synchronizing two Adaptive Server Anywhere databases via MobiLink. One of these databases is the consolidated database, and the other is a remote database. In this tutorial, you create these databases and then synchronize the two.

 About MobiLink

 Lesson 1: Create the consolidated database

 Lesson 3: Create the remote database

 Lesson 3: Synchronize the databases

 Summary


About MobiLink
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

 About MobiLink

Data replication is the sharing of data among physically distinct databases. Changes made to shared data in any one database are replicated to the other databases in the replication setup. Sybase provides three distinct technologies for data replication:

•	MobiLink

•	SQL Remote

•	Replication Server

Sybase MobiLink technology enables replication between an ODBC-compliant consolidated database and Adaptive Server Anywhere or UltraLite remote databases. The consolidated database and the database server can be made by many database systems, including Sybase Adaptive Server Anywhere, Sybase Adaptive Server Enterprise, Oracle, Microsoft SQL Server, or IBM DB2. The remote database must be Adaptive Server Anywhere.

MobiLink is designed for two-way replication involving a consolidated data server and large numbers of remote databases, typically including many mobile databases. Administration and resource requirements at the remote sites are minimal. The system is connection-based and a remote site can connect as often as desired. At the end of each connection, the databases are fully synchronized.

MobiLink works by lumping the results of multiple transactions on the remote database into one set of changes to be applied to the consolidated database. Since synchronization always occurs at a transaction boundary, referential integrity is preserved. The order of the individual changes made during the component transactions is not preserved: since uncommitted data is never replicated, data integrity is preserved.

  For more information about synchronization strategies, including complete MobiLink documentation, see the ASA Replication and Synchronization Guide.

  For an introduction to SQL Remote synchronization, see Replicating Data with SQL Remote.


Lesson 1: Create the consolidated database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

 Lesson 1: Create the consolidated database

In this tutorial, you create a consolidated database, extract a remote database, and then synchronize the two databases via MobiLink.

The first step in the tutorial is to create a .sql file for the database. This file has two purposes. Firstly, the file creates and populates the tables in the consolidated database. This process is not part of synchronization, but makes the tutorial simpler by setting up the data that will be synchronized.

Secondly and most importantly, the .sql file contains synchronization scripts that control the synchronization process. These scripts specify how uploaded data is to be incorporated into the consolidated database and which data is to be downloaded to the remote database. Synchronization scripts are SQL statements or procedures that control the synchronization process. Each script controls a particular event during synchronization. The scripts, which are written in the native language of the consolidated DBMS, are stored in tables within the consolidated database.

Notes

•	The consolidated database is the central database and is connected to a MobiLink synchronization server. Remote databases are, as their name indicates, the remote databases that must be synchronized with the consolidated database.

A reference database is an Adaptive Server Anywhere database that is created for the sole purpose of creating remote databases. The consolidated database and reference database can have different schemas. The reference database must be an Adaptive Server Anywhere database, but the consolidated database can be made by a wide range of Sybase and non-Sybase database-management systems. For the purpose of simplicity in this tutorial, we use the same database as both the reference and consolidated database. This is possible because the consolidated database in this tutorial is an Adaptive Server Anywhere database and because the schema of the remote database is a subset of the schema of the consolidated database.

•	This tutorial assumes that you have Adaptive Server Anywhere 7.0 installed on Windows 95/98/NT.

 Create a SQL file for the consolidated database

 Build the consolidated database


Create a SQL file for the consolidated database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 1: Create the consolidated database

 Create a SQL file for the consolidated database

In this section, you create a file that uses SQL commands to create and insert data into two tables, People and Pets, and uses synchronization scripts to specify how the databases should be synchronized.

♦ To create the SQL file:

1	Create the directory c:\synchtutorial.

2	Copy the following code into a new file and save it as server.sql in your c:\synchtutorial directory.

create table People (

person_id	integer	primary key,

fname	char(30),

lname	char(40),

notes	char(500)

);

create table Pets (

pet_id	integer	primary key,

person_id	integer,

name	char( 30 ),

species	char( 30 ),

foreign key person_id references People

);

insert

into People

values( 0, 'Alan', 'Able', 'good guy' );

insert

into People

values( 1, 'Betty', 'Best', NULL );

insert

into Pets

values( 0, 0, 'Judy', 'rat' );

insert

into Pets

values( 1, 0, 'Carmel', 'guinea pig' );

insert

into Pets

values( 3, 1, 'Marshall', 'cat' );

commit;

call ml_add_table_script(

'version1',

'People',

'upload_cursor',

'select person_id, fname, lname from People where

person_id=?'

);

call ml_add_table_script(

'version1',

'People',

'download_cursor',

'select person_id, fname, lname from People'

);

call ml_add_table_script(

'version1',

'Pets',

'upload_cursor',

'select pet_id, person_id, name, species from Pets

where pet_id=?'

);

call ml_add_table_script(

'version1',

'Pets',

'download_cursor',

'select pet_id, person_id, name, species from Pets'

);

commit;

 Explanation of the sample code


Explanation of the sample code
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 1: Create the consolidated database

          Create a SQL file for the consolidated database

 Explanation of the sample code

The server.sql sample code creates tables in the consolidated database, inserts data into the tables, and stores synchronization scripts that specify which data is to be uploaded to the consolidated database and downloaded to the remote database.

Create tables

The sample code creates two tables: People and Pets. The People table has four columns: person_id, fname, lname, and notes. The Pets table also has four columns: pet_id, person_id, name and species.

Insert data

The following is the People table in the consolidated database after data has been inserted:

person_id	fname	lname	notes
0	Alan	Able	good guy
1	Betty	Best	(NULL)
The following is the Pets table in the consolidated database after data has been inserted:

pet_id	person_id	name	species
0	0	Judy	rat
1	0	Carmel	guinea pig
3	1	Marshall	cat
Synchronization scripts

The last part of the file is four synchronization scripts. Each script starts with call ml_add_table_script. The scripts specify that the first three columns in the People table should be uploaded to the consolidated database and downloaded to the remote database, and that all four columns in the Pets table should be uploaded to the consolidated database and downloaded to the remote database. When server.sql is run, the scripts are stored in tables in the consolidated database for future use.


Build the consolidated database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 1: Create the consolidated database

 Build the consolidated database

In this section, you use the file server.sql to create a consolidated database and add the tables Pets and People to it.

♦ To build the consolidated database:

1	Create a new consolidated database called master.db:

•	Open a command prompt window and navigate to c:\synchtutorial.

•	Type dbinit master.db

2	Start the consolidated database server by typing dbeng7 master.db

3	Start Interactive SQL and connect to master.db:

•	On the Identification tab, enter the user name DBA and password SQL.

•	On the Database tab, in the Database File box, type or browse to c:\synchtutorial\master.db.

•	Click OK.

4	To add the database tables and synchronization scripts, you use the file server.sql, which you created earlier in the tutorial:

•	From the File menu, choose Run Script.

•	Select the file c:\synchtutorial\server.sql, and click Open.

The statements in the file are executed. Your consolidated database now contains two tables with data: People and Pets. In addition, synchronization scripts are now stored in the consolidated database; these will determine the upload and download contents.

5	Verify that tables have been created and data has been inserted successfully by displaying the contents of each file:

•	Execute the following SQL statement:

SELECT * from People

Tip
To execute an SQL statement in Interactive SQL, type or copy the statement in the SQL Statements pane, and then either press F5, or choose SQL [image] Execute.

The contents of the People table are displayed:

person_id	fname	lname	notes
0	Alan	Able	good guy
1	Betty	Best	(NULL)
•	Execute the following SQL statement:

SELECT * from Pets

The contents of the Pets table are displayed:

pet_id	person_id	name	species
0	0	Judy	rat
1	0	Carmel	guinea pig
3	1	Marshall	cat
What next?

Now you are ready to create the remote database.


Lesson 3: Create the remote database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

 Lesson 3: Create the remote database

In this section you create a remote database and verify its contents.

Remote databases can be created by extracting information from a reference database. A reference database is an Adaptive Server Anywhere database that is used to create remote databases. Under certain conditions, you can use the consolidated database as a reference database. The conditions are that the consolidated database is an Adaptive Server Anywhere database, and the remote database is a subset of the consolidated database. For the sake of simplicity in this tutorial, we are using the consolidated database as the reference database.

In order to create remote databases, you must create templates that define their structure and location.

 Create a template and site for synchronization

 Create the remote database


Create a template and site for synchronization
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 3: Create the remote database

 Create a template and site for synchronization

Next, we create a synchronization template and a synchronization site that are used to create the remote database. The synchronization template identifies which tables and columns are to be replicated, and which server they are to be replicated to. The synchronization site identifies a particular remote database.

♦ To create the templates:

1	Create a synchronization template.

•	To create a synchronization template, execute the following SQL statement:

CREATE SYNCHRONIZATION TEMPLATE testpub

TYPE 'tcpip'

ADDRESS 'host=localhost;port=2439;'

Option sv='Version1'

(	TABLE People ( person_id, fname, lname ),

TABLE Pets

);

2	Create a synchronization site. To create a site called demo_sync_site, execute the following SQL statement:

CREATE SYNCHRONIZATION SITE 'demo_sync_site'

USING testpub;

In the next section of this tutorial, you will create the remote database for this site.

3	Disconnect from master.db by executing the following SQL statement:

disconnect


Create the remote database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 3: Create the remote database

 Create the remote database

To create the remote database, you extract from the reference database, which in this case is the consolidated database, master.db.

♦ To extract the remote database:

1	Ensure that the consolidated database is still running. If it is not, restart the server by entering the following command at a command prompt in your c:\synchtutorial directory.

dbeng7 master.db

2	Extract the remote database:

•	Open a command prompt window and navigate to c:\synchtutorial. Type the following command on one line.

mlxtract -c "dbn=master;uid=DBA;pwd=SQL"

-an remote.db

c:\synchtutorial demo_sync_site

This command causes the Sybase database extraction utility to connect to your master database as user DBA and create the database for site demo_sync_site. The new database is called remote.db and is placed in your c:\synchtutorial directory.

While the remote database is being created, the following message appears in the command prompt window: Adaptive Server Anywhere MobiLink Extract version 7.0.X.xxx.

3	Shut down the database server:

•	Right-click on the database server icon in the system tray at the bottom right-hand corner of your screen and click Exit.

4	Start another engine for both the master and remote databases by issuing the following command at a command prompt in your c:\synchtutorial directory:

dbeng7 master.db remote.db

5	Open a new window in Interactive SQL and connect to the remote database:

•	In Interactive SQL, select File [image] New Window.

•	When the Connection dialog appears, supply the user name DBA and the password SQL. On the Advanced tab, enter the parameter dbn=remote. Click OK.

When you are connected, the new Interactive SQL window appears. The title bar now says, remote (DBA) on master.

6	The tables do not yet contain any data, but we can verify that they have been created successfully:

•	Execute the following SQL statement:

SELECT * from People

•	Execute the following SQL statement:

SELECT * from Pets

What next?

You are now ready to synchronize the consolidated and remote databases.


Lesson 3: Synchronize the databases
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

 Lesson 3: Synchronize the databases

In this section you synchronize your remote and consolidated databases, and then modify the remote database and synchronize them again.

 Synchronize the databases

 Modify the remote database

 Upload changes


Synchronize the databases
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 3: Synchronize the databases

 Synchronize the databases

To synchronize the databases the first time, you must create an ODBC data source. This is because the MobiLink synchronization server can connect to the database engine only through ODBC.

♦ To synchronize the consolidated and remote databases:

1	Create an ODBC data source for the master database:

•	From the Windows Start menu, choose Programs [image] Sybase SQL Anywhere 7 [image] Adaptive Server Anywhere 7 [image] ODBC Administrator.

•	Click Add. In the Create New Data Source dialog, select Adaptive Server Anywhere 7.0, and then click Finish.

•	Configure the new data source. To do this, select the ODBC tab and supply the Data source name MobiLink/ASA tutorial. Select the Login tab, and supply the User ID DBA and the password SQL. Select the Database tab and supply the Database file c:\synchtutorial\master.db. Click OK.

•	Click OK again to close the ODBC Data Source Administrator.

2	Start the MobiLink synchronization server.

•	The following command starts the MobiLink synchronization server and identifies the ODBC data source MobiLink/ASA tutorial as the consolidated database. At a command prompt, type

start dbmlsrv7 -c "dsn=MobiLink/ASA tutorial"

•	When finished, a MobiLink message dialog appears stating, Ready to handle requests. Do not close this dialog: it is updated as you proceed. Read the text to ensure you have started the MobiLink synchronization server successfully.

3	Synchronize the two databases by typing

dbmlsync -c "dbn=remote;uid=DBA;pwd=SQL"

When finished, a DBMLSync message dialog appears. Read the text to ensure you have synchronized the databases successfully.

4	Verify that the data has transferred successfully.

•	Execute the following SQL statement:

select * from People

The People table in the remote database should appear as follows. Note that the remote database lacks the notes column because that column was not mentioned in the synchronization template.

person_id	fname	lname
0	Alan	Able
1	Betty	Best
•	Execute the following SQL statement:

select * from Pets

The Pets table in the remote database should appear as follows.

pet_id	person_id	name	species
0	0	Judy	rat
1	0	Carmel	guinea pig
3	1	Marshall	cat


Modify the remote database
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 3: Synchronize the databases

 Modify the remote database

In this section, you make some changes to the remote database. Because the remote database contains a synchronization definition, Adaptive Server Anywhere automatically remembers which data must be sent to the consolidated database when you next synchronize.

♦ To modify the remote database:

1	Add a new person to the remote database with the following SQL statement:

insert into People values( 2, 'Charles', 'Fish')

2	Add Charles's pet guinea pig using the following statement.

insert into Pets

values( 4, 2, 'Hershey', 'guinea pig' )

3	Commit these changes by typing

commit

4	The above changes will be sent to the consolidated database automatically. However, you can make changes to only the remote database by temporarily suspending synchronization. You can suspend the synchronization of delete statements only; all other statements will continue to be synchronized.

•	Temporarily suspend logging of deletions for synchronization by executing the following SQL statement.

stop synchronization delete

•	Delete Carmel the guinea pig and commit the change.

DELETE from Pets WHERE pet_id=1;

commit

•	Restart logging of changes for synchronization.

start synchronization delete

5	Verify your changes:

•	Execute the following SQL statement:

select * from People

The People table in the remote database should appear as follows.

person_id	fname	lname
0	Alan	Able
1	Betty	Best
2	Charles	Fish
•	Execute the following SQL statement:

select * from Pets

The Pets table in the remote database should appear as follows.

pet_id	person_id	name	species
0	0	Judy	rat
3	1	Marshall	cat
4	2	Hershey	guinea pig


Upload changes
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Lesson 3: Synchronize the databases

 Upload changes

Now you need to synchronize the two databases again.

♦ To upload changes made to the remote database:

1	Ensure that the MobiLink synchronization server is still running. If it is not, restart it by typing

dbeng7 master.db remote.db

2	Synchronize the two databases.

Run the command dbsync as follows:

dbmlsync -c "dbn=remote;uid=DBA;pwd=SQL"

3	Open a new window in Interactive SQL and connect to the master database.

•	In Interactive SQL, choose File [image] New Window.

•	When the Connect dialog appears, select ASA via jConnect, and then choose localhost from the list of servers. Supply the user name DBA and the password SQL. Enter the parameters dbn=master. Click OK.

When you are connected, the new Interactive SQL window appears. The title bar now says, master (DBA) on master.

4	Verify that the databases have been synchronized.

•	Execute the following SQL statement:

select * from People

The People table in the master database should appear as follows.

person_id	fname	lname	notes
0	Alan	Able	good guy
1	Betty	Best	(NULL)
2	Charles	Fish	(NULL)
•	Execute the following SQL statement:

select * from Pets

The Pets table for both databases should appear as follows.

pet_id	person_id	name	species
0	0	Judy	rat
1	0	Carmel	guinea pig
3	1	Marshall	cat
4	2	Hershey	guinea pig

The inserts and updates made only to the remote database prior to synchronization now apply to both databases after synchronization.

5	Close your connections and exit Interactive SQL:

•	Disconnect from the master database and from the remote database by executing the following SQL statement in each Interactive SQL window.

disconnect

•	Exit Interactive SQL.

6	Shut down the MobiLink synchronization server by typing the following at a command prompt:

dbmlstop

7	Shut down the database server.

•	Right-click the database server icon in the system tray at the bottom right hand corner of your screen and click Exit.


Summary
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

 Summary

During this tutorial, you accomplished the following tasks.

•	Created a new Adaptive Server database that served as both the consolidated database and the reference database.

•	Created a synchronization template and synchronization site that defined which tables and columns were to be extracted for the remote database.

•	Extracted a remote database, using the original database as the reference database.

•	Started a MobiLink synchronization server, using the original database as the consolidated database, and synchronized the remote database in order to download an initial set of data.

•	Modified the remote database and explicitly prevented one of these changes from being logged for synchronization.

•	Synchronized the remote database again, uploading your changes to the consolidated database.

 Where do I go from here?


Where do I go from here?
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 8.  Adaptive Server Anywhere Synchronization Using MobiLink

        Summary

 Where do I go from here?

  For more information about MobiLink, see the ASA Replication and Synchronization Guide.

  For an introduction to SQL Remote synchronization, see Replicating Data with SQL Remote.


CHAPTER 9.  Creating Reports with InfoMaker
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

 CHAPTER 9.  Creating Reports with InfoMaker

About this chapter

This chapter includes a brief tutorial of InfoMaker report making that gets you started and teaches you the basic skills required to be productive in the InfoMaker environment.

 About InfoMaker

 Lesson 1: Getting Started

 Lesson 2: Create a basic report

 Lesson 3: Enhance your report

 Summary


About InfoMaker
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

 About InfoMaker

InfoMaker is a powerful reporting and data maintenance tool. With InfoMaker, you can create the following objects:

•	Reports to view data.

•	Forms to view and change data.

•	Queries to automatically retrieve data for reports or forms.

•	Pipelines to pipe data from one database (or DBMS) to another.

•	Applications to bundle reports and forms and distribute them to users.

InfoMaker provides built-in connectivity to a broad range of desktop and server-based databases. When you work in InfoMaker, you work in a graphical environment—and working with data in this environment means you don't need to understand SQL. InfoMaker creates all SQL statements behind the scenes as you build your reports and other objects graphically.

  For more information about InfoMaker, choose Start [image] Programs [image] Sybase SQL Anywhere 7 [image] InfoMaker 7.0 [image] Online Help Files. Complete documentation is also available within the application by clicking the Help menu.


Lesson 1: Getting Started
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

 Lesson 1: Getting Started

To use this InfoMaker tutorial, you must be connected to the Adaptive Server Anywhere sample database. To connect to the SQL Anywhere Studio 7.0 sample database you need to create a Database Profile.

♦ To start InfoMaker:

•	From the Start menu, choose Programs [image] Sybase SQL Anywhere 7 [image] InfoMaker 7.0 [image] InfoMaker.

The InfoMaker initial window appears. It includes the PowerBar, which has buttons for accessing the InfoMaker painters and online Help.

♦ To create a Database Profile for the SQL Anywhere Studio sample database with InfoMaker:

1	In InfoMaker, open the Database Profiles dialog by clicking DB Profile in the PowerBar.

2	Select ODBC and click New.

3	The Database Profile Setup dialog appears.

•	Enter the Profile Name Anywhere.

•	Select ASA 7.0 Sample as the Data Source.

•	Enter DBA as the user ID.

•	Enter SQL as the password.

•	Click OK.

The profile Anywhere should now be listed under ODBC.

♦ To connect to the Adaptive Server Anywhere sample database once a Data Source has been created:

1	If it is not already open, open the Database Profiles window by clicking DB Profile in the PowerBar.

2	Select Anywhere from the ODBC group and click Connect.

What next?

Next you will create a table using an InfoMaker wizard.


Lesson 2: Create a basic report
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

 Lesson 2: Create a basic report

This section shows you how to create a basic report, how to preview and save it, and how to change the settings in your design environment.

 Create a report

 Preview your report

 Save the report

 Set up the design environment


Create a report
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Lesson 2: Create a basic report

 Create a report

There are many types of reports you can create. This section shows you how to create a table.

♦ To create the report:

1	Click the New button.

2	In the New dialog, select the Object tab.

3	Double-click the icon labeled Tabular. This selects the tabular presentation style.

The wizard for creating tabular reports appears.

4	Select the Quick Select data source, and ensure that Retrieve on Preview is selected. Click Next.

The Quick Select dialog appears. This allows you to select a database table and some or all of the table's columns.

5	Select the table called contact.

6	Select the following columns: last_name, first_name, title, phone, and fax. (You may need to scroll down.)

InfoMaker moves the columns you selected to the grid at the bottom of the dialog. You can use this grid for reordering the columns, and for providing sort and selection criteria.

7	Click OK.

The Select Color and Border Settings dialog appears.

8	You can use the defaults, so click Next.

A dialog summarizing your specifications appears.

9	Click Finish.


Preview your report
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Lesson 2: Create a basic report

 Preview your report

In this section you will view your report to see what it looks like before printing it.

The main InfoMaker window has toolbars in the top section. Underneath the toolbars is the preview pane, which occupies the rest of the window. At the top of the preview pane is a title bar. At the left of the title bar is the name of the report you are previewing, and at the right are Minimize/Maximize buttons for adjusting the size of the preview pane.

♦ To preview your report:

1	Position your pointer in the upper-right corner of the preview pane and click the Maximize button.

The Preview pane fills the entire window. Notice that it now contains the header information for the report, as well as information about the database.

2	To see how to turn off the rulers, click in the section that contains the data, and choose File [image] Print Preview Rulers.

3	Click the Minimize button in the upper right corner of the preview pane to return the preview pane to its original size and location.


Save the report
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Lesson 2: Create a basic report

 Save the report

♦ To save the report:

1	Choose File [image] Save.

2	In the Reports box, type contacts_by_jobrole. This becomes the name of the report.

3	Click in the Comments box and type This report shows my contacts grouped by job role.

4	Press Enter.


Set up the design environment
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Lesson 2: Create a basic report

 Set up the design environment

In this section you will make modifications to the controls, grid and ruler.

♦ To set up the design environment:

1	From the Design menu, choose Options.

The Report Options property sheet appears.

2	Make sure that the following options are selected: Show Grid, Show Ruler, and Show Edges.

3	Make sure that Snap to Grid is not selected.

4	Click Help to see descriptions of all options.

5	Click OK.

What next?

You are now ready to customize the look of your report.


Lesson 3: Enhance your report
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

 Lesson 3: Enhance your report

In this section you'll learn how to make a number of enhancements to your report, including

•	Sorting the data.

•	Creating and formatting headers and titles.

•	Adding computed fields such as dates, page numbers, and totals.

 Define sorting and grouping

 Enhance the report


Define sorting and grouping
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Lesson 3: Enhance your report

 Define sorting and grouping

In this section you group contacts by title. To do this, you sort the data by title and then specify grouping by title.

♦ To group contacts:

1	Define the sorting. Ascending order is the default.

•	From the Rows menu, choose Sort.

The Specify Sort Columns dialog appears.

•	Drag tile from Source Data box to the Columns box.

•	Drag last_name and first_name into the Columns box.

•	Click OK.

2	Define the grouping based on the title column, so that all contacts with the same title are grouped together:

•	From the Rows menu, choose Create Group.

The Specify Group Columns dialog appears.

•	Drag title to the Columns box and click OK.

The grouping is completed. Expand the Preview pane in the same way you did earlier in this lesson, and you will see that the data is sorted. The grouping does not yet appear.


Enhance the report
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Lesson 3: Enhance your report

 Enhance the report

In this section we'll enhance the report by rearranging controls, adding a title and date, adding page numbers, and adding a total.

♦ To enhance the report:

1	Move the job role to the group header and change its font:

•	Move the pointer to the gray bar called 1: Header Group Title. The pointer changes to a double-arrow.

•	Drag the band down about five grid dots.

•	Drag the title column from the bottom of the preview pane into the bottom of the band for the group header.

•	With the title column still selected, click B and I on the style bar.

Review the report in the Preview view.

2	Fix up the headers:

•	Drag the First Name and Last Name text boxes into the right side of the header band to fill the empty space.

•	Drag the first_name and last_name columns into the right side of the detail band to fill the empty space.

•	From the Edit menu, choose Select [image] Select Text.

This selects all the headers. It may be difficult to see that they are selected, because the band is so narrow.

•	On the stylebar, click the Left Justification button. If your headers are not already bold, click the Bold button.

Review the report in the Preview pane.

3	Add a title to the report:

•	Drag the grey bar marked Header down about 10 grid dots.

•	From the Edit menu, choose Select [image] Select Text. This selects all the headers.

•	Drag all the headers down close to the grey band.

•	From the Insert menu, choose Control [image] Text.

•	Move the point of the pointer above the First Name box and down one grid dot from the top of the page. Click once. This positions the text box for the title.

•	Type My Contacts

•	Select size 14 from the dropdown list in the stylebar.

4	Add a date to the report:

•	From the Insert menu, choose Control [image] Today().

•	Move the point of the pointer to the upper-left corner of the report and click. This places the date into the report.

5	Add page numbers to the report:

•	Drag the grey bar marked Footer down about four grid dots. If necessary, use the scrollbar to make room at the bottom of the Design view. Dragging down the footer bar makes space for the page number in the footer band.

•	From the Insert menu, choose Control [image] Page n of n.

•	Place the pointer about two grid dots below the center of the footer band. Click.

•	Select size 10 in the stylebar.

6	Add a total by counting the number of last names:

•	Drag the grey bar marked Summary down about six grid dots. This makes space for the total.

•	From the Insert menu, choose Control [image] Computed Field.

•	Place the pointer about four grid dots below the center of the summary band. Click.

The Modify Expression dialog appears.

•	In the Functions box, click Count(#x for all).

•	In the Columns box, click last_name.

•	Click Verify.

You receive a message stating that your expression is OK. Click OK.

•	Click OK to complete the definition of the expression for the computed field.

7	Format the total:

•	From the Insert menu, choose Control [image] Text.

•	Position the pointer to the left of the computed field and click.

•	Type Total contacts:

•	Drag the text box to line it up with the computed field.

•	Lasso the text and computed field by circling them with the pointer while pressing the left mouse button, and then releasing the mouse button.

•	On the Stylebar, select the font size 10, and click B and I.

♦ To print, save and close the report:

1	From the File menu, choose Print Report.

2	In the Windows Print dialog, click OK.

3	From the File menu, click Close.

4	If you are prompted to save changes, click Yes.


Summary
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

 Summary

In this tutorial you learned how to

•	Connect to InfoMaker.

•	Create a basic report.

•	Preview your report.

•	Format the report by sorting the data, and adding headers, titles, dates, page numbers and a total.

 Where do I go from here?


Where do I go from here?
  Introducing SQL Anywhere Studio

    PART 2. Getting Results with SQL Anywhere Studio

      CHAPTER 9.  Creating Reports with InfoMaker

        Summary

 Where do I go from here?

In addition to reports, InfoMaker provides functionality for creating graphs, queries, and data entry forms.

  For more information about InfoMaker, choose Start [image] Programs [image] Sybase SQL Anywhere 7 [image] InfoMaker 7.0 [image] Online Help Files. Complete documentation is also available within the application by clicking the Help menu.


PART 3. Appendixes
  Introducing SQL Anywhere Studio

 PART 3. Appendixes

The appendix describes supported operating systems for the components of SQL Anywhere Studio.

 APPENDIX A.  Supported Platforms


APPENDIX A.  Supported Platforms
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

 APPENDIX A.  Supported Platforms

About this Appendix

This appendix summarizes the supported operating systems for each component of SQL Anywhere Studio.

 Introduction

 Adaptive Server Anywhere supported operating systems

 MobiLink supported operating systems

 Replication Agent supported operating systems

 SQL Remote supported operating systems

 UltraLite supported operating systems


Introduction
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

 Introduction

This appendix lists which components of SQL Anywhere Studio run on which operating systems. The tables are organized by component within SQL Anywhere Studio.

 Key to the tables


Key to the tables
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

        Introduction

 Key to the tables

Unless otherwise specified, the tables use the following keys:

•	DOS     MS DOS version 6.02 or above.

•	NetWare    Novell NetWare 3.2 or later.

•	Palm    Palm Computing Platform devices running the Palm OS versions 2.0.x, or 3.0.x on a Dragonball processor.

•	UNIX     UNIX includes Sun Solaris/Sparc, Hewlett Packard HP-UX, IBM AIX, and several Linux distributions. A detailed list of the flavors of UNIX supported, together with version numbers, is given in the SQL Anywhere Studio Read Me First for UNIX.

•	WindRiver VxWorks    A 386, 486 or Pentium PC running VxWorks 5.3 or above with the Intel x86 Board Support Package (BSP) version 1.1 or above, or a PowerPC running VxWorks 5.4.

•	Windows    Windows 95 or Windows 98.

•	CE    Both Windows CE 2.11 and Windows CE 3.0 are supported.

The Windows CE 2.11 operating system is supported on any of the following processors:

•	MIPS processor

•	Hitachi SH3 or SH4 processor.

•	ARM processor.

•	Intel x86 processor, with an SDK from the device manufacturer or Microsoft Platform Builder 2.11 (UltraLite only).

•	Power PC, with an SDK from the device manufacturer or Microsoft Platform Builder 2.11 (UltraLite only).

The Windows CE 3.0 operating system is supported on any of the following processors:

•	MIPS processor

•	Hitachi SH3 processor.

•	ARM processor.

The OLE DB driver on CE is available only for the MIPS, ARM, and SH3 chips.

•	NT    Windows NT 4.0 or later, or Windows 2000. The documentation describes the use of Windows NT. SQL Anywhere Studio components that run on Windows NT also run on Windows 2000.


Adaptive Server Anywhere supported operating systems
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

 Adaptive Server Anywhere supported operating systems

The following table lists the supported operating systems for various components of Adaptive Server Anywhere.

  For an explanation of the column headings, see Key to the tables.

Component	NetWare	CE	Windows	NT	UNIX
Personal database server
Network database server
Command-line utilities
Sybase Central
Interactive SQL
ODBC clients		*			*
Embedded SQL clients
SQL preprocessor
Open Client clients
JDBC clients (jConnect)
Java in the database
Remote data access
External stored procedures
* ODBC driver manager may not be available. May have to link directly to ODBC driver.


MobiLink supported operating systems
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

 MobiLink supported operating systems

The following table lists the supported operating systems for MobiLink components.

  For supported synchronization streams from UltraLite applications, see UltraLite supported operating systems.

  For an explanation of the column headings, see Key to the tables.

Component	CE	Windows	NT	UNIX
ASA MobiLink clients (dbmlsync)
MobiLink synchronization server
TCP/IP synchronization (client)
TCP/IP synchronization (server)
Serial synchronization (client)
Serial synchronization (server)
HTTP synchronization (client)
HTTP synchronization (server)
HotSync synchronization (server)
ScoutSync synchronization (server)
Sybase Central administration
MobiLink extraction utility (mlxtract)
Security (server)
 MobiLink synchronization consolidated databases


MobiLink synchronization consolidated databases
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

        MobiLink supported operating systems

 MobiLink synchronization consolidated databases

The MobiLink synchronization server connects to its consolidated database using ODBC. The following database management systems are supported.

•	Sybase Adaptive Server Anywhere.

•	Sybase Adaptive Server Enterprise.

•	IBM DB2 UDB 5.2

•	Microsoft SQL Server 7

•	Oracle 8. Version 8058 of the Oracle ODBC driver is recommended.


Replication Agent supported operating systems
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

 Replication Agent supported operating systems

The replication agent used in Replication Server installations, also called the Log Transfer Manager, is supported on NetWare, Windows, NT, and UNIX. The Replication Agent requires Open Server libraries.


SQL Remote supported operating systems
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

 SQL Remote supported operating systems

The following table lists the supported operating systems for SQL Remote components and message links.

  For more information, see Supported Platforms and Message Links.

  For an explanation of the column headings, see Key to the tables.

Component	CE	NetWare	Windows	NT	UNIX
ASA Message Agent (dbremote)
ASE Message Agent (ssremote)
ASE stable queue (ssqueue)
ASA extraction utility (dbxtract)
ASE extraction utility (ssxtract)
File message type
FTP message type
MAPI message type
SMTP message type
Administration of Adaptive Server Anywhere consolidated databases is carried out using Sybase Central. For information on availability, see Adaptive Server Anywhere supported operating systems.

Administration of Adaptive Server Enterprise consolidated databases is carried out from Sybase Central Windows Edition. This application is supported on Windows and NT platforms.


UltraLite supported operating systems
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

 UltraLite supported operating systems

UltraLite development takes place on a host platform, and deployment on a target platform.

  For a detailed listing, see Supported platforms.

 Supported host platforms

 Supported target platforms


Supported host platforms
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

        UltraLite supported operating systems

 Supported host platforms

This section lists the platforms on which the UltraLite development tools are supplied. UltraLite development also requires access to an Adaptive Server Anywhere reference database running Java in the database. Access can be over a client/server connection. Development is made easier by Sybase Central, and by a supported development tool or compiler.

  For a list of supported development tools, see Supported platforms for C/C++ applications, and Supported platforms for Java applications.

  For details of Adaptive Server Anywhere and Sybase Central platform availability, see Adaptive Server Anywhere supported operating systems.

  For an explanation of the column headings, see Key to the tables.

Component	Windows	NT	UNIX
SQL preprocessor (Embedded SQL only)
UltraLite generator


Supported target platforms
  Introducing SQL Anywhere Studio

    PART 3. Appendixes

      APPENDIX A.  Supported Platforms

        UltraLite supported operating systems

 Supported target platforms

The following table lists the supported operating systems for UltraLite deployment, including synchronization streams.

Windows and Windows NT are supported for testing purposes only.

For more information

For supported synchronization streams from UltraLite applications, see UltraLite supported operating systems.

For a list of MobiLink synchronization server platforms, see MobiLink supported operating systems.

For an explanation of the column headings, see Key to the tables.

Component	DOS	CE	Java*	Palm	VxWorks	Windows	NT
UltraLite deployment
TCP/IP synchronization
HTTP synchronization
Serial synchronization
HotSync synchronization
ScoutSync synchronization
Security
* JDK 1.1.4 or later required