Pervasive.SQL User's Guide - Novell

Pervasive.SQL 2000i

Pervasive.SQL User¡¯s Guide

Guide to Using Pervasive.SQL

Pervasive Software, Inc.

12365 Riata Trace Parkway

Building II

Austin, TX 78727 USA

Telephone: +1 512 231 6000 or 800 287 4383

Fax: +1 512 231 6010

E-Mail: info@

Web:

disclaimer

PERVASIVE SOFTWARE INC. LICENSES THE SOFTWARE AND

DOCUMENTATION PRODUCT TO YOU OR YOUR COMPANY SOLELY ON AN ¡°AS

IS¡± BASIS AND SOLELY IN ACCORDANCE WITH THE TERMS AND CONDITIONS

OF THE ACCOMPANYING LICENSE AGREEMENT. PERVASIVE SOFTWARE INC.

MAKES NO OTHER WARRANTIES WHATSOEVER, EITHER EXPRESS OR IMPLIED,

REGARDING THE SOFTWARE OR THE CONTENT OF THE DOCUMENTATION;

PERVASIVE SOFTWARE INC. HEREBY EXPRESSLY STATES AND YOU OR YOUR

COMPANY ACKNOWLEDGES THAT PERVASIVE SOFTWARE INC. DOES NOT

MAKE ANY WARRANTIES, INCLUDING, FOR EXAMPLE, WITH RESPECT TO

MERCHANTABILITY, TITLE, OR FITNESS FOR ANY PARTICULAR PURPOSE OR

ARISING FROM COURSE OF DEALING OR USAGE OF TRADE, AMONG OTHERS.

trademarks

Btrieve, Tango, Client/Server in a Box, and the Pervasive Software logo are registered

trademarks of Pervasive Software Inc.

Built on Pervasive, Built on Pervasive Software, Extranet in a Box, Pervasive.SQL, Jtrieve, Plug n¡¯ Play

Databases, SmartScout, Solution Network, Ultra-light Z-DBA, Z-DBA, ZDBA, UltraLight,

MicroKernel Database Engine, and MicroKernel Database Architecture are trademarks of Pervasive

Software Inc.

Microsoft, MS-DOS, Windows, Windows NT, Win32, Win32s, and Visual Basic are registered

trademarks of Microsoft Corporation.

Windows 95 is a trademark of Microsoft Corporation.

NetWare and Novell are registered trademarks of Novell, Inc.

NetWare Loadable Module, NLM, Novell DOS, Transaction Tracking System, and TTS are

trademarks of Novell, Inc.

All other company and product names are the trademarks or registered trademarks of their

respective companies.

? Copyright 2001 Pervasive Software Inc. All rights reserved. Reproduction,

photocopying, or transmittal of this publication, or portions of this publication, is

prohibited without the express prior written consent of the publisher.

This product includes software developed by Powerdog Industries.

? Copyright 1994 Powerdog Industries. All rights reserved.

The ODBC Driver Manager for NetWare (ODBC.NLM) included in this product is

based on the GNU iODBC software ? Copyright 1995 by Ke Jin

and was modified by Simba Technologies Inc. in June 1999.

This library is free software; you can redistribute it and/or modify it under the terms

of the GNU Lesser General Public License as published by the Free Software

Foundation; either version 2 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY

WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS

FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for

more details.

A copy of the GNU Lesser General Public License is included in your installation of

Pervasive.SQL 2000i at \pvsw\doc\lesser.htm. If you cannot find this license, write to

the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 021111307 USA. You may contact Pervasive Software Inc. using the contact information on

the back cover of this manual.

Pervasive.SQL User¡¯s Guide

March 2001

100-003672-004

Contents

Contents

About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . .

ix

Who Should Read This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Manual Organization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1

Introducing Pervasive.SQL . . . . . . . . . . . . . . . . . . . . . .

x

xi

xii

1-1

Understanding Pervasive.SQL and its Capabilities

Understanding Pervasive.SQL . . . . . . . . . .

What is a Database? . . . . . . . . . . . .

What is a Database Management System?

Components of Pervasive.SQL . . . . . .

Key Concepts . . . . . . . . . . . . . . . . . . .

Basic Database Structures and Terms . .

Unique Benefits of Pervasive.SQL . . . .

Why Pervasive.SQL? . . . . . . . . . . . . . . .

Understanding the Product Family . . . . . . .

Workstation . . . . . . . . . . . . . . . .

Workgroup . . . . . . . . . . . . . . . . .

Server . . . . . . . . . . . . . . . . . . . .

Replication . . . . . . . . . . . . . . . . .

Crystal Reports for Pervasive.SQL . . . .

Helpful Utilities . . . . . . . . . . . . . . . . . .

Using Pervasive.SQL Documentation . . . . . .

Getting Started Guides. . . . . . . . . . .

User¡¯s Guide . . . . . . . . . . . . . . . .

Advanced Operations Guide . . . . . . .

SQL Engine Reference . . . . . . . . . . .

Status Codes and Messages . . . . . . . .

Pervasive Products and Services . . . . .

Online Help. . . . . . . . . . . . . . . . .

File System Security . . . . . . . . . . . . . . . .

2

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

Using Pervasive.SQL . . . . . . . . . . . . . . . . . . . . . . . . .

1-2

1-2

1-3

1-3

1-5

1-5

1-8

1-10

1-12

1-12

1-13

1-13

1-14

1-15

1-16

1-18

1-18

1-19

1-19

1-19

1-19

1-20

1-20

1-21

2-1

A Walk-through of Basic User Tasks

Starting and Stopping the Database Engine. . . . . . . . .

