Programmer's Guide - Table of Contents

Pervasive PSQL SDK v9 SP2 (9.5) (code GDE_950, revision 1)  Books in this release

About This Manual

Where to Get Information

Who Should Read This Manual

Manual Organization

Conventions

Overview of Database Access Methods

Overview of Pervasive Access Methods

Overview of Pervasive Access Methods

SQL Access in Pervasive PSQL

Oracle-Pervasive Interoperability

Developer Quick Start

Choosing a Development Tool

Database Connection Quick Reference

ADO.NET Connections

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 Applications for the Transactional Interface

Transactional Interface Environment

Documentation

Configuration Issues

Transactional Interface Fundamentals

Overview of the Transactional Interface 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

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 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

SQL Security

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 PSQL Applications

Distribution Rules for Pervasive PSQL Client/Server and Workstation Engines

Shipping Requirements for Glue DLLs

Registering Pervasive PSQL ActiveX Files

Installing Pervasive PSQL 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 PSQL Application

Adding Pervasive PSQL 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 PSQL Databases

Transaction Control in Procedures

ODBC Programming

Where to Find ODBC Information

Other SQL Access Methods

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