SQL/MX Comparison Guide for SQL/MP Users

Embedded SQL
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
4-2
Variable-Length Character Data in Embedded SQL
C Programs
SQL/MP Example
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));
NonStop SQL/MX Example
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.
Variable-Length Character Data in Embedded SQL C Programs
In NonStop SQL/MP, the VARCHAR data type defined for a column represents one
data element. However, suppose the column CUSTNAME is defined as VARCHAR(26)
in NonStop SQL/MP. The SQL/MP INVOKE directive generates a structure of the form:
struct
{
short len;
char val[27];
} custname; /* SQL/MP variable-length character data */
where len is a numeric data item that represents the length, and val is a fixed-length
character data item for the string plus an extra byte for the null terminator. In NonStop
SQL/MP, you can also explicitly declare a structure of the preceding form as a host
variable for a VARCHAR column.
In NonStop SQL/MX, in addition to the char data type, you can declare the VARCHAR
data type for host variables within embedded SQL C programs (SQL:1999 provides the
VARCHAR data type for C programs). For example, this declaration is for a column up
to 26 bytes:
VARCHAR custname[27];/* SQL/MX variable-length character data */
As in NonStop SQL/MP, the last byte of the array is for the null terminator. You must
declare the array one byte larger to allow for the null terminator. The null terminator
follows the data inserted, so if you insert a six-character string into a VARCHAR host
variable specified as 27 characters, the null terminator is the seventh character.