HP 3000 Manuals HP 3000 Manuals
Declaring Host Variables
Host variables may be declared wherever you can declare variables in C
programs. For the purpose of this discussion, we define declaration part
as the portion of a C program where variables having the scope of a file,
a function, or a block can be declared.
At run time, the scope of a host variable is the same as that of any
other C variable declared in the same declaration part. At preprocessing
time, however, all host variable declarations are treated as global
declarations. Therefore host variables having the same name in different
declaration parts must also have the same C type description in each
variable declaration.
Creating Declaration Sections
Host variables must be declared in what is known as a declare section. A
declare section consists of the SQL command BEGIN DECLARE SECTION, one or
more variable declarations, and the SQL command END DECLARE SECTION (as
shown in Figure 3-1). More than one declare section may appear in a
given declaration part. However, a host variable name may appear only
once in a given declaration part.
Each host variable is declared by using a C type declaration. The
declaration contains the same components as any C variable declaration:
EXEC SQL BEGIN DECLARE SECTION;
int OrderNumber;
| |
| |
| data name
|
data type
EXEC SQL END DECLARE SECTION;
The data name must be the same as the host variable name in the
corresponding SQL statement. The data type must satisfy ALLBASE/SQL data
type and ALLBASE/SQL C preprocessor requirements.
_________________________________________________
| |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . Declarations for global host variables.|
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| int query() |
| { |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . Declarations for local host variables. |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| . |
| . Declarations for local host variables. |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| } |
| . |
| . |
| . |
| main() |
| { |
| . |
| . |
| . |
| } |
| |
| |
| |
| |
| |
| |
_________________________________________________
Figure 3-1. Host Variable Declarations
Declaring Variables for Data Types
Any variable can be used as a host variable. Table 3-1 summarizes C data
declarations for host variables of each ALLBASE/SQL data type. Only the
type descriptions shown in Table 3-1 are supported by the C preprocessor.
Note in particular that the preprocessor does not support user-defined
data types.
CHAR Data.
A CHAR column can be declared for character strings ranging from 1 to
3996 bytes.
A single byte of the char data type in ALLBASE/SQL is directly equivalent
to a single byte of the CHAR data type in C. Strings greater than one
byte in C are associated with the char array data type. Character arrays
in C are not directly equivalent to the ALLBASE/SQL CHAR data type;
however, they are compatible. ALLBASE/SQL handles the conversion between
ALLBASE/SQL CHAR data types and C char array data types through code
generated by the preprocessor and embedded in the application's modified
source code file.
For C strings (character arrays), C has the convention of using an ASCII
0 ('\0'), the null character, to mark the end of the string. Therefore,
char host variables declared in C must have a size one greater than their
ALLBASE/SQL column definition, to allow for the null character at the end
of the string. The PartNumber column in the PurchDB.Parts table is
defined as CHAR(30). The associated host variable is therefore declared:
char PartNumber[31];
When ALLBASE/SQL assigns CHAR data to a char array host variable, the
total length of the ALLBASE/SQL CHAR field is stored in the host variable
including any trailing blanks on the right of the data string. An ASCII
0 is then added after the last byte of the string.
VARCHAR Data.
A VARCHAR column can be declared for strings ranging from 1 to 3996
bytes. ALLBASE/SQL stores only the actual value of the string, not any
trailing blanks.
Strings greater than one character in C are equivalent to the VARCHAR
data type in ALLBASE/SQL. C host variables for VARCHAR data types in
ALLBASE/SQL are declared the same as variables declared for CHAR data
types. The only difference is that when ALLBASE/SQL assigns VARCHAR data
to a char array host variable, no trailing blanks are added. An ASCII 0
is placed after the last character of the C string based on the specified
length of the string.
The VendorRemarks column in the PurchDB.Vendors table is defined as
VARCHAR(60). It is therefore declared:
char VendorRemarks[61];
Table 3-1. Data Type Declarations
-------------------------------------------------------------------
| | |
| SQL DATA TYPES | C DATA DECLARATIONS |
| | |
-------------------------------------------------------------------
| | |
| CHAR(1) | char dataname; |
| | |
| CHAR(n) | char dataname[n+1]; |
| | |
| VARCHAR(n) | char dataname[n+1]; * |
| | |
| SMALLINT | short dataname; or |
| | |
| | short int dataname; |
| | |
| INTEGER | int dataname; or |
| | |
| | long int dataname; or |
| | |
| | long dataname; |
| | |
| REAL | float dataname; |
| | |
| FLOAT(1..24) | float dataname; |
| | |
| FLOAT(25..53) | double dataname; |
| | |
| DOUBLE PRECISION | double dataname; |
| | |
| BINARY | sqlbinary dataname; |
| | |
| | sqlbinary dataname[n]; |
| | |
| VARBINARY | sqlvarbinary dataname[m]; ** |
| | |
| DECIMAL | double dataname; |
| | |
| DATE | char dataname[11]; |
| | |
| TIME | char dataname[9]; |
| | |
| DATETIME | char dataname[24]; |
| | |
| INTERVAL | char dataname[21]; |
| | |
| | * This declaration is for |
| | non-dynamic commands only. Refer to |
| | the chapter, "Using Dynamic |
| | Operations," for a description of |
| | how to use VARCHAR dynamically. |
| | ** See the "BINARY Data" section |
| | later in this chapter for the |
| | calculation of m. |
| | |
-------------------------------------------------------------------
Table 3-2. Program Element Declarations
-------------------------------------------------------------------
| | |
| PROGRAM ELEMENT | C DATA DECLARATIONS |
| | |
-------------------------------------------------------------------
| | |
| Indicator variable | sqlind indvarname; |
| | |
-------------------------------------------------------------------
| | |
| Array of n rows | struct structtypename{ |
| | |
| Data values | validdatatype column1name; |
| | |
| | validdatatype column2name; |
| | |
| Indicator variable | sqlind indvarname; |
| | }structname[n]; |
| | |
| StartIndex | short startindexname; or |
| | int startindexname; |
| | |
| NumberOfRows | short numrowsname; or |
| | int numrowsname; |
| | |
-------------------------------------------------------------------
| | |
| Dynamic commands | char commandname[n+1]; |
| | |
-------------------------------------------------------------------
| | |
| Savepoint numbers | int savepointname; |
| | |
-------------------------------------------------------------------
| | |
| Message catalog messages | char messagename[n+1]; |
| | |
-------------------------------------------------------------------
| | |
| DBEnvironment name | char DBEName[n+1]; |
| | |
-------------------------------------------------------------------
SMALLINT Data.
You can assign values ranging from -32,768 to +32,767 to a column defined
as SMALLINT.
INTEGER Data.
You can assign values ranging from -2,147,483,648 to +2,147,483,647 to a
column defined as INTEGER.
FLOAT Data.
ALLBASE/SQL offers the option of specifying the precision of floating
point data. You have the choice of a 4-byte or an 8-byte floating point
number. (This conforms to ANSI SQL86 level 2 specifications.) The
keyword REAL and FLOAT(1) through FLOAT(24) specifications map to a
4-byte float. The FLOAT(25) through FLOAT(53) and DOUBLE PRECISION
specifications map to an 8-byte float.
The REAL data type could be useful when the number you are dealing with
is very small, and you do not require a great deal of precision.
However, it is subject to overflow and underflow errors if the value goes
outside its range. It is also subject to greater rounding errors than
double precision. With the DOUBLE PRECISION (8-byte float) data type,
you can achieve significantly higher precision and have available a
larger range of values.
By using the CREATE TABLE or ALTER TABLE command, you can define a
floating point column by using a keyword from the following table. See
the ALLBASE/SQL Reference Manual for complete syntax specifications.
Table 3-3. ALLBASE/SQL Floating Point Column Specifications
-----------------------------------------------------------------------------------------------
| | | |
| Possible Keywords | Range of Possible Values | Stored In |
| | | and Boundary |
| | | Aligned On |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| REAL | -3.402823 E+38 through -1.175495 E-38 | 4 bytes |
| or | and | |
| FLOAT(n) | 1.175495 E-38 through 3.402823 E+38 | |
| where | and | |
| | 0 | |
| n = 1 through 24 | | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| DOUBLE PRECISION | -1.79769313486231 E+308 through -2.22507385850721 E-308 8 bytes |
| or | and | |
| FLOAT | +2.22507385850721 E-308 through +1.79769313486231 E+308 |
| or | and | |
| FLOAT(n) | 0 | |
| where | | |
| | | |
| n = 25 through 53 | | |
| | | |
-----------------------------------------------------------------------------------------------
Floating Point Data Compatibility.
Floating point data types are compatible with each other and with other
ALLBASE/SQL numeric data types (DECIMAL, INTEGER, and SMALLINT). All
arithmetic operations and comparisons and aggregate functions are
supported.
BINARY Data.
As with other data types, use the CREATE TABLE or ALTER TABLE statement
to define a binary or varbinary column. Up to 3996 bytes can be stored
in such a column.
BINARY data is stored as a fixed length of left-justified bytes. It is
zero padded up to the fixed length you have specified. VARBINARY data is
stored as a variable length of left-justified bytes. You specify the
maximum possible length. (Note that CHAR and VARCHAR data is stored in
the same manner except that CHAR data is blank padded.)
Binary Data Compatibility.
BINARY and VARBINARY data types in an ALLBASE/SQL database are compatible
with each other and with CHAR and VARCHAR data types. They can be used
with all comparison operators and the aggregate functions MIN and MAX,
but arithmetic operations are not allowed.
Using the LONG Phrase with Binary Data Types.
If the amount of data in a given column of a row can exceed 3996 bytes,
it must be defined as a LONG column. Use the CREATE TABLE or ALTER TABLE
command to specify the column as either LONG BINARY or LONG VARBINARY.
LONG BINARY and LONG VARBINARY data is stored in the database just as
BINARY and VARBINARY data, except that its maximum possible length is
practically unlimited.
When deciding on whether to use LONG BINARY versus LONG VARBINARY, and if
space is your main consideration, you would choose LONG VARBINARY.
However, LONG BINARY offers faster data access.
LONG BINARY and LONG VARBINARY data types are compatible with each other,
but not with other data types. Also, the concept of inputting and
accessing LONG column data differs from that of other data types. Refer
to the ALLBASE/SQL Reference Manual for detailed syntax and to the
chapter in this document titled "Defining and Using Long Columns" for
information about using LONG column data.
Declaring Host Variables for BINARY Data.
Host variables for BINARY data columns must be declared as sqlbinary, as
in the following example:
EXEC SQL BEGIN DECLARE SECTION;
:
sqlbinary BinaryHostVariableName[n];
:
EXEC SQL END DECLARE SECTION;
The host variable array size n equals the length of the column as defined
in the database.
At preprocessing time, the ALLBASE/SQL preprocessor sqlbinary data type
is defined as an unsigned char with the following statement in the SQL
Type Include File:
typedef unsigned char sqlbinary;
An sqlbinary host variable is used for fixed length data. It is your
responsibility to appropriately load binary data into the host variable
before an insert or update operation.
In the following example, data from
a binary column defined with a length of 12 is selected into an sqlbinary
host variable.
:
EXEC SQL BEGIN DECLARE SECTION;
sqlbinary BinaryHV[12];
EXEC SQL END DECLARE SECTION;
:
EXEC SQL SELECT BinaryColumn
INTO :BinaryHV
FROM TableA;
:
Declaring Host Variables for VARBINARY Data.
Host variables for VARBINARY data columns must be declared as
sqlvarbinary, as in the following example:
EXEC SQL BEGIN DECLARE SECTION;
:
sqlvarbinary VarbinaryHostVariableName[m]
:
EXEC SQL END DECLARE SECTION;
At preprocessing time, the ALLBASE/SQL preprocessor sqlvarbinary data
type is defined as an integer with the following statement in the SQL
Type Include File:
typedef int sqlvarbinary;
You specify the host variable array size m based on the following
formula:
m = 1 + [n/4]
where:
* m = the host variable array size
* n = the length of the column as defined in the database
* [n/4] = the LeastInteger of (n/4)
LeastInteger is the smallest integer >= (n/4).
In order to pass information between
an sqlvarbinary host variable and the database, a special format is used.
The internal format of an sqlvarbinary host variable is illustrated
below:
Array Index 0 1 ... m-1
Byte Offset 0 4 (m-1)*4 m*4
+---------+----------------------------------+---------+
| Length | Data |
+---------+----------------------------------+---------+
where:
* Length, in the first four bytes, represents the actual data
length.
* Data starts from byte offset four and represents the varbinary
data.
* (m-1)*4 equals the byte offset of the last element of the
sqlvarbinary array.
* m*4 equals the end of the sqlvarbinary array.
For example, to declare an sqlvarbinary host variable for a varbinary
column having a maximum length of nine, you define the host variable with
an array size of four (one four byte element to hold the actual data
length and three four byte elements for the data):
EXEC SQL BEGIN DECLARE SECTION;
sqlvarbinary VarbinaryHV[4];
EXEC SQL END DECLARE SECTION;
Inserting and Updating VARBINARY Data.
Before issuing an INSERT or UPDATE statement, you must load the
sqlvarbinary host variable using the format mentioned in the previous
section. Two examples of loading data into an sqlvarbinary host variable
are presented below. The first example loads data from a buffer; the
second example loads data from a union structure.
Using a Buffer
Suppose your varbinary column is defined in the database with a maximum
length of 16. You want to to load it with data from a buffer called
bbuff as follows:
:
int length;
char bbuff[16];
EXEC SQL BEGIN DECLARE SECTION;
Derive the host variable length from the formula described in the previous section,
"Declaring Host Variables for Varbinary Data," as follows:
1 + the LeastInteger of (16/4).
sqlvarbinary VarbinaryHV[5];
EXEC SQL END DECLARE SECTION;
:
Load the first array element with the actual data length.
Here we'll assume actual length to be 11 bytes.
VarbinaryHV[0] = 11;
Load the data in bbuff, starting at the second element of the array.
memcpy(&VarbinaryHV + 1, &bbuff, 11);
EXEC SQL INSERT INTO TableA VALUES (:VarbinaryHV, ...);
:
Using a Union Structure
Here is another method of loading the same host variable. In the
following example, the data length and data are loaded into a union
structure and then into the sqlvarbinary host variable:
:
int length;
char bbuff[16];
union u_t {
struct {
int length;
int data[4];
} s1;
int ubuff[5];
} u1;
EXEC SQL BEGIN DECLARE SECTION;
:
Derive the host variable length from the formula described in the previous section,
"Declaring Host Variables for Varbinary Data," as follows:
1 + the LeastInteger of (16/4).
sqlvarbinary VarbinaryHV[5];
EXEC SQL END DECLARE SECTION;
:
Load the length field of the structure with the actual column data length.
Here we'll assume actual length to be 11 bytes.
u1.s1.length = 11;
Load the data in bbuff, into the data field of the structure.
memcpy(u1.s1.data, &bbuff, 11);
Load the length and the data into the varbinary host variable.
memcpy(&VarbinaryHV, &(u1.ubuff), sizeof(u1.ubuff));
EXEC SQL INSERT INTO TableA VALUES (:VarbinaryHV, ...);
:
Selecting and Fetching VARBINARY Data.
After the successful execution of a SELECT or FETCH statement, the length
of the data returned for any varbinary column is found in the first
element of the related host variable array.
Two examples of retrieving data from an sqlvarbinary host variable are
presented below. The first example loads retrieved data into a buffer;
the second example loads retrieved data into a union structure. The same
data declarations used for the examples in the previous section are
assumed.
Using a Buffer
The following example selects data into an sqlvarbinary host variable,
loads it into a buffer named bbuff, and saves the length of the data in
an integer variable named length:
:
EXEC SQL SELECT * FROM TableA INTO :VarbinaryHV, ...;
memcpy (&bbuff, &VarbinaryHV+1, VarbinaryHV[0]);
length = VarbinaryHV[0];
:
Using a Union Structure
The following example selects data into an sqlvarbinary host variable,
loads it into a union structure, saves the length of the data in an
integer variable named length, and saves the data in a buffer named
bbuff:
:
EXEC SQL SELECT * FROM TableA INTO :VarbinaryHV, ... ;
memcpy(&(u1.ubuff), &VarbinaryHV, sizeof(u1.ubuff));
length = u1.s1.length;
memcpy(&bbuff, &(u1.s1.data), u1.s1.length);
:
DECIMAL Data.
The DECIMAL data type is not supported by ALLBASE/SQL C. The DECIMAL data
type is compatible with an ALLBASE/SQL C double data type.
When you use DECIMAL values in arithmetic operations and certain
aggregate functions, the precision and scale of the result are functions
of the precisions and scales of the values in the operation. Refer to
the ALLBASE/SQL Reference Manual for a complete account of how to
calculate the precision and scale of DECIMAL results.
DATE, TIME, DATETIME, and INTERVAL Data.
DATE, TIME, DATETIME, and INTERVAL data types are declared as character
strings. (See the previous section, "CHAR Data.") For example:
/* Declare host variables and, where applicable, indicator variables. */
EXEC SQL BEGIN DECLARE SECTION; /* DATETIME DATA TYPE */
char BatchStamp[24]; /* DATE DATA TYPE */
char TestDate[11];
sqlind TestDateInd; /* TIME DATA TYPE */
Char TestStart[9];
sqlind TestStartInd; /* INTERVAL DATE TYPE */
char LabTime[21];
sqlind LabTimeInd;
EXEC SQL END DECLARE SECTION;
/*DECLARE and OPEN CURSOR C1 here. Nulls not allowed for BatchStamp.*/
EXEC SQL FETCH C1
INTO :BatchStamp,
:TestDate :TestDateInd,
:TestSart :TestStartInd,
:LabTime :LabTimeInd;
See the chapter, "Understanding Date/Time Functions," for information on
using date/time data types with date/time functions.
Using Default Data Values
You can choose a default value other than NULL when you create or alter a
table by using the DEFAULT specification. Then when data is inserted,
and a given column is not in the insert list, the specified default value
is inserted. Or when you alter a table, adding a column to existing
rows, every occurrence of the column is initialized to the default value.
(This conforms to ANSI SQL1 level 2 with addendum-1 and FIPS 127
standards.)
When a table or column is defined
with the DEFAULT specification, you will not get an error if a column
defined as NOT NULL is not specified in the insert list of an INSERT
command. Without the DEFAULT specification, if a column is defined as
NOT NULL, it must have some value inserted into it. However, if the
column is defined with the DEFAULT specification, it satisfies both the
requirement that it be NOT NULL and have some value, in this case, the
default value. If a column not in an insert list does allow a NULL, then
a NULL is inserted instead of the default value.
Your default specification options are:
* NULL.
* USER (this indicates the current DBEUser ID).
* A constant.
* The result of the CURRENT_DATE function.
* The result of the CURRENT_TIME function.
* The result of the CURRENT_DATETIME function.
Complete syntax for the CREATE TABLE and ALTER TABLE commands as well as
definitions of the above options are found in the ALLBASE/SQL Reference
Manual . In effect, by choosing any option other than NULL, you assure
the column's value to be NOT NULL and of a particular format, unless and
until you use the UPDATE command to enter another value.
In the following example, the OrderNumber column defaults to the constant
5, and it is possible to insert a NULL value into the column:
CREATE PUBLIC TABLE PurchDB.Orders (
OrderNumber INTEGER DEFAULT 5,
VendorNumber INTEGER,
OrderDate CHAR(8))
IN OrderFS
However, suppose you want to define a column default and specify that the
column cannot be null. In the next example, the OrderNumber column
defaults to the constant 5, and it is not possible to insert a NULL value
into this column:
CREATE PUBLIC TABLE PurchDB.Orders (
OrderNumber INTEGER DEFAULT 5 NOT NULL,
VendorNumber INTEGER,
OrderDate CHAR(8))
IN OrderFS
Coding Considerations.
Any default value must be compatible with the data type of its
corresponding column. For example, when the default is an integer
constant, the column for which it is the default must be created with an
ALLBASE/SQL data type of INTEGER, REAL, or FLOAT.
In your application, you input or access data for which column defaults
have been defined just as you would data for which defaults are not
defined. In this chapter, refer to the section, "Declaring Variables for
Data Types", for information on using the data types in your program.
Also refer to the section, "Declaring Variables for Compatibility", for
information relating to compatibility.
When the DEFAULT Clause Cannot be Used.
* You can specify a default value for any ALLBASE/SQL column except
those defined as LONG BINARY or LONG VARBINARY. For information on
these data types, see the section in this document titled "Using
the LONG Phrase with Binary Data Types."
* With the CREATE TABLE command, you can use either a DEFAULT NULL
specification or the NOT NULL specification. An error results if
both are specified for a column as in the next example:
CREATE PUBLIC TABLE PurchDB.Orders (
OrderNumber INTEGER DEFAULT NULL NOT NULL,
VendorNumber INTEGER,
OrderDate CHAR(8))
IN OrderFS
Declaring Variables for Compatibility
Under the following conditions, ALLBASE/SQL performs data type conversion
when executing SQL commands containing host variables under the following
circumstances:
* When the data types of values transferred between your program and
a DBEnvironment do not match.
* When data of one type is moved to a host variable of a different
type.
* when values of different types appear in the same expression.
Data types for which type conversion can be performed are called
compatible data types. Table 3-4 summarizes data type-host variable
compatibility. It also points out which data type combinations are
incompatible and which data type combinations are equivalent, i.e.,
require no type conversion. E describes an equivalent situation, C a
compatible situation, and I an incompatible situation.
Table 3-4. C Data Type Equivalency and Compatibility
----------------------------------------------------------------------------------------------------
| | | | | | |
| ALLBASE\SQL | char | char[n] | short | int | double |
| Data | | | shortint | long | longfloat |
| Types | | | | longint | |
| | | | | | |
----------------------------------------------------------------------------------------------------
| | | | | | |
| CHAR | E | C | I | I | I |
| | | | | | |
| VARCHAR | C | C | I | I | I |
| | | | | | |
| BINARY | C | C | I | I | I |
| | | | | | |
| VARBINARY | I | I | C | C | I |
| | | | | | |
| DATE | C | C | I | I | I |
| | | | | | |
| TIME | C | C | I | I | I |
| | | | | | |
| DATETIME | C | C | I | I | I |
| | | | | | |
| INTERVAL | C | C | I | I | I |
| | | | | | |
| SMALLINT | I | I | E | C | C |
| | | | | | |
| INTEGER | I | I | C | E | C |
| | | | | | |
| FLOAT | I | I | C | C | E |
| | | | | | |
| DECIMAL | I | I | C | C | C |
| | | | | | |
----------------------------------------------------------------------------------------------------
As the following example illustrates, the ISQL INFO command provides the
information you need to declare host variables compatible with or
equivalent to ALLBASE/SQL data types. It also provides the information
you need to determine whether an indicator variable is needed to handle
null values:
isql=> INFO PurchDB.OrderItems;
Column Name Data Type (length) Nulls Allowed
---------------------------------------------------------------------
ORDERNUMBER Integer NO
ITEMNUMBER Integer NO
VENDPARTNUMBER Char (16) YES
PURCHASEPRICE Decimal (10,2) NO
ORDERQTY Smallint YES
ITEMDUEDATE Char ( 8) YES
RECEIVEDQTY Smallint YES
For example, the query in Figure 3-2 produces a single-row query result.
The declare section contains data types equivalent to or compatible with
the data types in the PurchDB.OrderItems table:
* PurchasePrice is declared as a double variable because it holds
the DECIMAL result of an aggregate function on a DECIMAL column.
* Discount is declared as a double variable because it is used in an
arithmetic expression with a DECIMAL column, PurchasePrice.
* OrderQty is declared as a short int variable because it holds the
result of a SMALLINT column, OrderQty.
* OrderQtyInd is an indicator variable, necessary because the
resulting OrderQty can contain null values. Note in the INFO
example above that this column allows null values.
* OrderNumber is an integer variable because the column whose data
it holds is INTEGER.
_________________________________________________________
| |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| double Discount ; |
| double PurchasePrice ; |
| short int OrderQty ; |
| sqlind OrderQtyInd ; |
| int OrderNumber ; |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| { |
| . |
| EXEC SQL SELECT PurchasePrice * :Discount ,|
| OrderQty, |
| INTO :PurchasePrice, |
| :OrderQty :OrderQtyInd |
| FROM PurchDB.OrderItems |
| WHERE OrderNumber = :OrderNumber |
| . |
| . |
| } |
_________________________________________________________
Figure 3-2. Declaring Host Variables for Single-Row Query Results
The example in Figure 3-3 is similar to that in Figure 3-2. This query,
however, is a BULK query, which may return a multiple-row query result.
And it incorporates a HAVING clause.
* OrdersArray is the name of the array for storing the query result.
It can hold up to 26 rows. Each row in the array has the same
format as that in the single-row query result just discussed.
* FirstRow and TotalRows are declared as short int variables, since
their maximum value is the size of the array (in this case, 26).
* GroupCriterion is an integer variable because its value is
compared in the HAVING clause with the result of a COUNT function,
which is always an INTEGER value.
________________________________________________________________________
| |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| double Discount; |
| struct { |
| double PurchasePrice; |
| short int OrderQty; |
| sqlind OrderQtyInd; |
| int OrderNumber; |
| } OrdersArray[26]; |
| short int FirstRow; |
| short int TotalRows; |
| int LowValue; |
| int HighValue; |
| int GroupCriterion; |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| { |
| . |
| . |
| EXEC SQL BULK SELECT PurchasePrice * :Discount , |
| OrderQty, |
| OrderNumber |
| INTO :OrdersArray, |
| :FirstRow, |
| :TotalRows |
| FROM PurchDB.OrderItems |
| WHERE OrderNumber |
| BETWEEN :LowValue AND :HighValue |
| GROUP BY OrderQty, OrderNumber |
| HAVING COUNT(ItemNumber) > :GroupCriterion ;|
| . |
| . |
| } |
________________________________________________________________________
Figure 3-3. Declaring Host Variables for Multiple-Row Query Results
String Data Conversion.
When ALLBASE/SQL stores the characters in a C string into a CHAR column,
the final ASCII 0 is removed and any remaining positions to the right are
padded with spaces. Internally, when ALLBASE/SQL stores the characters
in a C string to a VARCHAR column, it only stores the string up to but
not including the ASCII 0. The length of the string is stored in a
four-byte header in the front of each VARCHAR data type.
When ALLBASE/SQL moves VARCHAR data to a character array variable, only
the length of the string is moved, no trailing blanks are added.
ALLBASE/SQL embeds preprocessor generated code into the modified source
code file to place an ASCII 0 at the end of the string variable. Refer
to the "Using Dynamic Operations" chapter in this manual for further
information specific to dynamic operations.
String Data Truncation.
If the target host variable used in a SELECT or FETCH operation is too
small to hold an entire string, the string is truncated. You can use an
indicator variable to determine the actual length of the string in bytes
before truncation:
EXEC SQL BEGIN DECLARE SECTION;
char LittleString[n];
sqlind LittleStringInd;
.
.
.
EXEC SQL END DECLARE SECTION;
.
.
.
{
.
.
.
EXEC SQL SELECT BigString
INTO :LittleString :LittleStringInd
.
.
.
}
When the value in column BigString is too long to fit in host variable
LittleString, ALLBASE/SQL puts the actual length of the string in bytes
into indicator variable LittleStringInd. In this example, the maximum
number of bytes that can be stored in LittleString is n-1, since the last
byte is always reserved for the ASCII 0 null character.
If a column is too small to hold a string in an INSERT or an UPDATE
operation, the string is truncated and stored. The sqlca sqlwarn[1]
field is set to W when this occurs.
It is possible to store native language data in a character column
defined as n-computer. It is the programmer's responsibility to verify
the language definition of the column that is to receive data. If the
character column is defined for a native language, truncation will always
occur on a proper character boundary for that language.
Numeric Data Conversion.
When you use numeric data of different types in an expression or
comparison operation, data of the lesser type is converted into data of
the greater type, and the result is expressed in the greater type.
ALLBASE/SQL numeric types have the following precedence, from highest to
lowest:
1. FLOAT
2. DECIMAL
3. INTEGER
4. SMALLINT
The following example illustrates numeric type conversion:
EXEC SQL BEGIN DECLARE SECTION;
int Discount;
int MaxPurchasePrice;
.
.
.
EXEC SQL END DECLARE SECTION;
.
.
.
{
.
.
.
EXEC SQL SELECT (MAX)PurchasePrice * :Discount
INTO :MaxPurchasePrice
FROM PurchDB.OrderItems;
.
.
.
}
The select list of the query illustrated contains an aggregate function,
MAX. The argument of the function is the PurchasePrice column, defined in
the PartsDBE DBEnvironment as DECIMAL(10,2). Therefore the result of the
function is DECIMAL. Since the host variable named Discount is declared
as an integer, a data type compatible with DECIMAL, ALLBASE/SQL converts
the value in Discount to a DECIMAL quantity having a precision of 10 and
a scale of 0.
After subtraction, data conversion occurs again before the DECIMAL result
is stored in the integer host variable MaxPurchasePrice. In this case,
the fractional part of the DECIMAL value is truncated.
Refer to the ALLBASE/SQL Reference Manual for additional information on
how type conversion can cause truncation and overflow of numeric values.
Declaring Variables for Program Elements
The following section discusses how to declare elements specific to
ALLBASE/SQL programs. In addition, Table 3-2 provides examples of these
special elements.
sqlca Array.
Every ALLBASE/SQL C program must have the SQL Communications Area (sqlca)
declared in the global declaration part. You can use the INCLUDE command
to declare the sqlca:
EXEC SQL INCLUDE SQLCA;
When the C preprocessor parses this command, it inserts the following
type definition into the modified source file:
sqlca_type sqlca;
Optionally, you can use this type definition in the global declaration
part of your source file instead of using the INCLUDE command to declare
the sqlca.
Refer to the chapter, "Runtime Status Checking," for further information
regarding the sqlca.
Dynamic Processing Arrays.
For programs which accept dynamic queries, you include three special
declarations in a declaration part:
EXEC SQL INCLUDE SQLDA;
This command causes the preprocessor to declare the sqlda as
type sqlda_type, defined in the preprocessor-generated type
declaration include file.
sqlformat_type sqlfmts[MaxFmtArray];
This declaration identifies the format array and its size.
MaxFmtArray is a constant representing the maximum number
of columns you expect in the query result. Sqlformat_type
is defined in the type declaration include file.
char DataBuffer[MaxDataBuff];
This declaration identifies a data buffer and its size.
MaxDataBuff is a constant representing the maximum number
of bytes you will need to hold the number of rows you
request in the sqlnrow field of the sqlda.
See the chapter on "Using Dynamic Operations" for more information.
Bulk Processing Arrays.
When you declare a structure array for holding the results of a BULK
SELECT or BULK FETCH operation, ensure that you declare the fields in the
same order as in the select list. (For single-row query results,
however, the order of declaration does not have to match the select list
order.) In addition, each indicator variable field must be declared in
the declaration of the structure array immediately after the host
variable field it describes. And if used, the bulk processing indicator
variables (starting index and number of rows) are referenced in order,
immediately following the reference to your array name. Figure 3-3
provides an example.
Indicator Variables.
Each indicator variable field must be declared immediately following the
host variable field it describes as shown in Figures 3-2 and 3-3. If a
column allows nulls, a null indicator must be declared for it.
Dynamic Commands.
The maximum size for the host variables used to hold dynamic commands is
32,762 bytes. In Figure 3-4, the host variable is declared to hold a
command as large as 2048 bytes.
__________________________________________________
| |
| . |
| . |
| . |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| char DynamicCommand[2048] ;|
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| { |
| . |
| . |
| . |
| EXEC SQL PREPARE CommandOnTheFly |
| FROM :DynamicCommand ; |
| . |
| . |
| . |
| } |
__________________________________________________
Figure 3-4. Declaring Host Variables for Dynamic Commands
Savepoint Numbers.
Savepoint numbers are positive numbers ranging from 1 to 2,147,483,647.
A host variable for holding a savepoint number should be declared as an
integer.
___________________________________________________
| |
| . |
| . |
| . |
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| int SavePoint1 ; |
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| { |
| . |
| . |
| . |
| EXEC SQL SAVEPOINT :Savepoint1 ; |
| . |
| . |
| . |
| EXEC SQL ROLLBACK WORK TO :Savepoint1 ;|
| . |
| . |
| } |
___________________________________________________
Figure 3-5. Declaring Host Variables for Savepoint Numbers
Messages from the Message Catalog.
The maximum size of a message catalog message is 256 bytes.
Figure 3-6 illustrates how a host variable for holding a message might be
declared.
____________________________________________
| |
| . |
| . |
| . |
| EXEC SQL BEGIN DECLARE SECTION; |
| char SQLMessage[256] ;|
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| { |
| . |
| . |
| . |
| EXEC SQL SQLEXPLAIN :SQLMessage ; |
| printf("%s\n",SQLMessage); |
| . |
| . |
| . |
| } |
____________________________________________
Figure 3-6. Declaring Host Variables for Message Catalog Messages
DBEnvironment Name.
The maximum pathname (either relative or absolute) of a DBECon file is
128 bytes. The DBECon file name is the same as the DBEnvironment name.
The name you store in this host variable does not have to be delimited by
single quotation marks.
_______________________________________________
| |
| EXEC SQL BEGIN DECLARE SECTION; |
| char SomeDBE[128]; |
| . |
| . |
| . |
| EXEC SQL END DECLARE SECTION; |
| . |
| . |
| . |
| { |
| printf("\n Enter DBEnvironment name> ");|
| scanf("%s",SomeDBE); |
| EXEC SQL CONNECT to :SomeDBE; |
| . |
| . |
| . |
| } |
_______________________________________________
Figure 3-7. Declaring Host Variables for DBEnvironment Names
This host variable can be declared as a string or as a character array.
In the example, it is declared as a character array large enough to hold
the absolute file name of any DBECon file.