Re: easier SQL statements in CL

[CRPence <CRPbottle@xxxxxxxxx>]

  Consider instead, something more like the following:

<code>

    Dcl &SQLstm *CHAR 2000 VALUE(/* +
    ....+....1....+....2....+....3....+....4....+....5 */+
    '+
    INSERT INTO LGFILE                       /*    1*/-
     ( LG_CLIENT, LG_SEQ, LG_DATE            /*   51*/-
     , LG_DIR,    LG_ID,  LG_MODE,  LG_NRECS /*  101*/-
     )                                       /*  151*/-
    VALUES                                   /*  201*/-
     ("_______"    /* &P_CLIENT   254/07 */  /*  251*/-
     ,___          /* &P_SEQ      303/03 */  /*  301*/-
     ,________     /* &CUR_Y4MMDD 353/08 */  /*  351*/-
     ,"___"        /* &P_DIR      404/03 */  /*  401*/-
     ,"_"          /* &MODE       454/01 */  /*  451*/-
     ,___          /* &P_NRECS    503/03 */  /*  501*/-
    ' /* +
    ....+....1....+....2....+....3....+....4....+....5 */+
    ) /* Note: 50 is nice for both DSPPGMVAR & DMPCLPGM */

    Dcl &P_CLIENT   *CHAR 07 STG(*DEFINED) DEFVAR(&SQLstm 254)
    Dcl &P_SEQ      *CHAR 03 STG(*DEFINED) DEFVAR(&SQLstm 303)
    Dcl &CUR_Y4MMDD *CHAR 08 STG(*DEFINED) DEFVAR(&SQLstm 353)
    Dcl &P_DIR      *CHAR 03 STG(*DEFINED) DEFVAR(&SQLstm 404)
    Dcl &MODE       *CHAR 01 STG(*DEFINED) DEFVAR(&SQLstm 454)
    Dcl &P_NRECS    *CHAR 03 STG(*DEFINED) DEFVAR(&SQLstm 503)

    ...
    chgvar &P_CLIENT 'RQ1234' /* blank padded to 7-bytes */
    chgvar &P_SEQ    8        /* pre-pad zeroes so '008' */
    chgvar &CUR_Y4MMDD ... etc.

</code>

Regards, Chuck

On 14 May 2012 11:03, rob@xxxxxxxxx wrote:
> I was thinking it was easier that way for simple data structures.
>
> My question is how would sql handle this for variable length fields?
> For example
> DCL   VAR(MYDS) TYPE(*CHAR) LEN(100)
>   DCL VAR(&STMT1) TYPE(*CHAR) STG(*DEFINED) LEN(41) DEFVAR(&MYDS 1)
>   DCL VAR(&STMT2) TYPE(*CHAR) STG(*DEFINED) LEN(1) DEFVAR(&MYDS 41)
>   DCL VAR(&STMT3) TYPE(*CHAR) STG(*DEFINED) LEN(8) DEFVAR(&MYDS 42)
>   DCL VAR(&STMT4) TYPE(*CHAR) STG(*DEFINED) LEN(1) DEFVAR(&MYDS 50)
> DCL&APOS        *CHAR   1 /* Apostrophe    */
>
> CHGVAR     VAR(&APOS) VALUE(X'7D')
> chgvar &stmt1 ('select cola, colb from myfile where colc=')
> chgvar &stmt2 &apos
> chgvar &stmt3 &myvar
> chgvar &stmt4 &apos
>
> It's not going to have a problem since we didn't truncate out the
> spaces between &stmt2 and &stmt4 if &stmt3 is equal to XYZ?
> Is the above really that much better?