---

PrimeBase SQL Database Server Reference Manual

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.

 

The information contained in this document applies to:

• PrimeBase SQL Database Server 4.0, build #4009 and higher

Last major update to this document on March 8th, 2001.

---

Table of Contents

1. Data Types

Integer types
Decimal types
Floating-point types
Boolean types
Time types
Character types
Binary types
Special types

2. DDL Reference

ADD USER
ALTER TABLE
ALTER USER
BACKUP DATABASE
BACKUP TABLE
CLOSE DATABASE
CLOSE DBMS
CLOSE TABLE
COMMENT ON
CREATE DATABASE
CREATE DEFAULT
CREATE DOMAIN
CREATE GROUP
CREATE INDEX
CREATE KEY
CREATE RULE
CREATE TABLE
CREATE VARIABLE
CREATE VIEW
DESCRIBE COLUMNS
DESCRIBE DATABASES
DESCRIBE DBMS
DESCRIBE LINKSETS
DESCRIBE OPEN DATABASES
DESCRIBE OPEN DBMS
DESCRIBE TABLES
DROP GROUP
DROP <object>
GRANT
MOUNT DATABASE
OPEN DATABASE
OPEN DBMS
OPEN TABLE
REMOVE USER
RENAME <object>
REORG TABLE
RESTORE DATABASE
REVOKE
SERVER CHECKPOINT
SERVER COMMENT
SERVER ERROR
SERVER RESTART
SERVER RESTORE
SERVER SHUTDOWN
SET VARIABLE
TRANS ERROR
TRANS RESTART
TRANS SHUTDOWN
UNMOUNT DATABASE
USE DATABASE
USE DBMS

3. Identification

Identifiers

Aliases

Database Alias
Table Alias
Column Alias

References

Object Reference
Column Reference
Column of Table Reference

5. System Procedures

Syntax

Devices

Add Device
Alter Device
Remove Device

5.3 Locations

Add Location
Alter Location
Remove Location

5.4 Partitions

Add Partition
Alter Partition
Remove Partition

6. System parameters

TransactionLimit
SystemFileLimit
LogBufferSize
LogThreshold
CheckpointThreshold
CacheSize
VirtualCacheSize
OfflineFunction
DataServerName
ConnectionLimit
ConnectionTotal
SerialNumber
ActivationKey
ExpiryDate
IdentificationString
InitialMemoryBlockSize
MemoryBlockSize
MemoryBlockTotal

Appendix A: System Database

A.1 Model Database

Domains
Tables

Appendix B: Error Codes

B.1 Data Definition Errors

Database related errors
Database alias related errors
Database objects
Database users and groups

B.2 Data Manipulation Errors

B.3 Privilege Violations

Primary errors
Secondary errors

B.4 Calculation and Conversion Errors

Invalid literal (string) values in conversion
String to floating point conversion errors
Invalid conversions
Error in calculations

B.5 Trapable Programmer Errors

Symbol related errors
Cursor related errors
Connection related errors

Appendix C: Golfers Database

Appendix D: Accessing PrimeBase SQL Database Server through Firewalls

---

1. Data Types

---

Integer types

TINYINT an unsigned 8-bit integer.

SMINT, SMALLINT a signed 16-bit integer.

INT, INTEGER a signed 32-bit integer.

<integer_literal> ::= [ '-' | '+' ] <digit> {<digit> }

<digit>           ::= '0' | '1' | '2' | '3' | '4' | '5' | '6' |
                        '7' | '8' | '9'

---

Decimal types

DECIMAL, NUMERIC a signed decimal number that has a total number of decimal digits and a scale, which is the total number of digits to the right of the decimal point.

MONEY a special type of decimal value that can be converted to and from character strings in the form of currency values, for example, $12.34, 1.234,000 DM.

<decimal_literal> ::= [ '-' | '+' ] <digit> { <digit> } '.'
                        <digit> { <digit> }

<money_literal>   ::= '$' <decimal_literal>

---

Floating-point types

SMFLOAT, SMALLFLOAT a 4-byte floating point value.

REAL a 4-byte floating point value.

FLOAT an 8-byte floating point value.

REAL10 a 10-byte floating point value. Supported on 68K MacOS only, PrimeBase-specific extension of the DAL standard.

REAL12 a 12-byte floating point value. Supported on 68K MacOS only, PrimeBase-specific extension of the DAL standard.

<float_literal> ::= [ '-' | '+' ] <digit> { <digit> }
                      [ '.' <digit> { <digit> } ] ( E | 'e' )
                      [ '-' | '+' ] { '0'..'9' }

---

Boolean types

BOOLEAN a truth value. PrimeBase uses 3-valued logic, therefore a boolean value can either be true false, or maybe.

<boolean_literal> ::= '$TRUE' | '$FALSE' | '$MAYBE'

---

Time types

DATE a 4-byte value consisting of the year, the month and the day.

TIME a 4-byte value consisting of hours (0-23), minutes, seconds and hundredths of a second.

TIMESTAMP, DATETIME an 8-byte value consisting of a date and a time value.

Submitted as character literals, according to the current value of either $datefmt, $timefmt, or $tsfmt.

---

Character types

CHAR, CHARACTER a fixed length character string.

VARCHAR a variable length character string.

<character_literal> ::= ( '"' | ''' ) { <character> }
                               ( '"' | ''' ) |
                          ':' <var_name>

---

Binary types

BIN, BINARY a fixed length byte string.

VAR, VARBINARY a variable length byte string.

---

Special types

GENERIC an item used to declare a variable that can assume any of the other data types when data is assigned to it.

OBJNAME a data item whose value identifies an identifier.

---

2. DDL Reference

This section provides a reference guide to the following statement groups: data control statements, database statements, information statements, object manipulation statements and users, groups and privileges. An explanation of the function of each command is given, followed by the syntax, and an explanation of each part of the syntax. Any idiosyncrasies of a particular command are noted under the section, "notes".

Most of the examples throughout this chapter are based on the "golfers" database which is provided in Appendix C.

The following list shows the statements of this manual grouped according to the kind of statement they are. The order in which they are documented in this manual, however, is alphabetical.

DBMS Statements

• OPEN DBMS
• CLOSE DBMS
• USE DBMS

Database Manipulation Statements

• OPEN DATABASE
• CLOSE DATABASE
• USE DATABASE
• CREATE DATABASE
• BACKUP DATABASE
• RESTORE DATABASE
• MOUNT DATABASE
• UNMOUNT DATABASE

Information Statements

• DESCRIBE DBMS
• DESCRIBE OPEN DBMS
• DESCRIBE DATABASES
• DESCRIBE OPEN DATABASES
• DESCRIBE TABLES
• DESCRIBE COLUMNS
• DESCRIBE LINKSETS
• COMMENT ON

Object Manipulation Statements

• CREATE DOMAIN
• CREATE TABLE
• ALTER TABLE
• OPEN TABLE
• CLOSE TABLE
• BACKUP TABLE
• REORG TABLE
• CREATE KEY
• CREATE DEFAULT
• CREATE INDEX
• CREATE RULE
• CREATE VIEW
• CREATE VARIABLE
• DROP <object>
• RENAME <object>
• SET VARIABLE

Database Privileges Statements

• ADD USER
• REMOVE USER
• ALTER USER
• CREATE GROUP
• DROP GROUP
• GRANT
• REVOKE

Server Control Statements

• SERVER RESTART
• TRANS RESTART
• SERVER SHUTDOWN
• TRANS SHUTDOWN
• SERVER ERROR
• TRANS ERROR
• SERVER RESTORE
• SERVER CHECKPOINT
• SERVER COMMENT

---

ADD USER

function

This statement is used to either add one or more users to a database, or, if the TO clause is included, to add the user to a specific group within the database.

syntax

ADD USER <user_name> { <user_detail> }
           {',' <user_name> <user_detail> {<user_detail>} }
           [ TO <group_name> ] ';'

<user_name>   ::= <character_literal>

<user_detail> ::= CREATOR <creator_name> |
                  PASSWORD <password> |
                  ABORT TIME <expression>

<password>    ::= <character_literal>

<group_name>  ::= <character_literal>

parameters

notes

A user may belong to any number of groups within the database; not just one group.

The <user_name> is unique within the whole database, and is used for log-in purposes. It identifies a user, and as a result, identifies also that user's privileges.

The <creator_name> is an identifier, and therefore must conform to the rules for identifiers. It is not necessarily database-wide unique. This name also becomes a component of the qualified name of any objects created by that user. In addition, if a user specifies a database object without completely qualifying it, then that user's <creator_name> is automatically used. If no object is found, the system will try using the creator names "Common" and "System".

New users to a database are automatically assigned to the system group called "Public".

To add a user to a normal database, the database must be in use, and the user must be the DBA.

To add a user to the Master database, the user must be an SA, and the Master database must be in use. Users added to this database do not receive SA status; they are, however, allowed to create their own databases. A user of the Master database can be promoted to SA, by granting SA privileges.

