SQL Data Manipulation Language (DML)
An important goal of Model 204 SQL Data Manipulation Language (DML) is to match the form and function of the ANSI SQL 1989 standard and some of the ANSI SQL 1992 standard DML. Model 204 SQL extensions mainly affect DDL. You should not need to alter your existing SQL DML applications to access a Model 204 database.
This chapter describes the Model 204 SQL DML extensions as well as ways to use SQL DML to access data stored in Model 204 INVISIBLE fields, multiply occurring fields, and file groups.
Using Model 204 SQL DML
The Model 204 SQL Server supports all standard SQL DML. This section has a reminder about data definition accuracy and information about privileges for using SQL DML statements, setting SQL isolation level, and restrictions on interspersing SQL DML and DDL.
Maintaining data definition consistency
The SQL data definition information stored in the Model 204 SQL catalog for a Model 204 file is independent of the Model 204 Dictionary metadata for that file. It is also independent of and unchanged by any updates to the file's Table A data, that is, the file's fields and their attributes.
Caution: If you run a Model 204 SQL application against a Model 204 file, you are responsible for ensuring consistency between the Model 204 SQL catalog entries and the Model 204 file's Table A data. You must ensure that if the Model 204 file definition changes, the corresponding SQL definitions are updated. Reviewing the SQL catalog contents by direct query or with the Model 204 SQL catalog reporting utility (CCACATREPT) can help you to maintain the consistency between the SQL catalog and the Model 204 file.
DML statement privileges
To issue SQL DML query and update statements (SELECT, INSERT, DELETE, and UPDATE) against an SQL catalog object, the object must have your authorization ID or you must have been granted the privilege for the specified operation and for the specified object. These privileges are specified with SQL GRANT and REVOKE statements only.
For more information about GRANT and REVOKE and individual statement security, see SQL Data Definition Language (DDL).
Setting SQL isolation level
Model 204 SQL supports the following levels of record locking in its statement processing:
Isolation level 3 (Serializable)
SQL standard requires that concurrently executing statements be serializable, that is, the processing of the statements must yield the same results as if the statements were executed serially, one complete statement after another. Because it is costly to performance, the serializability option is not recommended.
Isolation level 1 (Cursor Stability)
A shared lock is obtained for a found set of records. After the records are read, the lock is dropped. This is the same as FIND statement processing in Model 204 SOUL.
Isolation level 0 (Dirty Read)
No locks are obtained for a found set of records. This is the same as FIND WITHOUT LOCKS statement processing in Model 204 SOUL.
Isolation level 0 is not recommended for applications using the following:
- SQL nested tables
- SQL views that are used for record security
- Model 204 reuse record number (RRN) files
- Model 204 files that are being simultaneously updated with DELETE ALL RECORDS statements in SOUL and with SQL DML
SQL 32-bit Connect★ PC clients indicate the isolation level of statement processing by specifying an SQL Isolation Level option in the Model 204 ODBC Driver - Configuration Data Source dialog box or in the connection string for JDBC or .NET Framework connections.
Executing SQL DML and DDL simultaneously
Another user might issue SQL DDL against the SQL catalog at the same time that you are issuing SQL DML against the Model 204 database or to query the SQL catalog. However, the SQL DDL can update only SQL objects that are not being accessed by SQL DML statements.
Mixing SQL DML and DDL
The Model 204 SQL Server allows SQL DML statements interspersed with SQL DDL. An SQL DDL statement can follow an SQL DML statement only if the DML statement has first been committed. Model 204 SQL DDL statements result in an automatic Model 204 COMMIT.
For JDBC and ODBC
When Autocommit, the default, is on, any DDL can follow an INSERT, UPDATE, or DELETE statement without an intervening commit or rollback. However, DDL after a SELECT statement without an intervening commit or rollback will result in the following SQL error:
SQL Error -7342. DML transaction in progress. DDL disallowed until COMMIT/ROLLBACK, for EXECIMM, in EXECUTE completion routine.
For .NET Framework
DDL statements may not be issued while a transaction is active.
Using SET SCHEMA and SET USER
SET SCHEMA and SET USER are Model 204 SQL extensions that can be used with SQL DML or DDL statements.
SET SCHEMA allows any user to change the default schema name in SQL DDL or DML. Statements following SET SCHEMA are assumed to apply to this schema name. The SET SCHEMA syntax follows:
SET SCHEMA schemaname
For more information about SET SCHEMA, see Altering SQL objects.
SET USER allows a system manager to set or modify the current SQL authorization ID, in effect logging in as another SQL user without having to enter a password. SET USER therefore gives the system manager access to and authority to change all defined SQL objects.
The SET USER syntax follows:
SET USER authorization-id
For more information about SET USER, see Altering SQL objects.
Using SQL DML against INVISIBLE fields
Model 204 INVISIBLE fields are translated to SQL columns that have restricted functionality: you can use them to qualify searches for data, but they are not themselves retrievable. For example, you can use such columns in certain circumstances in the WHERE clause of a SELECT statement, but not in a SELECT list.
Note: Model 204 files that contain INVISIBLE fields not mapped to multicolumn unique constraints should be maintained by SOUL or Host Language Interface applications and not by SQL applications. This recommendation applies regardless of whether the fields are mapped to SQL columns.
Using SQL columns mapped to INVISIBLE fields
The restrictions on SQL DML operations against columns mapped to INVISIBLE fields are listed below for each DML statement. In this section, an SQL column mapped to an INVISIBLE field is called an invisible column.
You cannot use an invisible column in the SELECT list.
If there is an invisible column in a table against which a
SELECT * operates, the column is eliminated from the statement output, and you receive no warning message. If the
SELECT * is in an INSERT query, or in a query involving UNION, the statement is rejected.
In the following examples, the PARTS table has invisible column INV_S; the SUPPLIERS table has invisible column INV_IN. This SELECT fails, because you cannot use an invisible column in the SELECT list:
SELECT PNO, INV_S FROM PARTS WHERE PNO=1234
These SELECT statements are acceptable, but you cannot connect them with UNION:
SELECT * FROM PARTS WHERE PNO='P1'
SELECT * FROM PARTS WHERE COLOR='Red'
The use of invisible columns in a SELECT statement WHERE clause is limited. See WHERE clause for details.
You cannot use an invisible column as the target of or in the source expression of an UPDATE. For example, this UPDATE fails:
UPDATE PARTS SET INV_S='Red' WHERE PNO=1234
The use of invisible columns in an UPDATE statement WHERE clause is limited. See the ORDER BY clause.
You can use an invisible column as the target column.
For example, you can issue this statement:
INSERT INTO PARTS (PNO,PNAME,INV_S) VALUES ('123','Widget','Red')
Any SELECT query involved is subject to the limitations of the SELECT statement (see SELECT statement for details).
If you DELETE a row that contains an invisible column value, the invisible column value is not deleted.
The use of invisible columns in a DELETE statement WHERE clause is limited. See ORDER BY clause for details.
GROUP BY clause
You cannot use an invisible column as a column in a GROUP BY clause.
You cannot use an invisible column in a HAVING clause unless the invisible column is contained in a WHERE clause of a subquery contained in the HAVING clause, in which case the use of the column is subject to the WHERE clause rules.
ORDER BY clause
You cannot use an invisible column as a column in an ORDER BY clause.
The restrictions on the use of invisible columns in WHERE clauses of SQL DML statements are not easily generalized. Most cases are covered by the following rules. The remaining more complicated rules are listed in Additional restrictions on WHERE clause comparisons.
The use of invisible columns in WHERE clauses of SQL SELECT, INSERT, UPDATE, and DELETE statements is subject to the following restrictions:
- You cannot use invisible columns if the WHERE clause is part of a view definition that includes the WITH CHECK OPTION.
- You cannot use invisible columns in arithmetic expressions.
For example, the following statement fails:
SELECT SNO, STCODE FROM SUPPLIERS WHERE (STCODE = 20 * (INV_IN + 300) OR SNAME='NUT')
- In addition to the basic comparisons (<, <=, =, >, >=, ^= ) you can use an invisible column with the following SQL operators:
LIKE BETWEEN IN (but not the subquery form)
For example, the following statement fails (subquery form of IN):
SELECT PNO FROM PARTS WHERE INV_S IN (SELECT PNAME FROM PARTS P WHERE P.PNO='S1')
- If the INVISIBLE field has the KEY attribute, comparisons using the following operators are not allowed:
< <= > >= LIKE BETWEEN
- You can compare an invisible column to only the following:
SELECT * FROM SUPPLIERS WHERE INV_IN > 20
For example, where ? is a parameter marker whose values are substituted by a program at execution time:
SELECT SNO, SNAME FROM SUPPLIERS WHERE INV_IN=? AND SNO=?
- An outer reference, in which a WHERE clause in a subquery refers to a value from an outer query
SELECT SNAME FROM S WHERE 'P2' IN ( SELECT INV_IN FROM SP WHERE INV_IN=S.SNO )
- An expression of literals or parameters or outer references
SELECT * FROM SUPPLIERS WHERE INV_IN = 20 * (? + 300) AND SNO='S1'
Additional restrictions on WHERE clause comparisons
The restrictions on the use of invisible columns in WHERE clauses include the following additions to the rules for comparisons described previously:
You can compare an invisible column to an uncorrelated (no outer references) subquery, except the following types of quantified subqueries:
=ANY =SOME IN ^=ANY ^=SOME
For example, the following statement is valid:
SELECT PNO FROM PARTS WHERE INV_S > ALL (SELECT PNAME FROM PARTS P WHERE P.PNO='S1')
- If the invisible column is NOT NULL, comparisons with ^= or NOT LIKE are restricted: the value compared to the invisible column cannot include parameters, cannot be a subquery, and cannot include outer references if these might yield the null value.
- If the invisible column is not NOT NULL, comparisons with ^= or NOT LIKE are prohibited.
- If the WHERE clause is broken out into one or more comparisons connected by OR (disjuncts), with NOTs factored down to the individual comparison, each disjunct containing an invisible column comparison can contain only comparisons (invisible or visible) that refer to the same parent table and that satisfy all the preceding WHERE clause rules.
Using SQL DML against nested tables
The Model 204 SQL nested table design is an extension to standard SQL that enables an SQL application to access Model 204 multiply occurring fields and groups. Using SQL DML against Model 204 SQL nested tables is essentially the same as using DML against typical nonnested SQL tables.
Sample file and SQL mapping
The DML examples in this section are based on the same sample file and SQL catalog mapping used in the discussion of nested table DDL in SQL representation of Model 204 data.
A Model 204 file has the following fields:
|Field||Frequency of occurrence|
|NAME||Once per record|
|HIRE_DATE||Once per record|
|REV_DATE||Multiple times per record|
|SALARY||Multiple times per record|
|TITLE||Multiple times per record|
|TASK||Multiple times per record|
REV_DATE, SALARY, and TITLE are a repeating group that occurs once each salary review.
For the purposes of the example series, we consider the following cases:
- NAME is a unique identifier and is mapped to the primary key column of a parent table
- The NAME field is a unique identifier and is used as the primary key in the parent table
The file is mapped to one SQL parent table (PEOPLE) with two columns (NAME and HIRE_DATE) and two nested tables (REV_HIST and TASKS):
The parent table is linked to the nested tables by the common values of the primary key NAME in the parent and the foreign key FNAME in the nested tables. The following DDL provides this mapping:
CREATE TABLE PEOPLE ( NAME CHAR(60) NOT NULL PRIMARY KEY, HIRE_DATE CHAR(8) ) CREATE TABLE REV_HIST NESTED USING FNAME ( DATE INTEGER NOT NULL, SALARY DECIMAL (11,2) NOT NULL, TITLE CHAR (50) NOT NULL, FNAME CHAR (60) NOT NULL REFERENCES PEOPLE ) CREATE TABLE TASKS NESTED USING FNAME ( TASK CHAR (25) NOT NULL, FNAME CHAR (60) NOT NULL REFERENCES PEOPLE )
There is no unique identifier, so a system-generated key (PKID) is the parent table primary key. The file is mapped to one SQL parent table (PEOPLE) with three columns (PKID, NAME, and HIRE_DATE) and two nested tables (REV_HIST and TASKS):
The parent table is linked to the nested tables by the common values of the primary key PKID in the parent and the foreign key FKID in the nested tables. The names PKID and FKID are user-chosen. The following DDL provides this mapping:
CREATE TABLE PEOPLE ( PKID INTEGER NOT NULL PRIMARY KEY SYSTEM, NAME CHAR(60) NOT NULL, HIRE_DATE CHAR(8) ) CREATE TABLE REV_HIST NESTED USING FKID ( DATE INTEGER NOT NULL, SALARY DECIMAL (11,2) NOT NULL, TITLE CHAR (50) NOT NULL, FKID INTEGER NOT NULL REFERENCES PEOPLE ) CREATE TABLE TASKS NESTED USING FKID ( TASK CHAR (25) NOT NULL, FKID INTEGER NOT NULL REFERENCES PEOPLE )
For more information about system-generated keys, see Using system-generated keys.
DML example series
The examples in the series are presented by database operation and usually have a sample query specification for Case 1 (primary key maps to database field) and Case 2 (primary key is system-generated). The query specifications represent SQL cursor-based SELECT statements.
Retrieving a particular occurrence of a multiply occurring group
The task is to select a particular occurrence of the repeating group of field values that are mapped to the REV_HIST nested table. Each occurrence of the group is a set of related values (one value from each of the members of the group). The nested table definition in the example in this section maps each row in REV_HIST to a particular occurrence of the group. Therefore, retrieving a particular occurrence of the group translates to selecting a particular row from REV_HIST.
SELECT REV_DATE, SALARY, TITLE FROM REV_HIST WHERE FNAME='JOHN SLOWFOOT' AND REV_DATE BETWEEN 19900101 AND 19900331
The system-generated key (PEOPLE) qualifies the selection:
SELECT REV_DATE, SALARY, TITLE FROM REV_HIST, PEOPLE WHERE NAME='JOHN SLOWFOOT' AND REV_DATE BETWEEN 19900101 AND 19900331 AND PKID = FKID
Retrieving a range or series of occurrences
The following example selects only some of the values per occurrence of the group over a range of occurrences: the salaries of people who were senior secretaries from 1989 through 1991.
SELECT FNAME, SALARY FROM REV_HIST WHERE REV_DATE BETWEEN 19890101 AND 19911231 AND TITLE='SR SECTY'
SELECT NAME, SALARY FROM REV_HIST, PEOPLE WHERE REV_DATE BETWEEN 19890101 AND 19911231 AND TITLE='SR SECTY' AND PKID = FKID
Retrieving any or all occurrences based on a condition
The following example selects the names of those for whom all occurrences meet the condition, that is, all whose salary values are above 40,000. You can replace ALL with ANY in the example and therefore select the names of those who have any of their salary values above 40,000.
SELECT NAME FROM PEOPLE WHERE 40000 < ALL (SELECT SALARY FROM REV_HIST WHERE FNAME = NAME)
SELECT NAME FROM PEOPLE WHERE 40000 < ALL (SELECT SALARY FROM REV_HIST WHERE PKID = FKID)
Retrieving at least n occurrences based on a condition
The following example selects the names of those who have any of their salary values above 40,000 only if at least three names qualify.
SELECT NAME FROM PEOPLE WHERE 2 < (SELECT COUNT (*) FROM REV_HIST WHERE FNAME = NAME AND 40000 < SALARY)
SELECT NAME FROM PEOPLE WHERE 2 < (SELECT COUNT (*) FROM REV_HIST WHERE PKID = FKID AND 40000 < SALARY)
Correlating a table and a nested table
The examples in this section mix column selections from a nested table and its parent table and use them for output like the following, where the NAME and HIRE_DATE values for entries in the PEOPLE table are reported along with their TASK values from the nested table TASKS:
Maria Pena July 9, 1988 New prod plan Budget John Slowfoot November 22, 1989 Project A Project B
The method of deriving this output is fetching with a join query.
The following query selects from the nested table TASKS and its parent PEOPLE. The ORDER BY clause guarantees the ordering of the data.
SELECT NAME, HIRE_DATE, TASK FROM PEOPLE, TASKS WHERE NAME = FNAME ORDER BY NAME, TASK
SELECT NAME, HIRE_DATE, TASK FROM PEOPLE, TASKS WHERE PKID = FKID ORDER BY PKID, TASK
Working with nested table constraints
Referential constraint checking is done per row.
The NOT NULL constraint, which prevents SQL applications from updates that introduce null values into an SQL nested table column, is enforced only for SQL. A SOUL application is not prevented from introducing nulls into the Model 204 fields mapped to SQL nested columns, although this introduction is likely to invalidate queries involving these fields. Such an invalidation is an example of why you must be careful, especially with nested tables, when you use both SQL and SOUL to maintain file data.
Porting nested table applications
A Model 204 SQL nested table application is readily portable to other environments if the application does not have system-generated primary keys in the parent and if the environment ported to supports CASCADE for updates and deletes.
Options in the SELECT LIST statement
Correlation name feature
The name in the SELECT LIST statement can be assigned to a new name by either:
- AS keyword
- Equal sign (=) sign
- A white space.
If the name represents a column, the newly assigned name may be referred to in other clauses. The following examples are supported:
SELECT COUNT(*) AS FILECOUNT FROM TABLE5 SELECT T1.TITLE TITLE, CHAPTER = T2.TITLE FROM BOOK T1, CHAPTERS T2 WHERE TITLE = 'WWII' AND CHAPTER = 'US'
The following example is not supported:
SELECT DOUBLE_ADVANCE = (ADVANCE * 2) FROM PUBLISHERS WHERE DOUBLE_ADVANCE > 1000
Note: Correlation names may not carry over to a query at a different level of an SQL statement.
Wildcard asterisk (*) for an individual table feature
SELECT T.TITLE, T.*, P.* FROM TITLES T, PUBLISHERS P WHERE T.ID = P.ID
The CURRENT_TIME keyword is assigned as CHAR(11) in the form of hh:mm:ss.tt, representing the local time at the time of the query execution.
SELECT CURRENT_TIME, COUNT(*) FROM TABLE5 10:05:24.38
Note: In this example query and those to follow, the
COUNT(*) is added merely for the purpose of generating a single row of output.
The CURRENT_DATE keyword is assigned as CHAR(10) in the form of yyyy-mm-dd to represent the date at the time of the query execution.
SELECT CURRENT_DATE, COUNT(*) FROM TABLE5 2001-03-08
The CURRENT_TIMESTAMP keyword is assigned as CHAR(22) in the form of yyyy-mm-dd hh:mm:ss.tt, the time and the date at the time of the query execution.
SELECT CURRENT_TIMESTAMP, COUNT(*) FROM TABLE5 2001-03-08 10:05:24.38
The USER keyword is assigned as CHAR(10). It represents the name of the user who logged on to the thread.
SELECT USER, COUNT(*) FROM TABLE5 USER101
The SQLVERSION keyword is assigned as CHAR(16). It represents the version code of the RSQL module and the released date in the form: n.n.na mm/dd/yy.
SELECT SQLVERSION, COUNT(*) FROM TABLE5 7.1.0C 05/30/09
The SQLVERSION keyword is intended for diagnostic purposes.
The SQLERROR keyword is assigned as CHAR(5). It represents the last error code detected and recorded in the RSQL module. Six locations are reserved to store the last six error codes. For example:
SELECT SQLERROR, SQLERROR, COUNT(*) FROM TABLE5 +0000, -0206
The SQLERROR keyword is intended for diagnostic purposes.
The SQLSTATE keyword is assigned as CHAR(32). It represents the significant part of the last error message or the object name which caused the error, as recorded in the RSQL module. For example, SQLSTATE will contain 'UNITCOST', if the previous error was:
SQL Error -408. A value is not compatible with its object column UNITCOST, for EXECIMM, in EXECUTE completion routine
The SQLSTATE keyword is intended for diagnostic purposes.
SQL INNER JOIN features
CROSS JOIN feature
The CROSS JOIN feature produces a cross-product of a table join in which no join condition is specified. For example:
SELECT * FROM TITLES CROSS JOIN PUBLISHERS
The previous query is equivalent to the following example:
SELECT * FROM TITLES, PUBLISHERS
NATURAL JOIN feature
The NATURAL JOIN feature combines two tables on all columns that share the same names. For example, if PUB_ID and AUTHOR are the only two columns which are common to both tables, then the following query with the NATURAL JOIN clause produces the same results as the query with the WHERE clause:
SELECT * FROM TITLES NATURAL JOIN PUBLISHERS SELECT * FROM TITLES, PUBLISHERS WHERE TITLES.PUB_ID = PUBLISHERS.PUB_ID AND TITLES.AUTHOR = PUBLISHERS.AUTHOR
ON clause feature
The conditional join uses an ON clause to impose the join condition. It is interchangeable with the WHERE clause. For example, the following query uses an ON clause:
SELECT * FROM TITLES JOIN PUBLISHERS ON TITLES.PUB_ID = PUBLISHERS.PUB_ID
The previous query is equivalent to the following query which employs a WHERE clause:
SELECT * FROM TITLES, PUBLISHERS WHERE TITLES.PUB_ID = PUBLISHERS.PUB_ID
USING clause feature
The NATURAL JOIN feature matches all column names in one table with the same column names in a second table. If only some column names in one table match those column names in a second table, you can explicitly specify those columns with the USING clause, as follows:
SELECT * FROM TITLES JOIN PUBLISHERS USING (PUB_ID, AUTHOR)
The previous query is equivalent to a query in which the USING keyword is replaced by the ON clause, such as:
SELECT * FROM TITLES, PUBLISHERS ON (TITLES.PUB_ID = PUBLISHERS.PUB_ID AND TITLES.AUTHOR = PUBLISHERS.AUTHOR)
Note: The ON clause is not allowed to contain a subquery.
SQL OUTER JOIN features
Unlike the INNER JOIN feature, the OUTER JOIN feature preserves the unmatched rows from one of the two tables, depending on the keywords LEFT and RIGHT.
The following two equivalent queries are examples of a LEFT OUTER JOIN clause.
SELECT * FROM TITLES LEFT OUTER JOIN PUBLISHERS USING (PUB_ID, AUTHOR) SELECT * FROM TITLES LEFT OUTER JOIN PUBLISHERS ON (TITLES.PUB_ID = PUBLISHERS.PUB_ID AND TITLES.AUTHOR = PUBLISHERS.AUTHOR)
Note: The keyword OUTER in these queries is optional.
In these examples, each row in the first table, TITLES, in a LEFT OUTER JOIN clause must be included in the result. If no matching value is found in the second table, PUBLISHERS, the corresponding columns of PUBLISHERS are filled with NULLs.
The RIGHT OUTER JOIN clause operates similarly to a LEFT OUTER JOIN clause, except that the RIGHT or the second table is the parent table and its rows are preserved. For example, the previous two LEFT OUTER JOIN queries can be expressed as RIGHT OUTER JOIN queries.
SELECT * FROM PUBLISHERS RIGHT OUTER JOIN TITLES USING (PUB_ID, AUTHOR) SELECT * FROM PUBLISHERS RIGHT OUTER JOIN TITLES ON (TITLES.PUB_ID = PUBLISHERS.PUB_ID AND TITLES.AUTHOR = PUBLISHERS.AUTHOR)
Note: The ON clause is not allowed to contain a subquery.
SQL outer join features expanded
SQL join expressions with join types LEFT, RIGHT, CROSS, and UNION are supported where any table can be a base or a nested table or a viewed table.
- In a query a Left Outer Join and Right Outer Join can be combined in any order.
- In a view definition a Left Outer Join and Right Outer Join can be combined in any order.
- A nested table can be used in any place where a base table can be used.
- A view can be used in any place where a base table can be used.
- The ON clause can contain any columns from the joined tables.
- A subquery can contain a subquery.
SQL table expression
In the following example Join can be either a Left or Right join.
T1 Join T2 ON Pred1 Join T3 ON Pred2 Join T4 ON Pred3
Model 204 evaluates the previous code in the following sequence of steps:
- Table T1 joined with Table T2 by ON Pred1
- The expression (T1 Join T2 ON Pred1) is joined with T3 by ON Pred2
- The expression ((T1 Join T2 ON Pred1) Join T3 ON Pred2) is joined with T4 by ON Pred3
- SQL Server overview
- SQL Server installation
- SQL catalog
- SQL representation of Model 204 data
- SQL Data Definition Language (DDL)
- SQL DDL from the Table Specification Facility
- SQL catalog reporting and querying
- SQL Data Manipulation Language (DML)
- SQL DDL syntax
- SQL reserved words
- SQL DDL mapping of the demonstration database