So I think I have found a fix.
What I am doing is writing a tool that can parse ENTRY_DATA from QSYS2.JOURNAL_INFO.
What is returned there is a byte array that corresponds to the field lengths defined in DSPFFD.
So the process is this:
1. Call QSYS2.JOURNAL_INFO to get the journal entries
2. Call this, for the file you are viewing. This will convert it to the proper lengths that DSPFFD shows (have not tested for all data types yet)
SELECT CASE WHEN DATA_TYPE='DATE' THEN 10
WHEN DATA_TYPE='TIME' THEN 8
WHEN DATA_TYPE='TIMESTMP' THEN 26
WHEN DATA_TYPE='VARCHAR' THEN LENGTH+2
ELSE LENGTH END FROM qsys2.SYSCOLUMNS2 WHERE TABLE_SCHEMA='xx' AND TABLE_NAME='xx'
This will adjust the SQL reported storage lengths to what is actually occupied in ENTRY_DATA.
3. Iterate through all columns in orderinal position order, and you can convert all the data into the proper columns instead of reading that crazy data.
.NET luckily has EBCDIC convertors and what not built in, so the CHAR columns are easy to convert. There are also zoned numeric functions as well.
Wish ACS did this for you automatically. It kinda does with QSYS2.DISPLAY_JOURNAL_INFO, but you have to call it for each record, and it's slow. I want a dump of all easily viewable data for a whole series of records in one shot, which is what my tool will do.
-----Original Message-----
From: Rob Berendt <rob@xxxxxxxxx>
Sent: Thursday, October 10, 2019 12:52 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Question on buffer lengths vs SQL reported lengths
During the transition when they first came out with Date type columns I suspect they did an "interesting" implementation so that the version of RPG available at the time could read it. Even though it only takes four bytes of storage DSPPFM, DSPFFD etc have been tricked into thinking it takes 10.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Carel
Sent: Thursday, October 10, 2019 1:22 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Question on buffer lengths vs SQL reported lengths
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
The 4 with SQL is storage size in bytes.
The 10 in DSPFFD is display size in bytes.
Kind regards,
Carel Teijgeler
Op 10-10-2019 om 19:18 schreef Matt Olson via MIDRANGE-L:
Anyone have any ideas why if I do a "select * from qsys2.syscolumns where table_schema='yourschema' and table_name='tablename'" and you look at the STORAGE or LENGTH columns for a DATE field it says "4"
But when you do a DSPFFD against the same file you will see the field length is 10.
I've noticed a discprency on TIME fields as well. Where the qsys2.syscolumns STORAGE and LENGTH columns say "3", but DSPFFD shows 8
--
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@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
