Bug 5877

Embedded SQL Output into Variable via 'INTO' cuts off at 16834 bytes 14 January, 2021

Steven Green
13 January, 2021
Product: PowerBuilder Category: Database
Version: 2019 R2 Build: 2353
Classification: Publishing: Public
Priority: P3
Status: Verifying Reason:
Communication Status: Waiting for Customer
Ken Guo @Appeon 14 January, 2021
#4
Hi Steven,

Thanks for finding this workaround and sharing it with us.
When PB retrieves the database content, normally it is limited to characters within 32K. However, your limitation is 16K. Maybe the Unicode is causing the issue that it retrieves less content.

I suggest you use the workaround you found to bypass it.

Regards,
Ken
Steven Green 13 January, 2021
#3
(In reply to Steven Green from comment #2)
Before the output was cut off around 16300 bytes, now it is over that (16995
bytes)
and the whole of that is stored in a string variable
Steven Green 13 January, 2021
#2
Before the output was cut off around 16300 bytes, now it is over that (16995 bytes)
Steven Green 13 January, 2021
#1
I was able to work around this issue by using a blob:
------------------------------------------------------------------------------------------
//generating xml for ribbon
string xml_pre, xml_post, Ribbon_xml
blob sql_xml_out

 SELECT xmlagg( xmlelement ( "RibbonBar", xmlattributes( '0' as "BuiltinTheme", 'FaceName:Tahoma; TextSize:9' AS "Font" ) , xmlelement ( "RibbonItems" , xmlelement ( "Categories" ,
       (
              SELECT xmlagg ( xmlelement( "Category" ,xmlattributes(ribbon_category.text AS "Text", ribbon_category.tag AS "Tag", ribbon_category.enabled AS "Enabled", ribbon_category.visible AS "Visible" ),
                     (
                            SELECT xmlagg(xmlelement( "Panel" , xmlattributes(ribbon_panel.text AS "Text", ribbon_panel.tag AS "Tag", ribbon_panel.picturename AS "PictureName", ribbon_panel.enabled AS "Enabled", ribbon_panel.visible AS "Visible" ),
                                   (
                                          SELECT xmlagg(xmlelement( evalname ribbon_item.item_type , xmlattributes(ribbon_item.text AS "Text", ribbon_item.tag AS "Tag", ribbon_item.picturename AS "PictureName", ribbon_item.checked AS "Checked", ribbon_item.defaultcommand AS "DefaultCommand", ribbon_item.enabled AS "Enabled", ribbon_item.powertiptext AS "PowerTipText", ribbon_item.powertipdescription AS "PowerTipDescription", ribbon_item.visible AS "Visible", ribbon_item.clicked AS "Clicked", ribbon_item.selected AS "Selected" )))
                                          FROM   ribbon_item
                                          WHERE  ribbon_item.parent_id = ribbon_panel.sequence_id
                                          AND    securitypkg.isallowed('w_tplus_main'
                                                 /*ribbon_item.tag  this will be instead ribbon_item.tag */
                                                 , :gs_userid
                                                 /*gs_userid  this will be a var for users username */
                                                 , ribbon_item.text
											/* The text name of the ribbon_item has matching records in security_info table*/
											) = 0) ))
                            FROM   ribbon_panel
                            WHERE  ribbon_panel.parent_id = ribbon_category.sequence_id ) ))
              FROM   ribbon_category
              WHERE  ribbon_category.parent_id = ribbon_master.rm_sequence_id))))).getblobval(NLS_CHARSET_ID('UTF8'))
       AS "XML"
       INTO :sql_xml_out
FROM   ribbon_master
WHERE  ribbon_master.rm_sequence_id = :farg_seqid
       /*USING SQLCA*/
       ;

Ribbon_xml = string(sql_xml_out, EncodingUTF8!)

//loading ribbon with the xml
rbb_1.ImportXML(Ribbon_xml)
Steven Green 13 January, 2021
*Phenomenon:
When outputting from Oracle a large string into a string variable using the INTO statement cuts off the string at 16384 bytes. When running the same statement on the database directly it returns the full string with no issues. There appears to be some size limitation with the embedded sql layer. When using a string and a char[ ] set to 32000 bytes we get the same issue.

*Reproduce Steps:
Output a large string with over 16384 bytes of data from a database using SELECT INTO :variable
it should get cut off.

Remarks:

See our powerscript sql below:
----------------------------------------------------------------------------------------------------------------------------------------
//testing
char test_one[]
test_one = space(64000)
////


//generating xml for ribbon
string xml_pre, xml_post, Ribbon_xml

xml_pre = '<?xml version="1.0" encoding="utf-8" standalone="yes"?><RibbonBar BuiltinTheme="0" Font="FaceName:Tahoma; TextSize:9"><RibbonItems><Categories>'
xml_post = '</Categories></RibbonItems></RibbonBar>'

SELECT :xml_pre || XMLAGG(XMLELEMENT( "Category" ,
    XMLAttributes(ribbon_category.text AS "Text", ribbon_category.tag AS "Tag", ribbon_category.enabled AS "Enabled", ribbon_category.visible AS "Visible" ),
    ( SELECT XMLAGG(XMLELEMENT( "Panel" , XMLAttributes(ribbon_panel.text AS "Text", ribbon_panel.tag AS "Tag", ribbon_panel.picturename AS "PictureName", ribbon_panel.enabled AS "Enabled", ribbon_panel.visible AS "Visible" ),
        ( SELECT XMLAGG(XMLELEMENT( EVALNAME ribbon_item.item_type , XMLAttributes(ribbon_item.text AS "Text", ribbon_item.tag AS "Tag", ribbon_item.picturename AS "PictureName", ribbon_item.checked AS "Checked", ribbon_item.defaultcommand AS "DefaultCommand", ribbon_item.enabled AS "Enabled", ribbon_item.PowerTipText AS "PowerTipText", ribbon_item.powertipdescription AS "PowerTipDescription", ribbon_item.visible AS "Visible", ribbon_item.clicked AS "Clicked", ribbon_item.selected AS "Selected" ))) FROM ribbon_item WHERE ribbon_item.parent_id = ribbon_panel.sequence_id AND securitypkg.isAllowed('w_tplus_main' /*ribbon_item.tag  this will be instead ribbon_item.tag */, :gs_userid /*gs_userid  this will be a var for users username */, ribbon_item.text) = 0)
    )) FROM ribbon_panel WHERE ribbon_panel.parent_id = ribbon_category.sequence_id ) 
)).getClobVal() || :xml_post
AS "XML"
INTO :test_one //:Ribbon_xml
FROM ribbon_master
LEFT JOIN ribbon_category ON ribbon_category.parent_id  = ribbon_master.rm_sequence_id
WHERE ribbon_master.rm_sequence_id = :farg_seqid
USING SQLCA;

//loading ribbon with the xml
rbb_1.ImportXML(Ribbon_xml)
OS:
Windows 10
Platform:
64-bit
Database Type:
Oracle
Database Version:
19c