SQL Engine Reference - Table of Contents

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

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 Views

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 Relational Interface Limits

Fully Qualified Object Names

Data Source Name Connection String Keywords

ODBC Support

ODBC Interface Support

ODBC API Support

Exceptions to ODBC API Support

ODBC Attribute Support

SQL Grammar Support

Delimited Identifiers in SQL Statements

SQL Syntax Reference

Grammar Statements

ADD

ALL

ALTER (rename)

ALTER GROUP

ALTER TABLE

ALTER USER

ANY

AS

BEGIN [ATOMIC]

CALL

CASCADE

CASE (expression)

CASE (string)

CLOSE

COALESCE

COMMIT

CREATE FUNCTION

CREATE GROUP

CREATE INDEX

CREATE PROCEDURE

CREATE TABLE

CREATE (temporary) TABLE

CREATE TRIGGER

CREATE USER

CREATE VIEW

DECLARE

DECLARE CURSOR

DEFAULT

DELETE (positioned)

DELETE

DISTINCT

DROP FUNCTION

DROP GROUP

DROP INDEX

DROP PROCEDURE

DROP TABLE

DROP TRIGGER

DROP USER

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 CACHED_PROCEDURES

SET DECIMALSEPARATORCOMMA

SET OWNER

SET PASSWORD

SET PROCEDURES_CACHE

SET ROWCOUNT

SET SECURITY

SET TIME ZONE

SET TRUEBITCREATE

SET TRUENULLCREATE

SET

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 PSQL 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 PSQL Supported Data Types

Data Type Ranges

Operator Precedence

Data Type Precedence

Precision and Scale of Decimal Data Types

Truncation

Notes on Data Types

CHAR, VARCHAR, and LONGVARCHAR

BINARY and LONGVARBINARY

Limitations on LONGVARCHAR and LONGVARBINARY

Comparison of Floats

DATETIME

UNIQUEIDENTIFIER

Representation of Infinity

Legacy Data Types

Btrieve Key Data Types

SQL Reserved Words

Reserved Words

Words to Avoid

System Tables

Overview

System Tables Structure

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