CODEWITHSUNDEEP
ibm-midrangerpgle
Nuno Carriço
Nuno Carriço

Reputation: 13

Write CLOB data to IFS file in SQL RPGILE

I have an RPGILE program that's returning a SQL with XMLSERIALIZE to a CHAR(32000) variable. Sometimes the result will be over the 32k size. What's the easiest way of doing this? The program doesn't compile if I define a clob field and then use it to return the SQL result. I got it working creating a file in QTEMP with a clob field, then instead of returning the value in the RPG I insert the result in that file and then write it out to the IFS. But the XML file seems to have invalid data at the end and it's unusable, any ideas?

XML Processing

D ResultXML       S          32000A   varying        
........
/FREE                                       
exec sql declare :ResultXml  variable;    
exec sql declare :SQLMESSAGE VARIABLE;  
exec sql                                                      
SELECT                                       
XMLSERIALIZE (content                                         
xmlelement   (NAME "Document",                                
xmlnamespaces(                                                
DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pain.008.001.02'      
),                                                              
...
AS clob(1m)      ccsid 37   INCLUDING XMLDECLARATION      
as response                                                  
  INTO :ResultXML                                              
 FROM csp40      t

File Writing

... then we write it to the IFS
EVAL fd = open(%trimr(resp_file): 74 :511);                   
if fd=-1;                                                     
   eval      RC = perror(resp_file);                          
   return;                                                     
endif;                                                        
 EVAL rc = write(fd:%addr(ResultXML)+2:%len(ResultXML));       
 EVAL rc = close(fd);

Upvotes: 1

Views: 2212

Answers (1)

David G
David G

Reputation: 4014

Check out the DB2 SQL Embedded Programming guide.

You probably want to define your host variable as a SQL_TYPE like:

dcl-s ResultXML SQLTYPE(CLOB:32000);

Upvotes: 2

Related Questions