HP 3000 Manuals

Understanding the SQL Differences Between ALLBASE/SQL and SQLBase [ HP PC API User's Guide for ALLBASE/SQL and IMAGE/SQL ] MPE/iX 5.0 Documentation

HP PC API User's Guide for ALLBASE/SQL and IMAGE/SQL

Understanding the SQL Differences Between ALLBASE/SQL and SQLBase

ALLBASE/SQL and SQLBase differ somewhat, and you need to be aware of the
differences when you are coding SQL to access ALLBASE/SQL databases.
SQLWindows and other Gupta products are designed to access the SQLBase
DBMS, so the Gupta documentation defines SQL as it is used by SQLBase.
You also need to be aware of the differences if you are porting an
application which was designed to access SQLBase to access ALLBASE/SQL.

This section explains which SQL syntax to use within the application
software to access ALLBASE/SQL databases. Some logical constructs and
SQL syntax are handled differently by ALLBASE/SQL and SQLBase. The
following topics are discussed:

* Mapped constructs: a description of the minor differences that
are resolved automatically by PC API.

* Unsupported SQL syntax and logical constructs: a description of
differences that may require programmer modification.

Mapped Constructs

When you are writing an application against an ALLBASE/SQL database, and
you are using Gupta Technologies, Inc. application software to do so,
consider the following. When there are minor lexical differences in SQL
syntax and logical constructs, PC API automatically translates the
SQLBase syntax into ALLBASE/SQL syntax. These differences are
transparent to the application software. Write SQL statements using the
SQL syntax as defined in your application software documentation. These
SQL differences that are resolved automatically by PC API are called
mapped constructs.

The following SQLBase syntax is automatically translated by PC API into
ALLBASE/SQL syntax:

* Numbered bind variables.
* ROLLBACK and COMMIT statements.
* Comparison operators.
* The ; terminator.
* Ignored Warning and Error Messages

With each of these mapped constructs, you can use either SQLBase syntax
as documented in the Gupta documentation or ALLBASE/SQL syntax as
documented in the ALLBASE/SQL Reference Manual.

Numbered Bind Variables.

A Gupta bind variable in an SQL statement is associated (bound) to
variables defined in a program. SQLBase supports numbered bind variables
(such as :1, :2 ). Although ALLBASE/SQL does not support numbered bind
variables, PC API translates them into ALLBASE/SQL dynamic parameters
before passing the SQL statement to ALLBASE/SQL. An ALLBASE/SQL dynamic
parameter returns either a value or a format. PC API also tracks
numbered bind variables so that the binding process operates correctly.

ROLLBACK and COMMIT Statements.

The SQLBase statements ROLLBACK and COMMIT are mapped to ROLLBACK WORK
and COMMIT WORK to meet the requirements of ALLBASE/SQL.

Comparison Operators.

ALLBASE/SQL and SQLBase use different symbols to represent comparison
operators. PC API translates SQLBase symbols into their corresponding
ALLBASE/SQL symbols, as shown in Table 3-4 .

Table 3-4. Translation of SQLBase Symbols to ALLBASE/SQL Symbols
----------------------------------------------------------------------------------------------
| | | |
| SQLBase Symbol | ALLBASE/SQL Symbol | Meaning |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| != | <> | NOT EQUAL TO |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| !> | <= | NOT GREATER THAN |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| !< | >= | NOT LESS THAN |
| | | |
----------------------------------------------------------------------------------------------

The ; Terminator.

PC API adds a semicolon as required to SQL statements as they are passed
to ALLBASE/SQL.

Ignored Warning and Error Messages.

The following warning and error messages are ignored by PC API:

The data updated/deleted is in the RC/RU transaction. (DBWARN
2017)

NULL values eliminated from the argument of an aggregate function.
(DBWARN 2041)

Command requires a transaction to be in progress. (DBERR 2102)

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.
* IN predicate.
* Isolation levels.
* Row identifiers.
* 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 them 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.

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).

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 RO (Read Only) level is translated to the ALLBASE/SQL Read
Uncommitted (RU) isolation level.

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

Row Identifiers.

The SQLBase row identifier, ROWID, is unique for each version of a given
row. The ROWID is updated whenever there is any change to a row. When
optimistic locking is used, the application can determine if a row was
updated since it was initially read.

The ALLBASE/SQL row identifier, tuple ID (TID), is similiar to the
SQLBase ROWID, except it is not updated whenever there is a change to a
row. ALLBASE/SQL does not have optimistic locking.

System Catalog Views.

The SYSSQL system catalog views installed with the Views Script contain
all of the information needed to map the SYSSQL 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 their
differences. When equivalent information required to define the SYSSQL
view is not available from the ALLBASE/SQL system catalog view, an
invariant value, shown under "Differences" in Table 3-5 , is supplied.

Table 3-5. 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