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
 

 

multiple-row-insert in COBOL using a Dynamic SQL

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

New User


Joined: 04 Jun 2009
Posts: 30
Location: Bangalore

PostPosted: Thu Mar 14, 2013 6:52 pm    Post subject: multiple-row-insert in COBOL using a Dynamic SQL
Reply with quote

Hi,

Is it possible to code multiple-row-insert in COBOL using a Dynamic SQL?
Has anyone attempted it in past.

Code:
STRING
'INSERT INTO TABLE.ABC VALUES'
DELIMITED BY SIZE
WS-VARIABLE(I)
DELIMITED BY SIZE
INTO
SAMPLE-TEST

EXEC SQL
PREPARE STMT1 FROM :SAMPLE-TEST
END-EXEC


This way i can get only ONE OCCURENCE using the subscript to get inserted.

Let me know if there is a way possible for multiple-row-insert in COBOL using a Dynamic SQL

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

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Mar 14, 2013 8:49 pm    Post subject:
Reply with quote

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.apsg%2Fsrc%2Ftpc%2Fdb2z_dynamicexecutedatachangestmt.htm
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_insert.htm
Back to top
View user's profile Send private message
Furor

New User


Joined: 04 Jun 2009
Posts: 30
Location: Bangalore

PostPosted: Thu Mar 14, 2013 9:29 pm    Post subject: Reply to: multiple-row-insert in COBOL using a Dynamic SQL
Reply with quote

Thanks...I did go through these links before... But they don't work in COBOL on those lines. Say while PREPARE if I use '?' For a host variable it doesn't work.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Mar 14, 2013 9:58 pm    Post subject:
Reply with quote

"it doesn't work" is synonym for "I don't know how" ?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Mar 14, 2013 10:04 pm    Post subject: Re: Reply to: multiple-row-insert in COBOL using a Dynamic S
Reply with quote

Furor wrote:
Thanks...I did go through these links before... But they don't work in COBOL on those lines. Say while PREPARE if I use '?' For a host variable it doesn't work.
Please show what you've tried and possibly you might get some close answers to your problem.
Back to top
View user's profile Send private message
Furor

New User


Joined: 04 Jun 2009
Posts: 30
Location: Bangalore

PostPosted: Thu Mar 14, 2013 10:06 pm    Post subject: Reply to: multiple-row-insert in COBOL using a Dynamic SQL
Reply with quote

So true...and therefore I asked is there a way.... I showed what I am trying to do.. Which is not working.. U can take it as another synonym..
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 15, 2013 2:08 pm    Post subject:
Reply with quote

ffs:
stating something "doesn't work" is not a very useful starting point for us to help you. I can point you to the codes & messages manual. I'm sure one of the 10.000 "programmers responses" in it might help you.
google "db2 multi row insert"
google "db2 parameter markers cobol"
Post some errorcodes you are receiving
Back to top
View user's profile Send private message
Furor

New User


Joined: 04 Jun 2009
Posts: 30
Location: Bangalore

PostPosted: Fri Mar 15, 2013 3:29 pm    Post subject: Reply to: multiple-row-insert in COBOL using a Dynamic SQL
Reply with quote

Code:

01  STRING-VARIABLE.                                     
        49  STRING-VAR-LEN     PIC S9(4) COMP VALUE +200.
        49  STRING-VAR-TXT     PIC X(200).               

01  I                                     PIC  S9(04) COMP VALUE ZEROES.
01 WS-ACT-NO    PIC X(9) OCCURS 100 TIMES.


Code:
MOVE +47 TO STRING-VAR-LEN   
MOVE SPACES TO STRING-VAR-TXT


Code:
STRING  'INSERT INTO TABLE_ABC (ACT_NO) VALUES ? '
DELIMITED BY SIZE                                       
INTO STRING-VAR-TXT


Code:
EXEC SQL                               
 PREPARE STMT1 FROM :STRING-VARIABLE                 
END-EXEC.     



Code:
EXEC SQL                                       
        EXECUTE STMT1 USING :WS-ACT-NO  FOR :I ROWS
END-EXEC.     


Prepare runs fine. Execute error give SQL code -313

Quote:
-313
THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS


I have tried other permutations and combinations ... this is the last one i coded.
So, just wanted to know that has anyone coded "multiple-row-insert in COBOL using a Dynamic SQL " before.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Fri Mar 15, 2013 3:37 pm    Post subject:
Reply with quote

Well, just for starters WS-ACT-NO is 100 variables (OCCURS 100). Have you tried various permutations around that fact?
Back to top
View user's profile Send private message
Furor

New User


Joined: 04 Jun 2009
Posts: 30
Location: Bangalore

PostPosted: Fri Mar 15, 2013 3:52 pm    Post subject: Reply to: multiple-row-insert in COBOL using a Dynamic SQL
Reply with quote

Just finished my main course -

Code:
MOVE +19  TO ATT-VAR-LEN                     
STRING ' FOR MULTIPLE ROWS '                 
DELIMITED BY SIZE                           
INTO ATT-VAR-TXT                             
EXEC SQL                                     
    PREPARE STMT1 ATTRIBUTES :WS-ATT         
    FROM :STRING-VARIABLE                   
END-EXEC.                                   


Thanks
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
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts COBOL Version 6.1 Virendra Shambharkar COBOL Programming 5 Tue Nov 01, 2016 11:24 am
No new posts Cobol list of programs being called biswajit.dattagupta COBOL Programming 5 Tue Nov 01, 2016 2:10 am
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm


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