Swap our `DSPFD` for `object_statistics`

[worksofliam]

As we found in this comment, we are having issues with DSPFD when it comes to multiple encodings and a bad QCCSID. This is solved by using object_statistics.

Right now, this logic only has to be changed here: https://github.com/codefori/vscode-ibmi/blob/8239670ff1271fc1b3db68343f8ee680634c0d32/src/api/IBMiContent.ts#L505

select 
  objlib as LIBRARY,
  objname as NAME,
  objtype as TYPE,
  objattribute as ATTRIBUTE,
  objtext as TEXT,
  0 as NB_MBR,
  0 as IS_SOURCE,
  0 as SOURCE_LENGTH,
  65535 as CCSID,
  objsize as SIZE,
  objcreated as CREATED_TS,
  objowner as OWNER
from table(qsys2.object_statistics('AURORAKOR', '*ALL')) limit 1

[worksofliam]

We can also replace DSPFFD with a select from SYSTABLES

[chrjorgensen]

We can also replace DSPFFD with a select from SYSTABLES

I think you mean select from SYSCOLUMNS?

[sebjulliand]

I think it's actually SYSTABLES. If I'm not mistaken, it holds the FILE_TYPE = 'S' information needed to know which *FILE is a SPF.

[chrjorgensen]

@sebjulliand SYSTABLES correspond to DSPFD and SYSCOLUMNS correspond to DSPFFD - so the answer depends on whether we're talking about DSPFD or DSPFFD... 😉 😄

[chrjorgensen]

I just remembered that SYSCOLUMNS has been deprecated in favor of SYSCOLUMNS2, which should be faster and return more informartion: https://www.ibm.com/docs/en/i/7.3?topic=views-syscolumns2

[worksofliam]

@sebjulliand @chrjorgensen

We need:

• object_statistics for all objects (prev. DSPOBJD)
• SYSTABLES for the special *SRCPF type (prev. DSPFD)

[sebjulliand]

@sebjulliand SYSTABLES correspond to DSPFD and SYSCOLUMNS correspond to DSPFFD - so the answer depends on whether we're talking about DSPFD or DSPFFD... 😉 😄

Ugh, my bad, DSPFD it is!

[worksofliam]

-- only source
select 
  t.SYSTEM_TABLE_NAME as name,
  '*FILE' as type,
  '' as attribute,
  t.table_text as text,
  1 as is_source,
  -1 as nb_mbr, -- no idea how to get this
  c.character_maximum_length as SOURCE_LENGTH,
  c.ccsid
from qsys2.systables as t
right join qsys2.syscolumns2 as c on t.system_table_schema = c.system_table_schema and t.SYSTEM_TABLE_NAME = c.SYSTEM_TABLE_NAME and c.column_name = 'SRCDTA'
where t.table_schema = 'LIAMA' and t.file_type = 'S';

-- all objects
select 
  o.objlib as LIBRARY,
  o.objname as NAME,
  o.objtype as TYPE,
  o.objattribute as ATTRIBUTE,
  o.objtext as TEXT,
  0 as NB_MBR,
  0 as IS_SOURCE,
  0 as SOURCE_LENGTH,
  65535 as CCSID,
  o.objsize as SIZE,
  o.objcreated as CREATED_TS,
  o.objowner as OWNER,
  o.objdefiner as CREATED_BY,
  o.objsize as SIZE_IN_UNITS,
  0 as BYTES_PER_UNIT
from table(qsys2.object_statistics('LIAMA', '*ALL')) as o;

-- get all objects and figure out which files are source files
select 
  o.objlib as LIBRARY,
  o.objname as NAME,
  o.objtype as TYPE,
  o.objattribute as ATTRIBUTE,
  o.objtext as TEXT,
  -1 as NB_MBR,
  case when c.character_maximum_length is null then 0 else 1 end as IS_SOURCE,
  c.character_maximum_length as SOURCE_LENGTH,
  c.ccsid as CCSID,
  o.objsize as SIZE,
  o.objcreated as CREATED_TS,
  o.objowner as OWNER,
  o.objdefiner as CREATED_BY,
  o.objsize as SIZE_IN_UNITS,
  0 as BYTES_PER_UNIT
from table(qsys2.object_statistics('LIAMA', '*ALL')) as o
left outer join qsys2.syscolumns2 as c on o.objlib = c.system_table_schema and o.objname = c.SYSTEM_TABLE_NAME and c.column_name = 'SRCDTA'

[edmundreinhardt]

@worksofliam @sebjulliand If we were to go to common code, the usage and name mapping can be found below. BUT - this is only for database files and does not work for DSPF/PRTF files Based on that limitation, I am disposed not to attempt a replacement at this time.

cmd	old name	new service	USE	new name
FD	WHLIB	SF.SYSTEM_TABLE_SCHEMA	MI-	Library of file
FFD	WHNAME	SF.FORMAT_NAME	MII	record name
FD	APKEYN	SF.NUMBER_KEY_FIELDS	M--	number of keys
	WHPSUD		M--	DDS Field data type
	WHFLDT	SC.DATA_TYPE (map)	MCI	Field type
	WHFLDI		M--	Internal Field Name
	WHCSID	SC.CCSID	M--	CCSID
	WHNULL	SC.IS_NULLABLE	M--	Is NULLable
	WHALIS	SC.COLUMN_NAME	MCR	ALIAS field name
	WHFIOB	SC.HIDDEN	M--	Field usage
.	WHFLDP	SC.NUMERIC_SCALE	MCI	Decimal Positions
	WHIBO	SC.ORDINAL_POSITION (map)	M--	input buffer position
.	WHFOBO	SC.ORDINAL_POSITION (map)	M--	output buffer position
.	WHFMT		M--	Date time format
	WHVARL	SC.DATA_TYPE (map)	M-R	Variable length field boolean)
.	WHFLDB	SC.STORAGE	MCI	byte length
	WHFLDD	SC.NUMERIC_PRECISION	MCI	digit count
.	WHFLDE	SC.SYSTEM_COLUMN_NAME	-CR	external field name
	WHFILE	SF.SYSTEM_TABLE_NAME	-I-	System name
	WHFTXT	SC.COLUMN_TEXT	-II	Column text

SF = QSYS2.SYSFILES SC = QSYS2.SYSCOLUMNS

M - used by Merlin RPGLE LSP C - used by CL vscode-clle LSP R - used by RPG vscode-rpgle LSP I - used in interface of CL or RPG extension but not in the logic