Programmer's Guide - Table of Contents

Pervasive.SQL 8.7 (code GDE_870, revision 1)  Sync manual in Pervasive Library

About This Manual

Where to Get Information

Who Should Read This Manual

Manual Organization

Part 1: Database Access Methods

Part 2: Transactional Programming with Btrieve

Part 3: Relational Programming

Part 4: ADO/OLE DB Programming

Part 5: Java Programming

Part 6: ActiveX and I*net Data Server

Part 6: Appendixes

Conventions

Overview of Database Access Methods

Overview of Pervasive Access Methods

Overview of Pervasive Access Methods

SQL Access in Pervasive.SQL

Oracle-Pervasive Interoperability

Developer Quick Start

Choosing a Development Tool

Database Connection Quick Reference

ADO/OLE DB Connections

JDBC Connections

Java Class Library

DSN-Less Connections

Where to Go from Here

Conceptual Information

Reference Information

Online Developer Resources

Sample Code

Transactional Database Design and Concepts

Developing Btrieve Applications

Btrieve Environment

Documentation

Configuration Issues

Btrieve Fundamentals

Overview of Btrieve Features

Pages

Page Types

Page Size

File Types

Standard Data Files

Data-Only Files

Key-Only Files

Large Files

Long File Names

Data Types

Key Attributes

Key Attributes Description

Key Specification

Database URIs

Syntax

Special Characters

Remarks

Examples

Double-Byte Character Support

Record Length

File Sharing

Workgroup vs. Server Engines

Local vs. Remote Clients

Data Integrity

Record Locks

Transactions

Transaction Durability

System Data

Shadow Paging

Backing Up Your Files

Event Logging

Performance Enhancement

System Transactions

Memory Management

Page Preallocation

Extended Operations

Disk Usage

Free Space List

Index Balancing

Data Compression

Blank Truncation

Designing the Database

Understanding MicroKernel Data Files

Creating a Data File

Data Layout

Creating File and Key Specification Structures

Calling the Create Operation

Create Index Operation

Calculating the Logical Record Length

Choosing a Page Size

Estimating File Size

Optimizing Your Database

Duplicatable Keys

Page Preallocation

Blank Truncation

Data Compression

Index Balancing

Variable-tail Allocation Tables

Key-Only Files

Setting Up Security

Owner Names

Exclusive Mode

Language Interfaces Modules

Interface Modules Overview

Compiling, Linking and Running Samples

C/C++

Interface Modules

Programming Requirements

COBOL

Delphi

Pascal

Visual Basic

Interface Libraries

Overview of Interface Libraries

Distributing Pervasive.SQL Applications

Distribution Rules for Pervasive.SQL Client/Server and Workstation Engines

Shipping Requirements for Glue DLLs

Registering Pervasive.SQL ActiveX Files

Installing Pervasive.SQL with your Application

Working with Records

Sequence of Btrieve Operations

Accessing Records

Accessing Records by Physical Location

Accessing Records by Key Value

Reading Variable-Length Records

Accessing Records by Chunks

Inserting and Updating Records

Ensuring Reliability in Mission-Critical Inserts and Updates

Inserting Non-Duplicatable Keys

Inserting and Updating Variable-Length Records

Reading and Updating Fixed-length Portions

Updating Non-Modifiable Keys

No-Currency-Change (NCC) Operations

Multi-Record Operations

Terminology

Background

Validation

Optimization

Performance Tips

Adding and Dropping Keys

Supporting Multiple Clients

Btrieve Clients

Passive Concurrency

Record Locking

User Transactions

Locks

Record Locks in Concurrent Transactions

Implicit Locks

File Locks

Examples of Multiple Concurrency Control Tools

Example 1

Example 2

Concurrency Control for Multiple Position Blocks

Multiple Position Blocks

ClientID Parameter

Debugging Your Btrieve Application

Trace Files

Indirect Chunk Operations in Client/Server Environments

Engine Shutdowns and Connection Resets

Reducing Wasted Space in Files

Btrieve API Programming

Basic Btrieve API Programming

Btrieve API Flow Chart

Special Notes on Visual Basic

Special Notes on Delphi

Starting a Pervasive.SQL Application

Adding Pervasive.SQL Source Modules

Btrieve API

Creating a File

Inserting Records

Updating Records

Performing Step Operations

Performing Get Operations

Chunking/BLOBs/Variable-Length Records

Working with Segmented Indexes

Declarations of Btrieve API Functions for Visual Basic