Users of the Master database that have SA (System Administrator's) privileges can open any database, and SAs are automatically made DBA (Database Administrator) of any database the open. However, normal users of the Master database that do not have SA privileges are not automatically made DBA of any database they open.

Users of the Master database, that do not have SA privileges, are not automatically made DBA of any database they open, as is the case with the SA.

examples

Example /* ** In this example, the user, called Caspar Fyson is ** added to the database. He is given a creator name, ** "golf", the password, "Birdy", and is made a member ** of the group called "GolfersPros". */ CREATE GROUP "GolfersPros"; ADD USER "Caspar Fyson" CREATOR golf PASSWORD "Birdy"; ADD USER "Caspar Fyson" TO "GolfersPros";

see also

ALTER USER, REMOVE USER, GRANT, REVOKE, CREATE GROUP, DROP GROUP

---

ALTER TABLE

function

This statement is used to change the structure of an existing relation.

syntax

ALTER TABLE <table_reference> <column_command> ';'

<column_command> ::= <append_column> | <rename_column>

<append_column>  ::= (APPEND | ADD) [COLUMN] <column_def>

<rename_column>  ::= RENAME [COLUMN] <column_name>
                     TO <column_name>

parameters

notes

In the APPEND clause, the name of the column must be distinct from those already existing in the table. If <column_def> is a composite column definition then the component columns must be simple columns already existing in the table. New columns must allow NULLs, as the value stored in new simple columns is NULL.

examples

Example ALTER TABLE Golfers APPEND COLUMN medalswon INT; ALTER TABLE Golfers RENAME COLUMN medalswon TO medals;

see also

CREATE TABLE, DROP TABLE, RENAME TABLE, REORG TABLE, BACKUP TABLE, OPEN TABLE, CLOSE TABLE

---

ALTER USER

function

This statement is used to alter details associated with a user.

syntax

ALTER [ USER <user_name> ] <user_detail>
      { <user_detail> }
      { ',' <user_name> <user_detail>
        { <user_detail> } } ';'

<user_detail> ::= CREATOR <creator_name> |
                  PASSWORD <password> |
                  ABORT TIME <expression>

<password>    ::= <character_literal>

<group_name>  ::= <character_literal>

parameter

notes

Users may set their own passwords, but only the DBA may set the creator name of a user and the password of another user.

If the USER clause is omitted, then the current user is assumed.

examples

Example /* ** In this example, the password of the user, Caspar ** Fyson is altered, and is changed to "Eagle". The ** ABORT TIME is set at 5 seconds. */ ADD USER "Caspar Fyson" PASSWORD "WOODY"; ALTER USER "Caspar Fyson" PASSWORD "Eagle" ABORT TIME 5;

see also

ADD USER, REMOVE USER, GRANT, REVOKE, CREATE GROUP, DROP GROUP

---

BACKUP DATABASE

function

This statement is used to backup a database.

syntax

BACKUP DATABASE <database_name>
                { <file_location_spec> }
                { <backup_options> } ';'

<file_location_spec> ::= [ DATA | INDEX ] [ IN ]
                         LOCATION <character_literal>

<backup_options>     ::= <include_index> |
                         <preserve_previous>

<include_index>      ::= ( WITH | WITHOUT ) INDEX

<preserve_previous>  ::= ( REPLACE | PRESERVE | UPDATE )
                         [ PREVIOUS ]

parameters

notes

The BACKUP/RESTORE facility in PrimeBase is designed to guarantee complete recovery of a database including changes applied to the database after the backup was completed.

Backup of a database can be done while the database is online (i.e. while it is in normal use), using the BACKUP DATABASE statement. A backup of a database can be restored using the RESTORE DATABASE statement. The backup image of a database looks identical to the normal database image. However, if the backup was made while the database was in use, then the image may not be consistent, due to the fact that the tables were copied at different times. If the database was not in use during backup, the backup image can be mounted as any other database, provided the backup is the first of the database in that location. If this is done, the mounted database will reflect the state of the database at the time of the backup, and will not include any subsequent changes. It may be necessary to mount a backup image if any of the log files at the time of backup has been lost or corrupted.

By default, offline logs are deleted by the server. Offline logs are logs no longer needed by the server to do a normal restart (recovery). In order to bring a database completely up-to-date from a backup, the offline logs must be archived. To do this, the system administrator must set the system variable OfflineFunction to "Archive", as follows:

OPEN DATABASE master;

SET VARIABLE offlinefunction = "Archive";

CLOSE DATABASE;

To set the offline log function back to deletion, set Offline Function to "Delete". When the offline location function is set to Archive, log files are not deleted, but copied to a log archive location. An archived log is given a different name (the first letter of the log name is changed). The restore function will only look for an archived log in the archive location to which it was copied. In order for restore to succeed, all required archive logs must be available. This means that all volumes containing archive logs must be online.

examples

Example /* ** In this example, the database Golfers is backed up. */ BACKUP DATABASE Golfers;

---

BACKUP TABLE

function

This statement does a backup of a table.

syntax

BACKUP TABLE <table_reference> ';'

parameters

notes

The backup of the table is added to the last backup done of the database. The backup options and locations used are those specified in the original database backup command. It is necessary to backup a table after it has been reorganized (REORG TABLE).

---

CLOSE DATABASE

function

This statement closes a currently open database.

syntax

CLOSE DATABASE [ <database_alias> ]';'
<database_alias> ::= <identifier>

parameters

notes

When a database is opened it becomes the current default database. If the default database is closed it is not possible to determine which open database will become the new default database (unless there is only one open database left). The USE DATABASE statement below should be used to reset the default database. You can first use the statement DESCRIBE OPEN DATABASES, which lists the default database.

examples

Example /* ** In this example the database "Golfers" is closed. In ** OPEN DATABASE, "Golfers" was assigned the alias "G", ** which is then used in this CLOSE DATABASE statement. */ OPEN DATABASE Golfers ALIAS G; CLOSE DATABASE G;

Example /* ** In this example, the same as above is achieved, but ** in two steps rather than one. Notice that the alias ** is not included in the syntax of the close statement. */ OPEN DATABASE Golfers ALIAS G; USE DATABASE G; CLOSE DATABASE;

see also

BACKUP DATABASE, RESTORE DATABASE, OPEN DATABASE, USE DATABASE, CREATE DATABASE DROP DATABASE, MOUNT DATABASE, UNMOUNT DATABASE

---

CLOSE DBMS

function

This statement closes an open DBMS. If <dbms_brand> is not given, then the current DBMS is closed. All open databases of the DBMS are also closed by this statement.

syntax

CLOSE [<dbms_brand>] DBMS ';'

parameters

examples

Example CLOSE DBMS;

Example CLOSE my_connection_alias DBMS;

notes

In PrimeBase the a DBMS is a server or gateway. An "open dbms" is an open connection to a server/gateway. The command CLOSE DBMS closes the connection.

Note that the name of the DBMS must be placed in quotes if it contains spaces or special characters.

see also

OPEN DBMS, USE DBMS, DESCRIBE DBMS, DESCRIBE OPEN DBMS

---

CLOSE TABLE

function

This statement closes a table opened with the OPEN TABLE statement.

syntax

CLOSE TABLE <table_reference> ';'

parameters

examples

Example /* ** This example is a transaction which opens ** (locks) the table "Golfers" for EXCLUSIVE ** access for the time of the transaction. ** Afterwards the table is closed (unlocked) ** again. */ BEGIN; OPEN TABLE Golfers FOR EXCLUSIVE UPDATE; CLOSE TABLE Golfers; COMMIT;

notes

The PrimeBase server ignores this statement, as a table is automatically closed at the end of the transaction in which the table was opened.

see also

CREATE TABLE, DROP TABLE, RENAME TABLE, ALTER TABLE, BACKUP TABLE, REORG TABLE, OPEN TABLE

---

COMMENT ON

function

This statement allows you to place a comment on any type of object and on columns. The type of object may be specified, but it is optional.

syntax

COMMENT ON (<object_comment> | <column_comment>)
        IS <expression> ';'

<object_comment> ::= [<object_type>] <object_reference>

<object_type>    ::= DOMAIN | TABLE | KEY | DEFAULT |
                     INDEX | RULE | VIEW | VARIABLE

<column_comment> ::= COLUMN <column_of_table_reference>

parameters

notes

A previous comment may be removed by specifying the comment as an empty string, ("").

examples

Example /* ** In this example, a comment is added to the Courses ** table. */ COMMENT ON TABLE Courses IS "Each club has a number of courses. " + "The details of each hole of each course " + "are stored in the Courses table.";

see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS

---

CREATE DATABASE

function

This statement creates the necessary system folders and files for a new database.

syntax

CREATE DATABASE <database_name> { <file_location_spec> } ';'

<file_location_spec> ::= [ DATA | INDEX] [ IN ]
                         LOCATION <character_literal>

parameters

notes

Only system administrators (SA) and master database users with DBA status may create a database.

The new database is created but not opened.

The creator of the database is entered as the second user of the database with DBA privileges. The first user of a database is the user "System". "System" is the creator and owner of the system tables and other system objects. When a system administrator opens a database in which he is not a user, he is then considered to be the user, "System".

In creating a database two file system locations may be specified. The location for the data (DATA keyword in the IN LOCATION clause), and the location for the indices (INDEX keyword). These locations may be the same (i.e. both DATA and INDEX keywords may be omitted. If no location is specified, then location for both data and index is the DataServer root path by default. The dataserver root path is given when installing the dataserver, and contains the Master and Model databases. The dataserver will append a directory to the specified location, and then place the data/index files within that directory. The name of the directory is identical to the name of the database.

A database name must be an identifier, whether specified as a character string or not. Since the name of the directory containing the database files is identical to that of the database, the names of databases are limited as for directory names of the underlying operating system. For example, a dataserver running under DOS would only support database names of maximum 8 characters in length. However, the system ensures that the names of databases are case insensitive like all other identifiers.

New databases are created by duplicating the model database. The SA is able to configure created databases by modifying the model database.

Warning: Do not remove, rename, or delete any files or directories created by the server. If you wish to delete a database, use the DROP DATABASE statement. If you want to change the location of a database, you can use the MOUNT DATABASE and UNMOUNT DATABASE command.

examples

Example /* ** In this example, the database "Golfers2" is created. */ CREATE DATABASE Golfers2;

see also

RESTORE DATABASE, BACKUP DATABASE, OPEN DATABASE, CLOSE DATABASE, USE DATABASE, DROP DATABASE, MOUNT DATABASE, UNMOUNT DATABASE

---

CREATE DEFAULT

function

This statement is used to specify a value that will be automatically inserted into a column, if no value is explicitly supplied at insert time.

syntax

CREATE DEFAULT <default_reference> ON
               ( [ COLUMN ] <column_of_table_reference> |
                 DOMAIN <domain_reference> )
               <default_def> ';'

<default_def> ::= AS ( <expression> |
                       USER |
                       SERIAL <variable_reference> |
                       NOW )

parameters

notes

Defaults may be specified on a simple column or a simple domain. If a default is placed on a domain, a further default may still be specified on a column defined on that domain. This default takes priority over the domain default. If no default value is stated, the value is recorded as missing (NULL). If the column does not allow missing values an insert in which the column value is not specified is rejected. On insert the column default takes priority.

The default value must be compatible with the data type of the column or domain to which it is bound.

examples

Example /* ** In this example, a default is created on the domain, ** GolferID2, called GolferDef2. It is a SERIAL default, ** based on the counter variable, called GolferCnt2. */ CREATE COUNTER INTEGER GolferCnt2 = 1; CREATE DOMAIN GolferID2 INTEGER NOT NULL; CREATE DEFAULT GolferDef2 ON DOMAIN GolferID2 AS SERIAL GolferCnt2;

Example /* ** In this example, a default, ParDef2, is created on ** the column Stroke of the table Courses. A default ** value of 4 is always inserted into this column. */ CREATE DEFAULT ParDef2 ON Courses.Stroke AS 4;

see also

DROP DEFAULT, RENAME DEFAULT

---

CREATE DOMAIN

function

The domain manipulation statement, create domain, allows the declaration of a user-defined, extended data type, which is distinct from any other domain within the database (simple or composite).

syntax

CREATE [ PRIMARY ] DOMAIN <domain_reference>
                          ( <simple_domain_def> |
                            <composite_domain_def> ) ';'

<simple_domain_def>    ::= <data_type>
                           { [ ',' ]
                             <domain_specification> }

<domain_specification> ::= <missing_specification> |
                           <arithmetic_specification> |
                           <order_specification>

<missing_specification>    ::= [ NOT ] NULL

<arithmetic_specification> ::= ARITHMETIC [ NOT ]
                               APPLICABLE

<order_specification>  ::= ORDER [ [ NOT ] APPLICABLE ]
                           [ AS <sequence> ]

<sequence>             ::= COLLATING SEQUENCE
                             <variable_reference> |
                           <system_sequence>
                             { ',' <system_sequence> }

<system_sequence>      ::= COMMON |
                           CASE INSENSITIVE |
                           IGNORE DIACRITICAL MARKS

<composite_domain_def> ::= '(' <simple_domain>
                             ',' <simple_domain>
                               { ',' <simple_domain> } ')'

<simple_domain>        ::= <domain_reference> | <data_type>

parameters

notes

A domain in its definition stores information as to its basic data type, whether values of the domain are allowed to be missing, and information as to whether the comparative and arithmetic operators can be meaningfully applied on data of this domain. (The operator '=' can always be meaningfully applied.)

The range of values permitted on a domain may be specified by placing a rule on the domain, (see CREATE RULE).

By default, the <missing_specification> should be set as NULL in the case of a non-primary domain, and NOT NULL in the case of a primary domain.

By specifying that a domain is primary (CREATE PRIMARY DOMAIN), the user indicates that values in primary keys defined on that domain must be domain-wide unique. For example, it is possible to create a number of primary keys that draw values from a common domain. The system ensures that the sets of values in various primary keys on a primary domain are disjoint. If the domain is not primary, uniqueness of primary key values on the domain are only ensured within the table on which the primary key is defined. Note that if there is only ONE primary key on a domain then it makes no difference whether the domain is primary or not.

Please remember, however, that when we talk about a primary domain, we do not mean a domain that has a primary key defined on it, but we mean a domain that has been explicitly declared as primary.

Domains cannot be declared recursively, in terms of one another.

Composite domains may include basic data types as well as simple domains as components. Domains cannot be declared recursively, in terms of one another.

The order of the components of a composite domain is significant in that, when sorting values in a domain, the left-most component is considered the most important. This means that if a domain is ordered, its components are sorted from right to left, and compared left to right.

Note that <domain_reference> in <simple_domain> is the name of a previously declared simple domain.

The order specification statement declares the following information:

1. Whether or not the operators '<', '>', '<=', or '>=' can be meaningfully applied to values of the domain (ORDER APPLICABLE). If order is not applicable, only equality tests, (equals ('='), not-equals ('!=')), may be done when comparing two values of the domain. Sorting, however, is still possible.

2. That a collating sequence should be used when comparing values of the domain. The name of the collating sequence may be explicitly specified (<variable_reference>), or a system defined collating sequence can be selected using the COMMON, CASE INSENSITIVE... keywords.

Warning Values of domain A cannot be directly assigned to or directly compared to values of domain B, even though the underlying datatypes and sizes (e.g. domain A and domain B have the underlying datatype VARCHAR(64)) might be identical, else the following error will occur: Mismatch of underlying column domains in binary operation. Explicit datatype conversion must be applied in such cases. For example change... SELECT * FROM MyTableA a, MyTableB b WHERE a.street = b.street; ...(assuming a.street and b.street are not based on the same (VARCHAR) domain) to... SELECT * FROM MyTableA a, MyTableB b WHERE VARCHAR( a.street ) = VARCHAR( b.street );

Collating Sequences

COMMON: The common ordering is an improved ordering of the ASCII character set, which places alphabetically similar characters together, and upper case before lower case:

AÄaäâBbCÇcç...

CASE INSENSITIVE: In a case-insensitive sequence, the case of the characters is ignored. In such a sequence, A=a, and Ä=ä, etc.

IGNORE DIACRITICAL MARKS: When ignoring diacritical marks, A=Ä, and a=ä, for example.

examples

Example /* ** In this example a series of domains, defaults and ** variables are created, to provide a structure for ** the table "Golfers". */ CREATE DATABASE GolfersTemp; OPEN DATABASE GolfersTemp; CREATE COUNTER INTEGER GolferCnt = 1; CREATE DOMAIN GolferID INTEGER NOT NULL; CREATE DEFAULT GolferDef ON DOMAIN GolferID AS SERIAL GolferCnt; CREATE DOMAIN NameType VARCHAR[55] ORDER APPLICABLE AS CASE INSENSITIVE; CREATE DOMAIN StatusType CHAR[8]; CREATE RULE StatusRule ON StatusType AS StatusType IN ( 'Amateur', 'Pro', 'Pro/Am' ); CREATE DOMAIN HandicapType SMINT; CREATE RULE HandicapRule ON HandicapType AS HandicapType BETWEEN 36 AND -5; CREATE COUNTER INTEGER ClubCnt = 1; CREATE DOMAIN ClubID INTEGER; CREATE DEFAULT ClubDef ON DOMAIN ClubID AS SERIAL ClubCnt; CREATE TABLE Golfers ( ID GolferID NOT NULL, SurName NameType NOT NULL, FirstNames NameType NOT NULL, Name (SurName, FirstNames), Title CHAR[10], Sex CHAR[1] NOT NULL, Nationality NameType, DateOfBirth DATE, Status StatusType, Handicap HandicapType, MemberOfClub ClubID, Earnings MONEY[12,2] ); /* ** Set the currently in use database ** back to Golfers again. */ USE DATABASE Golfers;

see also

DROP DOMAIN, RENAME DOMAIN

---

CREATE GROUP

function

This statement creates a group, or number of groups, within a database.

syntax

CREATE GROUP <group_name> { ',' <group_name> } ';'

parameter

notes

The group will be created in the database that you are currently working in.

examples

Example /* ** In this example a group is created, called ** GolfersPros, containing the names of ** professional golfers, who use this database. */ CREATE GROUP "GolfersPros";

see also

ADD USER, ALTER USER, ADD USER, REMOVE USER, GRANT, REVOKE, DROP GROUP

---

CREATE INDEX

function

This statement is used to create an index on a column, group of columns or a domain.

syntax

CREATE <index_spec> <index_reference>
  ON [TABLE] <table_reference>  <column_group>  ';'

<index_spec>::= INDEX { SUPPRESS ZERO | SUPPRESS NULL }

parameters

notes

Indices are a performance related feature, for example, they can speed up data retrieval, but can slow down data insert and update.

If duplicate key values occur very often, the speed of data retrieval will not increase, and update/delete will slow down.

The fewer rows there are that match the search conditions, the more effective the index will be.

Remember that is zero depression has been defined on an index, a search on that index will not retrieve any zeros.

examples

Example /* ** In this example an index is created, called ** GolfersIndex2, and is defined on the table ** called "Golfers" - on the column, ID. The ** primary key for the table is also defined on ** this column. It is recommended that you define ** your indices on the same columns as your primary ** keys. */ CREATE INDEX GolfersIndex2 ON Golfers (Earnings);

Example /* ** In the next example, an index is defined on a ** composite column. In this case, each separate ** component of the composite column must be listed. ** You may not simply give the name of the composite ** column itself. */ CREATE INDEX CoursesIndex2 ON Courses (Club, Course);

see also

DROP INDEX, RENAME INDEX

---

CREATE KEY

function

The key manipulation statement, CREATE KEY, defines a primary, candidate or foreign key on a base relation.

syntax

CREATE <key_spec> <key_reference>
  ON [ COLUMN ] <column_of_table_reference>
    [ <reference_spec> ] ';'

<key_spec>           ::= UNIQUE | ( ( PRIMARY |
                           CANDIDATE | FOREIGN ) KEY )

<reference_spec>     ::= REFERENCES <table_reference>
                           { ',' <table_reference> }
                           { <triggered_action> }

<triggered_action>   ::= ON UPDATE <referential_action> |
                         ON DELETE <referential_action>

<referential_action> ::= RESTRICT | CASCADE | SET NULL |
                         SET DEFAULT

parameters

notes

Foreign keys may only be defined on a column that is based on a previously defined domain (simple or composite).

All primary keys must fulfill the entity integrity rule, which states: no component of the primary key in a base relation is allowed to contain a NULL. When a primary key is defined on a column which allows NULLs, the column will no longer accept missing values.

All primary and candidate keys must satisfy the uniqueness property, which states: No two tuples of a key may have the same value, therefore although it is allowed for candidate keys to be defined on a column that allows NULLs, this columns may only include one NULL, as two NULLs are considered as duplicate values.

All composite candidate and primary keys must satisfy the minimality property, which states: If a candidate key is composite, then no component of that key can be removed from that combination without the uniqueness of that key being lost. However, adherence to this requirement of the relational model cannot be verified by the DBMS.

All foreign keys must fulfil the referential integrity rule, which states: The database may not contain any unmatched foreign key values. These values are all drawn from the primary key which is being referenced, via the primary domain on which the foreign key is based.

A base relation must have one and only one primary key defined on it.

A primary key must be defined on a base relation before the relation can be used.

examples

Example /* ** In this example, a primary key is created, ** GolfersPk, and defined on the column, ID, ** in the table, Golfers. */ CREATE TABLE Test ( ID integer ); CREATE PRIMARY KEY GolfersPk2 ON Test.ID;

Example /* ** A candidate key is defined on the same table, on the ** column ID. */ CREATE TABLE Test ( ID integer ); CREATE CANDIDATE KEY GolferNameCk2 ON Test.ID;

see also

DROP KEY, RENAME KEY

---

CREATE RULE

function

Rules can be applied to tables or simple domains. They restrict the values and combinations of values of a row of a table or a simple domain.

syntax

CREATE RULE <rule_reference>
  ON ( [TABLE] <table_reference> |
    [DOMAIN] <domain_reference> ) <rule_def> ';'

<rule_def>::= ( CHECK | AS ) <search_condition>

parameters

notes

If a base table is dropped, all rules defined on that table are also dropped.

In order for a new rule to be defined on a given table, the old rule must first be dropped.

When a rule is defined on a table, existing rules are not checked to conform to the rule. Only subsequent inserts and updates are checked.

Columns or components of columns referenced in <search_condition> are limited to the columns of <table_reference> (a single row). Note that only the comparison operators are defined on composite columns

Rules can only be specified on a simple domain (i.e. a domain defined on a single basic data type). In the case of rules defined on domains, the name of the domain (optionally qualified by the creator name) may be used in place of columns in <search_condition>.

examples

Example /* ** In this example a rule called StatusRule2 ** is defined on the domain StatusType2. */ CREATE DOMAIN StatusType2 CHAR[8]; CREATE RULE StatusRule2 ON StatusType2 AS StatusType2 IN ( 'Amateur', 'Pro', 'Pro/Am' );

Example /* ** A rule, ParRule is defined on the column ** NoOfCourses in the table Clubs. */ CREATE RULE ParRule ON Clubs AS NoOfCourses IN ( 3, 4, 5 );

see also

DROP RULE, RENAME RULE

---

CREATE TABLE

function

This statement creates a relation within a currently open database.

syntax

CREATE TABLE <table_reference> <table_def> ';'

<table_def>     ::= '(' <column_def> {',' <column_def> } ')'

<column_def>    ::= <column_name> ( <simple_column_def> |
                      <composite_column_def> )

<simple_column_def>    ::= <simple_domain>
                             [<missing_specification>]

<simple_domain> ::= <domain_reference> | <data_type>

<composite_column_def> ::= <column_group>
                             [<domain_reference>]

<column_group>  ::= '(' <column_name>
                      { ',' <column_name> } ')'

parameters

notes

A column can either be simple or composite. A simple column is defined using a simple domain (<simple_domain> in <simple_column_def>). Please note however, that <simple_domain> also allows the direct specification of a basic data type. This means that the user is not required to declare a domain for every column in the database, and also renders PrimeBase compatible with other database management systems.

A composite column is a combination of simple columns.

If a composite domain is specified in the declaration of a composite column, it is not required that the simple columns mentioned in the <column_group> have been previously declared, as the simple column definition can be deduced from the composite domain that follows, (<domain_name> in <composite_column_def>).

The <missing_secification> for simple columns declared in this way is as per default. If the <missing_specification> in the definition of a simple column is omitted, the column will assume the <missing_specification> of the underlying domain, or missing value allowed (NULL) in the case of simple columns defined on a basic data type. If the underlying simple domain is defined as NOT NULL, then the column cannot be defined as NULL.

Please note, that a simple column may be a member of more than one composite column.

A table may not be used until a primary key has been defined on it. Equally, when a primary key is dropped from a base relation, that relation is temporarily disabled until a new primary key has been defined.

A CHAR or VARCHAR defined column with NULLs allowed takes up much more space than a VARCHAR or CHAR column where NULLs are not allowed.

examples

Example /* ** A table is created, called Results. Two of the ** columns are defined on the domains, CompetitionID, ** and GolferID. A composite column has been created, ** called Key. The primary key is defined on this ** column, as is the index. */ CREATE TABLE Results2 ( Year SMINT NOT NULL, Competition CompetitionID NOT NULL, Place SMINT NOT NULL, Key ( Year, Competition, Place ), Golfer GolferID, TotalScore SMINT, Points SMINT, Winnings MONEY[10,2] ); CREATE PRIMARY KEY Results2Pk ON Results2.Key; CREATE FOREIGN KEY Results2GolferFk ON Results2.Golfer; CREATE INDEX Results2Index ON Results2 (Year, Competition, Place);

see also

DROP OBJ, RENAME TABLE, ALTER TABLE, REORG TABLE, BACKUP TABLE, OPEN TABLE, CLOSE TABLE

---

CREATE VARIABLE

function

This statement is used to create a database variable.

syntax

CREATE (<collating_sequence> | <user_counter> |
         <user_variable> ) ';'

<collating_sequence>  ::= COLLATING SEQUENCE [VARIABLE]
                            <variable_reference> '='
                            <comparison_order>

<comparison_order>    ::= '('<equivalent_sequence> { ','
                            <equivalent_sequence> } ')'

<equivalent_sequence> ::= <expression> |
                          '(' <expression> { ','
                            <expression> } ')'

<user_counter>        ::= COUNTER [VARIABLE] <data_type>
                            <variable_reference>
                            [ '=' <expression> ]

<user_variable>       ::= VARIABLE <data_type>
                            <variable_reference>
                            [ '=' <expression> ]

parameters

examples

Example /* ** In the following example, a collating sequence ** variable is created, called "normal". It defines ** the order for sorting and comparison purposes. */ CREATE COLLATING SEQUENCE VARIABLE normal = ( 'AÀÄÃÅaáàâäãå', 'Ææ', 'Bb', 'CÇcç', 'Dd', 'EÉeéèêë', 'Ff', 'Gg', 'Hh', 'Iiíìîï', 'Jj', 'Kk', 'Ll', 'Mm', 'NÑnñ', 'OÖÕoóòôöõ', 'Œœ', 'Øø', 'Pp', 'Qq', 'Rr', 'Ssß', 'Tt', 'UÜuúùûü', 'Vv', 'Ww', 'Xx', 'Yyÿ', 'Zz', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' );

Here are some examples of how the equivalent sequence 'AÀÄÃÅaáàâäãå' could have been written:

('A', 'À', 'Ä', 'Ã', 'Å', 'a', 'á', 'à', 'â', 'ä', 'ã', 'å' )

...or...

('AÀÄÃÅ', 'aáàâäãå' )

Note that the characters that are omitted (there are 256 characters, although many are not printable) from the collating sequence are added automatically, each in their own equivalent sequence in order of ASCII numbers.

Example /* ** In the next example, three counter variables are ** created. These counter variables are referred to ** in later default statements. */ CREATE COUNTER INTEGER GolferCnt3 = 1; CREATE COUNTER INTEGER ClubCnt3 = 1; CREATE COUNTER INTEGER CompetitionCnt3 = 1;

Example /* ** In the last example, a user defined variable is ** created, called "year_end_no". It is of the data ** type, DATE, and the expression is the date of the ** year - "1992". */ CREATE VARIABLE DATE year_end_no = "01/01/1992";

notes

A database user variable can be used to store certain values used by an application permanently in the database.

The order of characters within an <equivalent_sequence> is important when sorting

Each expression in an equivalent sequence represents a character or sequence of characters. Strings (CHAR, VARCHAR, BIN and VARBIN) represent a sequence of characters. Other numbers (INTEGER, FLOAT,...) represent single characters. The number is considered to be the ASCII value of a character (e.g. 65 = 'A', 66 = 'B', etc.).

All the characters in an <equivalent_sequence> are considered to be equal for comparison ('=', '<', '>', BETWEEN and LIKE) purposes. The order of characters within an <equivalent_sequence> is important when sorting (e.g. by the ORDER BY clause). In this case, characters are sorted ascending from left to right. For example, the <equivalent_sequence> "Aa" indicates that "A" and "a" are equal for comparison purposes, but when sorting, "A" will appear before "a".

While an <equivalent_sequence> consists of characters that are considered equal when compared, the <comparison_order> indicates the result of '<', '<=', '>' and '>=' operations performed on characters from different <equivalent_sequences>.

The only way of finding the current value of a database variable is by selecting the value from the SysVariables table.

see also

SET VARIABLE, DROP VARIABLE, RENAME VARIABLE

---

CREATE VIEW

function

The statement create view enables you to create a virtual, derived table. Any table that can be retrieved via a SELECT statement (any derivable table) can be defined as a view.

syntax

CREATE VIEW <view_reference> [ <column_group> ]
  AS <query_spec> ';'

parameters

example

Example /* ** In this example, a view, GolfersAmateurs is ** created. It consists of columns from the table ** Golfers, and includes all golfers with the status ** Amateur. */ CREATE VIEW GolfersAmateurs2 AS SELECT ID, Name, Title, Handicap, Status FROM Golfers WHERE Status = "Amateur" WITH CHECK OPTION;

notes

Views are dynamic, meaning that changes to the underlying table will automatically and immediately be reflected in the view.

Update operations (INSERT, UPDATE, DELETE) are not supported on view.

see also

DROP VIEW, RENAME VIEW

---

DESCRIBE COLUMNS

function

This statement describes all columns of a particular table. The table must be accessible to the user and must be in an already open database. The resulting table is described below:

syntax

DESCRIBE COLUMNS [ OF ] <table_reference>
  [ INTO <cursor> ] ';'

return values

col#	Data Type	Name	Description
1	SMINT	colnr	Column number
2	SMINT	level	Column level number
3	VARCHAR[31]	name	Column name
4	SMINT	type	Column data type
5	SMINT	len	Column length in bytes
6	SMINT	places	Column scale
7	BOOLEAN	nullsok	Nulls allowed
8	BOOLEAN	groupcol	Group column
9	SMINT	parentnr	Parent column number
10	SMINT	occurs	Number of occurrences
11	SMINT	occdep	Occurs depending on column
12	BOOLEAN	updtok	Can the column be updated
13	VARCHAR[31]	title	Column title
14	VARCHAR[255]	remarks	Column remarks

parameters

notes

It is not necessary for the table specified by <table_reference> to have been previously opened with an OPEN TABLE statement.

examples

Example /* ** Describe columns of the table Golfers is carried ** out in this example. */ DESCRIBE COLUMNS OF Golfers; PRINTALL;

see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS

---

DESCRIBE DATABASES

function

This statement returns a list of all databases on a specific DBMS (server or gateway).

syntax

DESCRIBE [ <dbms_brand> ] DATABASES
  [ [ IN ] LOCATION <character_literal> ]
  [ INTO <cursor> ] ';'

return values

The rowset returned into <cursor> is as follows:

col#	Data Type	Name
1	VARCHAR[31]	name

parameters

notes

A PrimeBase server can deliver information other than just the names f the databases. This information includes the ID of the databases and the privilege level of the user. The PBCTL statement is used to control what information is provided by DESCRIBE DATABASES. By default, only the names of the databases are listed in order to maintain DAL compatibility.

examples

Example /* ** In this example the databases of the default DBMS ** are listed. */ DESCRIBE DATABASES; PRINTALL;

see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS, DESCRIBE COLUMNS, OPEN DATABASE

---

DESCRIBE DBMS

function

This function returns the names of all DBMSs that can be accessed by the client. The result is a rowset, as described below.

syntax

DESCRIBE DBMS [ INTO <cursor> ] ';'

<cursor>::= <identifier>

return values

The resulting rowset contains one row for each DBMS. Each row contains the 17 columns of information shown in the table below.

col#	Data Type	Name	Information
1	VARCHAR[31]	brand	DBMS name or server alias
2	VARCHAR[31]	rev	Version number
3	VARCHAR[31]	brparms	Brand open parameters
4	VARCHAR[31]	dbparms	Database open parameters
5	VARCHAR[31]	tbparms	Table open parameters
6	VARCHAR[31]	struct	Database structure info
7	VARCHAR[31]	txns	Transaction support
8	VARCHAR[31]	types	Supported data types
9	VARCHAR[31]	stmts	Supported statements
10	VARCHAR[31]	queries	Query processing options
11	VARCHAR[31]	aggfcns	Aggregate function support
12	VARCHAR[31]	unused1	Reserved by standard DAL
13	VARCHAR[31]	unused2	Reserved by standard DAL
14	VARCHAR[255]	unused3	Reserved by standard DAL
15	VARCHAR[31]	unused4	Reserved by standard DAL
16	VARCHAR[31]	unused5	Reserved by standard DAL
17	VARCHAR[31]	unused6	Reserved by standard DAL

parameters

examples

Example /* ** In this example all available DBMSs are listed. ** The printall statement is used to print the ** results. */ DESCRIBE DBMS; PRINTALL;

notes

Unlike standard DAL, the PrimeBase DAL software considers a DBMS to be a server or gateway anywhere on the network (not just on a particular host). The DESCRIBE DBMS command lists all such DBMSs for which a connection has been defined on the client machine.

Connections definitions are stored in the "connect.def" file in the "PrimeBase Setup" folder (called "PB-SETUP" under Windows, and "primebase.setup" under UNIX). A connection definition describes how to connect to a particular server or gateway anywhere on the network, or internet.

The DBMS brand (the first column of the rowset described above) is the connection alias used to identify the connection definition.

DAL compatible applications using PrimeBase DAL should always login to a "dummy" host called "PrimeBaseHost". The application then does a DESCRIBE DBMS and using OPEN DBMS will connect to a server using the selected connection definition.

Other details provided by the DESCRIBE DBMS command include the version number and profile strings.

The DBMS profile strings provide a description of a particular DBMS (PrimeBase server or gateway). They tell which features are supported or not supported by the server or gateway, what parameters are required or optional, and so forth. The strings are positional, with character positions numbered from 0 (the first one) to N-1, where N is the length of the string. In each position of each string, the character will either be a Y (meaning the feature is supported) or an N (meaning the feature is not supported).

BRPARMS

The profile string, BRPARMS, specifies which parameters of the OPEN DBMS statement are relevant for the DBMS brand. It has three characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Is the user name used?
1	Y	Is the password used?
2	Y	Is the option string used?

DBPARMS

The profile string DBPARMS specifies which parameters of the OPEN DATABASE statement are relevant for the DBMS brand. It has eight characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Is the database name used?
1	N	Is the location used?
2	N	Is the user name used?
3	N	Is the password used?
4	N	Is the option string used?
5	N	Is SHARED mode supported?
6	Y	Is PROTECTED mode supported?
7	N	Is EXCLUSIVE mode supported?

TBPARMS

The profile string TBPARMS specifies which parameters of the OPEN TABLE statement are relevant for the DBMS brand. It has three characters in the following positions:

Position	PrimeBase Server	Meaning
0	N	Is SHARED mode supported?
1	Y	Is PROTECTED mode supported?
2	N	Is EXCLUSIVE mode supported?

STRUCT

The profile string STRUCT specifies general structural information about how the DBMS brand organizes its databases and whether various database features are present or absent. It has nine characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Does the DBMS support creation of multiple databases on a single host system? (If N, there is one system-wide database.)
1	Y	Are individual databases named? (If N, databases are unnamed.)
2	Y	Does the DBMS use host locations (directories, catalogues) to structure its databases?
3	Y	Does the DBMS support concurrent access to multiple databases? (If Y, this DBMS brand supports multiple OPEN DATABASE statements; if N, only one database of this brand can be open at a time.)
4	Y	Does the DBMS support queries across different databases? (If Y, the FROM clause of a SELECT statement can include tables from multiple databases; if N, all tables must be from the same database.)
5	N	Are linksets present in databases of this brand? (If N, the DESCRIBE LINKSETS statement will always produce a rowset with no row.)
6	N	Are hierarchical columns present in databases of this brand? (If Y, column names can have the form a.b.c.)
7	N	Are repeating columns present in databases of this brand? (If Y, columns names can have the form colname[6].)
8	N	Are variable repeating columns present in databases of this brand?

TXNS

The profile string TXNS specifies the transaction-processing support provided by the DBMS brand. It has three characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Does the DBMS support transactions (that is, is the DAL COMMIT / ROLLBACK mechanism supported?)
1	Y	Are transactions performed in repeatable-read (RR) mode? That is, can the client application be sure that data it has read during the current transaction will be identical if re-read before a COMMIT or ROLLBACK?
2	N	Are transactions performed in cursor-stability (CS) mode? That is, can the client application be sure only that data read through a single cursor is consistent? (Either CS or RR mode will be TRUE for a given DBMS, but not both.)

TYPES

The profile string TYPES specifies which DAL data types can result from a database of the DBMS brand. Each position of the string corresponds to a single DAL data type. It has 16 characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Does the DBMS generate NULL data?
1	Y	Does the DBMS generate BOOLEAN data?
2	Y	Does the DBMS generate SMINT data?
3	Y	Does the DBMS generate INTEGER data?
4	Y	Does the DBMS generate SMFLOAT data?
5	Y	Does the DBMS generate FLOAT data?
6	Y	Does the DBMS generate DATE data?
7	Y	Does the DBMS generate TIME data?
8	Y	Does the DBMS generate TIMESTAMP data?
9	Y	Does the DBMS generate CHAR data?
10	Y	Does the DBMS generate DECIMAL data?
11	Y	Does the DBMS generate MONEY data?
12	Y	Does the DBMS generate VARCHAR data?
13	Y	Does the DBMS generate VARBIN data?
14	N	Does the DBMS generate LONGCHAR data?
15	N	Does the DBMS generate LONBIN data?

STMTS

The profile string STMTS specifies which DAL statements are supported for databases of the DBMS brand. Each position of the string corresponds to a single DAL data-manipulation statement. It has 12 characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Is SELECT statement supported?
1	Y	Is FETCH statement supported?
2	Y	Is DESELECT statement supported?
3	Y	Is searched UPDATE statement supported?
4	Y	Is positioned UPDATE statement supported?
5	Y	Is searched DELETE statement supported?
6	Y	Is positioned DELETE statement supported?
7	Y	Is INSERT statement supported?
8	N	Is LINK statement supported?
9	N	Is UNLINK statement supported?
10	Y	Is COMMIT statement supported?
11	Y	Is ROLLBACK statement supported?

QUERIES

The profile string QUERIES specifies the features supported in DAL queries against databases of the DBMS brand. It has ten characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Are select-list expressions supported? (If N, only column references and aggregate functions of column references can appear in a select list.)
1	Y	Are joins supported? (If N, the FROM clause of a SELECT statement can include only a single table.)
2	Y	Are row-selection criteria supported? (If N, there can be no WHERE clause in a SELECT statement.)
3	Y	Is grouping supported? (If N, there can be no GROUP BY clause in a SELECT statement.)
4	Y	Is group selection supported? (If N, there can be no HAVING clause in a SELECT statement.)
5	Y	Is sorting supported? (If N, there can be no ORDER BY clause in a SELECT statement.)
6	Y	Are subqueries supported? (If N, the IN (subquery) predicate, the EXISTS predicate, the quantified predicates, and comparison predicates with their associated subqueries are not supported.)
7	Y	Are aggregate functions supported in a select list?
8	Y	Are aggregate functions supported in a HAVING clause?
9	Y	Are aggregate functions with outer references supported in subqueries?

AGGFCNS

The profile string AGGFCNS specifies which DAL aggregate functions are supported in queries against databases of the DBMS brand. It has eight characters in the following positions:

Position	PrimeBase Server	Meaning
0	Y	Is the COUNT(*) supported?
1	Y	Is the COUNT(DISTINCTx) function supported?
2	Y	Is the MIN(x) function supported?
3	Y	Is the MAX(x) function supported?
4	Y	Is the SUM(x) function supported?
5	Y	Is the SUM(DISTINCT x) function supported?
6	Y	Is the AVG(x) function supported?
7	Y	Is the AVG(DISTINCT x) function supported?

see also

DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS, DESCRIBE COLUMNS

---

DESCRIBE LINKSETS

function

This statement is included for DAL compatibility only.

syntax

DESCRIBE LINKSETS [ [ OF ] <database_alias> ]
  [ INTO <cursor> ] ';'

parameters

notes

It returns no rows, as there are no linksets in PrimeBase. They are not included, as they are not a feature of the relational database model.

see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE COLUMNS

---

DESCRIBE OPEN DATABASES

function

This statement returns a cursor containing information about the currently open databases. The structure of the table is given below.

syntax

DESCRIBE OPEN DATABASES [ INTO <cursor> ]';'

return values

The resulting rowset has one entry for each database that is currently open. The first database described in the rowset is the current default database. Each row contains the five columns of information shown below:

col#	Data Type	Name	Description
1	SMINT	order	Sequence number
2	VARCHAR[31]	alias	Database alias
3	VARCHAR[31]	brand	DBMS brand
4	SMINT	shrmode	Sharing mode
5	SMINT	updmode	Update mode
6	VARCHAR[31]	owner	current owner

notes

The rowset created by this statement is done using the EXTRACT mode, so the number of databases described is available through the $rowcnt system variable.

The sharing mode (shrmode) is reported as 1 = SHARED, 2 = PROTECTED, 3 = EXCLUSIVE.

The update mode (updmode) is reported as 1 = READONLY, 2 = UPDATE, 3 = SCROLLING, 4 = EXTRACT mode.

The owner column is empty since PrimeBase does not support the database owner concept. In PrimeBase a database belongs to one or more DBAs.

examples

Example DESCRIBE OPEN DATABASES; PRINTALL;

see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS, DESCRIBE COLUMNS

---

DESCRIBE OPEN DBMS

function

This statement describes all currently open database management systems (DBMSs).

syntax

DESCRIBE OPEN DBMS [ INTO <cursor> ] ';'
		

return values

A rowset which consists of a DBMS number followed by a row with the identical structure of the rowset returned by the DESCRIBE DBMS statement.

parameter

notes

This statement lists all open connections to DBMSs. The first row returned is the current DBMS (as specified in USE DBMS.

see also

DESCRIBE DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE TABLES, DESCRIBE LINKSETS, DESCRIBE COLUMNS

---

DESCRIBE TABLES

function

This statement returns a cursor describing the tables of a particular database.

syntax

DESCRIBE TABLES [ [ OF ] <database_alias> ]
  [ INTO <cursor> ] ';'

return values

The structure of the table returned into <cursor> is given below.

col#	Data Type	Name	Description
1	VARCHAR[255]	name	Table name
2	VARCHAR[1]	type	Table(T) or view (V)
3	BOOLEAN	ordered	Is table ordered?
4	SMINT	colcnt	Column count
5	INTEGER	rowcnt	Row count
6	SMINT	parentcnt	Parent count
7	SMINT	childcnt	Child count
8	VARCHAR[31]	title	Table title
9	VARCHAR[255]	remarks	Remarks
10	VARCHAR[255]	owner	Table owner

parameters

notes

The OPEN DATABASE statement must be used to open a database before the DESCRIBE TABLES statement can be used.

If the <database_alias> is omitted, the default database is used (see the USE DATABASE and OPEN DATABASE statements).

DESCRIBE TABLES lists only the user defined tables. System tables are not listed by default.

examples

Example /* ** In this example the tables of the currently ** in use database are described. */ DESCRIBE TABLES; PRINTALL;

see also

DESCRIBE DBMS, DESCRIBE OPEN DBMS, DESCRIBE DATABASES, DESCRIBE OPEN DATABASES, DESCRIBE LINKSETS, DESCRIBE COLUMNS

---

DROP GROUP

function

This statement drops a specific group or groups from the database.

syntax

DROP GROUP <group_name> { ','<group_name> } ';'

parameter

notes

Naturally, once a group has been dropped, the users who were part of it are no longer members of it.

examples

Example /* ** In this example the freshly created ** group called GolfersPros is dropped. */ CREATE GROUP "GolfersPros"; DROP GROUP "GolfersPros";

see also

ADD USER, ALTER USER, REMOVE USER, GRANT, REVOKE

---

DROP <object>

function

This statement allows the deletion of a database object from the database.

syntax

DROP [ <object> ] <object_reference>
  ( ',' <object_reference> ) ';'

parameters

notes

Please note, that it is not possible to undo this statement.

examples

Example /* ** In this example, a database is dropped. Please note, ** that first the database has to be closed, in order to ** be dropped. */ CLOSE DATABASE Golfers; DROP DATABASE Golfers;

---

GRANT

function

This statement grants object or command privileges to the specified users and groups.

syntax

GRANT ( <command_privileges> | <object_privileges> )
  TO ( PUBLIC | <user_name> { ',' <user_name> } )
  [ WITH GRANT OPTION ] ';'

<command_privileges> ::= DBA | SA | RESOURCE

<object_privileges>  ::= ALL [ PRIVILEGES ] |
                         ( <object_privs_spec>
                           { ','<action> } )
                         ON <object_reference>
                           [ <column_group> ]

<object_privs_spec>  ::= INSERT | DELETE | REFERENCE |
                         SELECT | UPDATE

parameters

notes

Two types of privileges can be granted with this statement, command or object privileges. A command privilege determines whether a user is permitted to issue certain commands (or statements). Object privileges, on the other hand, associate privileges with specific objects (and columns) in the database.

DROP privileges are a special case. Users can drop any object that they themselves have created, and DBAs can drop any object in the database, including the database itself, but he/she may not drop any system objects.

When a user is added to the database, he/she is automatically given the lowest privileges possible. To add a user to a normal database, the database must be in use and the user must be a DBA. To add a user to the Master database, the master database must be in use and the user must be an SA. Users added to the Master database are not given SA status, but are allowed to create their own databases (they are called DBAs in the master database). A user of the Master database may be promoted to SA by granting SA privileges (SA in <command_privilege> above). Users of the master database who do not have SA privileges still need to be a user of a database to open the database. A user of the Master database that has SA privileges, however, is able to open any database and is automatically given DBA privileges in that database.

If <column_group> is not specified all columns are included in the privilege grant. A <column_group> may be specified in the case of INSERT, SELECT and the UPDATE privilege granting. DELETE and REFERENCE are always granted on an object level. The REFERENCE privilege can only be granted on a domain and means the user has the power to create a foreign key on that domain.

examples

Example /* ** In this example the privilege RESOURCE is granted ** to the group called GolfersPros. This means that all ** users who are members of this group are automatically ** assigned these privileges: namely that they can issue ** all CREATE object commands. */ CREATE GROUP "GolfersPros"; GRANT RESOURCE TO "GolfersPros";

Example /* ** In the following example, DBA privileges are granted ** to the user Julian Baldock. */ ADD USER "Julian Baldock"; GRANT DBA TO "Julian Baldock";

Example /* ** In the next example, the privilege to issue the ** SELECT statement on the table Courses, specifically ** to select from the columns, Key, and Description, ** has been granted to Heather Fyson. She may also grant ** this privilege to other users - hence the WITH GRANT ** OPTION clause. This clause does not apply to the ** <command_privileges>. */ ADD USER "Heather Fyson"; GRANT SELECT ON Golfers (ID, Status, Name) TO "Heather Fyson" WITH GRANT OPTION;

see also

ADD USER, ALTER USER, REMOVE USER, REVOKE, DROP GROUP

---

MOUNT DATABASE

function

This statement makes a database available to the users of the PrimeBase server.

syntax

MOUNT DATABASE <database_name> { <file_location_spec> } ';'

<file_location_spec> ::= [ DATA | INDEX ] [ IN ]
                         LOCATION <character_literal>

parameters

notes

Do NOT mount a database that is already mounted by another server. Use the UNMOUNT statement before you mount the database to a new server, or make a copy of the entire database (when the server is not running).

Restore is not possible for a newly mounted database until a backup has been done.

If no location for data and indices is specified, then their location is by default the server root path. The server root is the directory containing the Master database. The PrimeBase server will append the name of the database to the specified location(s). It will expect to find the index and data files of the database at these locations.

examples

Example /* ** In this example, the database Golfers is mounted. ** No location is specified, as this is not strictly ** required. */ CLOSE DATABASE Golfers; UNMOUNT DATABASE Golfers; MOUNT DATABASE Golfers; OPEN DATABASE Golfers;

see also

CREATE DATABASE, RESTORE DATABASE, BACKUP DATABASE, OPEN DATABASE, CLOSE DATABASE, USE DATABASE, DROP DATABASE, UNMOUNT DATABASE

---

OPEN DATABASE

function

This statement opens a database on the host.

syntax

OPEN [ <dbms_brand> ] DATABASE [ <database_name> ]
  [ALIAS <database_alias>]
  [ [ IN ] LOCATION <character_literal>]
  [ [ AS ] USER <character_literal>
  [ [ WITH ] PASSWORD <character_literal>]]
  [ FOR [ <shared_mode> ] [ <access_mode> ] ] ';'

<database_name>  ::= <character_literal>

<database_alias> ::= <identifier>

<shared_mode>    ::= SHARED | PROTECTED | EXCLUSIVE

<access_mode>    ::= READONLY | UPDATE

parameters

The syntax after and including the IN LOCATION clause is accepted but ignored by the PrimeBase server.

notes

To access a database the PrimeBase server uses the user name and password specified in the OPEN DBMS statement. User name and password specified in OPEN DATABASE are ignored.

The LOCATION clause is also ignored. The location of a database is specified in the CREATE DATABASE / MOUNT DATABASE statements.

The PrimeBase server ignores the FOR clause in the OPEN DATABASE statement. Databases are always open for protected update no matter what mode is specified.

When a database is opened, it becomes the current default database.

examples

Example /* ** In this example, the Golfers database is opened, and ** given an alias "G". */ CLOSE DATABASE Golfers; OPEN DATABASE Golfers ALIAS G;

see also

CREATE DATABASE, RESTORE DATABASE, BACKUP DATABASE, CLOSE DATABASE, USE DATABASE, DROP DATABASE, MOUNT DATABASE, UNMOUNT DATABASE

---

OPEN DBMS

function

This statement opens a DBMS.

syntax

OPEN <dbms_brand> DBMS
  [ [ AS ] USER <character_literal>
  [ [ WITH ] PASSWORD <password> ] ]
  [ OPTION <character_literal> ] ';'

parameters

notes

The PrimeBase DAL software considers a DBMS to be a PrimeBase server or gateway. The OPEN DBMS statement opens a connection to a particular server or gateway anywhere on the network, or internet.

Standard DAL allows only DBMSs on the host computer to be opened. PrimeBase has no "host" concept as such. DAL compatible applications should connect to a "dummy" host called "PrimeBaseHost" when using PrimeBase DAL.

Before opening a connection using OPEN DBMS you should define a connection to the server or gateway. Connection definitions are stored in the "connect.def" file on the client workstation. Each connection definition has an alias name. The alias name of the connection must be used as the <dbms_brand> in the OPEN DBMS statement.

A PrimeBase session can have connections to multiple servers (each server is an open DBMS). These servers can be located locally, or anywhere on the network.

see also

DESCRIBE DBMS, CLOSE DBMS, USE DBMS

---

OPEN TABLE

function

This statement opens a particular table for use. The purpose of opening the table is to lock it for read-only or exclusive use.

syntax

OPEN TABLE <table_reference>
  [ FOR [ <shared_mode> ] [ <access_mode> ] ] ';'

<shared_mode> ::= SHARED | PROTECTED | EXCLUSIVE

<access_mode> ::= READONLY | UPDATE

parameters

notes

Tables need not be opened before accessing data in the table. There is no performance loss if a table is not opened before use. The main reason for using OPEN TABLE is to gain table level readonly or exclusive locks during a transaction.

OPEN TABLE is ignored unless it appears within a transaction that was explicitly started with the BEGIN statement.

Not all combinations of sharing and access modes are supported by PrimeBase. The default mode is PROTECTED UPDATE, which means that the user can read and update data concurrently with other users, but updates are not allowed to conflict.

A further two modes are supported: PROTECTED READONLY and EXCLUSIVE UPDATE. PrimeBase treats EXCLUSIVE READONLY as PROTECTED READONLY. If the user really wants exclusive access to a table, then the table must be opened for EXCLUSIVE UPDATE. In this mode the user may read or update the table, and no other user is allowed to either read or update the table.

The keyword SHARED is not supported by PrimeBase. PrimeBase automatically upgrades SHARED mode to PROTECTED mode.

All tables opened are automatically closed at the end of a transaction. This means that to continue with exclusive access to a table, an OPEN TABLE statement must be issued after every BEGIN transaction statement.

examples

Example /* ** In the following example, the table "Golfers" is ** opened for PROTECTED UPDATE. There is no need to ** explicitly enter the keywords, as PROTECTED UPDATE ** is the default setting. It means that the user can ** read and update data concurrently with other users. */ BEGIN; OPEN TABLE Golfers; CLOSE TABLE Golfers; COMMIT;

Example /* ** In the next example, the table "Golfers" is opened ** for EXCLUSIVE UPDATE. This prevents other users from ** reading or updating the table. */ BEGIN; OPEN TABLE Golfers FOR EXCLUSIVE UPDATE; CLOSE TABLE Golfers; COMMIT;

see also

CREATE TABLE, DROP TABLE, RENAME TABLE, ALTER TABLE, REORG TABLE, BACKUP TABLE, CLOSE TABLE

---

REMOVE USER

function

This statement removes users either from the database or from a specific group.

syntax

REMOVE USER <user_name> { ',' <user_name> }
  [ FROM <group_name> ] ';'

parameter

return values

notes

To use this statement, the users must be users of the default database, (i.e., the database last used or opened); the group must also be an existent group.

When a user is removed from the database, all privileges that he or she granted are also removed.

examples

Example /* ** In this example, the user, called "Heather Fyson" is ** removed from the group called "GolfersPros". She is ** still in the database - just not in the group, ** "GolfersPros". */ CREATE GROUP "GolfersPros"; ADD USER "Heather Fyson"; ADD USER "Heather Fyson" TO "GolfersPros"; REMOVE USER "Heather Fyson" FROM "GolfersPros";

see also

ADD USER, ALTER USER, ADD USER, GRANT, REVOKE, CREATE GROUP, DROP GROUP

---

RENAME <object>

function

This statement alters the name of an already existing object - such as a domain, a table, an index, etc., etc.

syntax

RENAME [ <object> ] <object_reference> TO <object_name> ';'

parameters

examples

Example /* ** In this example, the table Clubs is renamed to ** GolfClubs. */ RENAME TABLE Clubs TO GolfClubs;

---

REORG TABLE

function

This command performs a low-level reorganization of the table. The user requires exclusive update access to the table before the command may run, and if the user has not already opened the table, the exclusive access mode will be acquired automatically for him by the system. The function statement packs the data (eliminating spaces left in the data file due to previous deletions) and rebuilds the indices of the table. Only the DBA or the creator of a table may reorganize a table.

syntax

REORG TABLE <table_reference> ';'

parameters

---

RESTORE DATABASE

function

This command restores a database from backup.

syntax

RESTORE DATABASE <database_name> { <restore_options> } ';'

<restore_options> ::= FROM <expression> | <partial_restore>

<partial_restore> ::= PARTIAL |
                      UNTIL ( ERROR | LOG <log_spec> )

<log_spec>        ::= '{' <expression> ',' <expression> '}'

parameters

notes

In its simplest form, the restore command will restore any database using the previous backup and all the log files starting at the time of backup.

In order to bring a database up to date during restore, the RESTORE DATABASE command requires access to the copies made of the database tables during backup (the backup image), and all log files written since the backup began. If a log files is missing, the restore statement cannot bring the database up to date beyond this point in time.

A database may be partially restored if a missing log, or an error prevents complete restore. A database must be recovered to be restored, as the restore process is atomic. This means, that if it fails it has no effect on the current state of the database.

A database may not be in use while it is being restored.

examples

Example /* ** In this example, the database, Golfers is restored. */ CLOSE DATABASE Golfers; BACKUP DATABASE Golfers REPLACE; RESTORE DATABASE Golfers; OPEN DATABASE Golfers;

---

REVOKE

function

This statement removes specific privileges from a user or group of users.

syntax

REVOKE ( <command_privileges> | <object_privileges> )
  FROM ( PUBLIC | <user_name> { ',' <user_name> } ) ';'

parameter

examples

Example /* ** In this example, the privileges that were assigned to ** Heather Fyson are revoked. */ ADD USER "Heather Fyson"; GRANT SELECT ON Golfers (ID, Status, Name) TO "Heather Fyson"; REVOKE SELECT ON Golfers (ID, Status, Name) FROM "Heather Fyson";

see also

ADD USER, ALTER USER, ADD USER, REMOVE USER, GRANT, CREATE GROUP, DROP GROUP

---

SERVER CHECKPOINT

function

This command starts a full checkpoint of the server.

syntax

SERVER CHECKPOINT ';'

parameter

notes

The checkpoint process flushes all cache pages, and then writes a checkpoint record to the log file.

---

SERVER COMMENT

function

Display a message on the console of all connected workstations.

syntax

SERVER COMMENT <character_literal> ';'

parameter

notes

The server comment command is used to display a message on the screen of all online users. For example, the command may be used to inform the user that a database backup or reorganization is about to take place.

---

SERVER ERROR

function

This statement loads error information from the error manager into a cursor.

syntax

SERVER ERROR [ INTO <cursor> ] ';'

parameters

return values

This command returns a rowset containing information on file I/O errors that may occur during a query, or certain other commands. The rowset has the following structure.

col#	Data Type	Name	Information
1	SMINT	PrimaryError	The primary error code
2	SMINT]	SecondaryError	Additional error information
3	SMINT	SystemError	System specific error code
4	TIMESTAMP	Time	Time of error
5	VARCHAR[31]	FunctionName	Operation attempted
6	INTEGER	DevID	The device on which the error occurred
7	INTEGER	SeekPosition	Seek position of error
8	INTEGER	TransferSize	Byte transfer required
9	INTEGER	DatabaseID	The database in which the error occurred
10	VARCHAR[255]	FileName	The system file name in which the error occurred

PrimaryError is the primary error code of the error that occurred. If this is zero, no error has occurred, and in this case, all other columns will have the value NULL.

The system error is the error code provided by the system. The meaning of this error is dependent on the operating system on which the server is running.

The columns SeekPosition and TransferSize have significance depending on the value of the column FunctionName. In the table below, 'Yes' indicates that the value is significant to the operation, and '-' indicates that the value is not applicable.

FunctionName	SeekPosition	TransferSize
Read	Yes	Yes
Write	Yes	Yes
Grow	-	Yes
Seek	Yes	-
Flush	-	-
Create	-	-
Open	-	-
Delete	-	-
Rename	-	-
Make Directory	-	-
Remove Directory	-	-

The file name on which the operation occurred is given in the last column of the rowset.

notes

The statement loads the details of the most recently occurred errors from the error manager on the server. This error information is global for the entire server, and not related to an individual session. Error can be traced to the files in which they occurred using this statement.

If no errors have occurred since startup, the statement will return no rows.

---

SERVER RESTART

function

Start the normal server startup sequence, which includes recovery of all databases.

syntax

SERVER RESTART [ COLD ] [ <partial_restart> ]
  [ <restart_location_spec> ] ';'

<restart_location_spec> ::= [ IN ] LOCATION
                            [ <character_literal> ]
                            { ',' [ <character_literal> ] }

<partial_restart>       ::= [ PARTIAL ]
                            [ WITHOUT <expression>
                              { ',' <expression>} ]

parameters

notes

This statement initiates the normal server restart sequence. The normal restart sequence begins automatically when the server is started. However if this fails, the server requires the intervention of the system administrator. If a normal restart fails, the system administrator should attempt to correct the problem and manually initiate the restart sequence using this command.

Details of the error that occurred during restart can be obtained from the error manager using the TRANS ERROR command. The system error number, file name, database identifier and other details are contained in the rowset returned by this command.

The restart process includes: (a) restarting the transaction manager, and recovering the master database, (b) recovery of all user databases that are set recover pending in the master database, (c) bringing the previous active log online, and (d) setting all system parameters as recorded in the master database.

For this purpose, the server searches for the following: firstly, a restart file ('RESTART.SQL'), which indicates in which log files, and where recovery should begin. Secondly, the restart process must locate the master database (this location is also known as the server root).

Once the master database has been recovered, it is opened, and the information concerning the location of the user databases is used to recover the user databases.

In general, the keyword COLD, indicates that the actual recovery process should be skipped. Using a cold start, databases can be brought online without recovery. This should only be done if there is no way the problem that prevents recovery can be fixed, because without recovery the server cannot guarantee the integrity of the database or prevent data loss. After a cold start, a database will probably need to be reorganized. If the integrity of the system tables has been compromised, it may not be possible to open the database. In this case the only possibility is to restore the database from backup.

A partial restart (the PARTIAL keyword) is in any case better than a cold start as it tells the server to ignore errors during recovery and to do what it can. The convenience of the partial restart carries with it the problem that you will not be quite sure what was done and what not. For example, if restart was failing because of insufficient disk space, partial restart could result in significant data loss.

Cold starting the server means that all databases are cold started. This is an extremely harsh measure if the error is only occurring in one database. Using the WITHOUT clause, you may indicate to the server to leave certain databases out of the recovery process. Once the server and most of the databases are online, you may then attempt to correct the problem concerning the database. A database can be recovered separately using the RECOVER DATABASE command.

To prevent an inadvertent cold start, the server requires the administrator to remove any restart files before it will proceed with a cold start. As long as the server finds a file called 'RESTART.SQL', it will ignore the COLD keyword.

If the restart files or log files are lost of corrupted there is no other option but to cold start. As a result, and due to the fact that the restart file is vital for restoring the master database, the restart file can be duplicated by the server. The location of the restart files and the server root are stored in the server environment file ("server.env"). If this information is lost or has changed, the paths may be specified in the LOCATION clause in the SERVER RESTART statement.

For security, the restart file, 'RESTART.LOG', may be duplicated. To do this, shutdown the transaction manager with TRANS SHUTDOWN, and the restart the transaction manager with SERVER RESTART, this time specifying the two restart locations.

---

SERVER RESTORE

function

This command restores the Master database from backup.

syntax

SERVER RESTORE [ <restart_location_spec> ]
                 { <restore_options> } ';'

<restart_location_spec> ::= [ IN ] LOCATION
                            [ <character_literal> ]
                            { ',' [ <character_literal> ] }

parameters

notes

This statement restores the Master database from the most recent backup. The backup device(s) of the Master database must be online for the SERVER RESTORE statement to function correctly. When the restore is complete, only the Master database will be considered recovered. At this point a normal server restart should be attempted.

---

SERVER SHUTDOWN

function

Shutdown the server application.

syntax

SERVER SHUTDOWN [ <expression> ]
  [ COMMENT <character_literal> ] ';'

parameters

notes

On shutdown, any transactions still active are automatically rolled back. The alert that appears on the users screen, can be disabled on the client machine. If the expression is NULL the current shutdown sequence is cancelled.

---

SET VARIABLE

function

This statement sets the value of an existing database variable.

syntax

SET VARIABLE <variable_reference> '=' <expression> ';'

parameters

examples

Example /* ** In this example, the variable, "YearEndNo" is set to ** a new value. It was originally set at "01/01/1992" - ** in the CREATE VARIABLE statement. In this statement, ** it is set at "01/01/1993". */ CREATE VARIABLE DATE YearEndNo = "01/01/1992"; SET VARIABLE YearEndNo = "01/01/1993";

see also

CREATE VARIABLE, DROP VARIABLE, RENAME VARIABLE

---

TRANS ERROR

function

This statement returns details of the error that caused the transaction manager to go down.

syntax

TRANS ERROR [ INTO <cursor> ] ';'

parameters

notes

The details of the error the transaction manager to go down are returned from the error manager on the server. If the transaction manager is not down, this command will return no rows. The transaction manager goes down when it can no longer guarantee that transactions are atomic, or that committed data will be written to the database. This command can be used to determine more precisely the reason for the transaction manager going down. When the problem has been corrected, use the SERVER RESTART command to start the transaction manager.

---

TRANS RESTART

function

Start the transaction manager and recover the master database.

syntax

TRANS RESTART [ COLD | PARTIAL ]
  [ <restart_location_spec> ] ';'

<restart_location_spec> ::= [ IN ] LOCATION
                            [ <character_literal> ]
                            { ','
                              [ <character_literal> ] }

parameters

notes

This command is similar to SERVER RESTART, but performs only two steps of a normal server restart, namely: (a) the restarting the transaction manager, and recovering the master database, (b) bringing the previous active log online.

This command can be used to correct problems that occur when a user database is recovered, or when a system parameter is set. After TRANS RESTART, it is possible to open the master database and adjust parameters and databases locations.

After the transaction manager has be started, the server is effectively still in single-user mode, due to the fact that the communications will only accept remote connection after server restart. As a result, transaction restart should be followed at some stage by either a full or partial server restart.

see also

TRANS SHUTDOWN, SERVER SHUTDOWN, SERVER RESTART

---

TRANS SHUTDOWN

function

This command is used to shut down the transaction manager.

syntax

TRANS SHUTDOWN [ <expression> ] ';'

parameters

notes

While the transaction manager is shutting down, transactions can only be committed. Attempts to begin a transaction will return an error.

see also

TRANS RESTART, SERVER SHUTDOWN, SERVER RESTART

---

UNMOUNT DATABASE

function

This statement removes the link that a server has to a database. The database can then be mounted by another server, or the locations of the database can be changed and the database remounted by the server.

syntax

UNMOUNT DATABASE <database_name> ';'

return values

OK the database has been successfully unmounted.

parameters

notes

Here is an example situation: Changing the index location of a database.

1. Make sure that no users have the database open.

2. UNMOUNT the database.

3. Create a directory in the target location, and give it the same name as the database.

4. Move the index files of the database to this directory. Index files have a .ind extension.

5. You may now delete the directory in which you found the index files, if it is empty.

6. MOUNT the database giving the new index location using the INDEX clause. (The data location must also be given if it not the DataServer root path.)

examples

Example /* ** In this example the database Golfers is unmounted. */ CLOSE DATABASE Golfers; UNMOUNT DATABASE Golfers;

see also

CREATE DATABASE, RESTORE DATABASE, BACKUP DATABASE, OPEN DATABASE, CLOSE DATABASE, USE DATABASE, DROP DATABASE, MOUNT DATABASE

---

USE DATABASE

function

The USE DATABASE statement establishes a particular database as the default database.

syntax

USE DATABASE <database_alias> ';'

<database_alias> ::= <identifier>

parameters

examples

Example /* ** In this example the database Golfers is to be set ** as the default database. In OPEN DATABASE it was ** given the alias "G". */ OPEN DATABASE Golfers ALIAS G; USE DATABASE G;

see also

CREATE DATABASE, RESTORE DATABASE, BACKUP DATABASE, OPEN DATABASE, CLOSE DATABASE, DROP DATABASE, MOUNT DATABASE, UNMOUNT DATABASE

---

USE DBMS

function

This statement establishes a previously opened DBMS as the default DBMS.

syntax

USE <dbms_brand> DBMS ';'

parameters

examples

Example USE PrimeServer DBMS;

see also

OPEN DBMS, CLOSE DBMS

---

3. Identification

This section provides you with a quick reference to the syntax that is required for identifiers, aliases and references. You will encounter these procedures in almost all data definition language statements.

---

Identifiers

An identifier is a sequence of characters, that defines a database object. Like keywords, case is not significant in identifiers. Maximum length is 31 characters. An identifier is unique within the database.

Identifiers are so called because they are used to identify the many different types of objects that may be created using DAL and the PrimeBase data definition language. This includes DAL variables, procedures and cursors, PrimeBase databases, database objects (tables, domains, keys, etc.) and columns.

The following are all identifiers.

<var_name>      ::= <identifier>

<cursor>        ::= <identifier>

<database_name> ::= <identifier>

<dbms_brand>    ::= <identifier> |
                    ':' <var_name> |
                    <expression>

<object_name>   ::= <identifier> | ':' <var_name>

<column_name>   ::= <identifier> | [':'] <var_name>

<creator_name>  ::= <identifier> | ':' <var_name>

The following are object names:

<domain_name> ::= <object_name>

An identifier is a sequence of characters, defined as follows:

syntax <identifier> ::= ( <alpha> | <diac> | '_' | '$' )
                        { <alpha> | <diac> | <digit> |
                          '_' | '#' }

<alpha>             ::= 'a' | 'b' | ... | 'z' |
                        'A' | 'B' | ... | 'Z'

<diac>              ::= Upper and lower case alphabetic
                        characters with diacritical marks
                        (e.g. ä, å, à, ...)

notes

The hash ('#') symbol is one of the characters of an identifier (however, not the first character). So a column 'supplier number' can be given the name: 'P#'.

The dollar ('$' ) symbol is only allowed as the first character of an identifier. Note that identifiers starting with the character '$' are used by DAL to indicate system variables and functions.

<var_name> must be of data type OBJNAME, see 1.8 Special types in section 1. Data Types.

---

Aliases

Aliases are names that may be different to the actual name of the object that they identify. Specifying an alias name is always optional. When not specified, the actual name of the object is taken as the alias. This is due to the fact that in the context in which an alias is used, the use of an alias is not optional. For example, databases are always referred to using an alias, whether an alias is specified in the OPEN DATABASE statement or not.

Aliases can be created for three types of objects: for databases, tables, and columns.

---

Database Alias

A database alias is created in the OPEN DATABASE statement. The database alias is either the name of the database or the identifier specified in the ALIAS clause. Each open database must be specified by a unique alias. The database alias is valid until the database is closed and may be used to refer to the database and objects and columns within the database.

syntax

<database_alias> ::= <identifier>

---

Table Alias

A table alias is created in a query specification, and is only valid within the query or any subqueries.

A unique table alias is required for every table in the query specification, and is the table name by default.

syntax

<table_alias> ::= <identifier>

---

Column Alias

A column alias is created in the select list of a query specification. The column alias may then be used to make cursor based references. The column alias is valid until the cursor is deselected, or until another rowset is selected into the cursor.

syntax

<column_alias>::= <identifier>

---

References

References fully identify database objects such as domains, tables, keys, etc..

---

Object Reference

An object reference is a specification that identifies a particular database object. In its complete form, an object reference consists of the database alias, the object creator name, and the object name. If the database alias is omitted from this list, then the default database is assumed. If <creator_name> is omitted, the creator name of the user of the database specified in database alias (or the default database) is assumed. If no such object exists, then the creator names "common" and "System" will be tried.

syntax

<object_reference> ::= [ <database_alias> '!' ]
                       [ <creator_name> '.' ]
                       <object_name> | [ ':'] <var_name>

The following are also object references:

<domain_reference>  ::= <object_reference>

<table_reference>   ::= <object_reference>

<key_reference>     ::= <object_reference>

<default_reference> ::= <object_reference>

<index_reference>   ::= <object_reference>

<rule_reference>    ::= <object_reference>

<view_reference>    ::= <object_reference>

---

Column Reference

A column reference is a specification that identifies a particular column in a database. In its complete form, it consists of the table alias and the name of the column. If <table_alias> is omitted the column reference is resolved by searching all possible tables in the query. Note that ambiguous references are not reported. The user may assume that the tables in a query are searched in the order mentioned in the FROM clause, beginning with the innermost nested subquery and moving outwards.

syntax

<column_reference> ::= [ <table_alias> '.' ] <column_name>

---

Column of Table Reference

A column of table reference specifies a column within a table, where the table in which the column is situated, is different to the table mentioned in the FROM clause. In its complete form, the <column_of_table_reference> consists of the database alias, the creator name of the table, the object name, and the column name itself.

syntax

<column_of_table_reference> ::= [ <database_alias> '!' ]
                                [ <creator_name> '.' ]
                                <object_name> '.'
                                  <column_name> |
                                ':' <var_name>

---

5. System Procedures

System procedures are used to initiate an operation performed by the server. Master database specific objects such as devices, locations and partitions can be created and deleted using system procedures.

---

Syntax

Server procedures all have the same syntax, consisting of two keywords followed by parameters in parenthesis.

syntax

<identifier> <name> '(' [ <proc_input_spec> ] ')'
  [ <proc_output_spec> ] ';'

<proc_input_spec>  ::= <required_params> {
                         <optional_param> }

<required_params>  ::= <server_param> { ',' <server_param> }

<server_param>     ::= <name> '=' <expression>

<optional_param>   ::= ',' [ <name> '=' ] <expression>

<proc_output_spec> ::= <returning_variables> |
                       ( INTO <cursor> )

parameters

<identifier>	an identifying keyword.
<name>	an identifying keyword or character expression.
<proc_input_spec>	specification of procedure input.
<proc_output_spec>	procedure output specification.

notes

Server procedures define a generic syntax for services performed by the server. The actual services provided depend on the server type, and version. There are a number of possibilities regarding the output of server procedures:

1. The procedure may return no values. In this case the <proc_output_spec>, if any is ignored.

2. The procedure returns values. If a <returning_variables> clause has been specified, then the values are placed in DAL variables. Additional values are ignored. If no <returning_variables> clause was specified, the values are printed to the output stream.

3. The procedure returns a rowset. If an INTO clause is supplied, then the rowset goes to specified cursor, otherwise into the default cursor ($cursor).

In all cases, no output is generated if an error occurs during execution of the server procedure. Error details can be retrieved as all other DAL errors.

All server procedures have a set of required parameters, and a variable number of optional parameters.The required parameters must be passed in the correct order before the optional parameters. Optional parameters must be identified using the <name> = syntax of server procedures.

Optional parameters, in the case of add type procedures have default values which are used when the value is not specified. Optional parameters not specified in alter type procedures leave that particular aspect unchanged.

All names of parameters, and values given as input to system procedures are case-insensitive, unless otherwise noted.

---

Devices

A device definition is the basic requirement of the server to access data residing on permanent storage. Device descriptions tell the server the type of the device, the location within the file system of the host, and any other information required to access data stored on the device. Device descriptions are stored in the SysDevices table in the master database. The following server procedures are provided for maintaining devices:

---

Add Device

Add a device description to the SysDevices table. The device can subsequently be used to create locations, specify partitions, or locate log files. Exactly which types of devices, and which options are supported, depends on the server version, and host platform.

Required parameters:

NAME	The name of the new device.
PATH	A string identifying the location of the device within the host filing system. This path may be relative to the location of the server application. This value may be case sensitive, depending on the host file system.

Optional parameters:

TYPE	(default: FileSystem) The type of device. Determines the internal device driver used by the server.
REMOVEABLE	(default: FALSE) True if the device contains removable media.
READONLY	(default: FALSE) True if the device is read-only.
RANDOMACCESS	(default: TRUE) False if the device does not support random seek operations.
USEABLESPACE	(default: $NULL) A value in bytes that determines the maximum amount of space the server may use on the device. $NULL means the space used is only limited by physical size of the device.
ACCESSSTRING	The access string is device specific information required by the server's device driver in order to access the device.

---

Alter Device

Alter a device definition. Note that certain aspects of a device cannot be altered.

Required parameters:

ID The device identifier as it appears in the SysDevices table. When a device is added, it is automatically allocated a unique identifier by the system.

Optional parameters:

NAME	Change the name of the device.
REMOVEABLE	Determines if the device contains removable media.
READONLY	Determines if the device is read-only.
RANDOMACCESS	Indicates whether the device supports random seek operations.
USEABLESPACE	Determines how much space may be used by the server on the device.
ACCESSSTRING	Change the device specific access string.
PATH	Change the device path.

---

Remove Device

Devices cannot be removed if there are any databases/backups or logs currently located on the device. If so, an error is returned, indicating that the device is in use.

Required parameters:

ID	The device identifier.

---

Locations

Locations indicate storage and backup areas of various types of files used by the server. Current locations are searched for objects (databases, backups, log, etc.) already existing. Future locations are used when objects are created, to determine where the files should be located.

---

Add Location

Add a location that indicates the placement of various database and server control files.

Required parameters:

NAME	The name of the location.
FILETYPE	The type of file to be stored in this location. Current possibilities are: Data, Index, Blob, Log, Restart, VM.
FILEPURPOSE	Indicates the purpose of files stored (or to be stored) in the location. The following are valid purposes: CurrentStorage, FutureStorage, CurrentBackup, FutureBackup.
DEVICEID	The device on which the location resides.

Optional parameters:

GROUPNUMBER	(default: $NULL) The group number of the location. Location groups are only used for future locations. When creating new objects, if the modulus maximum group number of the group number equals the minor identifier of the new object, then the location is used to locate the new object.
ALLOCATION	(default: Automatic) Allocation determines the allocation strategy to be used by the server when creating objects using the given location. Possibilities are: Automatic: The location is automatically a candidate for the creating of new objects. Manual: The server never creates objects in this location, however it does search for existing objects in the area. Default: This location is only considered for creation of an object if there are no other candidate locations.
MEDIANUMBER	(default: $NULL) The media number of a specific media associated with the device.
COMMENTS	(default: "") Description of the location or any other user specific information.

---

Alter Location

Alter an existing location.

Required parameters:

ID	The location identifier of an existing location.

Optional parameters:

NAME	Change the name of the location.
FILETYPE	Change the type of files stored in the location.
FILEPURPOSE	The new purpose of the location.
DEVICEID	The device identifier of an existing device.
GROUPNUMBER	New group number.
ALLOCATION	Change the allocation strategy for the location.
MEDIANUMBER	The media number of a particular media associated with the device.
COMMENTS	Change the comments on the location.

---

Remove Location

Delete a location.

Required parameters:

ID	The location identifier of an existing location.

---

Partitions

Partitions divide databases and backups into various locations. Each database/backup file will be located completely within a particular partition. Types of database files are: Data, Index and Blob (Binary large objects). Server partitions are logical entities that have no physical affect on the device.

---

Add Partition

Add a partition to a particular database or backup.

Required parameters:

DATABASEID	The identifier of an existing database.
DEVICEID	The identifier of the device on which to place the partition.

Optional parameters:

BACKUPNUMBER	(default: $NULL) The backup number, if the partition is intended for a particular existing backup.
DATA	(default: $TRUE) True if data type files should be stored in this partition.
INDEX	(default: $TRUE) True if index type files should be stored in this partition.
BLOB	(default: $TRUE) True if blob type files should be stored in this partition.
MEDIANUMBER	(default: $NULL) The media number (if any) of a particular media associated with the device.
ALLOCATION	(default: Automatic) The allocation strategy used by the server when using this partition: Automatic: The location is automatically used to locate/create database files. Manual:- The server never creates files in this partition, however it does locate existing database files. Default: This partition is only used if there are no others.

---

Alter Partition

Change certain parameters of an existing partition. Changing the types of files that may be stored on a partition will not change the location of existing file on the partition. However, the server will no longer find certain files, depending on how the locations is altered.

Required parameters:

ID	The identifier of an existing partition.

Optional parameters:

DATA	Determines whether data type files can be stored in this partition.
INDEX	Determines whether index type files can be stored in this partition.
BLOB	Determines whether blob type files can be stored in this partition.
ALLOCATION	Alter the allocation method for this partition.

---

Remove Partition

Delete a partition. Deleting a partition does not delete the files on the device.

Required parameters:

ID	The identifier of an existing partition.

---

6. System Parameters

---

TransactionLimit

The transaction limit is the maximum number of transactions that the server can process concurrently. If a transaction is begun, and the server has no transactions available, the user will be returned the error 'Too many active transactions'. There should be approximately one transaction available per connection. The minimum transaction limit is 32, and the maximum is 255.

---

SystemFileLimit

The system file limit is the number of system file handles the server will use.

When the server has consumed this number of file handles, it will recycle its file handles on a least-recently-used basis. In addition to the file handles used by the server, one file handle is required to access the environment file, and one is required per 'execute file' command entered from the console.

If this value exceeds the actual number of files available to the server (as provided by the system), it is possible that users will occasionally receive the 'Too many files open' error. If this occurs, set the SystemFileLimit down by one or two files. The default is 240 on all platforms.

---

LogBufferSize

This is the size in bytes of the log buffers.

The log buffers cache the data to be written to the logs. Before the log is flushed, the contents of the log buffer is written to the log file. The log must be flushed when a transaction is committed, or when the log buffer is full. Large log buffers can improve the performance of long running transactions.

The transaction manager will allocate at most 2 log buffers, one for each online log file. The minimum size for the log buffer is 32K.

---

LogThreshold

The log threshold is the point at which a new online log is created.

It is a size in bytes. When the current active log (this is log with the highest number), reaches this size, an new log is created.

The new log becomes the active log, and the old log becomes inactive. Transaction always begin writing to the active log. Transactions cannot change the log to which they write. This means that transactions that began when the now inactive log was active continue to write to the log when it becomes inactive.

Both the active and the inactive logs are called the 'online logs'. When the inactive log has no more transaction writing to it, it is taken offline.

An offline log is no longer required by the dataserver for recovery purposes, and if the offline log function is set to 'delete', then it will be deleted by the server. If the inactive log grows to 150% of its threshold it is forced offline. Transactions still writing to the inactive log when this occurs are aborted, and rolled back.

---

CheckpointThreshold

After CheckpointThreshold bytes have been written to the log, the server writes a checkpoint record to the log.

The more frequently a checkpoint is written, the less time the server takes to restart after the server application was unexpectedly quit. The time taken to restart, however also depends on the size of the server disk cache memory (CacheSize).

The more information cached when the server was quit, the more time required to restart. Writing a checkpoint record does not take much time.

---

CacheSize

Cache size is the maximum amount of memory, in bytes, used by the server to cache records read from disk.

Increasing the cache size improves the performance of the server, but may also increase time required to restart the server if the server is not shutdown correctly.

Cache memory is taken from the total memory allocated (or available) to the server. If set too high, insufficient memory may remain for the correct operation of other parts of the server.

To be safe about 256K should be available per connection, after cache memory and virtual cache memory have been subtracted from total available memory.

---

VirtualCacheSize

Virtual cache size is the maximum amount of memory in bytes used by the virtual memory manager.

When the virtual memory manager has no more physical memory available to it, it begins to swap data out to disk. The servers virtual memory system is used to store intermediate result sets during the execution of queries. Data being sorted is also maintained in server virtual memory.

Certain queries will execute much faster when enough physical memory is available to the virtual memory manager.

---

OfflineFunction

This variable determines what happens to the inactive log when it is moved offline. There are only two permissible values: 'Delete' and 'Archive'. After installation, the offline log function is set to 'Delete'. Offline logs are only required to restore a database from backup. If no backups have been made, the offline logs are not required. In this case, the offline log function can be set to 'Delete', which causes the server to automatically delete logs as they are moved offline. If the log function is set 'Archive', the offline logs are moved to the log archive location. If the server requires a log when restoring a database, it looks in the location in which the log was archived.

---

DataServerName

This variable contains the name of the server used by client applications to access the server.

This name is published, and is visible over the network on protocols such as ADSP and PPC. When set, the server changes the network visible name immediately. Connected clients are not affected by change of the server name.

---

ConnectionLimit

The connection limit is the current number of connections permitted by the server.

This number may range from zero to ConnectionTotal (see below). Increasing this variable immediately makes more connections available for client. If the connection limit is zero, the server is not published (is not visible) over the network.

Decreasing the value of connection limit will decrease the number of connection available to clients. If the number is decreased below the number of clients currently connected to the server, some of the client connections will be terminated by the server.

---

ConnectionTotal

This variable contains the maximum number of connections permitted according to the registration license of the server.

The connection limit cannot be set to a value greater than the connection total. Connection total must be set to the number of connections permitted according to the license granted to use the server.

---

SerialNumber

The serial number of the server. This value must be provided upon installation.

---

ActivationKey

A valid activation key is required to register a server.

Without a valid activation key the server runs in demonstration mode. In demonstration mode the server shuts down after 2 hours of operation.

---

ExpiryDate

The expiry date of the server determines how long the server will run as a registered server.

Setting this variable to $NULL indicates that there is no expiry date. After the expiry date, the server runs in demonstration mode. Before the expiry date, the server runs as a registered server.

---

IdentificationString

The identification string contains the characteristics of an installed server that are required for registration.

The value of this variable, along with the serial number must be sent to PrimeBase Systems GmbH in order to register a server.

---

InitialMemoryBlockSize

The initial memory block size is the size of the initial memory block allocated by the server in bytes. This is one of three variables used to control the amount of memory used by the server.

Memory is allocated by the server from the system in blocks. The memory in these blocks is then managed by the server itself. The server allocates memory from these blocks using a very fast best-fit algorithm. This memory management is faster and more efficient than any operating system memory management we have tested so far.

When the server if finished using the memory in a block it frees the memory to the system. The size indicated by this variable is the size of the initial block of memory allocated by the server. It may be larger the subsequent blocks allocated. In addition, the initial memory block is never freed to this system while the server is running. This block is managed like all other blocks, and therefore must be calculated as part of the block total (see below). Setting this variable has no immediate effect on the size of the initial memory block. Only when the server is started up again, will the new initial memory block size be used.

You should set this variable to the minimum amount of memory you wish the server to use. On the Macintosh, make sure that enough memory is allocated to the server application that the server can allocate the initial memory block. The server will not start if it cannot allocate this block.

By setting this variable to NULL, you indicate to the server that the memory variable should be automatically configured. On the Macintosh this is the optimal setting, as the servers memory parameters are then automatically set according to the memory limit given to the server application in the finder. Under UNIX the automatic memory configuration causes the system to allocate memory until the system says there is no more.

---

MemoryBlockSize

This is the size, in bytes, of all blocks of memory allocated by the server from the system besides the initial memory block (which has size InitialMemoryBlockSize). Changing this variable has no affect on blocks already allocated, but subsequent blocks will be allocated using the new size. The server only allocates memory blocks when cannot find a free segment of memory of the required size amongst the blocks that it already has. If the segment of memory that the server wishes to allocate exceeds the size of memory blocks the server will try to allocate a block of the required size from the system. This means that MemoryBlockSize is, in fact, a minimum block size. As a rule, however, the server does not require memory segments of much larger than 64K. One exception to this are the log buffers, whose size may be set by the system administrator (see LogBufferSize).

---

MemoryBlockTotal

This is the total number of memory blocks (including the initial memory block) that may be allocated by the server. After the server has allocated this number of blocks, the server will report an 'Out of Memory' error. if the system does not allow the server to allocate all its memory blocks, the server may report a 'out of Memory' error sooner. The maximum amount of memory used by the server can be calculated as:

MemTot = InitialMemoryBlockSize + MemoryBlockSize * (MemoryBlockTotal - 1)

Under UNIX it is recommended that MemTot be set such that when allocated, all this memory will reside in physical RAM. The server has its own virtual memory management scheme, and as a result it is better if the memory actually used by the server is real and not virtual memory.

---

Appendix A: System Database

---

A.1 Model Database

---

Domains

CREATE DOMAIN System.ObjectType CHAR(4), ORDER NOT APPLICABLE AS CASE INSENSITIVE; /* 1 */
CREATE DOMAIN System.ObjectID INTEGER;
CREATE DOMAIN System.DBObjectID (ObjectType, ObjectID);

CREATE DOMAIN System.ColumnID SMINT;
CREATE DOMAIN System.DBColumnID (ObjectType, ObjectID, ColumnID);

CREATE DOMAIN System.ComponentID SMINT;
CREATE DOMAIN System.DBComponentID (ObjectType, ObjectID, ComponentID);

CREATE DOMAIN System.DBColCompID (ObjectType, ObjectID, ColumnID, ComponentID);

CREATE DOMAIN System.SysName VARCHAR(31), ORDER NOT APPLICABLE AS CASE INSENSITIVE;
CREATE DOMAIN System.DBName (SysName, SysName);

CREATE DOMAIN System.UserID INTEGER;
CREATE DOMAIN System.DataType SMINT;  /* 12 */

CREATE DOMAIN System.UserType CHAR(3), ORDER NOT APPLICABLE;
CREATE DOMAIN System.UGID (UserID, UserID);
CREATE DOMAIN System.KeyAction CHAR(4), ORDER NOT APPLICABLE;
CREATE DOMAIN System.PrivilegeID (ObjectType, ObjectID, UserID, UserID);
CREATE DOMAIN System.ColPrivID (ObjectType, ObjectID, ColumnID, UserID, UserID);

---

Tables

CREATE TABLE System.SysUsers
(
  ID UserID NOT NULL,
  Name SysName NOT NULL,
  CreatorID UserID NOT NULL,
  CreationTime TIMESTAMP NOT NULL,
  UserType UserType NOT NULL,
  Resource BOOLEAN NOT NULL,
  CreatorName SysName NOT NULL,
  Password VARCHAR(11) NULL,
  AbortTimeout INTEGER NULL,
  LoginCnt INTEGER NOT NULL,
  LastLogin TIMESTAMP NULL,
  OnlineTime INTEGER NOT NULL
);

CREATE TABLE System.SysMembers
(
  UserID UserID  NOT NULL,
  GroupID UserID  NOT NULL,
  UGID (UserID, GroupID) UGID
);

CREATE TABLE System.SysDataTypes
(
  DataType DataType NOT NULL,
  Name SysName NOT NULL,
  Scale BOOLEAN NOT NULL,
  Length BOOLEAN NOT NULL,
  Size SMINT NULL,
  Comments VARCHAR(120) NULL
);

CREATE TABLE System.SysObjects
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  CreatorName SysName NOT NULL,
  Name SysName NOT NULL,
  CreatorID UserID NOT NULL,
  CreationTime TIMESTAMP NOT NULL,
  Comments VARCHAR(120) NULL,
  DBID (Type, ID) DBObjectID,
  DBName (CreatorName, Name) DBName
);

CREATE TABLE System.SysDomains
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  Primary BOOLEAN NOT NULL,
  PrimaryKeyCnt SMINT NOT NULL,
  ComponentCnt SMINT NOT NULL,
  DataType DataType NULL,
  Scale SMINT NULL,
  Length INTEGER NULL,
  Nulls BOOLEAN NULL,
  Arithmetic BOOLEAN NULL,
  Ordered BOOLEAN NULL,
  SequenceType ObjectType NULL,
  SequenceID ObjectID  NULL,
  DBID (Type, ID) DBObjectID,
  DBSequenceID (SequenceType, SequenceID) DBObjectID
);

CREATE TABLE System.SysDomainComps
(
  Type ObjectType NOT NULL,
  ID ObjectID  NOT NULL,
  ComponentID ComponentID NOT NULL,
  DataType DataType  NULL,
  Scale SMINT NULL,
  Length INTEGER NULL,
  DomainType ObjectType NULL,
  DomainID ObjectID  NULL,
  DBID (Type, ID) DBObjectID,
  DBComponentID (Type, ID, ComponentID) DBComponentID,
  DBDomainID (DomainType, DomainID) DBObjectID
);

CREATE TABLE System.SysTables
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ColumnCnt SMINT NOT NULL,
  CompColCnt SMINT NOT NULL,
  FileName CHAR(8) NULL,
  CheckPending BOOLEAN NOT NULL,
  ReorgPending BOOLEAN NOT NULL,
  BackupPending BOOLEAN NOT NULL,
  RowCnt INTEGER NULL,
  AverageDirTime SMFLOAT NULL,
  TotalDirTime SMFLOAT NULL,
  DirectCnt INTEGER NULL,
  AverageSeqTime SMFLOAT NULL,
  TotalSeqTime SMFLOAT NULL,
  SequentialCnt INTEGER NULL,
  DBID (Type, ID) DBObjectID
);

