Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Error while using dynamic SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
nitin_d

New User


Joined: 27 Oct 2005
Posts: 15
Location: Pune

PostPosted: Thu Apr 10, 2008 12:44 pm    Post subject: Error while using dynamic SQL
Reply with quote

Hello All,

I have been trying to open a cursor with fetch first n rows only, where the value of n has to change dynamically.

The code snippets that i have used are as follows

EXEC SQL
INCLUDE SQLCA
END-EXEC.

01 DSTRING.
05 STRING-VAR-LEN PIC S9(4) COMP VALUE 92.
05 STRING-VAR-TXT PIC X(92).
( I build the query in a different group variable and move that into STRING-VAR-TXT)

EXEC SQL
DECLARE C1 CURSOR FOR STMT
END-EXEC.

EXEC SQL
PREPARE STMT FROM :DSTRING
END-EXEC.

I get DB2 pre-compiler error message as follows

UNDEFINED OR UNUSABLE HOST VARIABLE "DSTRING"
STRING VARIABLE "DSTRING" IS NOT "VARCHAR" TYPE

Can anybody please help me with this error....
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 10, 2008 3:10 pm    Post subject:
Reply with quote

remove value clause 92 from the length field ...declare it as

05 STRING-VAR-LEN PIC S9(4) COMP.

varchar dynamically assigns the length whn u move the field to the txt field ...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 10, 2008 3:15 pm    Post subject:
Reply with quote

one more thing use 49 level for ur varchar declaration

01 DSTRING.
49 STRING-VAR-LEN PIC S9(4) COMP.
49 STRING-VAR-TXT PIC X(92).
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Apr 10, 2008 5:33 pm    Post subject:
Reply with quote

ashimer wrote:
remove value clause 92 from the length field ...declare it as

05 STRING-VAR-LEN PIC S9(4) COMP.

varchar dynamically assigns the length whn u move the field to the txt field ...


WRONG
Back to top
View user's profile Send private message
zero

New User


Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad

PostPosted: Fri Apr 11, 2008 9:30 am    Post subject: Reply to: Error while using dynamic SQL
Reply with quote

Hi,
when a Query is executed dynamically, we should use the VARCHAR datatype. we can define a VARCHAR field in cobol, by using the approach that you are using. as ashimer said, the level numbers should be 0f 49.
So, use this :

Code:
01 DSTRING. 
    49 STRING-VAR-LEN   PIC S9(4) COMP.
    49 STRING-VAR-TXT   PIC X(92).


Moving a string to -TXT field does not automatically finds its length. we have to find it ourselves.
After preparing the SQL stmt, find its length and move it to STRING-VAR-LEN field.

hope this helps,
Back to top
View user's profile Send private message
nitin_d

New User


Joined: 27 Oct 2005
Posts: 15
Location: Pune

PostPosted: Fri Apr 11, 2008 4:09 pm    Post subject:
Reply with quote

Thanks all, I was declaring the VARCHAR elemnets at 05 level and not 49. That was what the actual root cause was.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
No new posts FUSE - IBM MQ - CICS integration error Kevin Vaz CICS 1 Mon Sep 18, 2017 4:31 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us