Re: CCSID issues with IFS files created with SQL

[Justin Barrett <justyb11@xxxxxxxxx>]

The first question you mentioned, rather than repeat, I'll indicate that 
you check out this https://www.rpgpgm.com/2018/05/creating-xml-file.html.

Second, I do not see CCSID being used for SQLTYPE(CLOB_FILE) anywhere in 
the SQL embedded manual, so I'm not sure what CCSID after SQLTYPE will 
do, but clearly it isn't what you wanted.  See the link I gave to have a 
bit more control over the flow of data.

Finally, the SQLTYPE(CLOB_FILE) is talked about in the Embedded SQL 
Programming manual.  For 7.1 you can find that here. 
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzajp/rzajp.pdf?view=kc

Thank you

On 8/26/20 2:07 AM, Carel wrote:
> Hi all,
>
> For a short term project working on a contract at the moment we have 
> some issues with files created on the IFS regarding CCSID.
> (Note: when I talk about readable in Notepad, it means also readable 
> on another sytem)
>
> First some data on the environment the problem occurs. Do not know 
> whether all is relevant.
>
> OS release              V7.1
> CCSID system value      65535
> CCSID job               65535
> CCSID SRCPF             37
> CCSID PGM               37
> CCSID SRVPGM            65535
> CCSID PGM               65535
>
> We are in the process of creating XML docs that have to be sent to 
> another system (SAP).
>
> My colleague wriote a program to generate an XML doc is written to the 
> IFS.
> The program is initiated (called from) a client application running in 
> a Windows environment, wriiten in java.
> That would run a PJ job in QUSRWRK (with CCSID 37) and the XML doc on 
> the IFS has also CCSID 37.
> With WRKLNK readable data, with Notepad unreadable data.
>
> I am writing a program to generate an XML doc as well, testing on the 
> green screen only at the moment (job CCSID 65535).
> One of my test programs uses embedded SQL.
> This programs generates 2 IFS files (XML docs) at the moment: one 
> written with the IFS APIs (open, write close), the second using SQL.
>
> For using SQL to write to the IFS I have the following lines of code 
> in my program (placed in a procedure in a SRVPGM).
>
> D Outfile         S                   sqltype(CLOB_FILE) 
> CCSID(819)                (IFS File)
> D aXMLDoc         S sqltype(CLOB:15728640)
>
> *****C/EXEC SQL
> *****C+ DECLARE :aXMLDoc VARIABLE CCSID 819
> *****C/END-EXEC
>
> C                   Eval      L_IFSFile = %trim(IFSPath) + 
> %trim(IFSName)          (name IFS file)
> C                   Eval      aXMLDoc_Data = 
> %trim(dsLongText)                     (dsLongText contains the 
> generated XML doc, aprogram variable without a CCSID set)
> C                   Eval      aXMLDoc_Len = 15728640
>
> C                   Eval      Outfile_Name = %trim(L_IFSFile)
> C                   Eval      Outfile_NL = %len(%trimr(Outfile_Name))
>  * SQFCRT = parameter to create xml
> C                   Eval       Outfile_FO = SQFCRT
>
> C/EXEC SQL
> C+ SET :Outfile = :aXMLDoc
> C/END-EXEC
>
> The file created on the IFS has CCSID 37; with WRKLNK readable data, 
> with Notepad unreadable data.
> Changing the CCSID to 810 in WRKLNK: with WRKLNK unreadable data, with 
> Notepad unreadable data.
>
> If I activate the commented out lines with DECLARE: CCSID IFS file is 
> 819; with WRKLNK readable data, with Notepad unreadable data.
> (So, the CCSID of the field overrules the given CCSID of the IFS file).
>
> THe other file written to the IFS (using the IFS APIs):
> CCSID 819 (indicated on the open API); with WRKLNK readable data, with 
> Notepad readable data.
>
> Searching the Internet and the archives I came across a similar 
> question in 2011 with provided some code (and copied by me, see above) 
> for the solution.
>
> So, some questions:
> 1) How can we create in this environment an IFS file created with SQL 
> that is readable in both WRKLNK and Notepad?
> 2) Why does SQL ignores the set CCSID when the data field has not 
> assigned a CCSID (CCSID 37 for the IFS file)
> 3) Where is this documented? In the manuals for V7.1 there is no 
> mentioning or examples with key word sqltype(CLOB_FILE) CCSID(819) and 
> such others (SQFCRT).
>
> We can use the IFS APIs, but doing it entirely with SQL would be nice.
>
> TIA.
>
> Kind regards,
>
> Carel Teijgeler