Re: SQL column size in CLLE

Rob,

While I agree with your idea of a stored procedure, I have to be mindful of the customer's developers. In the end they need to manage this.

The CLLE (that in and of itself is pushing them unfortunately) is quite simple; read the file and delete the older receivers. Same reason I did not use VARCHAR in CLLE

I put the SQL into a source file member, then used RUNSQLSMT. It’ll take some convincing to allow SQL as opposed to using commands to list it out to outfiles and use those. That’s way too much like work.




Jim Oberholtzer
Agile Technology Architects

On May 28, 2025, at 2:12 PM, Rob Berendt <robertowenberendt@xxxxxxxxx> wrote:

Multiple ways to skin this cat.
One, learn how to use variable length character columns in CLLE. Easier
said than done.
Two, 'cast' the variable length columns to character.
SELECT *char*(JOURNAL_RECEIVER_LIBRARY, *10*) as RCVLIB
,*char*(JOURNAL_RECEIVER_NAME, *10*) as RCVNAM
Three, stop using CL and use a stored procedure to process stuff like this
(create, followed by execution)
CREATE PROCEDURE Change_IFS_Owner
(IN CURRENT_OWNER *VARCHAR*(*10*),
IN NEW_OWNER *VARCHAR*(*10*),
IN START_PATH *VARCHAR*(*50*) )
LANGUAGE SQL MODIFIES SQL DATA
SET OPTION DATFMT = *ISO
P1: BEGIN
DECLARE WORK_PATH_NAME *VARCHAR*(*500*);
DECLARE COMMAND *CHAR*(*500*);
*-- Ensure this is large enough.* DECLARE END_TABLE *INT* DEFAULT
*0*;
DECLARE C1 CURSOR FOR
select path_name

*-- 'CHGOWN OBJ(''' concat path_name concat ''') NEWOWN(' concat
new_owner concat ')'*

*--, QCMDEXC('CHGOWN OBJ(''' concat path_name concat ''') NEWOWN('
concat new_owner concat ')')*
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME *=>*
START_PATH,
SUBTREE_DIRECTORIES *=>*
'YES'))
WHERE object_owner=CURRENT_OWNER
and path_name not like '%' *concat* x'7D' *concat* '%';

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = *1*;

*-- DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION*
*-- SET DEPT_SALARY = NULL;* OPEN C1;
FETCH C1 INTO WORK_PATH_NAME;
WHILE END_TABLE = *0* DO
SET COMMAND = 'CHGOWN OBJ(''' *concat* work_path_name *concat*
''') NEWOWN(' *concat* new_owner *concat* ')';
CALL QSYS2.QCMDEXC(COMMAND);
FETCH C1 INTO WORK_PATH_NAME;
END WHILE;
CLOSE C1;
END P1
;
CALL ROB.Change_IFS_Owner('JREINKIN', 'TRACIL', '/Banking');

On Wed, May 28, 2025 at 2:19 PM Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
wrote:

I am running this SQL in a CLLE program:

Runsql ('CREATE TABLE QTEMP/LISTJRN AS (
SELECT JOURNAL_RECEIVER_LIBRARY as RCVLIB
,JOURNAL_RECEIVER_NAME as RCVNAM
,DETACH_TIMESTAMP as TIMESTMP
FROM QSYS2.JOURNAL_RECEIVER_INFO
WHERE DETACH_TIMESTAMP IS NOT NULL
AND SUBSTR(JOURNAL_RECEIVER_LIBRARY, 1,1) <> ''Q''
AND DETACH_TIMESTAMP < CURRENT_TIMESTAMP - 14 DAYS
ORDER BY DETACH_TIMESTAMP)
WITH DATA')
COMMIT(*NONE)

In SQL runscrips, the SQL works great, until I go to use the columns
RCVLIB and RCVNAM. The SQL produces a file with varchar output which the
CLLE does not like.

How to I put a size on the two columns (ideally Char 10) so the CLLE
compiler won’t complain?
--
Jim Oberholtzer
Agile Technology Architects




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.