SQL/MX Comparison Guide for SQL/MP Users

Embedded SQL
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
4-6
Updatable Cursors
UNLOCK command or exit the MXCI session. You can also use standalone UPDATE,
INSERT or DELETE statements instead of using PREPARE and EXECUTE. Only
SQL/MP tables can be non-audited.
Updatable Cursors
In NonStop SQL/MP, delete operations using a cursor do not require the FOR UPDATE
OF clause of the DECLARE CURSOR statement; you must use this clause when you
update rows, but it is optional when you delete rows. In NonStop SQL/MX, both update
and delete operations require an updatable cursor.
In NonStop SQL/MP, the cursor defaults to read only unless specified FOR UPDATE.
In NonStop SQL/MX, within the DECLARE CURSOR statement, the optional FOR
clause has this form:
FOR {READ ONLY | UPDATE [OF colname [,colname]...]}
By using the FOR clause, you can specify whether the cursor is FOR READ ONLY
(read-only cursor) or FOR UPDATE OF (updatable cursor). If you do not specify the
FOR clause, and if ORDER BY or GROUP BY is specified for the cursor or if the query
expression defining the cursor specifies a read-only table, the cursor defaults to
read-only. It is the query that determines that the table is read-only, not the table’s
attributes. Otherwise, the cursor defaults to updatable (without a column list). If you
know that you are not going to update a cursor, specify FOR READ ONLY.
FETCH Statement
In NonStop SQL/MP, when a FETCH statement executes, if the number of host
variables is different from the number of columns in the result table of the declared
cursor, a warning is issued, and NonStop SQL/MP returns the number of values in the
shorter list.
In NonStop SQL/MX, the number of host variables must be the same as the number of
columns in the result table or an error is returned.
Positioned UPDATE Statement
In NonStop SQL/MP, you can execute the UPDATE ... WHERE CURRENT OF
statement for static SQL only.
In NonStop SQL/MX, you can execute the UPDATE ... WHERE CURRENT OF
statement by static SQL and dynamic SQL.
Dynamic SQL
Dynamic SQL allows a program to construct, compile, and execute all or part of an
SQL statement at run time. NonStop SQL/MP provides a descriptor area, referred to as
the SQLDA, containing information about input parameters and output variables in
dynamic SQL statements.