NonStop Server for Java (NSJ) Programmer's Guide (NSJ 2.1+)

DECLARE CURSOR❍
DELETE❍
INSERT❍
SELECT❍
UPDATE❍
Substitution also works on subqueries.
The alias substitution mechanism does not substitute Guardian file names and Guardian DEFINEs. If your SQL query uses a
DEFINE, you cannot use this alias substitution mechanism.
In the following example, no alias substitution is possible:
select jobcode, deptnum, first_name, last_name from persnl.employee
where jobcode > 500 and deptnum <= 3000 order by jobcode browse access
In the following example of a nested statement, the candidates for substitution are printed in bold type:
update employee set salary = salary*1.1
where deptnum in (select deptnum from dept where location = "San Francisco")
In the following example, the candidates for substitution are printed in bold type. Candidates include the correlation
names, o and p, because they can be user-defined names that can be replaced by Guardian table names (such as parts).
select ordernum,
sum(qty_ordered*price) from parts p, odetail o
where o.partnum = parts.partnum
and ordernum in (select ordernum from orders o, customer c
where o.custnum = c.custnum and state = "CALIFORNIA" group by ordernum)
Note: Mapping parts to a fully qualified Guardian name (such as $sys1.vol.subvol.parts) results in
an invalid SQL statement because the substitution procedure also replaces the second occurrence of parts.
To check the effect of alias substitution during development, use the SQLMPConnection.nativeSQL(String str)
function. This function returns the SQL statement that results from performing alias substitutions on the SQL statement str.
Troubleshooting
This subsection explains the following subjects:
No suitable driver error●
Data truncation●
Dangling statements●
No suitable driver Error
The error message No suitable driver means that the Java Virtual Machine (JVM) could not load the JDBC driver that
the program needs. For instructions on loading JDBC drivers, see Loading a Driver.
Data Truncation
Data truncation occurs without warning. To prevent data truncation, use Java data types that are large enough to hold the SQL
data items that are to be stored in them. (See Compatible Java and SQL/MP Data Types.)