Developer Reference > Data Access Methods > SQL Engine Reference > SQL Overview
SQL Overview
The following topics present an overview of SQL and provide details on Zen support for SQL.
You can also go to SQL Syntax Reference to look up specific SQL grammar supported by Zen.
Working with SQL in Zen
Structured Query Language (SQL) uses Englishlike statements to perform database operations. Both the American National Standards Institute (ANSI) and IBM have defined SQL standards. The IBM standard is Systems Application Architecture (SAA). Zen implements most features of both ANSI SQL and IBM SAA SQL and provides extensions that neither standard specifies. The following table lists the SQL statements that you can create in Zen and the tasks you can accomplish using each type of statement.
SQL Statement Type
Tasks
Data Definition
Create, modify, and delete tables.
Create and delete views.
Create and delete indexes.
Create and delete stored SQL procedures.
Create and delete triggers.
Create and delete user-defined functions.
Data Manipulation
Retrieve, insert, update, and delete data in tables.
Define transactions.
Define and delete views.
Execute stored SQL procedures.
Execute triggers.
Data Control
Enable and disable security for a dictionary.
Create and delete users.
Add and drop users from groups.
Change user passwords.
Grant and revoke table access rights.
The rest of this topic gives general information about each type of SQL statement. For detailed information about each statement, see SQL Syntax Reference.
Note:  Most SQL editors do not use statement delimiters to execute multiple statements, but SQL Editor in ZenCC requires them. To execute the examples here in other environments, you may need to remove the pound sign or semicolon separators.
Data Definition Statements
Data definition statements let you specify the characteristics of your database. When you execute data definition statements, Zen stores the description of your database in a data dictionary. You must define your database in the dictionary before you can store or retrieve information.
Zen allows you to construct data definition statements to do the following:
• Create, modify, and delete tables.
• Create and delete views.
• Create and delete indexes.
• Create and delete triggers.
• Create and delete stored procedures.
• Create and delete user-defined functions.
The following topics briefly describe the SQL statements associated with each of these tasks. For general information about defining the characteristics of your database, see Zen Programmer’s Guide.
Creating, Modifying, and Deleting Tables
You can create, modify, and delete tables from a database using the following SQL statements.
CREATE TABLE
Defines a table and optionally creates the corresponding data file.
ALTER TABLE
Changes a table definition. With an ALTER TABLE statement, you can perform such actions as add a column to the table definition, remove a column from the table definition, change column data type or length (or other characteristics), add or remove a primary key or a foreign key, and associate the table definition with an different data file.
DROP TABLE
Deletes a table from the data dictionary and optionally deletes the associated data file.
Creating and Deleting Views
You can create and delete views from a database using the following SQL statements.
CREATE VIEW
Defines a new view.
DROP VIEW
Deletes a view.
Creating and Deleting Indexes
You can create and delete indexes from a database using the following SQL statements.
CREATE INDEX
Defines a new index (a named index) for an existing table.
DROP INDEX
Deletes a named index.
Creating and Deleting Triggers
You can create and delete triggers from a database using the following SQL statements.
CREATE TRIGGER
Defines a trigger for an existing table.
DROP TRIGGER
Deletes a trigger.
Zen provides additional SQL control statements, which you can only use in the body of a trigger. You can use the following statements in triggers.
BEFORE
Defines the trigger execution before the INSERT, UPDATE, or DELETE operation.
AFTER
Defines the trigger execution after the INSERT, UPDATE, or DELETE operation.
Creating and Deleting Stored Procedures
A stored procedure consists of statements you can precompile and save in the dictionary. To create and delete stored procedures, use the following SQL statements.
CREATE PROCEDURE
Stores a new procedure in the data dictionary.
DROP PROCEDURE
Deletes a stored procedure from the data dictionary.
Zen provides additional SQL control statements, which you can only use in the body of a stored procedure. You can use the following statements in stored procedures.
IF...THEN...ELSE
Provides conditional execution based on the truth value of a condition.
LEAVE
Continues execution by leaving a block or loop statement.
LOOP
Repeats the execution of a block of statements.
WHILE
Repeats the execution of a block of statements while a specified condition is true.
Creating and Deleting User-Defined Functions (UDF)
In addition to the built-in functions, Zen allows you to create your own user-defined functions (UDFs) and use them in SQL queries.
A user-defined function is a database object that encapsulates one or more SQL statements that can be reused. A user-defined function takes zero or more input arguments and evaluates a scalar return value.
User-defined functions are defined within the context of a database. Successful execution of a CREATE FUNCTION statement stores the UDF definition in the database where it was executed. The UDF can then be modified, invoked, or deleted.
A UDF can use one or more SQL statements to output a scalar value of the data type in the RETURNS clause of its CREATE FUNCTION statement. For a list of supported data types, see Supported Scalar Input Parameters and Returned Data Types.
To create and delete user-defined functions, use the SQL statements listed in the following table.
CREATE FUNCTION
Creates a scalar user-defined function in the database.
DROP FUNCTION
Deletes a scalar user-defined function from the database.
Data Manipulation Statements
Data manipulation statements let you access and modify the contents of your database. Zen allows you to construct data manipulation statements to do the following:
• Retrieve data from tables.
• Modify data in tables.
• Define transactions.
• Create and delete views.
• Execute stored procedures.
• Execute triggers.
The following sections briefly describe the SQL statements associated with each of these tasks.
Retrieving Data
All statements you use to retrieve information from a database are based on SELECT.
SELECT
Retrieves data from one or more tables in the database.
When you create a SELECT statement, you can use various clauses to specify different options in retrieving data. The following table lists the types of clauses used in a SELECT statement.
FROM
Specifies the tables or views from which to retrieve data.
WHERE
Defines search criteria that qualify the data a SELECT statement retrieves.
GROUP BY
Combines sets of rows according to the criteria you specify and allows you to determine aggregate values for one or more columns in a group.
HAVING
Allows you to limit a view by specifying criteria that the aggregate values of a group must meet.
ORDER BY
Determines the order in which Zen returns selected rows.
In addition, you can use the UNION keyword to obtain a single result table from multiple SELECT queries.
Modifying Data
The following table gives statements to add, change, or delete data from tables and views.
INSERT
Adds rows to one or more tables or a view.
UPDATE
Changes data in a table or a view.
DELETE
Deletes rows from a table or a view.
When you create a DELETE or UPDATE statement, you can use a WHERE clause to define search criteria that restrict the data upon which the statement acts.
Creating and Deleting Views
You can create and delete views using the following SQL statements.
CREATE VIEW
Defines a database view and stores the definition in the dictionary.
DROP VIEW
Deletes a view from the data dictionary.
Executing Stored Procedures
A stored procedure consists of statements that you can precompile and save in the dictionary. To execute stored procedures, use the following SQL statements.
CALL or EXEC[UTE]
Recalls a previously compiled procedure and executes it.
Executing System Stored Procedures
A system stored procedure helps you accomplish those administrative and informative tasks that are not covered by the Data Definition Language. The system stored procedures have a psp_ prefix. To execute system stored procedures, use the following SQL statements.
CALL or EXEC[UTE]
Recalls a system stored procedure and executes it.
For more details, see System Stored Procedures.
Executing Triggers
A trigger consists of statements you can precompile and save in the dictionary. Triggers are executed automatically by the engine when the specified conditions occur.
Data Control Statements
Data control statements let you define security for your database. When you create a dictionary, no security is defined for it until you explicitly enable security for that dictionary. Zen allows you to construct data control statements to do the following:
• Enable and disable security.
• Create and delete users and groups.
• Add and drop users from groups and change user passwords.
• Grant and revoke rights.
Note:  If you have a Btrieve owner name set on a file that is a table in a secure database, the Master user of the database must include the owner name in any GRANT statement to give permissions on the table to any user, including the Master user.
The following sections briefly describe the SQL statements associated with each of these tasks.
Enabling and Disabling Security
You can enable or disable security for a database by issuing the following statement.
SET SECURITY
Enables or disables security for the database and sets the Master password.
Creating and Deleting Users and Groups
You can create or delete users and user groups for the database using the following SQL statements.
ALTER USER
Rename a user or change a password.
CREATE USER
Creates a new user with or without a password or membership in a group.
DROP USER
Delete a user.
ALTER GROUP
Adds users to a group. Drops users from a group.
CREATE GROUP
Creates a new group of users.
DROP GROUP
Deletes a group of users.
GRANT LOGIN TO
Creates users and passwords, or adds users to groups.
REVOKE LOGIN FROM
Removes a user from the dictionary.
Granting and Revoking Rights
You can assign or remove rights from users or groups by issuing the following statements.
GRANT (access rights)
Grants a specific type of rights to a user or a group. The rights you can grant with a GRANT (access rights) statement are All, Insert, Delete, Alter, Select, Update, and References.
GRANT CREATETAB TO
Grants the right to create tables to a user or a group.
REVOKE (access rights)
Revokes access rights from a user or a group.
REVOKE CREATETAB FROM
Revokes the right to create tables from a user or a group.
Zen Metadata
The Zen relational interface supports two versions of metadata, referred to as version 1 or V1 and version 2 or V2.
Metadata version is a property of the database that you specify when you create a database. V1 metadata is the default. When you create a database, you must specify V2 metadata if you want that version.
Metadata version applies to all data dictionary files (DDFs) within that database. A single database cannot use some DDFs with V1 metadata and others with V2 metadata. DDFs from the two versions cannot interact.
The database engine can, however, concurrently access multiple databases and each database can use either V1 metadata or V2 metadata.
All databases created with Zen versions before PSQL v10 use V1 metadata. A database created in PSQL v10 or later may use either metadata version depending on the setting at the time of database creation.
Comparison of Metadata Versions
Version 2 metadata allows for many identifier names to be up to 128 bytes long. See Relational Engine Limits for additional information. In addition, V2 metadata allows for permissions on views and stored procedures. See Permissions on Views and Stored Procedures.
DDF names for V2 metadata differ from those for V1. V2 DDFs contain additional fields and changes to V1 fields. See System Tables.
Relational Engine Limits
The following table shows the limits or conditions that apply to features of the Relational Engine. A Zen database may contain four billion objects in any valid combination. The objects are persisted in the data dictionary files.
See also Naming Conventions in Zen Programmer’s Guide.
Zen Feature
Limit or Condition
Metadata
 
 
V1
V2
Arguments in a parameter list for a stored procedure
300
X
X
CHAR column size
8,000 bytes1
X
X
Character string literal
See String Values.
X
X
Columns in a table
1,536
X
X
Columns allowed in a trigger or stored procedure
300
X
X
Column name2
20 bytes
X
 
