Wednesday, April 28, 2021

Handling variable length variables in CL

cl pogram coping with varchar data

More and more of us are using SQL to modernize our databases from DDS files to SQL tables, view, and indexes. When you rework your files into tables you quickly realize the benefit of converting that 50 alphanumeric field in the DDS file to a variable length character, VARCHAR, column in the SQL equivalent. This conversion saves disk space as the data in that column is only stored as the length it is, not the whole 50 characters.

RPG and SQL easily copes with this type of column. Alas, CL does not. If I try to declare file, DCLF, that contains variable length columns the program will not compile without using a special option in DCLF.

Before I start showing CL code I need to start with a SQL table, that contains a VARCHAR column:

01  CREATE TABLE QTEMP.MY_TEST_TABLE
02     FOR SYSTEM NAME "TESTTABLE"
03  (FIRST_COLUMN FOR COLUMN "FIRST" VARCHAR(10) ) ;

04  INSERT INTO QTEMP.MY_TEST_TABLE
05    VALUES('1'),('222222'),(NULL) ;

I put both of these SQL statements into the same source member, and then ran them using the Run SQL Statement command, RUNSQLSTM.

Lines 1 – 3: My statement for creating the table I will be using here.

Line 2: I have given the table a long SQL name on line 1, here I am giving it the system name that it will be known by in IBM i commands, such as WRKOBJPDM.

Line 3: I only need one column in this test table. I have defined it with both a long and short names, and as VARCHAR.

Lines 4 and 5: I am inserting my test data into the table that was created by the previous statement. Here I insert three rows into the table, rather than use three separate Insert statements.

The CL program is very simple. As it just reads the table:

01  PGM

02  DCL VAR(&WORK10) TYPE(*CHAR) LEN(10)
03  DCL VAR(&LOOP) TYPE(*LGL) VALUE('1')

04  DCLF FILE(QTEMP/TESTTABLE)

05  DOWHILE COND(&LOOP)
06    RCVF
07    MONMSG MSGID(CPF0864) EXEC(LEAVE)

08    CHGVAR VAR(&WORK10) VALUE(&FIRST)
09  ENDDO

10  ENDPGM

Lines 2 and 3: Variables defined.

Line 4: The table is defined as a file.

Lines 5 – 9: I am using a DOWHILE so that all records (rows) in the file (table) are received. Then the loop is exited, line 7.

Line 8: I have added this line so I can see what the variable from the file (table), &FIRST, contains, and when I move that value to another variable that is fixed width, &WORK10.

The above program will not compile.

When I look in the compile listing I see the following inserted into the code:

500- DCLF FILE(QTEMP/TESTTABLE)
QUALIFIED FILE NAME - QTEMP/TESTTABLE
RECORD FORMAT NAME - TESTTABLE
CL VARIABLE   TYPE    LENGTH   PRECISION   TEXT
 &FIRST       *CHAR       12

CPI0300 10  Character CL variable declared for variable length field &FIRST.

I guess that counts as a warning. Which I will ignore.

The compile error is shown further down the spool file:

CPD085A 40  Variable length field not allowed for variable &FIRST.

Fortunately the solution is in the DCLF command. There is a variable within the command called ALWVARLEN, as its name suggests it allows for the file (table) to have variable length variables (columns).

In my example the only change I made was to line 4. I replaced what I had before with:

04  DCLF FILE(QTEMP/TESTTABLE) ALWVARLEN(*YES) +
           ALWNULL(*YES)

"Aha!", I hear you all say, "but you did more than allow for variable length variables". I did. As the file (table) contains a row that contains null I need to use the ALWNULL too.

With these additions to the DCLF command the program compiled. I still found the warning in the compile listing, but I can ignore that.

What about proof that this works?

I started debug and put a breakpoint on line 9. Now I can call the program.

The first time the program "breaks" in debug I check the value of &FIRST the variable (column) from the file (table).

> EV &FIRST
&FIRST = '??1         '

What are those two characters at the front of the variable? They are the part that "tells" anything that uses this column how long it is.

I do not want this in &WORK10, which is why I used the substring built in function, %SST. When I check the value of &WORK10 I see:

> EV &WORK10
&WORK10 = '1         '

Onto the second row, I received:

&FIRST = '??222222    '

&WORK10 = '222222    '

And finally the row that is null:

&FIRST = '??          '

&WORK10 = '          '

By using the ALWNULL in the DCLF command the null has been converted to blank.

Yes it is that easy to allow a CL program to handle variable length data, a file (table or view) at a time.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

6 comments:

  1. You mention the use of %SST but I don't see it anywhere in your example ?

    ReplyDelete
  2. Last year I have created a RFE for implementing variable length fields i CL.
    Please vote for it.

    http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=134919

    ReplyDelete
  3. My opinion, CL should handle VARCHAR in a native way (like RPG).

    ReplyDelete
  4. Reynaldo Dandreb MedillaAugust 8, 2021 at 10:13 AM

    good share Simon, thanks

    ReplyDelete
  5. Referred for ALWVARLEN(*YES) & got the answer. Thanks.

    ReplyDelete
  6. Good stuff. Thanks.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.