Re: help with tables

On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick"
<Frank.Swarbrick@xxxxxxxxxxxxxx> wrote:

On 1/29/2008 at 9:37 PM, in message
<15ovp3p40dmvcovmgc3co257gi595dn3ii@xxxxxxx>, Robert<no@xxxxxx> wrote:
If you don't like BULK COLLECT syntax, leave it out. Selecting into an
array does the
same thing and isn't tied to Oracle. It works on DB2 the same way. For
example:

01 table2-area.
05 table2-entry occurs 1 to 100 depending on sqlerrd (3).
10 t2-name pic x(30).
10 t2-dept pic x(04).

compute sqlerrd(3) = length of table2-area / length of table2-entry
select name, department into table2-entry from table2
where name in (select distinct name from table1)
display 'There are ' sqlerrd(3) ' departments.'

This does not appear to work, at least not using the DB2 pre-processor.

Here's my test program:

IDENTIFICATION DIVISION.
PROGRAM-ID. tabtest.
ENVIRONMENT DIVISION.

DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry occurs 1 to 100 depending on sqlerrd(3).
10 t2-name pic x(30).
10 t2-dept pic x(04).
EXEC SQL
END DECLARE SECTION
END-EXEC
77 ERRLOC PIC X(80) VALUE SPACES.

PROCEDURE DIVISION.
CALL 'connect'
PERFORM get-into-table.
PERFORM DB-DISCONNECT
STOP RUN.


get-into-table.
compute sqlerrd(3) = length of table2-area
/ length of table2-entry
exec sql
select name, dept
into :table2-entry
from test.table2
where name in (select distinct name from test.table1)
end-exec
CALL 'checkerr' USING SQLCA ERRLOC

display 'There are ' sqlerrd(3) ' departments.'
exit.

DB-DISCONNECT.
MOVE 'DB-DISCONNECT' TO ERRLOC
EXEC SQL
CONNECT RESET
END-EXEC
CALL 'checkerr' USING SQLCA ERRLOC
EXIT.

END PROGRAM tabtest.


And here's the output from the DB2 pre-processor:
Database Connection Information

Database server = DB2/NT 9.5.0
SQL authorization ID = FSWARBRI
Local database alias = TEST1

prep tabtest.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN
yes EXPLSNAP yes

LINE MESSAGES FOR tabtest.sqb
------
--------------------------------------------------------------------
SQL0060W The "COBOL" precompiler is in progress.
19 SQL0008N The token "occurs" found in a host variable
declaration is not valid.
41 SQL4943W The number of host variables in the INTO clause
is not the same as the number of items in the SELECT
clause. SQLSTATE=01503
41 SQL0306N The host variable "TABLE2-ENTRY" is undefined.
SQL0095N No bind file was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "3"
errors and "1" warnings.

Frank

The following is allowed on V9, but on Z/OS.

WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
BEGIN DECLARE SECTION
END-EXEC
01 table2-area.
05 table2-entry.
10 t2-name pic x(30) occurs 100.
10 t2-dept pic x(04) occurs 100.
EXEC SQL
END DECLARE SECTION
END-EXEC
EXEC SQL
DECLARE CURSOR-X CURSOR FOR
select name, dept
from test.table2
where name in (select distinct name from test.table1)
END-EXEC.
.......
PROCEDURE....
exec sql
FETCH NEXT CURSOR-X
into :T2-NAME, :T2-DEPT
for 100 rows
end-exec

It seems that Windows/Unix versions do not allow multiple fetch/insert
rows. Pitty.


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
.

Relevant Pages

  • Re: VARCHAR columns in COBOL
    ... The following pertails to DB2 LUW, but I imagine Oracle is similar (or ... will trailing spaces be removed upon INSERT ... EXEC SQL BEGIN DECLARE SECTION END-EXEC. ...
    (comp.lang.cobol)
  • Re: help with tables
    ... department into table2-entry from table2 ... This does not appear to work, at least not using the DB2 pre-processor. ... EXEC SQL ... 41 SQL4943W The number of host variables in the INTO clause ...
    (comp.lang.cobol)
  • Re: help with tables
    ... department into table2-entry from table2 ... This does not appear to work, at least not using the DB2 pre-processor. ... EXEC SQL ... BEGIN DECLARE SECTION ...
    (comp.lang.cobol)
  • Re: Can I use ESQL/C datetime type to define non-host variables / parameters (oops)
    ... Do I need to put the parameters with esql/c datatypes between EXEC SQL BEGIN DECLARE SECTION; and EXEC SQL END DECLARE SECTION;. ... If you want to write datetime as the type name without a typedef, then you have to use the ESQL/C preprocessor to change the datetime into dtime_t for you. ...
    (comp.databases.informix)
  • Re: Opinions on approach, please...
    ... It means the first column in the result set. ... EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. ...
    (comp.lang.cobol)