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
 
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: 1281
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: 1281
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: 1868
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
No new posts ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 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
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am

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