Page 1 of 1

How to use host variable in dynamic sql?

PostPosted: Fri Apr 29, 2011 11:52 am
by yang li
Hi,
I want count rows for each table(10 tables) and write count of rows in data set. But met problem with host variable issue.
In Cobol program sysout.
Display select sql string:
SEL-EXP-STR-DTA=SELECT COUNT(*) INTO :WS-REC-CNT FROM tab_name_1 WHERE FLG = 'Y'

ERROR MSG:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "-". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: . FROM : , <IDENTIFIER>

Here are details:
01 DYNAMIC-SQL-STR.
03 SEL-EXP-STR.
49 SEL-EXP-STR-LEN PIC S9(04) COMP.
49 SEL-EXP-STR-DTA PIC X(750).
01 WORK-FIELDS.
05 WS-TAB-NAME PIC X(40).
05 WS-REC-CNT PIC S9(08) COMP.
......
MOVE 1 TO SEL-EXP-STR-LEN
MOVE 'tab_name_1' TO WS-TAB-NAME
STRING 'SELECT COUNT(*) INTO :WS-REC-CNT' DELIMITED BY SIZE
' FROM ' DELIMITED BY SIZE
WS-TAB-NAME DELIMITED BY SPACE
" WHERE FLG = 'Y' " DELIMITED BY SIZE
INTO SEL-EXP-STR-DTA
WITH POINTER SEL-EXP-STR-LEN
END-STRING

SUBTRACT 1 FROM SEL-EXP-STR-LEN
EXEC SQL
PREPARE SEL_EXP FROM :SEL-EXP-STR
END-EXEC

EXEC SQL
EXECUTE SEL_EXP
END-EXEC
.....

Is there anything wrong with this code?
Thanks in advance.

Re: How to use host variable in dynamic sql?

PostPosted: Fri Apr 29, 2011 1:43 pm
by yang li
Finally, I found the reason.
"Dynamic SELECT statement cannot use INTO, a cursor must be used to put the results into host variables" ---come from the doc DB2 UDB: App Programming -Advanced.
So I changed it to dynamic cursor, then it works. But I'm not sure it's a good practice,
Each time will open , fetch , and close cursor even we know it only has one record in cursor.