SQL Programming Manual for Pascal

NonStop SQL Version Issues
HP NonStop SQL Programming Manual for Pascal—528614-001
D-6
Migrating an Application to Run on Release 2 (C30)
Check your programs for these words in SQL statements and change any that are
present as correlation names, cursor names, SQL statement names, and column
names. The Pascal compiler detects the use of these reserved words and issues a
syntax error.
•
Result of subqueries
If a subquery in a WHERE clause returns no rows, NULL is returned to the
subquery. To be ANSI compatible, this result has changed from error 100 (no rows
selected or modified) to NULL for the NonStop SQL C10 implementation.
For example, consider the following select from tables T and T1, with the select
from T1 being a subquery that returns no rows:
SELECT * FROM t WHERE a = (SELECT a FROM t1)
Because the subquery returns no rows, the WHERE clause is evaluated as equal
to NULL and will fail. The evaluation is WHERE a = NULL
To migrate programs affected by the change in the result of subqueries, you do not
have to code extra checking in the program. Be aware, however, that C30
programs might retrieve more rows than C10 programs, because the C10
programs would have received error 100 (no rows selected or modified).
•
Querying the\ NULLP value in the SQLDA
NULLP means null pointer. For C10, the system returns a system-defined value for
NULLP in the VAR-PTR field of SQLDA if the names buffer is not large enough.
A C30 system returns a negative value in the VAR-PTR field instead of NULLP.
Programs should check for a value less than zero instead of checking for NULLP.
NULLP is an internal constant and can change; therefore, its actual value is not
documented.
•
Additional SQLDA fields
Using a C30 SQLDA structure is necessary only if the program might encounter
null values or the new C30 data types FLOAT, REAL, DOUBLE PRECISION,
DATETIME, DATE, TIME, TIMESTAMP, and INTERVAL. The SQLDA structure for
C30 has additional fields that can contain information about these new C30
features. You can use a C10 (RELEASE1) SQLDA on a C30 system if the program
does not encounter these C30 features.
Caution. A table or view created with NonStop SQL C10 and having INNER, JOIN, or LEFT
as a column name or constraint name will not be available to a NonStop SQL program that
references these columns or constraints and is SQL compiled with the C30 SQL compiler. The
SQL compiler detects references to C30 reserved words and issues an error.
Note. Remember that a program can encounter null values even if there are no null
values in the database.