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
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Ed Schechter
New Member
New Member

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                                        

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?

Veteran Member
Veteran Member

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) 
MyClob_Data = 'blabla......';
MyClob_Len = %Len(%Trim(My_ClobData);

You are not authorized to post a reply.

Acceptable Use Policy