CREATE TABLE System.SysColumns
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ColumnID ColumnID NOT NULL,
  Name SysName NOT NULL,
  Title VARCHAR(64) NULL,
  ComponentCnt SMINT NOT NULL,
  Comments VARCHAR(120) NULL,
  DomainType ObjectType NULL,
  DomainID ObjectID  NULL,
  Nulls BOOLEAN NULL,
  DataType DataType  NULL,
  Scale SMINT NULL,
  Length INTEGER NULL,
  DistinctValCnt  INTEGER NULL,
  SearchCnt INTEGER NULL,
  DBID (Type, ID) DBObjectID,
  DBColumnID (Type, ID, ColumnID) DBColumnID,
  DBDomainID (DomainType, DomainID) DBObjectID
);

CREATE TABLE System.SysColumnComps
(
  Type ObjectType NOT NULL,
  ID ObjectID  NOT NULL,
  ColumnID ColumnID  NOT NULL,
  ComponentID ComponentID NOT NULL,
  ObjectType ObjectType NOT NULL,
  ObjectID ObjectID  NOT NULL,
  CompColID ColumnID  NOT NULL,
  DBColumnID (Type, ID, ColumnID) DBColumnID,
  DBComponentID (Type, ID, ColumnID, ComponentID) DBColCompID,
  DBCompColID (ObjectType, ObjectID, CompColID) DBColumnID
);

