I have to write a COBOL program that reads some records from a file and does some computation with them. If the routine is called with a non-blank filter, results, should be filtered.
I tried to use parameter markers in SQL. Here's my attempt:
100 IDENTIFICATION DIVISION. 19/11/07
200 PROGRAM-ID. MINIMALEX. 01/07/20
300 AUTHOR. ME. 01/07/20
400 DATE-WRITTEN. JULY 2020. 01/07/20
500 *----------------------------------------------------------------* 01/07/20
600 *--- Minimal example. ---* 01/07/20
700 *----------------------------------------------------------------* 01/07/20
800 ENVIRONMENT DIVISION. 19/11/07
900 CONFIGURATION SECTION. 19/11/07
1000 SOURCE-COMPUTER. IBM-AS400. 19/11/07
1100 OBJECT-COMPUTER. IBM-AS400. 19/11/07
1200 SPECIAL-NAMES. DECIMAL-POINT IS COMMA. 28/12/10
1300 *----------------------------------------------------------------* 01/07/20
1400 INPUT-OUTPUT SECTION. 19/11/07
1500 FILE-CONTROL. 19/11/07
1600 *----------------------------------------------------------------* 19/11/07
1700 DATA DIVISION. 19/11/07
1800 FILE SECTION. 19/11/07
1900 *----------------------------------------------------------------* 19/11/07
2000 WORKING-STORAGE SECTION. 19/11/07
2100 *--- Variables for SQL. 01/07/20
2200 EXEC SQL 01/07/20
2300 INCLUDE SQLCA 01/07/20
2400 END-EXEC. 01/07/20
2500 EXEC SQL 01/07/20
2600 INCLUDE SQLDA 01/07/20
2700 END-EXEC. 01/07/20
2800 01/07/20
2900 01 WK-NAME PIC X(80). 01/07/20
3000 01 WK-LASTNAME PIC X(80). 01/07/20
3100 01 WK-SELECT PIC X(34). 01/07/20
3200 01 WK-WHERE PIC X(65). 01/07/20
3300 01 WK-STATEMENT PIC X(100). 01/07/20
3400 *----------------------------------------------------------------* 01/07/20
3500 LINKAGE SECTION. 19/11/07
3600 01 LK-NAME PIC X(80). 01/07/20
3700 *----------------------------------------------------------------* 01/07/20
3800 PROCEDURE DIVISION USING LK-NAME. 01/07/20
3900 *----------------------------------------------------------------* 01/07/20
4000 A01-START. 01/07/20
4100 *--- Create and insert data into file. 01/07/20
4200 EXEC SQL 01/07/20
4300 DROP TABLE QTEMP/T 01/07/20
4400 END-EXEC. 01/07/20
4600 EXEC SQL 01/07/20
4700 CREATE TABLE QTEMP/T 01/07/20
4800 (NAME CHAR (80), LASTNAME CHAR (80)) 01/07/20
4900 END-EXEC. 01/07/20
5100 EXEC SQL 01/07/20
5200 INSERT INTO QTEMP/T VALUES ("ALFRED", "HITCHCOCK") 01/07/20
5300 END-EXEC. 01/07/20
5500 01/07/20
5600 MOVE 'SELECT NAME, LASTNAME FROM QTEMP/T' TO WK-SELECT. 01/07/20
5700 21/03/08
5800 MOVE SPACES TO WK-WHERE. 01/07/20
5900 MOVE 0 TO SQLN. 01/07/20
6000 01/07/20
6100 IF LK-NAME NOT = SPACES 01/07/20
6200 STRING 'WHERE NAME LIKE ("%" CONCAT ' 01/07/20
6300 'TRIM(CAST(? AS CHAR(80))) CONCAT "%")' 01/07/20
6400 DELIMITED BY SIZE INTO WK-WHERE 01/07/20
6500 ADD 1 TO SQLN 01/07/20
6600 END-IF. 01/07/20
6700 28/04/08
6800 STRING WK-SELECT DELIMITED BY SIZE 01/07/20
6900 ' ' DELIMITED BY SIZE 01/07/20
7000 WK-WHERE DELIMITED BY ' ' INTO WK-STATEMENT 01/07/20
7100 END-STRING. 21/04/08
7200 30/06/20
7300 EXEC SQL 30/06/20
7400 PREPARE STMT FROM :WK-STATEMENT 01/07/20
7500 END-EXEC. 30/06/20
7700 EXEC SQL 30/06/20
7800 DESCRIBE INPUT STMT INTO :SQLDA 01/07/20
7900 END-EXEC. 30/06/20
8100 21/04/08
8200 *--- Setting values for SQLDA. 01/07/20
8300 IF LK-NAME NOT = SPACES 01/07/20
8400 SET SQLDATA(1) TO ADDRESS OF LK-NAME 01/07/20
8500 END-IF. 01/07/20
8600 30/06/20
8700 EXEC SQL 21/04/08
8800 DECLARE SQL_LIST CURSOR FOR STMT 01/07/20
8900 END-EXEC. 21/04/08
9000 21/04/08
9100 EXEC SQL 21/04/08
9200 OPEN SQL_LIST 01/07/20
9300 END-EXEC. 21/04/08
9500 21/04/08
9600 A01-FETCH. 01/07/20
9700 EXEC SQL 01/07/20
9800 FETCH NEXT FROM SQL_LIST INTO :WK-NAME, :WK-LASTNAME 01/07/20
9900 END-EXEC. 01/07/20
10100 IF SQLCODE NOT = 0 01/07/20
10200 GO TO A01-CLOSE 01/07/20
10300 END-IF. 01/07/20
10400 01/07/20
10500 *--- Do some stuff with the fetched data, then fetch next row. 01/07/20
10600 GO TO A01-FETCH. 01/07/20
10700 21/04/08
10800 A01-CLOSE. 01/07/20
10900 EXEC SQL 13/02/20
11000 CLOSE SQL_LIST 01/07/20
11100 END-EXEC. 13/02/20
11300 13/02/20
11400 A01-END. 01/07/20
11500 GOBACK. 01/07/20
Unfortunately, when I run the program (giving a vAlue other than blank to LK-NAME), the OPEN statement returns a SQLCODE -313 (The number of host variables specified in the EXECUTE or OPEN statement is not the same as the number of parameter markers (question marks) appearing in the prepared SQL statement.) It seems to me that the number of both host variables and parameter markers is 1, so where's the problem?
EDIT
Following Charles' suggestion, line 9200 was changed to
OPEN SQL_LIST USING DESCRIPTOR :SQLDA
This now gives SQLCODE -822.
EDIT 2
Upon further research, I understood that each host variable must be associated to an indicator variable in the linkage section and that occurrences of SQLIND must be to addresses of those indicator variables. Thanks @Charles
Knowing the platform would be helpful..
However, I'd expect see the variable you intend to use in the parameter makers in your open statement.
EXEC SQL
OPEN SQL_LIST USING :LK-NAME
END-EXEC.
The open statement is where the value used to replace the parameter marker is replaced.
Having said that, you may want to consider static SQL instead of dyanmic.
Static SQL doesn't have to be prepared...it'd just look like
EXEC SQL
DECLARE SQL_LIST CURSOR FOR
SELECT NAME, LASTNAME FROM QTEMP/T
WHERE :LK-NAME = ' '
OR NAME LIKE ( '%' CONCAT TRIM(:LK-NAME) CONCAT '%')
END-EXEC.
EXEC SQL
OPEN SQL_LIST
END-EXEC.
note that even here, the value of LK-NAME is passed in at the time the cursor is opened, the DECLARE CURSOR is really a compile time statement.
EDIT
You can use the descriptor area, if that case the open would specify OPEN…USING DESCRIPTOR descriptor-name according to the docs. I've honestly never done it that way.
You have read through the embedded SQL programming manual correct?
Yes, generally a static statement is faster. As long as it's not too complex compared to the dynamic version.