128 bytes
 
X
Column size
2 GB
X
X
Correlation name
Limited by memory
X
X
Cursor name
18 bytes
X
X
Database name2
20 bytes
X
X
Database sessions
Limited by memory
X
X
Data file path name
64 bytes (the maximum length of the data file path name is a combination of Xf$Loc path and the data file path)
X
 
250 bytes (the maximum length of the data file path name is a combination of Xf$Loc path and the data file path)
 
X
Function (user-defined) name2
30 bytes
X
 
128 bytes
 
X
Group name2
30 bytes
X
 
128 bytes
 
X
Index name2
20 bytes
X
 
128 bytes
 
X
Key name2
20 bytes
X
 
128 bytes
 
X
Label name
limited by memory
X
X
NCHAR column size
4,000 UCS-2 units (8,000 bytes1)
X
X
NVARCHAR column size
4,000 UCS-2 units (8,000 bytes1)
X
X
Number of ANDed and ORed predicates
3000
X
X
Number of database objects
65,536
X
 
4 billion
 
X
Parameter name
126 bytes
X
X
Password2
8 bytes
X
 
128 bytes
 
X
Procedure name2
30 bytes
X
 
128 bytes
 
X
Referential integrity (RI) constraint name
20 bytes
X
 
128 bytes
 