CREATE TABLE System.SysKeys
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  TableType ObjectType NOT NULL,
  TableID ObjectID NOT NULL,
  ColumnID ColumnID NOT NULL,
  KeyType CHAR(2) NOT NULL,
  ReferenceCnt SMINT NULL,
  UpdateAction KeyAction NULL,
  DeleteAction KeyAction NULL,
  DBID (Type, ID) DBObjectID,
  DBTableID (TableType, TableID) DBObjectID,
  DBColumnID (TableType, TableID, ColumnID) DBColumnID
);

CREATE TABLE System.SysReferences
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ComponentID ComponentID NULL,
  TableType ObjectType NULL,
  TableID ObjectID NULL,
  DBID (Type, ID) DBObjectID,
  DBComponentID (Type, ID, ComponentID) DBComponentID,
  DBTableID (TableType, TableID) DBObjectID
);

CREATE TABLE System.SysDefaults
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ObjectType ObjectType NOT NULL,
  ObjectID ObjectID NOT NULL,
  ColumnID ColumnID NULL,
  DefaultText VARCHAR(512) NOT NULL,
  DefaultType CHAR(3) NOT NULL,
  Literal VARBIN(128) NULL,
  SerialType ObjectType NULL,
  SerialID ObjectID NULL,
  DBID (Type, ID) DBObjectID,
  DBObjectID (ObjectType, ObjectID) DBObjectID,
  DBColumnID (ObjectType, ObjectID, ColumnID) DBColumnID,
  DBSerialID (SerialType, SerialID) DBObjectID
);

