HP 3000 Manuals HP 3000 Manuals
SQL Statements with Exceptions
ALTER TABLE
* ALTER TABLE cannot be used to add a column to an IMAGE/SQL table.
For example, the command
isql=> alter table sales.customer add (newcol float);
results in the following error:
Operation not allowed on non-SQL table. (DBERR 2454)
* ALTER TABLE cannot add a UNIQUE constraint to an IMAGE/SQL table.
The command
isql=> alter table sales.customer
add constraint unique (last_name);
results in this error:
Command not allowed on a TurboIMAGE table. (DBERR 13502)
* The ALTER TABLE DROP CONSTRAINT specification is also not
supported.
* ALTER TABLE cannot add a CHECK constraint to an IMAGE/SQL table.
The command
isql=> alter table sales.customer
add constraint check (last_name < 'SMITH')
constraint checkname;
is not allowed.
* ALTER TABLE cannot add a referential constraint to an IMAGE/SQL
table.
BEGIN ARCHIVE
SQL archive logging does not capture modifications made to IMAGE/SQL
databases.
TURBOSTORE Online Backup requires special procedures when TurboIMAGE/XL
databases (and therefore IMAGE/SQL databases) are in use during the store
operation.
BEGIN WORK
IMAGE/SQL transactions are managed by using the TurboIMAGE/XL intrinsics
DBXBEGIN and DBXEND. The BEGIN WORK statement does not invoke DBXBEGIN
until the first modification or repeatable read is requested for an
IMAGE/SQL table.
The priority specified in a BEGIN WORK statement is ignored by IMAGE/SQL.
IMAGE/SQL uses priority 100 for all lock requests involving IMAGE/SQL
tables.
If isolation level RR (repeatable read), CS (cursor stability), or RC
(read committed) is specified and the IMAGE/SQL user has a MODE attribute
of 1, 2, 3, or 4, all read operations within the transaction will be
repeatable reads.
If isolation level RU (read uncommitted) is specified, all read
operations within the transaction will be read-uncommitted.
CHECKPOINT
Contents of log buffers are written to the log file or files, but contain
no IMAGE/SQL work.
Data buffers containing changed pages are written to DBEFiles, but
contain no IMAGE/SQL data.
COMMIT ARCHIVE
SQL archive logging does not capture modifications made to IMAGE/SQL
databases.
COMMIT WORK
The KEEP CURSOR option of the OPEN statement cannot be used with a cursor
that references an IMAGE/SQL table. Because KEEP CURSOR does not apply
to IMAGE/SQL tables, COMMIT WORK:
* releases all IMAGE/SQL locks acquired during the current
transaction
* closes all cursors that reference IMAGE/SQL tables
* does not implicitly start a new IMAGE/SQL transaction
COMMIT WORK ends an IMAGE/SQL transaction by calling DBXEND and then
calls DBUNLOCK to release all locks acquired during the transaction.
CREATE INDEX
CREATE INDEX cannot be used to create an index on an IMAGE/SQL table.
The command
isql => create index sales.newindex on
sales.customer (last_name asc);
invokes this error:
Operation not allowed on non-sql table. (DBERR 2454)
CREATE SCHEMA
CREATE SCHEMA cannot be used to define an IMAGE/SQL database, but can be
used to define a view.
CREATE TABLE
CREATE TABLE is not supported.
DECLARE CURSOR
A cursor that references an IMAGE/SQL table (in the QueryEXPRESSION or
StatementName of the FOR clause) cannot be opened with the KEEP CURSOR
option of the OPEN statement.
DELETE
If SQL detects an error during a DELETE statement that references an
IMAGE/SQL table, the current transaction is aborted regardless of the
setting of the SET DML ATOMICITY and SET CONSTRAINTS statements.
The set of rows to be affected by the DELETE statement is determined
before any rule fires, and this set remains fixed until completion of the
rule. If the rule adds to the set, the additional rows will not be
deleted. If the rule deletes from the set, a TurboIMAGE/XL intrinsic
error will result and the current transaction will be aborted.
If an error occurs during the processing of any rule considered during
execution of this statement, the current transaction will be aborted.
DELETE WHERE CURRENT
This has the same considerations as DELETE.
DROP DBEFILE
DROP DBEFILE cannot be used to drop DBEFiles containing IMAGE/SQL
objects.
DROP TABLE
DROP TABLE cannot be used to drop an IMAGE/SQL table. An IMAGE/SQL table
can be dropped only by detaching the database with the IMAGE/SQL DETACH
statement.
DROP VIEW
Cannot be used to drop a view created by IMAGESQL. A view created by
IMAGE/SQL can be dropped only by detaching the database with the
IMAGE/SQL DETACH command.
INSERT
If SQL detects an error during an INSERT statement that references an
IMAGE/SQL table, the current transaction is aborted regardless of the
setting of the SET DML ATOMICITY and SET CONSTRAINTS statements.
If an error occurs during the processing of any rule considered during
execution of this statement, the current transaction will be aborted.
IMAGE/SQL columns are defined as NOT NULL with default values. Default
values are based on IMAGE/SQL item types. If columns are omitted from
the column name list of an INSERT statement, the missing columns will be
defined using the default values. The default values are listed in Table
2-5 .
LOCK TABLE
A LOCK TABLE statement always provides an EXCLUSIVE lock to a
TurboIMAGE/XL data set.
OPEN
A cursor referencing an IMAGE/SQL table (in the QueryExpression or
StatementName of the DECLARE CURSOR statement) cannot be opened with the
KEEP CURSOR option.
IMAGE/SQL columns are defined as NOT NULL. The INDICATOR option of the
OPEN statement therefore cannot be used with IMAGE/SQL.
REVOKE
REVOKE cannot be used to revoke authorities granted by the IMAGE/SQL
ATTACH statement.
ROLLBACK WORK
Savepoints cannot be defined in an IMAGE/SQL transaction. The TO clause
of ROLLBACK WORK therefore cannot be used with an IMAGE/SQL transaction.
SAVEPOINTS
Savepoints cannot be defined in an IMAGE/SQL transaction.
SELECT
IMAGE/SQL columns are defined as NOT NULL. The INDICATOR option of the
SELECT statement therefore cannot be used with IMAGE/SQL.
SET CONSTRAINTS
If a modification error occurs within an IMAGE/SQL transaction, the
current transaction is aborted regardless of the setting of SET
CONSTRAINTS.
SET DML ATOMICITY
If a modification error occurs within an IMAGE/SQL transaction, the
current transaction is aborted regardless of the setting of SET DML
AUTOMICITY.
START DBE NEW
The default DBEFile size of 150 pages used by START DBE NEW may not be
sufficient to allow a database to be attached with IMAGESQL.
TRANSFER OWNERSHIP
IMAGE/SQL objects created by the IMAGESQL ATTACH statement cannot be
transferred to another owner.
UPDATE
If ALLBASE/SQL detects an error during an UPDATE statement that
references an IMAGE/SQL table, the current transaction is aborted
regardless of the setting of the SET DML ATOMICITY and SET CONSTRAINTS
statements.
The set of rows to be affected by the UPDATE statement is determined
before any rule fires, and this set remains fixed until completion of the
rule. If the rule adds to the set, the additional rows will not be
updated. If the rule deletes from the set, a TurboIMAGE/XL intrinsic
error will result and the current transaction will be aborted.
If an error occurs during the processing of any rule considered during
execution of this statement, the current transaction will be aborted.
IMAGE/SQL columns are defined as NOT NULL. The NULL option of the SET
clause therefore cannot be used with IMAGE/SQL columns.
If an IMAGE/SQL column specified in an UPDATE statement corresponds to a
search or sort item in a TurboIMAGE/XL detail data set, the database's
Critical Item Update flag must be set to ON. If an IMAGE/SQL column
specified in an UPDATE statement corresponds to a search item in a
TurboIMAGE/XL detail data set and the corresponding master data set is a
manual master, the new column value must already exist as a chain head in
the master data set.
UPDATE WHERE CURRENT
This has the same considerations as UPDATE.