SQL statements in Easytrieve should be prefixed with the word, SQL.
For eg,
SQL SELECT 1 FROM + SYSIBM.SYSDUMMY1 INTO :WS-INT
+ statement is used for statement continuation in the next line.
Host variable datatype mapping between Easytrieve and DB2.
| DB2 Datatype | Number of Bytes | Easytrieve variable |
| INTEGER | 4 | WS-INT W 4 B |
| SMALLINT | 2 | WS-SMALLINT W 2 B |
| DATE | 10 | WS-DATE W 10 A |
| CHAR(25) | 25 | WS-CHAR-25 W 25 A |
| TIMESTAMP | 26 | WS-TIMESTAMP W 26 A |
| NULL INDICATOR | 2 | WS-NULLIND W 2 B |
| DECIMAL(16,2) | 8 | WS-DECI1 W 8 P 2 |
| DECIMAL(16,5) | 8 | WS-DECI2 W 8 P 5 |
| DECIMAL(17,5) | 9 | WS-DECI3 W 9 P 5 |
How to get a SQL’s return code?
Use the SQLCODE variable to check the DB2 SQL return code.
PARM SSID('DBXX')
EMP-ID W 6 N
EMP-NAME W 25 A
JOB INPUT NULL
SQL +
SELECT EMP_ID, EMP_NAME FROM +
EMPLOYEE INTO +
:EMP-ID, :EMP-NAME
CASE SQLCODE
WHEN +0
DISPLAY 'EMP_ID :' EMP-ID
DISPLAY 'EMP_NAME :' EMP-NAME
WHEN +100
DISPLAY 'NO ROW FOUND'
OTHERWISE
DISPLAY 'ERROR. SQLCODE ' SQLCODE
END-CASE
STOP
How to Run an Update query and get the number of rows updated?
SQLERRD(3) = number of rows updated from an Update query or deleted by a delete query
PARM SSID('DBXX')
JOB INPUT NULL
SQL +
UPDATE EMPLOYEE +
SET SALARY = SALARY * 1.05 +
WHERE EMP_ID = 113114
*
DISPLAY 'SQLCODE IS : ' SQLCODE
DISPLAY 'ROWS UPDATED : ' SQLERRD(3)
*
STOP
How to declare a Cursor and Fetch records?
PARM SSID('DBXX')
EMP-ID W 6 N
EMP-NAME W 25 A
*
SQL DECLARE C1 CURSOR FOR +
SELECT EMP_ID, EMP_NAME FROM +
EMPLOYEE +
WHERE EMP_ID > 5
*
JOB INPUT NULL
SQL OPEN C1
DISPLAY ' SQL CODE : ' SQLCODE
*
DO WHILE SQLCODE NE +100
SQL FETCH C1 INTO +
:EMP-ID +
,:EMP-NAME
DISPLAY ' SQL CODE : ' SQLCODE
IF SQLCODE NE 0 AND SQLCODE NE 100
DISPLAY 'ERROR : SQLCODE : ' SQLCODE
STOP
END-IF
IF SQLCODE EQ 0
DISPLAY 'EMP-ID :' EMP-ID
DISPLAY 'EMP-NAME :' EMP-NAME
END-IF
END-DO
*
SQL CLOSE C1
DISPLAY ' SQL CODE : ' SQLCODE
*
STOP
Also consider reading
http://www.angelfire.com/folk/anoop/easydb2.pdf
http://www.mvsforums.com/helpboards/viewtopic.php?t=478
http://ibmmainframes.com/about27446.html
Multi Row Fetch (Thanks to Roy for providing this link)
Use this code 
You might reconsider the title for the ‘Multi Row Fetch using a Cursor’ section.
It is not a DB2 multi-row fetch according to IBM/DB2’s definition. Your example is just a ‘Fetch many rows from a Cursor’s result set, one at a time.’
Multi-Row Fetch is the process of getting many rows from the Cursor’s result set in one fetch and loading them to arrays, one array for each column mentioned in the cursor. The array size (OCCURS nn) must be the same for every column.
Once I figure out a real coding example, I’ll send it.
Cheers,
Roy
Hi Roy,
Sorry, I have now corrected the heading for Cursor usage example. Can I post your sample code in this page?
Thanks,
Karthik
Sometimes we will handle the -811 in cobol-db2 program (with out using the cursor).
Similarly I want to get the -811 in eztrieve. Is this possible?
Your answer will be greatly appreciated !!!!!!!!!
Hi Raja,
yes, we can handle -811 in Easytrieve (when a singleton select returns more than one record)
IF SQLCODE EQ -811
DISPLAY ‘ERROR -811. MORE THAN ONE ROW RETRIEVED. USE A CURSOR’
END-IF
Thanks for the response…………
Hi Karthik,
If you share this with piece of code. it will be helpful for understanding….
Thanks in advance!!!!
hi header
can you send me a integrated program.thanks in advance
Hello Kartihk,
I need a sample program, Calling cobol module from easytrieve.
Any examples would be a great help!
I’m getting a -514.
I’m using a cursor to loop a fetch and another cursor to retrieve a row based on my looped fetch. I’m doing this with two different tables. Once I finish the first table, I process the second table. When I process the second table I get the -514 on the open of the cursor that’s retrieving based on what was retrieved in the looped fetch. I am closing the cursor before opening it each time.
-514 in the manual = CUSROR not in a PREPARED state. This makes no sense to me.
Null indicator definition should be modified to
WS-NULLIND W 2 B 0
Program would not compile in easytrieve without the 0 at the end
Can anyone tell me why this works in Easytrieve:
SQL DECLARE EOM_DATE CURSOR FOR +
SELECT (DATE(‘1995-01-01’) + 1 MONTH – 1 DAY) +
FROM SYSIBM.SYSDUMMY1 +
WITH UR
And this does not, I get a -418:
SQL DECLARE EOM_DATE CURSOR FOR +
SELECT (DATE(:W-HOLD-DATE) + 1 MONTH – 1 DAY) +
FROM SYSIBM.SYSDUMMY1 +
WITH UR
W-HOLD-DATE W 10 A VALUE ‘ ‘
******B204 SQL – SQL ERROR, SQLCODE IS -418
******B204 SQL – DSNT408I SQLCODE = -418, ERROR: A STATEMENT STRING TO BE PREPARED CONTAINS AN INVALID
*******B204 SQL – USE OF PARAMETER MARKERS
*******B204 SQL – DSNT418I SQLSTATE = 42610 SQLSTATE RETURN CODE
95 CASE SQLCODE
EZTC0644E >>> $ more qualification required
EZTC0124E >>> $ fieldname expected
96 WHEN 0
EZTC0377W >>> + skipping to end of statement
97 OUT-RECORD = W-OUT-REC
98 PUT OUTFILE
99 RETURN-CODE = 5
100 WHEN +100
EZTC0377W >>> + skipping to end of statement
101 DISPLAY ‘NO MATCHING RECORDS’
102 RETURN-CODE = 0
I am getting above error when I use the keyword SQLCODE, any idea how to fix this?