Problem:
There are two things to note when coding dynamic SQL in COBOL
Resolution:
First, according to DB2's syntax diagram, preparing from a string literal only works in PL/I so that's the reason why the mfuser\\projects\\demo\\sqldemo\\Test5 program EXEC SQL PREPARE won't compile
EXEC SQL PREPARE EM-INSERT FROM
'INSERT INTO EMPLOYEE
- ' VALUES (?,?,?,?,?,?,?,?,?,?)'
________________________________________________________________________
| |
| >>__PREPARE__statement-name__________________________________________> |
| |
| >__ ______________________________________________ __________________> |
| |_INTO__descriptor-name__ ___________________ _| |
| | _NAMES__ | |
| |_USING__|_LABELS_|_| |
| |_ANY____| |
| |_BOTH___| |
| |
| >__ _ ___________________________________ __FROM__host-variable_ ___>< |
| | | (1)| | |
| | |_ATTRIBUTES__attr-host-variable____| | |
| | (2) | |
| |_FROM__string-expression____________________________________| |
| |
| Notes: |
| (1) attr-host-variable must be a string host variable and the content |
| must conform to the rules for attribute-string. The ATTRIBUTES |
| clause can only be specified before host-variable. |
| |
| (2) string-expression is only supported for PLI. |
| |
|________________________________________________________________________|
Second, it is correct to use a host variable of type VARCHAR in the revised program. DB2 SQL reference says the following about the type of host variable that should be used in a PREPARE statement in COBOL.
host-variable
Must identify a host variable that is described in the application program in accordance with the rules for declaring string
| variables. If the source string is over 32KB in length, the
| host-variable must be a CLOB or DBCLOB variable. The maximum
| source string length is 2MB although the host variable can be
| declared larger than 2MB. An indicator variable must not be specified. In COBOL and Assembler language, the host variable must be a varying-length string variable. In C, the host variable must not be a NUL-terminated string.
There is another pitfall in the code snippet in that the length part of the VARCHAR host variable is not the exact length of the SQL string literal.
The STRING verb as a better way of putting together the SQL string into a VARCHAR host variable.
MOVE 1 TO WS-SQL-STMT-LEN
STRING 'INSERT INTO TUTORIAL.EMPLOYEE VALUES ' ç String literal 1
DELIMITED BY SIZE
'(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' ß String literal 2
DELIMITED BY SIZE
INTO WS-SQL-STMT-TXT ß Destination field
WITH POINTER WS-SQL-STMT-LEN
ON OVERFLOW GO TO 920-SQL-BUFFER-OVERFLOW
END-STRING
SUBTRACT 1 FROM WS-SQL-STMT-LEN
The key is the WITH POINTER clause. This clause specifies the character position in the destination field, WS-SQL-STMT-TXT. Moving a one there initially means to start writing in character position one of the destination. The DELIMITED BY SIZE after each string literal simply means the string will be written to destination field in its entirety. When all string literals have been processed, the value in the pointer field, WS-SQL-STMT-LEN, will contain a value that's one character position beyond the last character in the destination field, WS-SQL-STMT-TXT. Therefore the exact length of the VARCHAR can be derived by subtracting one from the pointer value calculated by STRING statement. Having the exact length of the string in the length part of the varchar host var ensures that it doesn't look beyond the SQL string and accidentally get the junk that might exist in the text part.
Attachments:
#MFDS
#EnterpriseDeveloper
