Well. I was wrong. Display_journal_entry_info can return values in their native format, meaning, if I define the cursor datastructure for packed(11:3) for example, and the value was a packed value, it will come in correctly. I noticed SQL complaining about data conversions during my testing. Wow, that's pretty powerful if I could figure out how to dynamically define a datastructure. I've pasted an example of what I have working so far for future use.
dcl-s RS1 sqltype(result_set_locator);
dcl-ds DS1 qualified;
Dta1 varchar(500);
Dta2 varchar(500);
Dta3 varchar(500);
Dta4 packed(11:3);
Dta5 packed(11:3);
Dta6 packed(11:3);
Dta7 varchar(500);
Dta8 varchar(500);
Dta9 varchar(500);
Dta10 varchar(500);
Dta11 varchar(500);
Dta12 varchar(500);
Dta13 varchar(500);
Dta14 varchar(500);
Dta15 varchar(500);
Dta16 varchar(500);
Dta17 varchar(500);
Dta18 varchar(500);
Dta19 varchar(500);
end-ds;
exec sql call qsys2.display_journal_entry_info(
2,
'#MXJRN',
'BPCS_ALL',
21119599,
'#MXJRN',
'ALLRCV7601',
'UB');
exec sql associate result set locators(:RS1) with procedure
qsys2.display_journal_entry_info;
exec sql allocate c1 cursor
for result set :RS1;
exec sql fetch C1 into :DS1;
exec sql close RS1;
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Monday, May 6, 2019 11:42 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL
Rob,
A couple months ago I dismissed this function, because the results were difficult to process, but I couldn't remember why. I recreated a consumer of this procedure in RPG and I remember now, but, I may not dismiss it this time. The display_journal_entry_info function returns one row in one result set with multiple columns (1 for each file entry column). Every entry is converted to varchar, so, even the numerics are formatted as character values. That makes sense, because Navigator is only returning a representation of that value, not the real data typed columns. That was why I dismissed it...because I wanted the real data typed columns. Now I'm thinking, however, that I could extract the data type from the SYSCOLUMNS view, and then ask SQL to convert '1235.33' to DECIMAL('1235.33',11,2). I'm not sure what will happen, especially with things like dates and timestamp stuff, but, your suggestion may have me down a road.
My current hope/plan is to create a function that gathers all the results, but then returns the column entries one value at a time either sequentially or by sequence # requests, with the dynamic journal view being written to refer to this function multiple times to get values for each column, and within the view, converting the values to their intended data types from the varchar values.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Rob Berendt
Sent: Wednesday, May 1, 2019 1:44 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL
I think what you are looking for is a function, or something besides a stored procedure, which will return the information that the following does. Something you can join directly with DISPLAY_JOURNAL
call qsys2.display_journal_entry_info
(2, --not sure what 2 means
'journal_library',
'journal name',
Sequence_number,
'receiver library',
'receiver name',
'journal code (aka PT, DL, etc)';
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Darren Strong
Sent: Wednesday, May 1, 2019 1:34 PM
To: midrange-l General Questions (midrange-l@xxxxxxxxxxxxxxxxxx) <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: DISPLAY_JOURNAL and ENTRY_DATA parsing using pure SQL
I developed a hybrid RPG and SQL program to query the DISPLAY_JOURNAL SQL UDTF from IBM, which if you use it, you know that the ENTRY_DATA column comes in as an incomprehensible BLOB. I've used RPG to overlay this BLOB onto a data structure and written this out to a file with the journalled file structure pre-built to be able to view this data. What I'm wondering is, if there is an SQL way to dump this data onto a file and/or structure so that I might be able to build this process as a view, rather than an outfile to further reduce unnecessary file I/O. If it were a view, then I imagine that the program would just create the view, in say QTEMP, and then I could query the fields as desired before the data was actually written anywhere.
The column I'm trying to parse is currently coded as follows:
cast(entry_data as char(5000) for bit data)
--
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
--
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
--
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