CREATE TABLE System.SysRules
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ObjectType ObjectType NOT NULL,
  ObjectID ObjectID NOT NULL,
  RuleText VARCHAR(1024) NOT NULL,
  RuleBinary VARBIN(1024) NOT NULL,
  DBID (Type, ID) DBObjectID,
  DBObjectID (ObjectType, ObjectID) DBObjectID
);

CREATE TABLE System.SysViews
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ColumnCnt SMINT NOT NULL,
  CompColCnt SMINT NOT NULL,
  Updateable BOOLEAN NOT NULL,
  UpdateCheck BOOLEAN NOT NULL,
  ViewText VARCHAR(12228) NOT NULL,
  ViewBinary VARBIN(16384) NOT NULL,
  BuildLastTime TIMESTAMP NULL,
  BuildFrenquency INTEGER NULL,
  BuildCnt INTEGER NULL,
  BuildTime INTEGER NULL,
  BuildRowCnt INTEGER NULL,
  UsageCnt INTEGER NULL,
  DBID (Type, ID) DBObjectID
);

CREATE TABLE System.SysIndices
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ObjectType ObjectType NOT NULL,
  ObjectID ObjectID NOT NULL,
  ComponentCnt SMINT NOT NULL,
  IndexType CHAR(3) NOT NULL,
  EqUsageCnt INTEGER NULL,
  EqAvgElements INTEGER NULL,
  EqAvgSearchTime SMFLOAT NULL,
  EqAvgReductionPh SMFLOAT NULL,
  MiUsageCnt INTEGER NULL,
  MiAvgElements INTEGER NULL,
  MiAvgSearchTime SMFLOAT NULL,
  MiAvgReductionPh SMFLOAT NULL,
  SiUsageCnt INTEGER NULL,
  SiAvgElements INTEGER NULL,
  SiAvgSearchTime SMFLOAT NULL,
  SiAvgReductionPh SMFLOAT NULL,
  DBID (Type, ID) DBObjectID,
  DBObjectID (ObjectType, ObjectID) DBObjectID
);

