?
Solved

Declare CLOB as a host variable and use it in SQL statement in PLI

Posted on 2009-04-23
4
Medium Priority
?
1,411 Views
Last Modified: 2013-11-25
Hi guys,
 i get compile error when I use the code 1, and SOC4 error when I use the code 2. This is in language PLI (mainframe)

1) Compile Error: UNUSABLE HOST VARIABLE HOST_VAR
I am declaring HOST_VAR as 02 level.

DCL  01 STRUC,
             02 VAR1               CHAR(10),
             02 VAR2               CHAR(5),
              02  HOST_VAR USAGE IS SQL TYPE IS CLOB (10M);

EXEC SQL
  SELECT :HOST_VAR FROM SYSIBM.SYSDUMMY1;

2) S04 error  : Indexing or subscripting outside defined limits
When I am declaring  HOST_VAR as seperate 01 level

01 HOST_VAR USAGE IS SQL TYPE IS CLOB (10M);

EXEC SQL
  SELECT :HOST_VAR FROM SYSIBM.SYSDUMMY1

any ideas?
0
Comment
Question by:AndrewBains
1 Comment
 

Accepted Solution

by:
AndrewBains earned 0 total points
ID: 24286317
I realized that I was not using the CLOB host variable in the SQL correctly.  
SInce I was running out of time, I went in other direction where I converted CLOB into array of strings and used the Multi row insert instead

Before logic (failed)
INSERT INTO TABLE1 (COL1,COL2...)
WITH CTE ( recursive sql to parse clob host variable to convert into multi row)
select col1,col2 from cte

After logic (successful)
write some logic in PL/I  to convert CLOB variable to array
INSERT INTO TABLE1  (col1, col2,...)    
               VALUES (:HV-COL1-ARRAY,    
                       :HV-COL2-ARRAY..          
)           FOR 50 ROWS
           ATOMIC

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Six Sigma Control Plans
Screencast - Getting to Know the Pipeline

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question