This section contains information on how to handle nonscalar and user-defined SQL-level data types in ECOBPG applications. Note that this is distinct from the handling of host variables of nonprimitive types, described in the previous section.
Arrays
SQL-level arrays are not directly supported in ECOBPG. It is not possible to simply map an SQL array into a COBOL array host variable. This will result in undefined behavior. Some workarounds exist, however.
If a query accesses elements of an array separately, then this avoids the use of arrays in ECOBPG. Then, a host variable with a type that can be mapped to the element type should be used. For example, if a column type is array of integer, a host variable of type PIC S9(9) COMP can be used. Also if the element type is varchar or text, a host variable of type VARCHAR can be used.
Here is an example. Assume the following table:
CREATE TABLE t3 (
ii integer[]
);
testdb=> SELECT * FROM t3;
ii
-------------
{1,2,3,4,5}
(1 row)The following example program retrieves the 4th element of the array and stores it into a host variable of type PIC S9(9) COMP-5:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 II PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[4] FROM t3 END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
EXEC SQL FETCH FROM cur1 INTO :II END-EXEC
DISPLAY "ii=" II
END-PERFORM.
END-FETCH.
EXEC SQL CLOSE cur1 END-EXEC.To map multiple array elements to the multiple elements in an array type host variables each element of array column and each element of the host variable array have to be managed separately, for example:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 GROUP-ITEM.
05 II_A PIC S9(9) COMP OCCURS 8.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[1], ii[2], ii[3], ii[4] FROM t3 END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
EXEC SQL FETCH FROM cur1 INTO :II_A[1], :II_A[2], :II_A[3], :II_A[4] END-EXEC
...
END-PERFORM.Note again that.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 GROUP-ITEM.
05 II_A PIC S9(9) COMP OCCURS 8.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii FROM t3 END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
* WRONG
EXEC SQL FETCH FROM cur1 INTO :II_A END-EXEC
...
END-PERFORM.would not work correctly in this case, because you cannot map an array type column to an array host variable directly.
Another workaround is to store arrays in their external string representation in host variables of type VARCHAR. For more details about this representation.
See
Refer to "Arrays" in "Tutorial" in the PostgreSQL Documentation for information more details about this representation.
Note that this means that the array cannot be accessed naturally as an array in the host program (without further processing that parses the text representation).
Composite Types
Composite types are not directly supported in ECOBPG, but an easy workaround is possible. The available workarounds are similar to the ones described for arrays above: Either access each attribute separately or use the external string representation.
For the following examples, assume the following type and table:
CREATE TYPE comp_t AS (intval integer, textval varchar(32)); CREATE TABLE t4 (compval comp_t); INSERT INTO t4 VALUES ( (256, 'PostgreSQL') );
The most obvious solution is to access each attribute separately. The following program retrieves data from the example table by selecting each attribute of the type comp_t separately:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 INTVAL PIC S9(9) COMP.
01 TEXTVAL PIC X(33) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
* Put each element of the composite type column in the SELECT list.
EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4 END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
* Fetch each element of the composite type column into host variables.
EXEC SQL FETCH FROM cur1 INTO :INTVAL, :TEXTVAL END-EXEC
DISPLAY "intval=" INTVAL ", textval=" ARR OF TEXTVAL
END-PERFORM.
END-FETCH.
EXEC SQL CLOSE cur1 END-EXEC.To enhance this example, the host variables to store values in the FETCH command can be gathered into one group item. For more details about the host variable in the group item form, see "Group Item". To switch to the group item, the example can be modified as below. The two host variables, intval and textval, become subordinate items of the comp_t group item, and the group item is specified on the FETCH command.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 COMP-T TYPEDEF.
02 INTVAL PIC S9(9) COMP.
02 TEXTVAL PIC X(33) VARYING.
01 COMPVAL TYPE COMP-T.
EXEC SQL END DECLARE SECTION END-EXEC.
* Put each element of the composite type column in the SELECT list.
EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4 END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
* Put all values in the SELECT list into one structure.
EXEC SQL FETCH FROM cur1 INTO :COMPVAL END-EXEC
DISPLAY "intval=" INTVAL ", textval=" ARR OF TEXTVAL
END-PERFORM.
END-FETCH.
EXEC SQL CLOSE cur1 END-EXEC.Although a group item is used in the FETCH command, the attribute names in the SELECT clause are specified one by one. This can be enhanced by using a * to ask for all attributes of the composite type value.
...
EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).* FROM t4 END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
* Put all values in the SELECT list into one structure.
EXEC SQL FETCH FROM cur1 INTO :COMPVAL END-EXEC
DISPLAY "intval=" INTVAL ", textval=" ARR OF TEXTVAL
END-PERFORM.This way, composite types can be mapped into structures almost seamlessly, even though ECOBPG does not understand the composite type itself.
Finally, it is also possible to store composite type values in their external string representation in host variables of type VARCHAR. But that way, it is not easily possible to access the fields of the value from the host program.
User-defined Base Types
New user-defined base types are not directly supported by ECOBPG. You can use the external string representation and host variables of type VARCHAR, and this solution is indeed appropriate and sufficient for many types.
Here is an example using the data type complex.
See
Refer to "User-defined Types" in "Server Programming" in the PostgreSQL Documentation for information on the data type complex..
The external string representation of that type is (%lf,%lf), which is defined in the functions complex_in() and complex_out() functions. The following example inserts the complex type values (1,1) and (3,3) into the columns a and b, and select them from the table after that.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 A PIC X(64) VARYING.
01 B PIC X(64) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INSERT INTO test_complex VALUES ('(1,1)', '(3,3)') END-EXEC.
EXEC SQL DECLARE cur1 CURSOR FOR SELECT a, b FROM test_complex END-EXEC.
EXEC SQL OPEN cur1 END-EXEC.
EXEC SQL WHENEVER NOT FOUND GOTO END-FETCH END-EXEC.
PERFORM NO LIMIT
EXEC SQL FETCH FROM cur1 INTO :A, :B END-EXEC
DISPLAY "a=" ARR OF A ", b=" ARR OF B
END-PERFORM.
END-FETCH.
EXEC SQL CLOSE cur1 END-EXEC.Another workaround is avoiding the direct use of the user-defined types in ECOBPG and instead create a function or cast that converts between the user-defined type and a primitive type that ECOBPG can handle. Note, however, that type casts, especially implicit ones, should be introduced into the type system very carefully.
For example:
CREATE FUNCTION create_complex(r double precision, i double precision) RETURNS complex LANGUAGE SQL IMMUTABLE AS $$ SELECT $1 * complex '(1,0)' + $2 * complex '(0,1)' $$;
After this definition, the following:
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 A COMP-2. 01 B COMP-2. 01 C COMP-2. 01 D COMP-2. EXEC SQL END DECLARE SECTION END-EXEC. MOVE 1 TO A. MOVE 2 TO B. MOVE 3 TO C. MOVE 4 TO D. EXEC SQL INSERT INTO test_complex VALUES (create_complex(:A, :B), create_complex(:C, :D)) END-EXEC.
has the same effect as
EXEC SQL INSERT INTO test_complex VALUES ('(1,2)', '(3,4)') END-EXEC.