|
View previous topic :: View next topic
|
| Author |
Message |
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
I'm trying to declare a cursor dynamically, and the select statement for the cursor varies according to input parameter.
But when I prepare the 'DECLARE CURSOR' statement, SQLCODE -084 always appear . As the the same cursor can be used without any problem in static SQL way, so , the DECLARE cursor itself is acceptable for DB2.
I cannot find why SQLCODE -084 always appear, can anybody help me on this? or, 'DECLARE CURSOR' statement cannot be used in dynamic way? |
|
| Back to top |
|
 |
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
Yes, dynamic SQL is possible with Cobol.
However, you do not Prepare a DECLARE statement. DECLARE statements look like this:
| Code: |
EXEC SQL DECLARE DT CURSOR FOR SEL END-EXEC
EXEC SQL DECLARE SEL STATEMENT END-EXEC |
In this example, it is the "SEL" sql statement that gets prepared:
| Code: |
EXEC SQL
PREPARE SEL INTO :SQLDA FROM :DYN-SQL-STMT
END-EXEC |
Where :DYN-SQL-STMT is a host variable that contains the SELECT statement. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
I'm afraid I did not statement myself clearly enough.
I'm going to -DECLARE CURSOR,--OPEN CURSOR , -FETCH CURSOR ,- POSITIONED DELETE, -CLOSE CURSOR .
the declare part like below, and should be executed by using dynamic sql way.
| Code: |
| DECLARE C1 CURSOR WITH HOLD FOR SELECT 1 FROM TMPA WHERE COL1 IN (SELECT COL1 FROM TMPB) FOR RS USE AND KEEP UPDATE LOCKS |
but when prepare above statement, -084 occurred. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| It failed because DECLARE is not an executable statement and cannot be Prepared. I suggest a careful reading of the DB2 Application Programming Guide be your next step. Pay particular attention to the sections dealing with dynamic SQL. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
| If cursor declaration cannot be dynamically prepared, is there an solution to my requirement? Please suggest. thanks in advance. |
|
| Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
You cannot just wrap a Prepare statement around a static SQL statement and expect it to work.
There is a solution, and you can find it in the manual I mentioned earlier. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
| Back to top |
|
|
|
|