X
Representation of single quote
Two consecutive single quotes ('')
X
X
Result name
Limited by memory
X
X
Savepoint name
Limited by memory
X
X
SELECT list columns in a query
1,600
X
X
Size of a single term (quoted literal string) in a SQL statement
14,997, excluding null terminator and quotations (15,000 total)
X
X
SQL statement length
512 KB
X
X
SQL statements per session
Limited by memory
X
X
Stored procedure size
64 KB
X
X
Table name2
20 bytes
X
 
128 bytes
 
X
Table rows
13.0 file format: 9,223,372,036,854,775,807 (~9.2 quintillion)
Older file formats: 2,147,483,647 (~2.1 billion)
X
X
Joined tables per query
Limited by memory
X
X
Trigger name2
20 bytes
X
 
128 bytes
 
X
User name2
30 bytes
X
 
128 bytes
 
X
VARCHAR column size
8,000 bytes1
X
X
Variable name
Limited by memory
X
X
View name2
20 bytes
X
 
128 bytes
 
X
1The maximum size of a CHAR, NCHAR, VARCHAR or NVARCHAR column that may be fully indexed is 255 bytes.
2See also Identifier Restrictions in Advanced Operations Guide.
Fully Qualified Object Names
A fully qualified object name uses dot notation to combine database and object names. For example, if the database mydbase has a view myview, then its fully qualified object name is mydbase.myview.
Fully qualified object names must be unique within a database. For example, if database mydbase has table acctpay and user-defined function acctpay, then Zen cannot resolve the name mydbase.acctpay.
Delimited Identifiers in SQL Statements
Table, column, and index names must be delimited if they contain spaces or nonstandard characters or if the identifier is a keyword. The delimiter character is the double quotation mark.
Examples
SELECT "last-name" FROM "non-standard-tbl"
The hyphen is a nonstandard character.
SELECT "password" FROM my_pword_tbl
"Password" is a keyword in the SET PASSWORD statement.
Last modified date: 10/31/2023