Starting and Stopping Services on Windows Server .

Starting and Stopping Services on NetWare . . . . .

Starting and Stopping Services on Unix . . . . . . .

Granting Administrative Rights for the Database Engine .

Tasks Requiring Administrative Rights. . . . . . . .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

2-2

2-3

2-4

2-5

2-6

2-6

iii

Contents

How Administrative Rights are Granted . . . . . . . . . . . . . . . . . .

Rights Provided to Normal Users . . . . . . . . . . . . . . . . . . . . . .

Granting Administrative Rights on Windows Server. . . . . . . . . . . .

Granting Administrative Rights on NetWare 3.2 . . . . . . . . . . . . . .

Granting Administrator Rights on NetWare 4.2 or 5.0 . . . . . . . . . .

Granting Administrator Rights on Unix . . . . . . . . . . . . . . . . . .

Logging in as Administrator . . . . . . . . . . . . . . . . . . . . . . . . .

Setting Up ODBC Database Access . . . . . . . . . . . . . . . . . . . . . . . . .

Basic Concepts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

What to Know if You are Recreating DSNs . . . . . . . . . . . . . . . . .

Prerequisites to Setting up Database Access. . . . . . . . . . . . . . . . .

Setting Up Database Access on a Windows Server or Workgroup/Workstation

Setting Up Database Access on a NetWare Server . . . . . . . . . . . . . . . . .

Setting Up Database Access on Unix . . . . . . . . . . . . . . . . . . . . . . . .

Setting Up Client Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Accessing Data via ODBC From Other Applications . . . . . . . . . . . . . . .

Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Accessing Data Using Microsoft Excel. . . . . . . . . . . . . . . . . . . .

Accessing Data Using Microsoft Access . . . . . . . . . . . . . . . . . . .

Deleting DSNs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Bound Databases and Enforced Integrity . . . . . . . . . . . . . . . . . . . . .

3

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

2-6

2-6

2-9

2-11

2-11

2-12

2-13

2-14

2-14

2-17

2-17

2-19

2-28

2-36

2-39

2-46

2-46

2-46

2-49

2-53

2-56

Using the Pervasive Control Center . . . . . . . . . . . . . . . . .

3-1

A Brief Tour of Pervasive Control Center

An Overview of Pervasive Control Center . . . . . . . .

Registering or Removing a Server. . . . . . . . . . . . .

Viewing Database Engines. . . . . . . . . . . . . . . . .

Interpreting Server Status Icons . . . . . . . . . .

Pervasive Control Center Wizards . . . . . . . . . . . .

Adding or Creating a Database . . . . . . . . . . . . . .

Deleting a Database . . . . . . . . . . . . . . . . . . . .

Adding a Table . . . . . . . . . . . . . . . . . . . . . . .

Modifying a Table Definition . . . . . . . . . . . . . . .

Dropping a Table. . . . . . . . . . . . . . . . . . . . . .

Setting Database Security . . . . . . . . . . . . . . . . .

Turning Security On and Off. . . . . . . . . . . .

Working with Groups and Users. . . . . . . . . .

Stopping and Restarting Services on Windows Servers .

Viewing and Modifying Table Properties . . . . . . . .

Viewing and Modifying Data . . . . . . . . . . . . . . .

Viewing Data . . . . . . . . . . . . . . . . . . . .

Writing and Executing SQL Statements . . . . . .

Using the SQL Statement Builder . . . . . . . . .

Exporting/Importing Data . . . . . . . . . . . . . . . .

Checking Consistency and Referential Integrity . . . . .

iv

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

3-2

3-4

3-7

3-7

3-9

3-11

3-15

3-18

3-25

3-29

3-31

3-31

3-32

3-38

3-39

3-42

3-42

3-42

3-45

3-46

3-54

Contents

Listing Referential Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Checking Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Checking Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4

Unix Supplementary Documentation . . . . . . . . . . . . . . . . .

3-54

3-55

3-57

4-1

Additional Information on Unix Utilities

User Manual Exclusions for Unix

Utilities . . . . . . . . . . .

Changes and Exclusions . .

Man Pages . . . . . . . . . . . . .

Available Utilities . . . . . . . . .

5

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

Basic Troubleshooting . . . . . . . . . . . . . . . . . . . . . . . . .

4-2

4-2

4-2

4-4

4-5

5-1

How to Identify and Solve Common Problems

General Troubleshooting . . . . . .

Error Messages from PCC . . . . .

Frequently Asked Questions . . . .

Installation . . . . . . . . . .

Security . . . . . . . . . . . .

Documentation. . . . . . . .

User Counts . . . . . . . . .

Networking . . . . . . . . . .

Difficulty Accessing Data . .

ODBC and DDFs. . . . . . .

Upgrading from Btrieve 6.15

Upgrading and Migration . .

Miscellaneous. . . . . . . . .

6

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

Pervasive.SQL Resources and Contacts . . . . . . . . . . . . . . .

5-2

5-8

5-12

5-15

5-17

5-17

5-18

5-19

5-19

5-21

5-27

5-28

5-29

6-1

A Guide to Pervasive.SQL Customer Information Resources

Printed Documentation . . . .

Developer Zone . . . . . . . . .

Pervasive.SQL Knowledge Base

FTP Site . . . . . . . . . . . . .

Online Documentation . . . . .

DevWire . . . . . . . . . . . . .

DevTalk . . . . . . . . . . . . .

Newsgroup . . . . . . . . . . .

E-Mail . . . . . . . . . . . . . .

Technical Support. . . . . . . .

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

6-2

6-3

6-4

6-5

6-6

6-7

6-8

6-9

6-10

6-11

v

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download