Relational Database Design and Concepts

Creating a Database

Named Databases

Bound Databases

Creating Database Components

Naming Conventions

Unique Names

Valid Characters

Maximum Name Lengths

Case Sensitivity

Creating a Data Dictionary

Creating Tables

Aliases

Creating Columns

Creating Indexes

Index Segments

Index Attributes

Relational Database Design

Overview of Database Design

Stages of Design

Conceptual Design

Logical Design

Physical Design

Inserting and Deleting Data

Overview of Inserting and Deleting Data

Inserting Values

Transaction Processing

Deleting Data

Dropping Indexes

Dropping Columns

Dropping Tables

Dropping an Entire Database

Modifying Data

Overview of Modifying Data

Modifying Tables

Setting Default Values

Using UPDATE

Retrieving Data

Overview of Retrieving Data

Views

Features of Views

Temporary and Stored Views

Read-Only Tables in Views

Mergeable Views

Selection Lists

Sorted and Grouped Rows

Joins

Joining Tables with Other Tables

Joining Views with Tables

Types of Joins

Subqueries

Subquery Limitations

Correlated Subqueries

Restriction Clauses

Restriction Clause Operators

Restriction Clause Examples

Functions

Aggregate Functions

Arguments to Aggregate Functions

Aggregate Function Rules

Scalar Functions

Storing Logic

Stored Procedures

Stored Procedure and Positioned Update

Declaring Stored Procedures

Invoking Stored Procedures

Deleting Stored Procedures

SQL Variable Statements

Procedure-Owned Variables

Assignment Statement

SQL Control Statements

Compound Statement

IF Statement

LEAVE Statement

LOOP Statement

WHILE Statement

SQL Triggers

Timing and Ordering of Triggers

Defining the Trigger Action

Managing Data

Overview of Managing Data

Defining Relationships Among Tables

Referential Integrity Definitions

Keys

Primary Keys

Foreign Keys

Referential Constraints

Referential Integrity Rules

RI in the University Database

Creating the Course Table

Adding a Primary Key to Course

Creating the Student Table with Referential Constraints

Administering Database Security

Understanding Database Rights

Establishing Database Security

Enabling Security

Creating User Groups and Users

Granting Rights

Dropping Users and User Groups

Revoking Rights

Disabling Security

Retrieving Information about Database Security

Concurrency Controls

Transaction Processing

Starting and Ending Transactions

Using Savepoints to Nest Transactions

Special Considerations

Isolation Levels

Explicit Locks

Passive Control

Atomicity in Pervasive.SQL Databases

Transaction Control in Procedures

ODBC Programming

Where to Find ODBC Information

Other SQL Access Methods

ADO/OLE DB Programming

Pervasive.SQL OLE DB Provider Introduction

Overview of Pervasive ADO and OLE DB Support

Overview of New Features in the OLE DB Provider

Increased Relational Performance

Remote Connections

Pessimistic Cursors

Support for ADO Refresh Method

Pervasive OLE DB Architecture

Review of Previous OLE DB Providers

Pervasive.SQL V8 Support for Visual Studio.NET

Wizards in Visual Studio.NET

Security with ASP.NET

Supported Objects

Performance Considerations with OLE DB

Best Performance is Navigational

Static vs. Dynamic Cursors

Disable Unused Services

Limitations of the OLE DB Provider

Where to Get More Information on ADO and OLE DB

Programming Concepts

OLE DB Reference Information

Programming with the Pervasive OLE DB Provider

Connecting to a Database Using the OLE DB Provider

Connecting to OLE DB Provider

Other Tasks

Remote Connections

New Syntax Supported

Backward Compatibility

Pessimistic Cursors

OLE DB Specification and Pessimistic Cursors

Syntax for Pessimistic Cursors

Summary of Pessimistic Cursor Behavior

Sample Visual Basic Code with Pessimistic Cursor

Programming Notes for Pervasive OLE DB Provider

Visibility of Updates

Using the OLE DB Provider with a UNC Path or Mapped Drive

Support for ADO Refresh Method Examples

Programming Notes for Pervasive OLE DB Provider

Seek with Static Cursors

Remote Connections

Table Definitions

Default LockType

Initialization Properties

COM+ Services Support

What is COM+ Services?

Example of COM+ Services for Visual Basic Programmers

Execute Method (ADO Command)

SELECT operations

Batch Insert, Update, or Delete

Java Programming

Introduction to the Pervasive JDBC Driver

Overview of JDBC Support

