Variable Usage
Fortran variables declared in an Embedded SQL declaration section can substitute for most elements of Embedded SQL statements that are not keywords. Of course, the variable and its data type must make sense in the context of the element. When you use a Fortran variable in an Embedded SQL statement, you must precede it with a colon (:). You must further verify that the statement using the variable is in the scope of the variable's declaration. As an example, the following select statement uses the variables "namevar" and "numvar" to receive data and the variable "idno" as an expression in the where clause.
Example: Fortran variables usage
exec sql select ename, eno
1 into :namevar, :numvar
2 from employee
3 where eno = :idno
Various rules and restrictions apply to the use of Fortran variables in Embedded SQL statements. The following sections describe the usage syntax of different categories of variables and provide examples of such use.
Simple Variables
The following syntax refers to a simple scalar-valued variable (integer, real or character string):
:simplename
Syntax Notes:
• If you use the variable to send values to Ingres, it can be any scalar-valued variable.
• If you use the variable to receive values from Ingres, it must be a scalar-valued variable.
The following program fragment demonstrates a typical error handling routine, which can be called either directly or by a whenever statement. The variables "buffer" and "buflen" are scalar-valued variables.
Example: Simple variables usage
subroutine ErrHnd
exec sql include sqlca
exec sql begin declare section
parameter (buflen = 100)
character*(buflen) buffer
exec sql end declare section
exec sql whenever sqlerror continue
exec sql inquire_sql (:buffer= errrortext)
print *, 'the following error occurred aborting session.'
print *, buffer
exec sql abort
...
end
Array Variables
The following syntax refers to an array variable:
:arrayname (subscripts)
Syntax Notes:
• You must subscript the variable because only scalar-valued elements (integers, reals and character strings) are legal SQL values.
• When you declare the array, the Embedded SQL preprocessor does not parse the array bounds specification. Consequently, the preprocessor accepts illegal bounds values. Also, when you reference an array, the preprocessor does not parse the subscript. The preprocessor confirms only that an array subscript is used with an array variable. You must ensure that the subscript is legal and that the correct number of indices is used.
• The preprocessor does not accept substring references for character variables.
• Arrays of indicator variables used with structure assignments must not include subscripts when referenced.
The following example uses the "i" variable as a subscript. It does not need to be declared in the declaration section because it is not parsed.
Example: Variable as subscript usage
exec sql begin declare section
character*8 frmnam(3)
exec sql end declare section
integer i
frmnam(1) = "empfrm"
frmnam(2) = "dptfrm"
frmnam(3) = "hlpfrm"
do 100 i=1,3
100 exec frs forminit:formname
Structure Variables
A structure variable can be used in two different ways if your Fortran compiler supports structures. First, the structure can be used as a simple variable, implying that all its members are used. This would be appropriate in the Embedded SQL select, fetch, and insert statements. Second, a member of a structure may be used to refer to a single element. This member must be a scalar value (integer, real or character string).
Structure Usage as a Collection of Variables
The syntax for referring to a complete structure is the same as referring to a simple variable:
:structurename
Syntax Notes:
• The structurename can refer to a main or nested structure. It can be an element of an array of structures. Any variable reference that denotes a structure is acceptable. For example:
C A simple structure
:emprec
C An element of an array of structures
:struct_array(i)
C A nested structure at level 3
:struct.minor2.minor3
• In order to be used as a collection of variables, the final structure in
the reference must have no nested structures or arrays. All the members of the structure will be enumerated by the preprocessor and must have scalar values. The preprocessor generates code as though the program had listed each structure member in the order in which it was declared.
• You must not use a structure containing a union declaration when the structure is being used as a collection of variables. The preprocessor generates references to all components of the structure and ignores the map groupings. Using a union declaration results in either a "wrong number of errors" preprocessor error or a runtime error.
The following example uses the" employee.dcl" file that DCLGEN generates to retrieve values into a structure. This example is not applicable if DCLGEN was run with the -f77 flag:
exec sql begin declare section
exec sql include 'employee.dcl'
exec sql end declare section
exec sql select *
1 into :emprec
2 from employee
3 where eno = 123
The example above generates code as though the following statement had been issued instead:
exec sql select *
1 into :emprec.eno, :emprec.ename, :emprec.age,
2 :emprec.job, :emprec.sal, :emprec.dept
3 from employee
4 where eno = 123
The example below fetches the values associated with all the columns of a cursor into a record:
exec sql begin declare section
exec sql include 'employee.dcl'
exec sql end declare section
exec sql declare empcsr cursor for
1 select *
2 from employee
3 order by ename
...
exec sql open empcsr
exec sql fetch empcsr into :emprec
exec sql close empcsr
The following example inserts values by looping through a locally declared array of structures whose elements have been initialized:
exec sql begin declare section
exec sql declare person table
1 (pname char(30),
2 page integer1,
3 paddr varchar(50)
structure /person_/
character*30 name
integer*2 age
character*50 addr
end structure
record /person_/ person(10)
integer*2 I
exec sql end declare section
...
do i=1,10
exec sql insert into person
1 values (:person(i))
end do
The insert statement in the example above generates code as though the following statement had been issued instead:
exec sql insert into person
1 values (:person(i).name, :person(i).age,:person(i).addr)
Structure Member Usage
The syntax Embedded SQL uses to refer to a structure member is the same as in Fortran:
:structure.member{.member}
• The structure member denoted by the above reference must be a scalar value (integer, real or character string). There can be any combination of arrays and structures, but the last object referenced must be a scalar value. Thus, the following references are all legal:
C Member of a structure
:employee.sal
C Member of an element of an array
:person(3).name
C Deeply nested member
:struct1.mem2.mem3.age
• Any array elements referred to within the structure reference, and not at the very end of the reference, are not checked by the preprocessor. Consequently, both of the following references are accepted, even though one must be wrong, depending on whether "person" is an array:
:person(1).age
:person.age
• The preprocessor expects unambiguous and fully qualified structure member references.
The following example uses the "emprec" structure, similar to the structure generated by DCLGEN, to put values into the "empform" form.
Example: Structure member usage
exec sql begin declare section
structure /emprec_/
integer*2 eno
character*2 ename
integer*2 age
integer*2 job
real*8 sal
integer*2 dept
end structure
record /emprec_/ emprec
exec sql end declare section
...
exec frs putform empform
1 (eno = :emprec.eno, ename = :emprec.ename,
2 age = :emprec.age, job = :emprec.job,
3 sal = :emprec.sal, dept = :emprec.dept)
Indicator Variables Usage
The syntax for referring to an indicator variable is the same as for a simple variable, except that an indicator is always associated with a host variable:
:host_variable:indicator_variable
or
:host_variable indicator :indicator_variable
• The indicator variable can be a simple variable or an array element that yields a 2-byte integer. For example:
integer*2 indvar, indarr(5)
:var_1:indvar
:var_2:indarr(2)
• If the host variable associated with the indicator variable is a structure, the indicator variable should be an array of 2-byte integers. In this case the array should not be dereferenced with a subscript.
• When an indicator array is used, the first element of the array corresponds to the first member of the structure, the second element with the second member, and so on. Array elements begin at subscript 1.
The following example uses the "employee.dcl" file that DCLGEN generates to retrieve non-null values into a structure and null values into the "empind" array:
exec sql begin declare section
exec sql include 'employee.dcl'
integer*2 empind(10)
exec sql end declare section
exec sql select *
1 into :emprec:empind
2 from employee
The previous example generates code as though the following statement had been issued:
exec sql select *
1 into :emprec.eno:empind(1), :emprec.ename:empind(2),
2 :emprec.age:empind(3), :emprec.job:empind(4),
3 :emprec.sal:empind(5), :emprec.dept:empind(6),
4 from employee