ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Reading VARCHAR fields into CLP/CLLE variable

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

  • Reading VARCHAR fields into CLP/CLLE variable

    I am facing trouble with CLLE program while reading a PF which contains VARCHAR fields.

    Below are the steps which were executed and observed.

    1. A table EMPDTL is created (using 'Create Table') through transact SQL. The table contains the following fields -
    a. EMPID - Numeric(6, 0)
    b. FIRSTNM - Varchar (20)
    c. LASTNM - Varchar (10)

    2. Data is entered into EMPDTL.
    EMPID - 1
    FIRSTNM - Sam
    LASTNM - Jones

    3. When the table EMPDTL is viewed through Transact SQL, data appears perfect.

    EMPID FIRSTNM LASTNM
    1 Sam Jones

    However, when the table is viewed using DSPPFM, 2 bytes of unreadable (like packed fields appears in DSPPFM view) characters shows in between the columns.

    000001**Sam **Jones

    Also, when I check table file properties using DSPFD, it shows record length = 40 instead of 36 (6 + 20 + 10).

    4. As I try to read (RCVF) field FIRSTNM from a CLLE program, those unreadable characters appears as a prefix of the field value (&FIRSTNM = '**Sam').

    I want to get rid of these unreadable characters while reading the value into a CL variable without using substring functions. Please advice.

  • #2
    Re: Reading VARCHAR fields into CLP/CLLE variable

    Columns with varying length are always preceeded with 2 bytes containing the occupied length of the data within the varying length field.
    The length is storead as integer value.
    So defining a data structure for those varying length fields with a sub field defined as INT(2) and a second one for the data, will help consuming those varying length values.
    Birgitta

    Comment


    • #3
      Re: Reading VARCHAR fields into CLP/CLLE variable

      Thanks a ton Birgitta for your advice.

      I am just curious if we can suppress the variable length details by any other means while reading the value in CLLE!!

      Some weird ideas could be, if at all possible, by controlling some parameters during compilation? Or use some command in CLLE to treat VARCHAR fields as CHAR?

      I will explain why I am thinking of some kind of alternative.

      Intially, when I coded this piece, it was giving the same trouble. However, I was making some other functional changes and recompling the code over and over. All of a sudden this logic started working (with no changes done in this part) and started receiving correct value excluding length part. However, I am so dumb, I could not understand how it has started working!!!

      But then I was doing further functional changes and complilation, and again the piece started receiving value with leangth in prefix.

      So I am thinking, could there be any other way?

      Comment


      • #4
        Re: Reading VARCHAR fields into CLP/CLLE variable

        What level of IBM i are you on?

        I'm on 7.1 - you MAY or MAY not be able to do this with 6.1... definitely can't with 5.4 or earlier.....

        On the DCLF statement add ALWVARLEN(*YES) and used defined variables:

        Code:
                     DCLF       FILE(RAMLIB/TESTVAR) ALWVARLEN(*YES)
                     DCL        VAR(&FLEN) TYPE(*INT) STG(*DEFINED) LEN(2) DEFVAR(&FIRSTNM)
                     DCL        VAR(&FIRSTNAME) TYPE(*CHAR) STG(*DEFINED) LEN(20) DEFVAR(&FIRSTNM 3)
        &FLEN will have the length of the actual data in &FIRSTNAME. I do not know you can bank on the field after that length being blank - they were on mine but your mileage may vary.... I'd verify it on your system before assuming it would. Otherwise you could simply add:

        Code:
                     CHGVAR     VAR(&FIRSTNAME) VALUE(%SST(&FIRSTNAME 1 &FLEN))
        This should ensure that you have the data you want.

        Comment


        • #5
          Re: Reading VARCHAR fields into CLP/CLLE variable

          I take that back - I compiled it back to V5R4M0 and it compiled and ran... I thought defined variables were newer than that...

          Comment


          • #6
            Re: Reading VARCHAR fields into CLP/CLLE variable

            First release that supported defined on variables was V5R4. Prior to that you could do this using the %BIN BIF

            Comment

            Working...
            X