Re: Embeded SQL retrieving a key from an insert

Note that you can use SELECT INTO only if you specify with a condition one
record which should be selected.
The syntax is:

EXEC SQL
SELECT HDRHEADKEY
INTO :HeaderKeySQL0199
FROM myTable
WHERE
myLogicalCondition
END-EXEC

The condition myLogicalCondition should guarantee that the above select
statement returns only one record.


When your select returns more records, then you need to use FETCH.
For example, something like this:

1. Declare Cursor for select statement:
EXEC SQL
DECLARE CLOOPTAB CURSOR FOR
SELECT HDRHEADKEY
FROM myTable
END-EXEC
MOVE SQLCODE TO WS-SQLCODETAB

2. Open the cursor:
EXEC SQL
OPEN CLOOPTAB
END-EXEC

3. Fetch the records into working storage fields and process them:
PERFORM UNTIL WS-SQLCODETAB NOT = ZERO
EXEC SQL
FETCH NEXT FROM CLOOPTAB INTO :HeaderKeySQL0199
END-EXEC
MOVE SQLCODE TO WS-SQLCODETAB
PERFORM PROCESS-FIELD
END-PERFORM

4. At the end of the processing close the cursor
EXEC SQL
CLOSE CLOOPTAB
END-EXEC

Note, that after every SQL operation you need to evaluate SQLCODE or
SQLSTATE which says you if it was succesfull or not - and why it wasn't
succesfull.

Roman



From: Cole Gantz <colegantz@xxxxxxxxx>
To: COBOL400-L@xxxxxxxxxxxx
Date: 17.02.2010 22:24
Subject: [COBOL400-L] Embeded SQL retrieving a key from an insert
Sent by: cobol400-l-bounces@xxxxxxxxxxxx



Howdy All,

I've got a table that was created via an SQL statement that looks like
this:
create
(hdrheadkey integer not null
generated always as identity
(start with 1
,increment by 1
,no maxvalue
,no cycle
,order)
,hdrdate numeric(8,0) not null default 0
,hdrtime numeric(8,0) not null default 0
,hdruser char(10) ccsid 37 not null default ''
,hdrcaption char(4) ccsid 37 not null default ''
,hdrdesc char(40) ccsid 37 not null default ''
,hdrinsname char(60) ccsid 37 not null default ''
,hdrsubject char(60) ccsid 37 not null default ''
,primary key (hdrheadkey));

I need to get the key that is created when an insert is done and this is
what I've pulled from a sample from a DB2 Cookbook but I'm getting an
error in WDSc when trying to enter it:
Exec SQL Select HDRHEADKEY InTo :HeaderKeySQL0199 Keyword TABLE
not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT
OPTIMIZE ValuesEnd-Exec


Am I trying to do something that can't be done? Is there another way
to get the newly created key from an insert to be used in another table?

Cole

I love deadlines. I like the whooshing sound they make as they fly by.
Douglass Adams (:NoteDate, :NoteTime, :NoteUser, ' ',
:InsuredName, ' ')) (HdrDate, HdrTime, HdrUser, HdrCaption,
HdrDesc, HdrInsName, HdrSubject) (Insert InTo NotePadHd From
Final Table table notepad/notepdhd