MX Database and Application Migration Guide

Converting SQL/MP Applications to SQL/MX

Applications

HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001

4-46

Embedded SQL Statements

EXECUTE Statement

In NonStop SQL/MX, input data is supplied by the USING clause that specifies either a

list of host variables or a SQL descriptor area. Output data is provided by the INTO

clause that specifies either a list of host variables or a SQL descriptor area. See

Descriptor Area

NonStop SQL/MP implements the RETURNING clause for output data. NonStop

SQL/MX does not provide a RETURNING clause.

If you perform a PREPARE and an EXECUTE of a SQL UPDATE, INSERT or DELETE

statement on a non-audited table, NonStop SQL/MX does not close the table and will

not release record and table locks. You can use FUP LISTLOCKS to check if your

MXCI session keeps locks on the table after completing the EXECUTE statement.

Then you can manually execute an 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

Positioned DELETE Yes

Positioned UPDATE Changed NonStop SQL/MX allows this statement to be

dynamic.

PREPARE Yes SQL/MX FROM clause does not allow

quoted strings.

RELEASE No

SELECT INTO Changed See SELECT Statements

on page 4-23.

WHENEVER Changed Usage changes. See WHENEVER

Declarative on page 4-52.

Positioned DELETE Yes

Table 4-12. Embedded SQL Statements (page2of2)

Embedded SQL/MP

Statements and Directives

Implemented

in SQL/MX? Comments