Was this helpful?
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
Last modified date: 11/09/2022