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.
Table 1 SQL Statement Types and Related Tasks
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.
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.
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.
Table 6 Data Definition Statements – Trigger Control
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.
Table 7 Data Definition Statements – Stored 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.
Table 8 Data Definition Statements – Stored Procedure Control
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 (UDF) and use them in SQL statements.
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 return value, which is a scalar value.
User-defined functions are always defined within the context of a database. Successful execution of this statement results in the storing of the UDF definition in the specific database. Once stored, the UDF can be modified, invoked, and deleted.
Zen supports scalar user-defined functions.
A scalar user-defined function returns a single value of the data type specified in the RETURNS clause of the SQL statement. A scalar UDF can contain multiple SQL statements. You can specify any data type value for the returned data except text, ntext, image, cursor, or timestamp.
To create and delete user-defined functions, use the following SQL statements.
Table 9 Data Definition Statements – User-Defined 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 the SELECT statement.
Table 10 Data Manipulation Statements – Retrieving Data
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. The types of clauses you use in a SELECT statement are as follows:
Table 11 Data Manipulation Statements – Retrieving Data Options
FROM
Specifies the tables or views from which to retrieve data.
WHERE
Defines search criteria that qualify the data a SELECT statement retrieves.
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.
A stored procedure consists of statements that you can precompile and save in the dictionary. To execute stored procedures, use the following SQL statements.
Table 14 Data Manipulation Statements- Stored Procedures
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 stored procedures, use the following SQL statements.
Table 15 Data Manipulation Statements – System Stored Procedures
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 statements using the following statement:
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.
Table 17 Data Control Statements – Groups and Users
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 statements using the following:
Table 18 Data Control Statements – Rights
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.
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.
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.