CREATE TABLE System.SysIndexComps
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  ComponentID ComponentID NOT NULL,
  TableType ObjectType NOT NULL,
  TableID ObjectID NOT NULL,
  ColumnID ColumnID NOT NULL,
  DBID (Type, ID) DBObjectID,
  DBComponentID (Type, ID, ComponentID) DBComponentID,
  DBTableID (TableType, TableID) DBObjectID,
  DBColumnID (TableType, TableID, ColumnID) DBColumnID
);

CREATE TABLE System.SysVariables
(
  Type ObjectType NOT NULL,
  ID ObjectID NOT NULL,
  VariableText VARCHAR(2048) NOT NULL,
  VariableType CHAR(3) NOT NULL,
  DataType DataType NULL,
  Scale SMINT NULL,
  Length INTEGER NULL,
  Value VARBIN(2048) NULL,
  DBID (Type, ID) DBObjectID
);

CREATE TABLE System.SysPrivileges
(
  ObjectType ObjectType NULL,
  ObjectID ObjectID NULL,
  GrantorID UserID NULL,
  GranteeID UserID NULL,
  CanInsert BOOLEAN NOT NULL,
  CanDelete BOOLEAN NOT NULL,
  CanSelect BOOLEAN NOT NULL,
  CanUpdate BOOLEAN NOT NULL,
  CanReference BOOLEAN NOT NULL,
  CanExecute BOOLEAN NOT NULL,
  GrantInsert BOOLEAN NOT NULL,
  GrantDelete BOOLEAN NOT NULL,
  GrantSelect BOOLEAN NOT NULL,
  GrantUpdate BOOLEAN NOT NULL,
  GrantReference BOOLEAN NOT NULL,
  GrantExecute BOOLEAN NOT NULL,
  DBID (ObjectType, ObjectID) DBObjectID,
  PrivilegeID (ObjectType, ObjectID, GrantorID, GranteeID) PrivilegeID
);

