HP 3000 Manuals

Unsupported SQLBase Syntax and Logical Constructs [ HP ALLBASE/SQL PC API User's Guide ] MPE/iX 5.0 Documentation

HP ALLBASE/SQL PC API User's Guide

Unsupported SQLBase Syntax and Logical Constructs

Some SQLBase syntax and logical constructs are not supported by
ALLBASE/SQL. Since there are no ALLBASE/SQL equivalents for unsupported
syntax and constructs, no translation takes place. If you use
unsupported SQLBase syntax or logical constructs while accessing an
ALLBASE/SQL database, an error condition results. This section offers
suggestions for possible solutions.

PC API handles the following logical constructs differently than SQLBase:

* Column specification with ORDER BY.
* The @ functions.
* Synonyms.
* ALTER TABLE statement.
* Correlation names in DELETE and UPDATE.
* Column numbers in GROUP BY clause.
* Authorization identifiers.
* Passwords.
* Outer join.
* IN predicate.
* Isolation levels.
* System catalog views.

Column Specification with ORDER BY

Both SQLbase and ALLBASE/SQL support the ORDER BY clause; however in
ALLBASE/SQL, the columns contained within an ORDER BY clause must be a
subset of the columns within the SelectList.

Suggestion. Change all ORDER BY clauses so that the columns contained
within it are a subset of the columns in the SelectList. For more
information, refer to the Gupta manual, SQLBase SQLTalk Language
Reference Manual.

The @ Functions

SQLBase supports a number of functions which are extensions to SQL. These
functions are identified by the @ prefix. For example, the function
@VALUE ('1234'), converts the string '1234' to the integer 1234. See the
SQLBase documentation for a complete list of these functions.

ALLBASE/SQL does not support @ functions. If you attempt to use them, an
error condition results.

Suggestion. Remove all @ functions.

Synonyms

SQLBase supports alternative names for tables and views called synonyms.
ALLBASE/SQL does not support synonyms. SQL statements containing
references to synonyms are treated as errors by ALLBASE/SQL.

Suggestion. Remove all CREATE SYNONYM and DROP SYNONYM statements from
applications that access ALLBASE/SQL. Also, remove all references to the
synonyms defined by CREATE SYNONYM statements.

ALTER TABLE Statement

ALLBASE/SQL places more restrictions on the SQL ALTER TABLE statement
than does SQLBase. The following SQLBase SQL statements are treated as
errors by ALLBASE/SQL:

* ALTER TABLE DROP.

* ALTER TABLE RENAME.

* ALTER TABLE MODIFY.

Suggestion. Remove all incompatible ALTER TABLE statements from
applications accessing ALLBASE/SQL. Replace ALTER TABLE DROP with DROP
TABLE, replace ALTER TABLE RENAME with DROP TABLE and CREATE TABLE, and
replace ALTER TABLE MODIFY with ALTER TABLE.

Correlation Names in DELETE and UPDATE

SQLBase supports the definition of correlation names in the DELETE
statement and the UPDATE statement. The correlation name is used as a
shorthand name for the table in the SearchCondition of the DELETE or
UPDATE statement.

ALLBASE/SQL does not support correlation names in DELETE or UPDATE
statements. The following SQL statements are treated as errors by
ALLBASE/SQL:

DELETE FROM correlationname WHERE SearchCondition
UPDATE correlationname SET...WHERE SearchCondition

Suggestion. Remove correlation names from DELETE and UPDATE statements.
Use the full tablename instead of the correlation name in the
SearchCondition.

Column Numbers in GROUP BY

SQLBase allows the reference in a GROUP BY clause to be a column number.
For example:

SELECT COUNT (employee), manager FROM persons GROUP BY 2

ALLBASE/SQL does not allow the GROUP BY reference to be the column
number; the reference must be to the column name.

Suggestion. Change all references to column numbers in the GROUP BY
clause to column names. The example above is changed to meet ALLBASE/SQL
requirements:

SELECT COUNT(employee), manager FROM persons GROUP BY manager

Authorization Identifiers

SQLBase defines authorization identifiers as valid user names that
contain up to eight characters.

ALLBASE/SQL categorizes authority to access a table or view as one of the
following owner names:

* DBEUserID.

* Group name.

* Class name.

These access authorizations are briefly described below. For more
information about owner names, refer to the ALLBASE/SQL Database
Administration Guide or ALLBASE/SQL Reference Manual.

DBEUserID.

The DBEUserID is derived from the MPE/iX logon string. If the logon
string is:

:HELLO DEMO.ALLBASE

then the DBEUserID is DEMO@ALLBASE.

Group Name.

A group name is an identifer that names a collection of users. A group
name and its members must be preconfigured with the ALLBASE/SQL CREATE
GROUP statement before it can be used.

Class Name.

A class name is an identifier that qualifies table names. A class name
is not preconfigured, and it does not contain members.

Passwords

SQLBase security is through passwords identified and maintained through
SQL statements. Each user name can be associated with one password.
ALLBASE/SQL security is managed with the GRANT and REVOKE statements and
password security is managed by the operating system. For this reason,
ALLBASE/SQL does not support SQL statements that reference passwords.

The following SQLBase SQL statements are not supported by ALLBASE/SQL,
and will cause an error condition if you use them while connected to an
ALLBASE/SQL database:

* ALTER PASSWORD

* GRANT CONNECT TO authid_list IDENTIFIED BY password_list;

To grant ALLBASE/SQL connect authorities, the following syntax must be
used:

{DBEUserID}
GRANT CONNECT TO {GroupName}[,...]
{ClassName}
For more information about ALLBASE/SQL security on the server, refer to
ALLBASE/SQL Database Administration Guide.

Outer Join

An outer join is used to force a row from a table to appear in a query
result if there is no matching row. SQLBase allows you to specify a
one-way outer join by adding a (+) to the join column of the table which
might not have rows to satisfy the join condition. For more information
about how SQLBase handles the outer join, refer to the Gupta manual,
SQLBase SQLTalk Language Reference Manual.

ALLBASE/SQL preserves the ANSI standard outer join syntax which is
specified in the FROM clause of the SELECT statement. For more
information about ALLBASE/SQL and outer joins, refer to the ALLBASE/SQL
Reference Manual.

The IN Predicate

The IN predicate compares a value with a list of values or a number of
values derived by the use of a subquery. The ALLBASE/SQL syntax of the
IN predicate is as follows:

Expression [NOT] IN {SubQuery }
{ValueList}
ALLBASE/SQL does not support an Expression as an alternative to SubQuery
and ValueList, as does SQLBase. For information about the SQLBase
syntax, refer to the Gupta manual, SQLBase SQLTalk Language Reference
Manual.

Suggestion. Change any Expression within an IN predicate to a ValueList.

Isolation Levels

Isolation levels allow you to control the degree of concurrency by
regulating the extent to which operations performed by one user in a
multi-user environment can be affected by operations performed by another
user.

ALLBASE/SQL allows four different isolation levels:

* Repeatable Read (RR)
* Cursor Stability (CS)
* Read Committed (RC)
* Read Uncommitted (RU). You cannot use RU with PC API.

For information on isolation levels, refer to the section "Concurrency
Control through Locks and Isolation Levels" in the ALLBASE/SQL Reference
Manual.

With SQLWindows

You can set the isolation level to RR (Repeatable Read), CS (Cursor
Stability), and RL (Release Lock) by using the SqlSetIsolationLevel
command. With PC API, the SQLBase RR and CS isolation levels are
equivalent to the RR and CS isolation level in ALLBASE/SQL, except that
rows are read one at a time from the buffer.

The SQLBase RL (Release Lock) is translated into the ALLBASE/SQL RC (Read
Committed). When the isolation level is translated to RC, ALLBASE/SQL
can read multiple rows at one time. But if you try to use UPDATE or
DELETE WHERE CURRENT OF CURSOR with RL isolation level, you will receive
an error.

The ALLBASE/SQL RU (Read Uncommitted) isolation level is not supported
with PC API. This level does not translate into an equivalent SQLBase
isolation level.

The RO (Read Only) level is not supported by ALLBASE/SQL.

With SQLTalk/Windows, you can set the isolation levels with the Settings
command under the Session Window.

System Catalog Views

The SYSSQL system catalog views installed with the Views Script contain
all of the information needed to map the server-independent system
catalog views to the ALLBASE/SQL system catalog views.
The following table lists the SQLBase views owned by SYSSQL and the
corresponding ALLBASE/SQL views owned by SYSTEM along with the their
differences. When equivalent information required to define the
server-independent view is not available from the ALLBASE/SQL system
catalog view, an invariant value, shown under "Differences" in Table 6-2
, is supplied.

Table 6-2. Mapping of System Catalog Views

Comments in the System Catalog Views

SQLBase supports comments in its system catalog views, while ALLBASE/SQL
does not support them.

The SQL statement COMMENT ON is treated as an error by ALLBASE/SQL.

Suggestion. Remove the COMMENT ON TABLE and COMMENT ON COLUMN statements
from applications that access ALLBASE/SQL.

MPE/iX 5.0 Documentation