ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

FieldProc Error invalid data on Zoned Numeric Column (488)

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

  • FieldProc Error invalid data on Zoned Numeric Column (488)

    First I want to apologize for my ignorance in RPGLE...we typically don't use this programming language. I've researched this issue for several days now, and cannot find a resolution. I'm currently trying to write a field procedure program. I have taken the IBM RPGLE example (https://www.ibm.com/support/knowledg...yfpexample.htm), and started to modify it to work for us.

    This program allows for several SQL types, clob, varchar, char, blob, etc, but I needed to add zoned numeric. So, to begin, I took the layout, and added everywhere necessary for the SQL_TYP_NUMERIC (488), and am now getting the error in the subject. Everything works when I set the fieldproc on a varchar column. I have stepped through the program on both numeric and varchar, and there are no errors, and everything looks like it is working, but when the fieldproc program finishes, I get the error:

    Message ID . . . . . . : SQL0685
    Date sent . . . . . . : 03/18/20 Time sent . . . . . . : 16:17:04

    Message . . . . : Field procedure on column ECNUM has returned invalid data.

    Cause . . . . . : Field procedure on column ECNUM has returned invalid data.
    Recovery . . . : Change the field procedure to return valid data.

    Thank you in advance for any help you can give on this.

  • #2
    It would be really helpful to see how you are defining the "layout" the numeric field(s) in particular. Are you defining them explicitly as Zoned (or type S in the old D-spec type definitions? Or are you just defaulting the type? RPG has five different numeric types float, integer, unsigned integer, zoned, and packed. Probably only the last two are in play here but which type you get depends on how the definition is done and where.

    Comment


    • #3
      Everything underlined is from me, everything else is from the sample program.

      Code:
      /copy QSYSINC/QRPGLESRC,QC3CCI
      /copy QSYSINC/QRPGLESRC,QUSEC
      /copy QSYSINC/QRPGLESRC,SQL
      /copy QSYSINC/QRPGLESRC,SQLFP
      
      // QSYSINC/H QC3DTAEN
      dcl-pr Qc3EncryptData extproc(*dclcase);
      clearData pointer value;
      clearDataLen int(10) const;
      clearDataFormat char(8) const;
      algorithmDesc likeds(QC3D0200); // Qc3_Format_ALGD0200
      algorithmDescFormat char(8) const;
      keyDesc likeds(T_key_descriptor0200) const;
      keyDescFormat char(8) const;
      cryptoServiceProvider char(1) const;
      cryptoDeviceName char(10) const;
      encryptedData pointer value;
      lengthOfAreaForEncryptedData int(10) const;
      lengthOfEncryptedDataReturned int(10);
      errorCode likeds(QUSEC);
      end-pr;
      
      // QSYSINC/H QC3DTADE
      dcl-pr Qc3DecryptData extproc(*dclcase);
      encryptedData pointer value;
      encryptedDataLen int(10) const;
      algorithmDesc likeds(QC3D0200); // Qc3_Format_ALGD0200
      algorithmDescFormat char(8) const;
      keyDesc likeds(T_key_descriptor0200) const;
      keyDescFormat char(8) const;
      cryptoServiceProvider char(1) const;
      cryptoDeviceName char(10) const;
      clearData pointer value;
      lengthOfAreaForClearData int(10) const;
      lengthOfClearDataReturned int(10);
      errorCode likeds(QUSEC);
      end-pr;
      
      // Constants from QSYSINC/H QC3CCI
      dcl-c Qc3_AES 22;
      dcl-c Qc3_ECB '0';
      dcl-c Qc3_Pad_Char '1';
      dcl-c Qc3_Bin_String '0';
      dcl-c Qc3_Key_Parms 'KEYD0200';
      dcl-c Qc3_Alg_Block_Cipher 'ALGD0200';
      dcl-c Qc3_Data 'DATA0100';
      dcl-c Qc3_Any_CSP '0';
      
      // Constants from QSYSINC/H SQL
      dcl-c SQL_TYP_CLOB 408; // CLOB - varying length string
      dcl-c SQL_TYP_NCLOB 409; // (SQL_TYP_CLOB + 1 for NULL)
      dcl-c SQL_TYP_VARCHAR 448; // VARCHAR(i) - varying length
      // string (2 byte length)
      dcl-c SQL_TYP_NVARCHAR 449; // (SQL_TYP_VARCHAR + 1 for NULL)
      dcl-c SQL_TYP_CHAR 452; // CHAR(i) - fixed length string
      dcl-c SQL_TYP_NCHAR 453; // (SQL_TYP_CHAR + 1 for NULL)
      dcl-c SQL_TYP_BLOB 404; // BLOB - varying length string
      dcl-c SQL_TYP_NBLOB 405; // (SQL_TYP_BLOB + 1 for NULL)
      [U]dcl-c SQL_TYP_NUMERIC 488; // NUMERIC[/U]
      
      // Other constants
      dcl-c KEY_MGMT_SIZE 16;
      dcl-c MAX_VARCHAR_SIZE 32767;
      dcl-c MAX_CLOB_SIZE 100000;
      [U]dcl-c MAX_NUM_SIZE 31;[/U]
      
      dcl-ds T_key_descriptor0200 template qualified;
      desc likeds(QC3D020000);
      key char(KEY_MGMT_SIZE);
      end-ds;
      
      dcl-ds T_DECODED_VARCHAR qualified template;
      len int(5);
      data char(MAX_VARCHAR_SIZE);
      end-ds;
      
      dcl-ds T_DECODED_CLOB qualified template;
      len int(10);
      data char(MAX_CLOB_SIZE);
      end-ds;
      
      [U]dcl-ds T_DECODED_NUM qualified template;
      len int(10);
      data char(MAX_NUM_SIZE);
      end-ds;[/U]
      
      dcl-ds T_ENCODED_VARCHAR qualified template;
      len int(5);
      keyManagementData char(KEY_MGMT_SIZE);
      data char(MAX_VARCHAR_SIZE);
      end-ds;
      
      dcl-ds T_ENCODED_CLOB qualified template;
      len int(10);
      keyManagementData char(KEY_MGMT_SIZE);
      data char(MAX_CLOB_SIZE);
      end-ds;
      
      [U]dcl-ds T_ENCODED_NUM qualified template;
      len int(10);
      keyManagementData char(KEY_MGMT_SIZE);
      data char(MAX_NUM_SIZE);
      end-ds;[/U]
      
      dcl-ds T_DECODED_DATA qualified template;
      varchar likeds(T_DECODED_VARCHAR) pos(1);
      clob likeds(T_DECODED_CLOB) pos(1);
      num likeds(T_DECODED_NUM) pos(1);
      end-ds;
      
      dcl-ds T_ENCODED_DATA qualified template;
      varchar likeds(T_ENCODED_VARCHAR) pos(1);
      clob likeds(T_ENCODED_CLOB) pos(1);
      num likeds(T_ENCODED_NUM) pos(1);
      end-ds;
      
      dcl-ds T_optional qualified template;
      bytes uns(10);
      type_indicator char(1);
      end-ds;
      Last edited by txhornsfan; March 19, 2020, 06:13 AM.

      Comment


      • #4
        When you alter a table and set the fieldproc program on a column, the first call is to Register(function code: 8), the next is Encode(function code: 0) to encode the values in said column.

        I set a breakpoint at the end of the program, and here are the parameters at the end of the program when setting the fieldproc on a VARCHAR column:

        -------------------------------------------------------------------------------
        -------------------------------register: varchar-------------------------------
        -------------------------------------------------------------------------------


        EVAL FuncCode
        FUNCCODE = 8

        EVAL OptionalParms
        OPTIONALPARMS.BYTES = 8
        OPTIONALPARMS.TYPE_INDICATOR = '0'

        EVAL DecodedDataType
        DECODEDDATATYPE.SQLFST = 452
        DECODEDDATATYPE.SQLFBL = 32
        DECODEDDATATYPE.SQLFL = 32
        DECODEDDATATYPE.SQLFP = 0
        DECODEDDATATYPE.SQLFS = 0
        DECODEDDATATYPE.SQLFC = 37
        DECODEDDATATYPE.SQLFAL = 0
        DECODEDDATATYPE.SQLRSV1 = ' '

        EVAL DecodedData
        DECODEDDATA.VARCHAR.LEN = **********
        DECODEDDATA.VARCHAR.DATA =

        EVAL EncodedDataType
        ENCODEDDATATYPE.SQLFST = 448
        ENCODEDDATATYPE.SQLFBL = 64
        ENCODEDDATATYPE.SQLFL = 64
        ENCODEDDATATYPE.SQLFP = 0
        ENCODEDDATATYPE.SQLFS = 0
        ENCODEDDATATYPE.SQLFC = 65535
        ENCODEDDATATYPE.SQLFAL = 64
        ENCODEDDATATYPE.SQLRSV1 = ' '

        EVAL EncodedData
        ENCODEDDATA.VARCHAR.LEN = **********
        ENCODEDDATA.VARCHAR.KEYMANAGEMENTDATA = '********** '
        ENCODEDDATA.VARCHAR.DATA =

        EVAL SqlState
        SQLSTATE = '00000'

        EVAL Msgtext
        MSGTEXT =

        -------------------------------------------------------------------------------
        --------------------------------encode: varchar--------------------------------
        -------------------------------------------------------------------------------


        EVAL FuncCode
        FUNCCODE = 0

        EVAL OptionalParms
        OPTIONALPARMS.BYTES = 8
        OPTIONALPARMS.TYPE_INDICATOR = '0'

        EVAL DecodedDataType
        DECODEDDATATYPE.SQLFST = 452
        DECODEDDATATYPE.SQLFBL = 32
        DECODEDDATATYPE.SQLFL = 32
        DECODEDDATATYPE.SQLFP = 0
        DECODEDDATATYPE.SQLFS = 0
        DECODEDDATATYPE.SQLFC = 37
        DECODEDDATATYPE.SQLFAL = 0
        DECODEDDATATYPE.SQLRSV1 = ' '

        EVAL DecodedData
        DECODEDDATA.VARCHAR.LEN = 16448
        DECODEDDATA.VARCHAR.DATA =
        ....5...10...15...20...25...30...35...40...45...50 ...55...60
        1 ' █{ ██ '
        61 ' KEYTYPE2 Ò█;Æ█ÐzaN-Ì@+█Ò§Ò█;Æ█ÐzaN-Ì@+█Ò§úSËð█@█]'
        121 'x██vOg´- '

        EVAL EncodedDataType
        ENCODEDDATATYPE.SQLFST = 448
        ENCODEDDATATYPE.SQLFBL = 64
        ENCODEDDATATYPE.SQLFL = 64
        ENCODEDDATATYPE.SQLFP = 0
        ENCODEDDATATYPE.SQLFS = 0
        ENCODEDDATATYPE.SQLFC = 65535
        ENCODEDDATATYPE.SQLFAL = 64
        ENCODEDDATATYPE.SQLRSV1 = ' '

        EVAL EncodedData
        ENCODEDDATA.VARCHAR.LEN = 64
        ENCODEDDATA.VARCHAR.KEYMANAGEMENTDATA = 'KEYTYPE2 '
        ENCODEDDATA.VARCHAR.DATA =
        ....5...10...15...20...25...30...35...40...45...5
        1 'Ò█;Æ█ÐzaN-Ì@+█Ò§Ò█;Æ█ÐzaN-Ì@+█Ò§úSËð█@█]x██vOg´-

        EVAL SqlState
        SQLSTATE = ' '

        EVAL Msgtext
        MSGTEXT =



        When I attempt to set the fieldproc program on a ZONED NUMERIC column, here are the parameters. Notice that it never gets past the register call, and I get the error in the title of this thread.

        -------------------------------------------------------------------------------
        ----------------------------register: zoned numeric----------------------------
        -------------------------------------------------------------------------------

        EVAL FuncCode
        FUNCCODE = 8

        EVAL OptionalParms
        OPTIONALPARMS.BYTES = 8
        OPTIONALPARMS.TYPE_INDICATOR = '2'

        EVAL DecodedDataType
        DECODEDDATATYPE.SQLFST = 488
        DECODEDDATATYPE.SQLFBL = 16
        DECODEDDATATYPE.SQLFL = 0
        DECODEDDATATYPE.SQLFP = 16
        DECODEDDATATYPE.SQLFS = 0
        DECODEDDATATYPE.SQLFC = 0
        DECODEDDATATYPE.SQLFAL = 0
        DECODEDDATATYPE.SQLRSV1 = ' '

        EVAL DecodedData
        DECODEDDATA.VARCHAR.LEN = **********
        DECODEDDATA.VARCHAR.DATA =

        EVAL EncodedDataType
        ENCODEDDATATYPE.SQLFST = 488
        ENCODEDDATATYPE.SQLFBL = 32
        ENCODEDDATATYPE.SQLFL = 32
        ENCODEDDATATYPE.SQLFP = 0
        ENCODEDDATATYPE.SQLFS = 0
        ENCODEDDATATYPE.SQLFC = 65535
        ENCODEDDATATYPE.SQLFAL = 32
        ENCODEDDATATYPE.SQLRSV1 = ' '

        EVAL EncodedData
        ENCODEDDATA.VARCHAR.LEN = **********
        ENCODEDDATA.VARCHAR.KEYMANAGEMENTDATA = '********** '
        ENCODEDDATA.VARCHAR.DATA =

        EVAL SqlState
        SQLSTATE = '00000'

        EVAL Msgtext
        MSGTEXT =

        Comment


        • #5
          Sorry - the only thing I can think of is that the field you are processing is perhaps not Zoned numeric but packed. Is the column defined as type NUMERIC or DECIMAL?

          Comment


          • #6
            If you see the datatype coming in (488), according to this (https://www.ibm.com/support/pages/de...eld-procedures), it's numeric. The column is defined as numeric:

            Code:
            ********** Beginning of data *************************************
            A**************************************************************** 
            A*                                                              * 
            A*    CREATED BY : BPDEV01                                      * 
            A*    DATE       : 12/17/2019                                   * 
            A*    FILE       : ENCRYPTION TEST TABLE                        * 
            A*                                                              * 
            A**************************************************************** 
            A                                                                 
            A          R REC                                                  
            A            ECNUM         16S 0       COLHDG('NUMBER')           
            A                                      ALIAS(EC_NUMBER)           
            A            ECALPHA       32A         COLHDG('ALPHA')            
            A                                      ALIAS(EC_ALPHA)            
            ************* End of data ****************************************

            Comment


            • #7
              Sorry I got no ideas. You might get a bigger audience on midrange.com - this site has been off the air a lot lately and folks may have stopped checking in.

              Comment


              • #8
                The example says it got the SQL_TYPE_* values from QSYSINC/H member SQL. In that include file, it shows 504, not 488, for SQL_TYP_NUMERIC.

                Code:
                #define SQL_TYP_NUMERIC        504     /* NUMERIC -> DECIMAL (m,n)
                Have you tried defining it as 504 instead of 488 which is for "SQL_TYP_ZONED"? (I don't know what the difference might be ...)

                Comment


                • JonBoy
                  JonBoy commented
                  Editing a comment
                  That's weird because according to the V7 docs 504 is "Display Leading Sign (Note 2)" and Note 2 says that code is not applicable!

                  It would be a zoned type but with leading sign. Something that can't actually exist in a table - only printer and display files.

                  From the values in the header file he should be using SQL_TYP_ZONED with a value of 488. Since it is just the name of a constant I don't see how it could make a difference but using the name SQL_TYP_NUMERIC is certainly incorrect.

              • #9
                Thanks JonBoy, I'll try there also.

                Barbara, Where are you seeing that? Do you have a link?
                The incoming parameters are what is telling me it is 488, which according to the link I sent above, shows that as SQL_TYP_NUMERIC.

                Comment


                • #10
                  I'm seeing SQL_TYP_NUMERIC in QSYSINC/H member SQL.

                  From the doc, it does look like SQL_TYP_NUMERIC isn't relevant. (Sorry, I didn't notice the doc link before.)

                  But Jon is right - it's just the name of the named constant, which shouldn't matter. "504" would only be relevant if the system was passing you that value when it called your proc.

                  Comment


                  • #11
                    What value are you returning for the zoned field?

                    Comment

                    Working...
                    X