Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm
No new posts INVOKE WEBSERVICE error danik56 CICS 11 Thu Mar 09, 2017 11:52 am
No new posts Row not found Sql error GaganGarg DB2 7 Tue Mar 07, 2017 6:29 am
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us