IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQLCODE 000000031J On Prepare statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
ballalsachin

New User


Joined: 22 Sep 2005
Posts: 15
Location: USA

PostPosted: Wed Dec 02, 2009 4:59 pm
Reply with quote

Hi,

I am trying to execute a cobol-db2 program having Dynamic SQL.
here is what I did
1. Declared
01 STRING-VAR-TXT.
49 STRING-VAR-LEN PIC S9(4) USAGE COMP.
49 STRING-QUERY PIC X(9000).

2. prepared the query in STRING0-VAR-TXT using String statements.
Number of columns i am fetching are constant. Query is a Simple select query with 13-15 where clauses, concatenated with 'AND'

3. Declared a Cursor using
EXEC SQL
DECLARE CSR CURSOR FOR FLSQL
END-EXEC.
Displayed the SQLcode after declaring the cursor. it is 00

4. prepared the statement using
EXEC SQL
PREPARE FLSQL FROM :STRING-VAR-TXT
END-EXEC.
displayed the SQLCODE of SQLCA; it is coming out to be 000000031J

just wondering what this code means. I am not being able to fetch the values using this cursor.
I tried to display the query which is constructed in the varchar. It seems to be correct.

Can someone please help ?

Thanks
Sachin
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Dec 02, 2009 5:05 pm
Reply with quote

31j is the zoned representation of a negative number

Code:

  number     printable      hex
  +0         {              C0             
  +1         A              C1             
  +2         B              C2             
  +3         C              C3             
  +4         D              C4             
  +5         E              C5             
  +6         F              C6             
  +7         G              C7             
  +8         H              C8             
  +9         I              C9             

  -0         }              D0             
  -1         J              D1             
  -2         K              D2             
  -3         L              D3             
  -4         M              D4             
  -5         N              D5             
  -6         O              D6             
  -7         P              D7             
  -8         Q              D8             
  -9         R              D9             


use the proper process to display correctly a negative number
given the above look for the meaning of a -311 sql code
Back to top
View user's profile Send private message
ballalsachin

New User


Joined: 22 Sep 2005
Posts: 15
Location: USA

PostPosted: Wed Dec 02, 2009 5:27 pm
Reply with quote

Thanks Enrico,

Appears to me that the length of host variable is a problem. Will dig down and let you know if the SQL works.

Appreciate your response.

Thanks
Sachin
Back to top
View user's profile Send private message
ballalsachin

New User


Joined: 22 Sep 2005
Posts: 15
Location: USA

PostPosted: Fri Dec 04, 2009 4:27 pm
Reply with quote

Subsequent to this, I wrote my query into the file and after inspection, made some corrections.

Now after prepare statement i am getting SQLCODE as 000000010M.
I assume this is SQLCODE -104.

The descripton of this errorcode is not very helpful in the Quickref:
ILLEGAL SYMBOL "token". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:
token-list

Can someone help me with what might the problem be ?

I tried executing the query that i build using a SPUFI, and it works.

Thanks
Sachin Ballal
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Dec 04, 2009 5:42 pm
Reply with quote

Quote:
I tried executing the query that i build using a SPUFI, and it works.


well since spufi is 'static' sql when compared to your program dynamic sql,
you comment is just worthless.

and Quickref is exactly that, it is an abbreviated reference to be used
by those who have spent a couple of minutes reading the complete reference.

since you seemed to be manually challanged and only like to use quick references use IBM LookAt Site
it is the same as quickref
(actually it is better,
tailored for someone like you,
you only have to enter the error code,
you don't have to know what you were trying to do before receiving the error,
and it provides links to error msg manuals - yes they exist).
Back to top
View user's profile Send private message
ballalsachin

New User


Joined: 22 Sep 2005
Posts: 15
Location: USA

PostPosted: Fri Dec 18, 2009 11:12 am
Reply with quote

When I said - I tried executing the query that i build using a SPUFI, and it works. - it meant there was not problem in the syntax of the query whatsoever.

Because the production version of my program is a CICS program, i do not have a luxury of displying what the query looks like - after 30 string statements.. So i wrote it into a file and tried executing that query using a spufi. .. anyways. Thanks for your help.

I figured out myself, that there was a problem with '<>' symbol. I was initially writing '¬=' by mistake. Another problem was - the length of the query in VARCHAR should be exactly equal to the length of the query.

Cheers
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Dec 18, 2009 9:34 pm
Reply with quote

Good to hear this is working - thank you for posting the solution icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Relate COBOL statements to EGL statement All Other Mainframe Topics 0
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts SQLCODE = -16002 when using XMLEXISTS DB2 1
No new posts process statement for SUPREC, CMPCOLM... TSO/ISPF 4
Search our Forums:

Back to Top