Embedded SQL Companion Guide > Embedded SQL Companion Guide > Embedded SQL for BASIC > Dynamic Programming for BASIC > SQLVAR Usage
Was this helpful?
SQLVAR 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, which now refers to the variables being pointed at by the SQLDA.
BASIC Variable Type Codes
The type codes listed above are the types that describe Ingres result fields or columns. For example, the SQL types date, decimal, and money do not describe a program variable, but rather result data types that are compatible with BASIC character string and numeric types. IISQ_LVCH_TYPE is SQL only character compatible too. When these types are returned by the describe statement, the type code must be a change to a compatible BASIC or ESQL/BASIC type.
SQLDA Type Codes
The following table lists the type codes to use with BASIC variables that are pointed at by the sqldata pointers.
BASIC Type
SQLType Code (sqltype)
SQL Length (sqllen)
byte
IISQ_INT_TYPE
1
word
IISQ_INT_TYPE
2
long
IISQ_INT_TYPE
4
real
IISQ_FLT_TYPE
4
double
IISQ_FLT_TYPE
8
string = LEN
IISQ_CHA_TYPE
LEN
string
IISQ_DEC_TYPE
10
As described in BASIC Variables and Data Types, all other types are compatible with the above BASIC data types. For example, you can retrieve an SQL date into a string variable, while you can retrieve money into a double variable.
Nullable data types (those variables that are associated with a null indicator) are specified by assigning the negative of the type code to sqltype. If the type is negative, you must point at a null indicator by the sqlind variable. The type of the null indicator must be a 2-byte integer, a word variable. For information on how to declare and use a null indicator in BASIC, see BASIC Variables and Data Types in this chapter.
Character data and the SQLDA have the exact same rules as character data in regular Embedded SQL statements. Because string lengths must be assigned to sqllen before using the SQLDA, you cannot point at BASIC dynamic string variables (those declared without a length) if they have not yet been assigned any storage. For more details on character string processing in SQL, see BASIC Variables and Data Types in this chapter.
Pointer Usage with BASIC 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 variable. If the element is nullable then the corresponding sqlind member must point at a legally declared null indicator variable.
Because both the sqldata and sqlind members of the sqlvar group are declared as long integers, you must assign integer values to them. This requires the use of the BASIC loc function.
For example, the following program fragment sets the type information of and points at a 4-byte integer variable, an 8-byte nullable floating-point variable, and an sqllen-specified character sub-string. 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 chosen from the pool the next available spot is incremented:
exec sql include sqlda
declare iisqlda sqlda
...

! Numeric and string 'pool' declarations
declare word         constant MAX_POOL = 50
declare word         ind_store(MAX_POOL)     ! Indicators
declare word         current_ind
declare long         int4_store(MAX_POOL)    ! Integers
declare word         current_int
declare double flt8_store(MAX_POOL)          ! Floats
declare word         current_flt
declare string char_store(3000) = 1          ! String buffer
declare word         current_chr
declare word         need_len
...

!
! Note that if SQLD is set to 3 we use SQLVAR elements ! 0 through 2
!
sqlda::sqlvar(0)::sqltype = IISQ_INT_TYPE      ! 4-byte integer
sqlda::sqlvar(0)::sqllen = 4
sqlda::sqlvar(0)::sqldata = loc(int4_store(current_int))
sqlda::sqlvar(0)::sqlind = 0
current_int = current_int + 1 ! Update integer pool
sqlda::sqlvar(1)::sqltype = -IISQ_FLT_TYPE     ! 8-byte null float
sqlda::sqlvar(1)::sqllen = 8
sqlda::sqlvar(1)::sqldata = loc(float8_store(current_flt))
sqlda::sqlvar(1)::sqlind = loc(ind_store(current_ind))
current_flt = current_flt + 1 ! Update float and
current_ind = current_ind + 1 ! 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 sub-string out of
! a large string space.
!

need_len = sqlda::sqlvar(2)::sqllen
sqlda::sqlvar(2)::sqltype = IISQ_CHA_TYPE
sqlda::sqlvar(2)::sqldata = loc(char_store(current_chr))
sqlda::sqlvar(2)::sqlind = 0
current_chr = current_chr + need_len ! Update char pool
...
Of course, in the above example, verification of enough pool storage must be made before each cell of the different arrays is referenced 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.
The IISQ_HDLR_TYPE is a host language type that is used for transmitting data to and from Ingres. Because it is not an Ingres data type, it will never be returned as a data type from the describe statement.
Last modified date: 04/03/2024