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
 

 

Dynamic SQL in COBOL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
kalhor

New User


Joined: 20 May 2007
Posts: 31
Location: iran

PostPosted: Thu Aug 06, 2009 5:11 pm    Post subject: Dynamic SQL in COBOL
Reply with quote

Hello everybody,

My program is receiving SQL CODE = 514 and I don't know what should to do, in my program I have used dynamic sql to fetching the data from table, also the select statement and the Where clause are in their variables compile, link, edit and bind return code are "0" but the sql code is 514 in run time.

thanks,
Kalhor
Back to top
View user's profile Send private message

Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1224
Location: Israel

PostPosted: Thu Aug 06, 2009 7:46 pm    Post subject: Reply to: Dynamic SQL in COBOL
Reply with quote

SQLCODE -514 says it all: "THE CURSOR cursor-name IS NOT IN A PREPARED STATE"

1. You cannot perform a single row SELECT when using dynamic SQL. You must use a cursor.

2. The cursor must be prepared before it is opened.

Please refer to Squeezing the Most Out of Dynamic SQL. Look at chapter 3.7 in PDF file for examples.
Back to top
View user's profile Send private message
kalhor

New User


Joined: 20 May 2007
Posts: 31
Location: iran

PostPosted: Sat Aug 08, 2009 11:26 am    Post subject:
Reply with quote

Hi Marso

Thanks for your help, can you please check the following code

WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL INCLUDE SQLDA END-EXEC.
01 DSTRING.
49 VAR-LEN PIC S9(4) COMP VALUE 40.
49 VAR-TEXT PIC X(40).
01 BSTRING.
02 FILLER PIC X(32) VALUE "SELECT TRANS_DATE FROM GLIF_VIEW"
02 STMTTEXT PIC X(8).
===================
PROCEDURE DIVISION.
0001-MAIN.
EXEC SQL DECLARE C1 CURSOR FOR STMT END-EXEC.
MOVE BSTRING TO DSTRING.
EXEC SQL PREPARE STMT FROM :DSTRING END-EXEC.
EXEC SQL
OPEN C1
END-EXEC.
EXEC SQL
FETCH C1 INTO
:TRANS-DATE
END-EXEC.
...
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Sat Aug 08, 2009 8:59 pm    Post subject:
Reply with quote

Hello,

It works better if you post the "real" code. . .

Why is the sqlcode not checked after every sql statement?
Back to top
View user's profile Send private message
kalhor

New User


Joined: 20 May 2007
Posts: 31
Location: iran

PostPosted: Mon Aug 10, 2009 10:49 am    Post subject:
Reply with quote

Hi Dick

As you have toled, I 've checked the SQL code after every SQL statment,
so the SQL code after prepare is 311
SQLCODE -311, Error: THE LENGTH OF INPUT HOST VARIABLE NUMBER IS NEGATIVE OR GREATER THAN THE MAXIMUM
But I don't know what dose it mean when it says MAXIMUM.
also this is a piece of real code which I've sent.

Thanks[/code][/quote]
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Mon Aug 10, 2009 3:42 pm    Post subject: Reply to: Dynamic SQL in COBOL
Reply with quote

Kalhor, Instead of moving moving BSTRING TO DSTRING..move "SELECT TRANS_DATE FROM GLIF_VIEW" to VAR-TEXT.
Back to top
View user's profile Send private message
kalhor

New User


Joined: 20 May 2007
Posts: 31
Location: iran

PostPosted: Mon Aug 10, 2009 5:06 pm    Post subject:
Reply with quote

Tankhs my friend it is solved.
Many thanks again.
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Mon Aug 10, 2009 7:41 pm    Post subject:
Reply with quote

Hello,

For future topics. . .
Quote:
I 've checked the SQL code after every SQL statment,
Then this should have been in the post. Copy/paste is far better than picking and choosing some parts of the info to type in with different names, missing code, etc.

One you have an error, it is best to solve that error rather than letting the code and trying to diagnose something that fails later in the code due to the already known error. As far as getting help from the forum, we can't do much if the real/first error is not the one posted. It just wastes time until the actual problem is posted and resolved.
Back to top
View user's profile Send private message
kalhor

New User


Joined: 20 May 2007
Posts: 31
Location: iran

PostPosted: Tue Aug 11, 2009 3:47 pm    Post subject:
Reply with quote

Hi Dick.

I used your advice of checking SQL code after every SQL statement and it was the key to reach -311 SQL code and solve the problem. but I prefer SQL code 514 which reminds me to thank you 514 times.

Regards,
Kalhor
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Tue Aug 11, 2009 8:01 pm    Post subject: Reply to: Dynamic SQL in COBOL
Reply with quote

You're welcome 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    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Dynamic array request/response contai... Suja.Sai CICS 2 Tue Jan 24, 2017 11:37 am
This topic is locked: you cannot edit posts or make replies. Full Time COBOL Software Development ... shally Mainframe Jobs 0 Fri Jan 20, 2017 5:24 am
This topic is locked: you cannot edit posts or make replies. COBOL Software Development Lead at Fi... shally Mainframe Jobs 0 Fri Jan 20, 2017 5:21 am
No new posts Executing OO COBOL program invoking J... Virendra Shambharkar COBOL Programming 2 Tue Jan 10, 2017 6:37 pm
No new posts OO COBOL compile error Virendra Shambharkar COBOL Programming 3 Tue Jan 10, 2017 6:05 pm


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