Returning a ResultSet longer than 32K
Last Post 21 Jan 2013 11:09 PM by B.Hauser. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Ed Schechter
New Member
New Member
Posts:1

--
21 Jan 2013 10:16 AM

We're on v6R1.

We have a number of result sets defined, which return a single column of long strings, which are generated programatically.

Up to 32K the programs work fine:


* Data structure for the Output Record to WEB: 
D OUTDATA         DS                  occurs(3) 
D w_xmlmsg                   32000a   varying

....

C/EXEC SQL                                        
C+ SET RESULT SETS ARRAY :OUTDATA FOR :OCUR ROWS  
C/END-EXEC


When we try to compile this with w_xmlmsg defined as 33000a, we get an SQL5011 -- host data structure not defined or not usable.

v6R1  increased the length of strings to 16M -- we have no problems using this internal to programs.  Is there a clean way to return the longer strings through a stored procedure?

B.Hauser
Veteran Member
Veteran Member
Posts:2014

--
21 Jan 2013 11:09 PM
The maximum length of character variables supported by SQL is 32740 Byte! For larger character strings large objects (CLOB, DBCLOB or BLOB) must be used.
Within RPG LOB variables can be defined with the keyword SQLTYPE(CLOB: Length) up to the maximum length an RPG variable can have.
A CLOB variable is converted by the SQL precompiler into a data structure with 2 subfields. The first has the suffix _Len and is defined as 10U 0 to hold the length of the data. The second subield has the suffix _Len, is defined as fixed length character field with lenght specified in the CLOB Variable definition.

D MyClob          S                              SQLTYPE(CLOB: 1000000) 
/Free
MyClob_Data = 'blabla......';
MyClob_Len = %Len(%Trim(My_ClobData);



Birgitta
You are not authorized to post a reply.

Acceptable Use Policy