JDBC Requirements

JDBC Features

Overview of Pervasive JDBC 2 Driver

Specifications

Upgrading from the Pervasive JDBC 1 Driver

JDBC API Improvements

JDBC Optional Package Support

Backward Compatibility

Class Names

Pervasive JDBC Driver Limitations

Unsupported APIs

Driver Limitations

Programming with the Pervasive JDBC 2 Driver

JDBC Programming Tasks

Connection String Overview

Connection String Elements

JDBC Connection String Example

Using Character Encoding

Notes on Character Encoding

Developing Web-based applications

Applets

Servlets and Java Server Pages

JDBC 2.0 Standard Extension API

DataSource

Connection and Concurrency Notes

Scrollable Result Set Notes

JDBC Programming Sample

JDBC API Reference

Introduction to the Pervasive Java Interface

The Pervasive Java Interface

History

Database Concepts

How to Set Up your Environment

Supported JDKs

CLASSPATH Environment Variable

Registering the Java Class Library

Running the Video Store Java Sample Application

Viewing the Source of the Java Video Store Application

Developer Resources at Pervasive

Web Site Resources

Developer's Resource Center

Where Do I Go From Here?

Programming with the Java Class Library

Pervasive Java Interface Class Structure

General Classes

Rowset Family Classes

Cursor Family Classes

Overview of Major Classes and Methods

Sequence of Steps for a Java Application

First Steps with the Java Class Library

Verify your Environment is Correct

Create your Database and Tables

Connect to a Pervasive Database

Obtain the Tables and Create Rowsets

Navigate the Rows in a Row Set

Restrict or Filter the Data

Insert, Update, or Delete Rows

Combine Operations into a Transaction

Store and Retrieve Binary Large Objects

Java Data Type Issues

Sample Database Files

Additional Java Samples

ActiveX and I*net Data Server

Introduction to ActiveX

Overview of the ActiveX Interface

Development Environments

Third-Party Data Bound Controls

ActiveX Control Objects

Choosing the ActiveX Interface

When should I consider using the ActiveX Interface?

Key Features

Data Source for Data-aware Controls in VB

Field-level Access

Property and Method Interface

Extended Operations

DDFs

ActiveX DDF Access Methods

Remote Data Access via I*net Data Server

Joined Tables

Pervasive.SQL Features Unavailable in the ActiveX Interface

SQL Syntax

Chunk Operations, Huge Records

Creating Files with Multiple ACS

Data Types

First Steps with ActiveX Controls

Creating a Database with SQL Data Manager

Importing ActiveX Interface into Visual Basic and Delphi

ActiveX Quick Start

Files and Directories

Creating an Application with the ActiveX Interface

Adding the ActiveX Interface to a Project

Using a VAccess Control

Accessing Properties

Setting Properties

Connecting to a Pervasive.SQL File

Selecting an Index

Setting Automatic vs. Manual File Opening

Linking Bound Controls to a Data Source

Retrieving Records

Searching for a Specific Record

Adding, Updating, and Deleting Records

Locking Records

Joining Files

Enabling Extended Operations

Distributing and Registering Pervasive.SQL ActiveX Files

Additional Information on the ActiveX Interface

Using I*net Data Server with ActiveX

HostAddress Property

URL Convention

UNC Notation

Mapped Drives

IDSHOSTS File

Using Data Dictionary Methods with ActiveX

DDF Handling with the ActiveX Data Source Control

File Contents vs. Memory Image

Reading a Dictionary

Modifying the DDF

Creating a Table Definition

Creating a Dictionary

Modifying the Data File

Other DDF Manipulations

Securing DDFs with an Owner Name

Deleting Existing Field and Index Information

Forcing Comments with Persisted DDF Memory Image

Methods with Wide-Ranging Effects

Connecting to the I*net Data Server

Overview of IDS

Connecting to IDS

Using the IDSHOSTS File

Using the PIDS Protocol Designation

Using the HostAddress Property

Appendixes

Sample Collations Using International Sorting Rules

German

Unsorted Data

Sorted Data

Spanish

Unsorted Data

Sorted Data

French

Unsorted Data

Sorted Data

University Database Tables and Referential Integrity

Overview of University Database

Structure of the University Database

Assumptions

Entity Relationships

Referential Integrity in the University Database

Table Design

BILLING Table

CLASS Table

COURSE Table

DEPARTMENT Table

ENROLLS Table

FACULTY Table

PERSON Table

ROOM Table

STUDENT Table

TUITION Table