SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-47
Dynamic SQL
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 for static SQL and dynamic SQL.
Dynamic SQL
Dynamic SQL allows a program to construct, compile, and execute 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.
NonStop SQL/MX provides for the allocation of a descriptor area with the use of
SQL:1999 dynamic SQL statements. Unlike NonStop SQL/MP, the SQL/MX application
cannot access the descriptor directly. Access to the descriptor is encapsulated using
ANSI SQL:1999 statements. This descriptor area consists of multiple item descriptor
areas, together with a count of the number of those item descriptor areas. For further
information about using the descriptor area, see Descriptor Area on page 10-48.