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
 
SQLCODE = -84 from Dynamic sql in cobol program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> ABENDS & Debugging
View previous topic :: :: View next topic  
Author Message
Baskaran
Warnings : 1

New User


Joined: 24 Oct 2006
Posts: 32
Location: India

PostPosted: Thu Apr 26, 2007 11:33 am    Post subject: SQLCODE = -84 from Dynamic sql in cobol program
Reply with quote

Hi,

I need to write a cobol program with dynamic SQl.I used

EXEC SQL
PREPARE STMT FROM :WS-SQL-TXT
END-EXEC.

working storage declaration:
01 WS-SQL-VARIABLE.
05 WS-SQL-LENGTH PIC S9(4) COMP.
05 WS-SQL-TXT PIC X(29).

I get a error when compilaing the program,How i can resolve this error
Error:

SQLCODE = -84.
SQLSTATE= '42612'
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Thu Apr 26, 2007 11:50 am    Post subject: Re: dynamic sql in cobol program
Reply with quote

Hi!

I am in a good mood and did the searching and the reading for You
( in the hope of teaching You something )

Quote:

-084 UNACCEPTABLE SQL STATEMENT

Explanation: This SQL statement is unacceptable to DB2. One of the following has occurred:

* An attempt has been made to PREPARE or EXECUTE IMMEDIATE an SQL statement that cannot be prepared; refer to the proper SQL statement in DB2 SQL Reference

* The embedded SQL statement is not an SQL statement supported by DB2.

* The statement referenced an undeclared cursor.

* An attempt was made to prepare an ALLOCATE CURSOR statement but the statement identifier is already associated with a declared cursor.

System Action: The statement cannot be executed.

Programmer Response: If the situation involves an SQL statement that cannot be prepared, the problem is in the source of the SQL statement, not the application program. Thus, no action is necessary unless the source of the SQL statement is the application program itself.

If the situation involves an SQL statement that is not supported by DB2, remove it from the application program and precompile again.

If the situation involves an invalid PREPARE of an ALLOCATE CURSOR statement, change the application program to use a statement identifier that is not associated with a declared cursor.


Quote:

42612
The statement string is an SQL statement that is not acceptable in the context in which it is presented.


if You do not know how to search, here is how

start from
http://www-03.ibm.com/servers/eserver/zseries/zos/bkserv/find_shelves.html

search for a book shelf for DB2

choose the bookshelf related to Your db2 version/release,
in case of multiple bookshelves for the same version/release choose the one with the latest date

search for -84
and You get the first quote

search for 42162
and You get the second quote
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Thu Apr 26, 2007 12:02 pm    Post subject:
Reply with quote

What is in "WS-SQL-TXT"? Please post the contents.
Back to top
View user's profile Send private message
kanak

Moderator


Joined: 12 Mar 2005
Posts: 252
Location: India

PostPosted: Thu Apr 26, 2007 2:15 pm    Post subject:
Reply with quote

i think while performing the operation where you are moving the unit sql staement to WS-SQL-TXT, there must be some some spaces or some unwanted charaters are there. So after doing a string operation on WS-SQL-TXT, you do a prepare and at the time it's giving error. Check by debugging the program (whatever you use: intertest, xpeditor, smarttest etc)...what exactly is there in WS-SQL-TXT. Verify that test properly. might be you will be able to locate that. if not plz post that string in forum, without removing any spaces, might experts over here will be able to help you out on the same.
Back to top
View user's profile Send private message
Baskaran
Warnings : 1

New User


Joined: 24 Oct 2006
Posts: 32
Location: India

PostPosted: Thu Apr 26, 2007 11:55 pm    Post subject: Dynamic SQL in cobol program
Reply with quote

Hi All,


I need to write a dynamic sql cobol progarm;the Sql will be a select statment .

My doubt is Do i have to use SQLCA or SQLDA in cobol program;Will i get error if i use SQLCA.I tried with SQLCA i got an error sqlcode = -84 error ,I ma not sure if the error is because i used SQLCA instead of SQLDA.

I didnt tried with SQLDA.

Thanks
Baskaran
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Apr 27, 2007 2:12 am    Post subject: Re: Dynamic SQL in cobol program
Reply with quote

