Re: CCSID issues with IFS files created with SQL

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