| View previous topic :: View next topic |
| Author |
Message |
nadh
Intermediate
Joined: 08 Oct 2004
Posts: 192
Topics: 89
|
 Posted: Tue Nov 11, 2008 8:15 am Post subject: Dynamic SQL |
 |
|
hi
I have written the below code
| Code: |
MOVE 'DELETE FROM TPRMO_DUP WHERE(DAYS(DATE(CURRENT_TIMESTAM
'P)) - DAYS(DATE(LST_ACT_TS)) > '?' )' TO :WS-STAT-TXT
DISPLAY 'WS-STAT-TXT:',WS-STAT-TXT
EXEC SQL
PREPARE STMT1 FROM :WS-STAT-TXT
END-EXEC.
EXEC SQL
EXECUTE STMT1 USING :WS-NBR-DAYS-IN
END-EXEC.
DISPLAY 'NUM OF RECORDS DEL:' SQLERRD(3). |
I have declared ws-stat-txt as
| Code: |
01 WS-STAT.
49 WS-STAT-LEN PIC S9(4).
49 WS-STAT-TXT PIC X(100).
01 WS-NBR-DAYS-IN PIC 9(4).
|
It's giving compilation error
| Code: |
STRING VARIABLE "WS-STAT-TXT" IS NOT "VARCHAR" TYPE
UNDEFINED OR UNUSABLE HOST VARIABLE "WS-NBR-DAYS-IN"
|
Please correct me where I'm wrong.
Thanks in advance
Nadh[/code] |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Nov 11, 2008 8:58 am Post subject: |
|
|
remove the colon <:> from this line:
'P)) - DAYS(DATE(LST_ACT_TS)) > '?' )' TO :WS-STAT-TXT
ws-stat-len should be a binary halfword
ws-nbr-days-in probably also.
move the length of the string put into ws-stat-txt to ws-stat-len
change your prepare statement to refer to ws-stat.
try that.
someone else with more experience will also contribute
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Tue Nov 11, 2008 12:54 pm Post subject: |
|
|
dbz provided you with the corrections that you'll need to apply to the WS-STAT-LEN and WS-NBR-DAYS-IN variables. I'm also thinking that you do not want to delimit the parameter marker, ?, with single quotes. One more thing, when you're generating dynamic SQL, it's good programming practice to CAST the parameter markers.
One suggestion for working with VARCHAR cols in a COBOL pgm. The STRING verb works great for VARCHAR. After you apply dbz's recommended fixes, try something like this:
| Code: | MOVE +1 TO WS-STAT-LEN
MOVE SPACES TO WS-STAT-TXT
STRING
'DELETE FROM TPRMO_DUP WHERE (DAYS(DATE(CURRENT_TIMESTAMP))'
' - DAYS(DATE(LST_ACT_TS)) > CAST(? AS INTEGER))'
DELIMITED BY SIZE
INTO WS-STAT-TXT
WITH POINTER WS-STAT-LEN
END-STRING
COMPUTE WS-STAT-LEN = WS-STAT-LEN - 1 |
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