icon_surprised.gif SQLCA and SQLDA are two different things.

SQLCA is the SQL Communications Area and contains information about the results of the last SQL statement issued by your program.

SQLDA is used by DB2 to share fields with your program.

In my last CICS/DB2 program, I used SQLCA to evaluate the return codes from my SQL queries, but did not use SQLDA at all.

The -84 return code you received was because of an invalid SQL statement, for one of the following reasons:

1. An attempt to Prepare or Execute Immediate an SQL statement that cannot be Prepared;

2. The embedded SQL statement isn't an SQL statement supported by DB2; or,

3. The statement referenced an undeclared cursor.



Post your statement and let us take a look at it. In the meantime, I highly recommend Murach's DB2 for the COBOL Programmer Parts 1 & 2. www.murach.com
Back to top
View user's profile Send private message
Baskaran
Warnings : 1

New User


Joined: 24 Oct 2006
Posts: 32
Location: India

PostPosted: Fri Apr 27, 2007 12:28 pm    Post subject: Re: SQLCODE = -84 from Dynamic sql in cobol program
Reply with quote

HI,

I have made my program which i have pasted here.For this code if i tried to compile i get an error.Can any one tell me hiow i should proceed
error :
==========================================
*****EXEC SQL
***** PREPARE STMT FROM :WS-SQL-TXT
*****END-EXEC.
MOVE -84 TO SQLCODE.
MOVE '42612' TO SQLSTATE.

DSNH080I E DSNHSM3D LINE 142 COL 36 STRING VARIABLE "WS-SQL-TXT" IS NOT "VARIABLE "WS-SQL-TXT" IS NOT "VARCHAR" TYPE"


Program i used is given below.
==========================================
WORKING STORAGE SECTION.

01 WS-SQL-VARIABLE.
05 WS-SQL-LENGTH PIC S9(4) COMP.
05 WS-SQL-TXT PIC X(100).
*DB2 COPYBOOK
01 TABLE-VLP1053.
05 VLP1053-ROW.
EXEC SQL INCLUDE VLP1053C END-EXEC.
*DB2 DCLGEN FOR TABLE TLP1053.
EXEC SQL INCLUDE VLP1053T END-EXEC.

=================================

Procedure division.
MOVE 24 TO WS-SQL-LENGTH.
MOVE "SELECT CNNO FROM VLP1053"
TO WS-SQL-TXT.
EXEC SQL
DECLARE C1 CURSOR FOR STMT
END-EXEC.
EXEC SQL
PREPARE STMT FROM :WS-SQL-TXT
END-EXEC. [/quote]
Back to top
View user's profile Send private message
Baskaran
Warnings : 1

New User


Joined: 24 Oct 2006
Posts: 32
Location: India

PostPosted: Fri Apr 27, 2007 2:06 pm    Post subject: DSNH312I- abend
Reply with quote

Hi All,

How do fix error DSNH312I?

Senario:

I am working with dynamic sql in a cobol program. i have following declaration in working storage

05 WS-SQL-VARIABLE.
49 WS-string-LENGTH PIC S9(4) COMP.
49 WS-string-TXT PIC X(100).



procedure divi:

move "sql statmanet " to WS-string-TXT .

EXEC SQL prepare STMT from WS-string-TXT
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Fri Apr 27, 2007 2:15 pm    Post subject: Re: DSNH312I- abend
Reply with quote

Quote:

DSNH312I
E csectname LINE nnnn COL cc UNDEFINED OR UNUSABLE HOST VARIABLE name


You have to check the program and fix it

Since YOU! are asking for help, it would be fair not to make us do the searching and the reading for the message text and explanation
Back to top
View user's profile Send private message
ashwinreddy

Active User


Joined: 16 Sep 2004
Posts: 106
Location: Hyderabad

PostPosted: Fri Apr 27, 2007 3:04 pm    Post subject:
Reply with quote

Hi,

This error is already discussed in the forum long back. Its problem with the Host variable length.

http://ibmmainframes.com/about1070.html


Cheers
Ashwin
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 -> ABENDS & Debugging All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Accessing CICS tran with map from JCL... navdeepaggarwal CICS 5 Tue Oct 03, 2017 6:15 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am

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