CREATE TABLE System.SysColumnPrivs
(
  ObjectType ObjectType NULL,
  ObjectID ObjectID NULL,
  ColumnID ColumnID NULL,
  GrantorID UserID NULL,
  GranteeID UserID NULL,
  CanSelect BOOLEAN NOT NULL,
  CanInsert BOOLEAN NOT NULL,
  CanUpdate BOOLEAN NOT NULL,
  GrantSelect BOOLEAN NOT NULL,
  GrantInsert BOOLEAN NOT NULL,
  GrantUpdate BOOLEAN NOT NULL,
  DBColumnID (ObjectType, ObjectID, ColumnID) DBColumnID,
  ColPrivID (ObjectType, ObjectID, ColumnID, GrantorID, GranteeID) ColPrivID
);

---

Appendix B: Error Codes

---

B.1 Data Definition Errors

All data definition and data manipluation errors can be trapped by the standard DAL ERRORCTL statement. Set ERRORCTL 1; in your program if you wish to handle any of these errors in you DAL program.

Where DAL compatible errors are used, the DAL defined macro has been given.

---

Database related errors

-12040 Unknown database.

-12039 Database already exists.

-12038 Database is currently in use.

-12037 Database cannot be opened, restore/recover pending.

-12036 Maximum number of open databases exceeded.

