ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

CLOB_FILE and SQLCODE=-332

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • CLOB_FILE and SQLCODE=-332

    I 'm working on iSeries V7R3M0

    I'm trying to use SQL REPLACE to remove unwanted characters from IFS file.
    I used the code found in this forum (https://www.code400.com/forum/forum/iseries-programming-languages/rpg-rpgle/149402-replace-character-in-a-ifs-file)
    Code:
    [FONT=Calibri][SIZE=10px]h Debug(*YES)                                                                              [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]h DatFmt(*ISO)                                                                             [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]h DecEdit('.')                                                                             [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]h Option(*SRCSTMT:*NODEBUGIO)                                                              [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]h DftActGrp(*NO) ActGrp(*NEW)                                                              [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]h CCSID(*CHAR :*JOBRUN)                                                                    [/SIZE][/FONT]
    
    [FONT=Calibri][SIZE=10px]  DCL-S FromIFSFile SQLTYPE(CLOB_file);                                                   [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]  DCL-S ToIFSFile SQLTYPE(CLOB_file) ; // CCSID(1250);                                    [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]  DCL-S msg char(512) ;                                                                   [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   FromIFSFile_NAME = '/home/source.txt';                              [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   FromIFSFile_NL   = %len(%trim(FromIFSFile_NAME));        [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   FromIFSFile_FO   = SQFRD;                                            [/SIZE][/FONT]
    
    [FONT=Calibri][SIZE=10px]    ToIFSFile_NAME = '/home/result_tmp.txt';          [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]    ToIFSFile_NL   = %len(%trim(ToIFSFile_NAME));                        [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]    ToIFSFile_FO   = SQFOVR;                                             [/SIZE][/FONT]
    
    
    [FONT=Calibri][SIZE=10px]    Exec Sql Set :ToIFSFile = Replace(:FromIFSFIle, [/SIZE][/FONT][FONT=Calibri][SIZE=10px]ABCDE', '    ');                                 [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]    if ( sqlCod <> 0 ) ;                                                 [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]      Exec Sql get Diagnostics condition 1 :msg = MESSAGE_TEXT;       [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]   endif;                                                                     [/SIZE][/FONT]
    [FONT=Calibri][SIZE=10px]  *inlr = *on ;            [/SIZE][/FONT]
    FromIFSFile exists and has codepage=1208

    Program still can not do conversion because of sqlCod = -332 which means (msg) : "Character conversion between CCSID 1208 and CCSID 65535 not valid"

    Next, I created empty reslut file with CCSID 1252 before. It does not help. Result the same.

    I tried also
    DCL-S ToIFSFile SQLTYPE(CLOB_file) CCSID(1250);
    result the same.


    Does anyone know how to solve this problem ?

  • #2
    What CCSID is your job running under, it that is 65535 then that might be the problem. Change it to what is proper for your system, US English is 37.

    Comment


    • #3
      My job uses CCSID= 870.
      This is proper value for my system.

      I've also changed job CCSID to 037 and it does not help me

      Comment


      • #4
        The SQL message says that your job is running 65535. (Which isn't a "real ccsid", it means "hex" -- so it can't translate to/from utf-8 because of that.) You say your job is 870 -- how come SQL thinks that it is 65535?! Is the SQL running in a different job?

        Comment


        • #5
          Side question Scott - does Run SQL statement in System i Navigator run a different CCSID from STRSQL (which I guess is the system CCSID)? While trying to get httpGetClob or httpPostClob to work, I thought I noticed differences. CCSID always baffles me!
          Your friends list is empty!

          Comment


          • #6
            I use green screen session job with CCSID = 870.

            DSPJOB definition attributes (4th page)
            Default coded character set identifier = 870
            Coded character set identifier = 870

            I just wrote and compiled program as above using green screen. Compiled with CRTSQLRPGI OBJ(A1) COMMIT (*NONE) DEBUG(*SOURCE).

            STRDBG A1
            CALL A1

            and I see SQLCOD = -332 and msg= '"Character conversion between CCSID 1208 and CCSID 65535 not valid"'

            The question is: where does runtime get CCSID 65535 for resulting conversions?

            Comment


            • #7
              What is the CCSID of your program? (DSPPGM, detail *BASIC)

              Program variables have CCSID's these days. By default I think, the CCSID of the program. String literals in programs are also interpreted as being in the CCSID of the program. SQL processing respects host variable CCSID's and does implicit character set conversions where necessary. Therefore if the program is CCSID 65535, sometimes SQL functions with string literals will complain about being unable to convert from/to CCSID 65535.

              Usually the program CCSID is the CCSID of the source file that the program was compiled from. If you can, try compiling it from a CCSID 37 (or 870) source file.

              Alternatively, instead of using string literals in the SQL replace function, try using host variables defined explicitly as CCSID 37/807
              Code:
              Dcl-S fromString varchar(50) ccsid(37);
              Dcl-S toString varchar(50) ccsid(37);
              
              fromString = 'ABCDE';
              toString = '     ';
              
              Exec Sql Set :ToIFSFile = Replace(:FromIFSFIle, :fromString, :toString);
              I think, you can override this with compile option/Ctl-Opt/h-spec TGTCCSID. That might eliminate the need to change the source file or use CCSID 37 host variables.
              Using TGTCCSID was the only way I could get the JSON_TABLE() SQL function working in a program that was in a CCSID 65535 source file.

              Comment


              • #8
                1. Current program A1 has CCSID = 65535
                2. I tougth that this CCSID was taken from source file as IBM described:
                New parameter TGTCCSID for CRTBNDRPG and CRTRPGMOD to support compiling from Unicode source The ILE RPG compiler normally reads the source files for a compile in the CCSID of the primary source file. Since the compiler supports reading the source only in an EBCDIC CCSID, this means that the compile fails when the primary source file has a Unicode CCSID such as UTF-8 or UTF-16.

                The TGTCCSID parameter for the CRTBNDRPG and CRTRPGMOD allows the RPG programmer to specify the CCSID with which the compiler reads the source files for the compile. Specify TGTCCSID(*JOB) or specify a specific EBCDIC CCSID such as TGTCCSID(37) or TGTCCSID(5035).

                The default is TGTCCSID(*SRC).
                3. I moved source program to new source file with CCSID=870

                4. Compiled and call program and .... SQLCOD=0 but dsppgm still shows CCSID =65535
                5. The resulting file on IFS got CCSID = 500 and cannot be read.
                6. Looking inside the file, you can see the bushes themselves.
                7. Changing the CCSID of the file to IFS to 870, 1208, 1250 is always wrong.

                I will run some tests and look for a solution.
                If you have any suggestions, I will gladly listen.
                Thank you very much for your help so far.

                Comment


                • #9
                  After next tests:
                  1. The bushes from the result file disappeared as when set it correctly is CCSID to 1250 for source ifs file. After transfer by SSH it got 819.
                  2. The resulting file was created as UTF8. Why UTF8? But I can handle it.
                  3. But REPLACE didn't do its job

                  I'll continue tests with others parameters

                  Comment


                  • #10
                    Originally posted by zukes1966 View Post
                    After next tests:
                    1. The bushes from the result file disappeared as when set it correctly is CCSID to 1250 for source ifs file. After transfer by SSH it got 819.
                    I'm not sure what is meant by "bushes".

                    819 is usually the default CCSID. SSH does not know about CCSIDs, so when you transfer a file with it, it will be given the default. You'll need to change the CCSID after the transfer to solve the problem.

                    Originally posted by zukes1966 View Post
                    2. The resulting file was created as UTF8. Why UTF8? But I can handle it.
                    Sorry, i don't understand what you're saying, here. What was created as UTF-8? What are you doing when something is created as UTF-8?

                    Originally posted by zukes1966 View Post
                    3. But REPLACE didn't do its job

                    I'll continue tests with others parameters
                    Where are you specifying REPLACE? On which command? What job are you expecting it to do?

                    Comment


                    • #11
                      "Busches" means unreadable characters.

                      The resulting file was created as UTF8.
                      Resulting file is file which is result of my program.
                      Source file had CCSID = 1250 ('/home/source.txt)
                      The resulting file received the UTF8 CCSID ( '/home/result_tmp.txt' )
                      Why UFT8?

                      I really want to use TRANSLATE but the example taken from this forum used REPLACE ( and I expect it to replace string 'ABCDE' to ' ' ) .
                      At the moment I want to control the situation. Where and how CCSID is to be defined.
                      TGTCCSID for SQLRPGLE I can't set for program compiled under iOS V7R3M0

                      Comment


                      • #12
                        "REPLACE didn't do its job" means string ABCDE exists in source file and in destination file too.
                        It means string ABCDE has not been replaced

                        Comment


                        • #13
                          I got tired of these CCSID pages.
                          I wrote a short program in RPGLE that did what I wanted. I used my own * TBL object, which solved the problem (*TBL works like SQL TRANSLATE).
                          A bit regret, because a SQL based program would be more elegant.

                          Comment

                          Working...
                          X