Was this helpful?
SQLVAR Array Usage
The SQL Reference Guide discusses the legal values of the sqlvar array. The describe and prepare into statements set the type, length, and name information of the SQLDA. This information refers to the result columns of a prepared select statement, the fields of a form, or the columns of a table field. When the program uses the SQLDA to retrieve or set Ingres data, it must assign type and length information that now refers to the variables being pointed at by the SQLDA.
Ada Variable Type Codes
The type codes listed above (as Ada constants) are the types that describe Ingres result fields and columns. For example, the SQL types date, decimal, long varchar and money do not describe a program variable, but rather data types that are compatible with the Ada character string and numeric data types. When these types are returned by the describe statement, the type code must be changed to a compatible Ada or ESQL/Ada type.
SQL Type Codes
The following table describes the data type codes to use with Ada variables that are pointed at by the sqldata pointers:
Ada Type
SQL Type Codes (sqltype)
SQL Length (sqllen)
Short_Short_Integer
IISQ_INT_TYPE
1
Short_Integer
IISQ_INT_TYPE
2
Integer
IISQ_INT_TYPE
4
Float
IISQ_FLT_TYPE
4
Long_Float
IISQ_FLT_TYPE
8
String(1..LEN)
IISQ_CHA_TYPE
LEN
IISQLHDLR
IISQ_HDLR_TYPE
0
As described in Ada Variables and Data Types, all other types are compatible with the above Ada data types. For example, you can retrieve an SQL date into an Ada string variable, while you can retrieve money into a long_float variable.
You can specify nullable data types (those variables that are associated with a null indicator) by assigning the negative of the type code to sqltype. If the type is negative then you must point at a null indicator by sqlind. The type of the null indicator must be a 2-byte integer, short_integer, or a derivative of that type.
Character data and the SQLDA have the same rules as character data in regular Embedded SQL statements. For details of character string processing in SQL, see Ada Variables and Data Types in this chapter.
Pointer Usage with Ada Variables
In order to fill an element of the sqlvar array, you must set the type information, and assign a valid address to sqldata. The address must be that of a legally declared and allocated variable. If the element is nullable then the corresponding sqlind component must point at a legally declared null indicator.
In order to assign addresses to sqldata and sqlind, you should use the Ada address attribute or some other function that yields an address. Because null indicators are not always required, you can sometimes assign sqlind a zero-valued address. This can be accomplished by assigning to sqlind the constant IISQ_ADR_ZERO, as defined in the ESQLDA package, or the constant ADDRESS_ZERO, if you have included the SYSTEM package.
When assigning addresses, you should be careful to follow the guidelines set by the VAX/VMS Ada. For example, you should not reference a variable whose lifetime has expired, and you should not access storage beyond the allocated amount. You can use the volatile pragma when addressing variables local to a subprogram body in order to prevent the compiler from referring to a local copy of a variable. When dynamically allocating result storage variables, you may want to use the controlled pragma together with an instantiation of the generic unchecked_deallocation procedure. The SQL Terminal Monitor Application and A Dynamic SQL/Forms Database Browser, which use Dynamic SQL and the SQLDA, do not use these pragmas, but rely on the rules defined in the VAX Ada Programmer's Runtime Reference Manual.
The following example fragment sets the type information of and points at a 4-byte integer variable, an 8-byte nullable floating-point variable, and a character slice (sub-string) whose length is specified by sqllen. This example demonstrates how a program can maintain a pool of available variables, such as large arrays of a few different typed variables and a large string space. When a variable is allocated out of the pool the next available spot is incremented:
exec sql include SQLDA;
max_pool: constant := 50;
sqlda: IISQLDA(MAX_POOL);
...

-- Numeric and string pool declarations.
ind_store: array(1..MAX_POOL) of
                         Short_Integer;               -- Indicators
current_ind: Integer := 0;
int4_store: array(1..MAX_POOL) of Integer;  -- Integers
current_int: Integer := 0;
flt8_store: array(1..MAX_POOL) of Long_Float; -- Floats
current_flt: Integer := 0;
char_store: String(1..3000);             -- String buffer
current_chr: Integer := 1;

...
sqlda.sqlvar(1).sqltype := IISQ_INT_TYPE;
                                                  -- 4-byte integer
sqlda.sqlvar(1).sqllen  := 4;
sqlda.sqlvar(1).sqldata:= int4_store(current_int)'Address;
sqlda.sqlvar(1).sqlind := IISQ_ADR_ZERO;
current_int            := current_int + 1;
                                          -- Update integer pool

sqlda.sqlvar(2).sqltype := -IISQ_FLT_TYPE;
                                 -- 8-byte nullable float
sqlda.sqlvar(2).sqllen := 8;
sqlda.sqlvar(2).sqldata :=flt8_store(current_flt)'Address;
sqlda.sqlvar(2).sqlind := ind_store(current_ind)'Address;
current_flt          := current_flt + 1; -- Update float
current_ind          := current_ind + 1; -- and indicator
                                          -- pool

--
-- SQLLEN has been assigned by DESCRIBE to be the length
-- of a specific result column. This length is used to  
-- pick off a slice out of the large string buffer.
-- The character counter is then updated.
--

sqlda.sqlvar(3).sqltype := IISQ_CHA_TYPE;
sqlda.sqlvar(3).sqldata
                    := char_store(current_chr)'Address;
sqlda.sqlvar(3).sqlind := IISQ_ADR_ZERO;
current_chr := current_chr + sqlda.sqlvar(3).sqllen;
Of course, in the above example, you must verify enough pool storage before referencing each cell of the different arrays in order to prevent sqldata and sqlind from pointing at undefined storage. For demonstrations of this method, see The SQL Terminal Monitor Application and A Dynamic SQL/Forms Database Browser.
You may also set the SQLVAR to point to a datahandler for large object columns.
If you code your own SQLDA and, in place of sqldata, you declare a variant record of access types to a subset of different data types you may find that you can use the Ada allocator, new, and basic access type assignments. If you confirm that the layout of the record with the variant component is the same as that of IISQLDA, then you can use this type of record as an SQLDA without the need to access object addresses. This approach is not discussed further.
Last modified date: 11/09/2022