---

Database alias related errors

-10211 (CEDBOPEN) There is no database open.

-923 (CENOCON) Database with alias '%s' does not exist.

-12035 Database alias '%s' already in use.

---

Database objects

-601 (CEEXISTS) Database object '%s' already exists.

-12034 Unknown database object.

-12033 Table is currently in use.

---

Database users and groups:

-12032 Unknown user.

-12031 Unknown group.

-12030 A user with this name already exists.

-12029 A group with this name already exists.

-12028 The user is already a member of this group.

---

B.2 Data Manipulation Errors

Error that can be specifically trapped (and are of particular interrest) during data manipulation.

-407	(CENONNUL) NULL assigned to NON-NULL column.
-803	(CEDUPROW) nsert or update would create duplicate row in a unique key column.
-10105	(CELOCK) The requested lock cannot be granted as a conflicting lock is already held by another transaction. This result is only possible if the transaction has a wait time (the time to wai for a lock) of anything less than 'infinity', and is only returned after the specified wait time.
-913	(CETXFAIL) The calling transaction has been rolled back due to deadlock with one other transaction (only) mutual deadlock can be detected). This result is only possible if wait time is set to a value greater than zero.
-12060	Rule violation.

---

B.3 Privilege Violations

---

Primary errors

-551	(CEOBJPRV) Object level privilege violation on %s.
-552	(CEOPNPRV) Command level privilege violation.
-922	(CECONAUTH) Database access denied, unknown user.
-12100	Database access denied, invalid password.
-12099	Column level privilege violation.

---

Secondary errors

-12080 Insert privilege required on %s.

-12079 Select privilege required on %s.

-12078 Delete privilege required on %s.

-12077 Update privilege required on %s.

-12076 Reference privilege required on %s.

-12075 Grant privilege required on %s.

-12074 DBA privileges required.

-12073 DBA privileges or object creator required.

-12072 Resource privileges required.

-1207 SA privileges required.

-12070 Invalid privilege required for %s.

---

B.4 Calculation and Conversion Errors

Errors that may occur during calculations and conversions.

-413 (CECNVOFL) Size overflow in conversion.

-10026 (CEUFLOW) Conversion underflow.

-10002 (CEDATLIT) Invalid date/time litera.

-103 (CENUMLIT) Invalid numeric literal.

---

Invalid literal (string) values in conversion

-12140 Invalid boolean literal.

-12139 Invalid decimal literal.

-12138 Invalid real value.

-12137 Date/time value out of range in conversion.

-12136 Invalid conversion of negative value to unsigned.

-12135 Binary value size mismatch in conversion.

-12123 Binary value is not a valid decimal number.

---

String to floating point conversion errors

-12134 Floating point value is not a number (NAN).

-12133 Floating point value is positive inifinity.

-12132 Floating point value is negitive inifinity.

---

Invalid conversions

-12131	Conversion between given types is not possible.
-12130	Illegal type in conversion (unknown or unsupported type in conversion). This includes: WORD_4, REAL_10/12 (sometimes), LONG...)

---

Error in calculations

-12129 Date/time calculation error.

-12128 Date calculation error

-12127 Time calculation error.

-12126 Size overflow.

-12125 Size underflow

-12124 Divide by zero.

---

B.5 Trapable Programmer Errors

These errors can occur due to a programmer error, but some programmers may be interested in handling them

-12160 Cannot open file for execution.

-12154 File not found.

-12153 Bad file handle.

---

Symbol related errors

-10004 (CEUNDEF) Symbol was not previously declared.

-10016 (CENOVAL) Symbol used before given an initial value.

-12158 A system variable was assigned an illegal value.

---

Cursor related errors

-10020	CENOQRY) The specified cursor cannot be used, it is inactive.
-10202	(CEROWNR) Absolute or relative cursor move not in rowset.
-508	(CECURROW) Current row of cursor is invalid in CURRENT OF reference.
-10021	(CENOFTCH) No fetch done yet (no current row).
-10022	(CEMXFTCH) Fetch beyond last (no current row).
-12156	No such column alias exists for the given cursor
-12155	Cursor column ordinal number out of range.

---

Connection related errors

-12152 No connection has been opened.

-12151 Multiple connections specified in one statement.

---

Appendix C: Golfers Database

In the reference sections of this manual, the golfers database is used to illustrate the various syntax possibilities of each command. A description of the tables in the golfers database is given in the PrimeBase DAL Reference, followed by the Create Script and the Data Insertion Script of the database.

---

Appendix D: Accessing PrimeBase SQL Database Server through Firewalls

In order to access a PrimeBase SQL Database Server instance through a firewall you have to open your firewall on the 'Server's TCP port number' bidirectionally.

This opening must be bidirectional, because communication between PrimeBase SQL Database Server and the PrimeBase clients is bidirectional.

The TCP port number used by PrimeBase SQL Database Server is determined by either the name of the server, which is used to calculate the TCP port number if the name is not a valid TCP port number, or by setting the PrimeBase SQL Database Server name to the TCP port number you wish to use.

WARNING: Since the TCP port number is determined by the server name (unless the server's name is a valid TCP port number), the TCP port number will change when you change the server name, thus you have to update the configuration of your firewall accordingly.

To find out the TCP port number, on the Macintosh GUI version of PrimeBase SQL Database Server open a new session from the menu 'Session', login and enter '#status' and press ENTER on the keypad.

On the 'Console Server' version of PrimeBase SQL Database Server on the Macintosh simply enter '#status' and press ENTER on the keypad.

On Unix and Windows enter '#status' on the server console (started with 'console' on Unix and 'console.exe' on Windows) and press ENTER.

Look for the line starting with 'Server's TCP port number:'. The trailing number is the TCP port number used by this instance of PrimeBase SQL Database Server.

The default TCP port number calculated from the default server name 'PrimeServer' is 50435.

Remember to open the firewall in both directions.

Contact us, or check out our Web site for further information, pricing and availability of the latest releases of our products.

e-mail: info@primebase.net

www: http://www.primebase.net

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.