SQL Error *N (38501) - empty LibraryList

[vuxat]

Discussed in https://github.com/orgs/halcyon-tech/discussions/1078

^{Originally posted by vuxat February 20, 2023}
Hi,

when I enable SQL support it throws error *N (38501) and it cannot load the library list.
I searched for the problem and found, that our IBM i Version is not up to date ...

Please add this to the Documentation for "Enable SQL":
If the IBM i Version is prior to 7.4 April 2021 (PTF Group SF99704 Level <= 11) then the library list cannot be retrieved, as the SPLIT command is not yet implemented.
The same goes for 7.3 prior to April 2021.
For detailed information please review the following RPG Cafe Entry:
https://www.ibm.com/support/pages/rpg-cafe-new-built-ins-lower-upper-and-split

src/api/IBMiContent.ts
SYSTOOLS.SPLIT(...)

If you feel bold enough and know a way to find out if SYSTOOLS.SPLIT is available or not
you could try to add a stand-in function in the temporary library (default = ILEDITOR) to mitigate this:
It looks a bit funky but it works. It is a variant from the following link I changed a bit on the last part.
For sure someone can come up with nicer code.
https://stackoverflow.com/questions/1305804/split-a-varchar-in-db2-to-retrieve-a-value-inside/10175295

CREATE OR REPLACE FUNCTION ILEDITOR.SPLIT(INPUT_LIST VARCHAR(3200),
	       DELIMITER VARCHAR(200))
RETURNS TABLE(ELEMENT VARCHAR(3200))
SPECIFIC SPLIT
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
F1: BEGIN
return
	with source(str, del) as
	(select INPUT_LIST, DELIMITER from sysibm.sysdummy1),
	target(str, del) as
	(select source.str, source.del from source
	where length(source.str) > 0
	union all
	select 
	(case when (instr(target.str, target.del) > 0) 
	then substr(target.str, 
	instr(target.str, target.del)+1, 
	length(target.str)-instr(target.str, target.del)) else null end),
	(case when (instr(target.str, target.del) > 0) 
	then target.del else null end)
	from target
	where length(target.str) > 0
	)
	select (CASE WHEN instr(str, target.del) > 0 
        THEN substring(str, 1, instr(str, target.del) -1) ELSE str END) str 
        from target where str is not null;
END

SELECT * FROM table(ILEDITOR.SPLIT('CURLIB,QTEMP,QGPL', ','))
more in line with the real split:
SELECT * FROM table(ILEDITOR.SPLIT(INPUT_LIST => 'CURLIB,QTEMP,QGPL', DELIMITER => ','))
(DROP FUNCTION ILEDITOR.SPLIT)

Thank you.

/vscode-ibmi/blob/master/src/api/IBMiContent.ts

getLibraryList

if (this.config.enableSQL) {
      const statement = `
        select os.OBJNAME as ODOBNM
             , coalesce(os.OBJTEXT, '') as ODOBTX
             , os.OBJATTRIBUTE as ODOBAT
          from table(SYSTOOLS.SPLIT( INPUT_LIST => '${libraries.toString()}', DELIMITER => ',' ) ) libs
             , table( QSYS2.OBJECT_STATISTICS( OBJECT_SCHEMA => 'QSYS', OBJTYPELIST => '*LIB', OBJECT_NAME => libs.ELEMENT ) ) os
      `;
      results = await this.runSQL(statement);
...

Best regards,
Stefan

[chrjorgensen]

@vuxat The SYSTOOLS.SPLIT table function became available in April 2019 - almost four years ago. It is not unreasonable to expect users to have this function on their system. I surely hope the admin has installed DB2 PTF group since April 2019, or they may face more serious problems than this one.

You can always create the function yourself - you have the source, I see. And there is no danger - your created function will just be overridden by the one from IBM if the admin installs the DB2 for i PTF group.

[chrjorgensen]

@vuxat Please also see my answer in discussion #1078 about your mixing of the RPG and SQL SPLIT functions...

[worksofliam]

I will close this for now. Perhaps a notice in the documentation about the use of the SQL SPLIT function would be nice, but not a requirement. @chrjorgensen is right. We recommend updating.