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-42
Host Variable Declarations
Host Variable Declarations
When converting an embedded SQL/MP application to an embedded SQL/MX
application, change the host variable declarations according to these guidelines:
•
Placement of Structure Definitions on page 10-42
•
SETSCALE Function on page 10-42
•
Declaration of Character Host Variables on page 10-43
•
Variable-Length Character Data in Embedded SQL C Programs on page 10-43
•
INVOKE Directive on page 10-43
•
Host Variables With Date-Time Data Types on page 10-44
Placement of Structure Definitions
Many SQL/MP programs declare host variables from structures that are defined
outside the DECLARE SECTION. NonStop SQL/MX requires that structure definitions
for host variables be declared between BEGIN DECLARE SECTION and END
DECLARE SECTION.
SETSCALE Function
In NonStop SQL/MP, using the C programming language, you use the SETSCALE
function to set the scale of certain data types. You must use the SETSCALE function
whenever the host variable is used in the query.
In NonStop SQL/MX, scale information for a host variable is supplied when the host
variable is declared inside the BEGIN DECLARE SECTION. The SETSCALE function
is not supported in NonStop SQL/MX.
In NonStop SQL/MP, this C program fragment uses SETSCALE with an INSERT
statement to create a new row with the value 98.34 in the PARTS.PRICE column after
storing the value in host variable
unit_price. The value is multiplied by 100 for
storing as a whole number.
unit_price = 9834;
EXEC SQL INSERT INTO =PARTS (PRICE)
VALUES (SETSCALE (:unit_price, 2));
In NonStop SQL/MX, you can declare a host variable as NUMERIC (p,s), where p is
the precision and
s is the scale. You do not use the SETSCALE function when you
use the host variable. For example, you can declare a numeric(4,2) host variable as:
NUMERIC(4,2) unit_price;
The same scale applies when a value is fetched. For this example, the fetched value
has an implicit scale of 2.