SQL Engine Reference - Table of Contents

Pervasive.SQL 9.1 (code SER_910, revision 1)  Sync manual in Pervasive Library

About This Manual

Who Should Read this Manual

Manual Organization

Conventions

For More Information

SQL Overview

Data Definition Statements

Creating, Modifying, and Deleting Tables

Creating and Deleting Indexes

Creating and Deleting Triggers

Creating and Deleting Stored Procedures

Creating and Deleting User-Defined Functions (UDF)

Data Manipulation Statements

Retrieving Data

Modifying Data

Defining Transactions

Creating and Deleting Views

Executing Stored Procedures

Executing System Stored Procedures

Executing Triggers

Data Control Statements

Enabling and Disabling Security

Creating and Deleting Users and Groups

Granting and Revoking Rights

ODBC Engine Reference

Pervasive ODBC Engine Interface Limits

Data Source Name Connection String Keywords

ODBC Conformance

ODBC Interface Conformance

ODBC API Conformance

ODBC Data Types Not Supported

Exceptions to ODBC API Conformance

ODBC Attribute Conformance

ODBC Descriptor Field Conformance

SQL Grammar Conformance

Delimited Identifiers in SQL Statements

SQL Syntax Reference

Grammar Statements

ADD

ALL

ALTER (rename)

ALTER TABLE

ANY

AS

BEGIN [ATOMIC]

CALL

CASCADE

CASE (expression)

CASE (string)

CLOSE

COALESCE

COMMIT

CREATE FUNCTION

CREATE GROUP

CREATE INDEX

CREATE PROCEDURE

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DECLARE

DECLARE CURSOR

DEFAULT

DELETE (positioned)

DELETE

DISTINCT

DROP FUNCTION

DROP GROUP

DROP INDEX

DROP PROCEDURE

DROP TABLE

DROP TRIGGER

DROP VIEW

END

EXECUTE

EXISTS

FETCH

FOREIGN KEY

GRANT

GROUP BY

HAVING

IF

IN

INSERT

JOIN

LEAVE

LIKE

LOOP

NOT

OPEN

PRIMARY KEY

PUBLIC

PRINT

RELEASE SAVEPOINT

RESTRICT

REVOKE

ROLLBACK

SAVEPOINT

SELECT (with into)

SELECT

SET ANSI_PADDING

SET DECIMALSEPARATORCOMMA

SET OWNER

SET PASSWORD

SET ROWCOUNT

SET SECURITY

SET TIME ZONE

SET TRUENULLCREATE

SET VARIABLE

SIGNAL

SQLSTATE

START TRANSACTION

UNION

UNIQUE

UPDATE

UPDATE (positioned)

USER

WHILE

Grammar Element Definitions

Global Variables

Other Characteristics

Temporary Files

Working with Literal Values

Working with NULL Values

Working with Binary Data

Creating Indexes

Closing an Open Table

Concurrency

Comma as Decimal Separator

OEM to ANSI Support

Bitwise Operators

Overview

Bitwise AND (&)

Syntax

Values Returned

Examples

Bitwise NOT (~)

Syntax

Values Returned

Examples

Bitwise OR (|)

Syntax

Values Returned

Examples

Bitwise Exclusive OR (^)

Syntax

Values Returned

Examples

Truth Table

Scalar Functions

String Functions

Examples

Numeric Functions

Examples

Time and Date Functions

Inserting Time and Date Function Values

Examples

System Functions

Examples

Logical Functions

Examples

Conversion Functions

Examples

System Stored Procedures

Pervasive.SQL System Stored Procedures

psp_columns

psp_column_attributes

psp_column_rights

psp_fkeys

psp_groups

psp_help_sp

psp_help_trigger

psp_help_udf

psp_help_view

psp_indexes

psp_pkeys

psp_rename

psp_stored_procedures

psp_tables

psp_table_rights

psp_triggers

psp_udfs

psp_users

psp_views

Performance Reference

Restriction Analysis

Modified CNF Conversion

Restrictions that Cannot be Converted

Conditions Under Which Conversion is Avoided

Restriction Optimization

Single Predicate Optimization

Closed Range Optimization

Modified Disjunct Optimization

Conjunct Optimization

Disjunctive Normal Form Optimization

Modified Conjunctive Normal Form Optimization

Closing Open Ended Ranges through Modified CNF Optimization

Single Join Condition Optimization

Conjunct with Join Conditions Optimization

Modified Conjunctive Normal Form with Join Conditions Optimization

Closing Join Condition Open Ended Ranges through Modified CNF Optimization

Multi-Index Modified Disjunct Optimization

Push-Down Filters

Efficient Use of Indexes

DISTINCT in Aggregate Functions

DISTINCT Preceding Selection-list

Relaxed Index Segment Order Sensitivity

Relaxed Segment Ascending Attribute Sensitivity

Search Update Optimization

Temporary Table Performance

Row Pre-fetch

Terminology

Data Types

Pervasive.SQL Supported Data Types

Data Type Ranges

Operator Precedence

Data Type Precedence

Precision and Scale of Decimal Data Types

Notes on Data Types

CHAR, VARCHAR, and LONGVARCHAR

BINARY and LONGVARBINARY

Limitations on LONGVARCHAR and LONGVARBINARY

Comparison of Floats

Representation of Infinity

Legacy Data Types

Btrieve Key Data Types

SQL Reserved Words

Reserved Words

Words to Avoid

System Tables

Overview

System Tables Structure

X$File

X$Field

X$Index

X$Attrib

X$View

X$Proc

X$User

X$Rights

X$Relate

X$Trigger

X$Depend

SQL Access for COBOL Applications

Overview

Restrictions

SQL Statements

Components

Using SQL Access

Step 1: Modify the Sample XML Templates

Step 2: Copy the Data File Specified in the XML Template

Step 3: Run the Schema Executor Utility

Step 4: Optionally, Deploy the System Tables

Example of How to Execute a Sample XML File

Additional Notes