Relational Interface Support

This section discusses the new and revised functionality to support the relational interface.


New Functionality and Features	Revised Functionality and Features
Metadata Versions	GRANT and REVOKE
CREATE DATABASE Statement and DROP DATABASE Statement	SET PASSWORD
Partial Indexes	String Functions
ORDER BY in VIEW
SET DEFAULTCOLLATE
@@SESSIONID Variable

Metadata Versions

The relational interface in Pervasive PSQL v10 supports two versions of metadata, referred to as version 1 (V1) and version 2 (V2).

Metadata version is a property of the database that you specify when you create a database. V1 metadata is the default. When you create a database, you must specify V2 metadata if you want that version.

Metadata version applies to all data dictionary files (DDFs) within that database. A single database cannot use some DDFs with V1 metadata and others with V2 metadata. DDFs from the two versions cannot interact.

The database engine can, however, concurrently access multiple databases and each database can use either V1 metadata or V2 metadata. See

A conversion utility is available to convert V1 metadata to V2 metadata. See pvmdconv in Pervasive PSQL User's Guide.

Comparison of Metadata Versions

The primary features of V2 metadata include the following:

Identifier names up to 128 bytes long for many identifiers. See Identifier Restrictions by Identifier Type in Advanced Operations Guide.

Permissions on views and stored procedures. See below.

Data dictionary files (DDFs) specific for V2 metadata. The DDFs are named differently than for V1 and in many cases contain additional fields and changes to existing fields. See System Tables in SQL Engine Reference.

Permissions on Views and Stored Procedures

Permissions can now be specified for views and stored procedures. In addition, views and stored procedures can be trusted or non-trusted, depending on how you want to handle the permissions for the objects referenced by the view or stored procedure.

A trusted view or stored procedure is one that can be executed without having to explicitly set permissions for each referenced object. A non-trusted view or stored procedure is one that cannot be executed without having to explicitly set permissions for each referenced object.

See Permissions on Views and Stored Procedures in SQL Engine Reference.

Additional Reading

See the following areas in the documentation for details about metadata versions.

Table 1-4 Metadata Versions References
Area of Discussion	Related Content
Concepts	Versions of Metadata in SQL Engine Reference
Identifiers	Identifier Restrictions by Identifier Type in Advanced Operations Guide
Conversion Utility	pvmdconv in Pervasive PSQL User's Guide
New Database Wizard	New Database GUI Reference in Pervasive PSQL User's Guide
Create Database from ODBC Setup	The online help from the ODBC Setup dialogs when you create a Pervasive PSQL engine or client DSN.
Relational Interface Conditions	Relational Interface Limits in SQL Engine Reference
Object Permissions	Permissions on Views and Stored Procedures in SQL Engine Reference.
System Tables	System Tables System Tables Structure Both references are in SQL Engine Reference
SDK Changes	PvCreateDatabase() PvGetDbFlags() PvModifyDatabase() All references are in Distributed Tuning Interface Guide

CREATE DATABASE Statement

A new statement, CREATE DATABASE, allows you to create a database using SQL syntax. See CREATE DATABASE in SQL Engine Reference.

DROP DATABASE Statement

A new statement, DROP DATABASE, allows you to delete a database using SQL syntax provided the database does not use the "database" security model. See DROP DATABASE in SQL Engine Reference.

Partial Indexes

A new keyword, PARTIAL, allows you to create partial indexes on CHAR or VARCHAR columns larger than 255 bytes, as long as the CHAR or VARCHAR column is the last or only column in the Index.

The CREATE INDEX statement allows you to use PARTIAL to create partial indexes.

See PARTIAL and CREATE INDEX in SQL Engine Reference and Index Attributes in Pervasive PSQL Programmer's Guide.

ORDER BY in VIEW

The CREATE VIEW syntax now supports the ORDER BY clause. See CREATE VIEW in SQL Engine Reference.

SET DEFAULTCOLLATE

A new statement, SET DEFAULTCOLLATE, allows you to specify the collating sequence file to use for all columns of data type CHAR, VARCHAR, or LONGVARCHAR.

See SET DEFAULTCOLLATE in in SQL Engine Reference.

@@SESSIONID Variable

A new global variable, @@SESSIONID, has been added. This variable returns an eight-byte integer value for the Pervasive PSQL connection. The integer is a combination of a time value and an incremental counter. This variable can be used to identify uniquely each Pervasive PSQL connection.

See @@SESSIONID in SQL Engine Reference.

GRANT and REVOKE

The GRANT and REVOKE statements now allow you to grant or revoke permissions on views and stored procedures in addition to tables. The asterisk (*) in their syntaxes now refers to all objects: tables, views and stored procedures.

See GRANT and REVOKE , both in SQL Engine Reference.

SET PASSWORD

The SET PASSWORD statement now permits a normal user (non-Master user) to change his or her logon password to the database. The user must be logged on the database to issue the statement. The changed password takes effect the next time the user logs on the database.

See SET PASSWORD in SQL Engine Reference.

String Functions

The string functions now support multiple-byte character strings. See String Functions in SQL Engine Reference.

Note, however, that the CASE (string) keyword does not support multiple-byte character strings. The CASE keyword instructs the database engine to ignore case when evaluating string columns (case insensitive). The keyword assumes that the string data is single-byte ASCII. See CASE (string) in SQL Engine Reference.