Hi all,
And special to Lynne Noll after reading your response I started on a whole
new path.
Instead of a temp file I create a cursor now and fetch the record into a
clob variable.
This is working now.
So for those who want to play, the source of a test program:
**free
ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no) debug(*yes);
dcl-s packageID packed(11:0) inz(2398066);
dcl-s xmldta sqltype(clob:1024000);
exec sql
declare c1 cursor for
select xmlserialize(xmlelement(name "SOAP:Envelope",
xmlnamespaces('
http://schemas.xmlsoap.org/soap/envelope/'
as "SOAP")
,
xmlelement(name
"SOAP:Body",
xmlelement(name
"ns0:DeliveredGoodsRequest",
xmlnamespaces('
http://VDW.AS400.Schemas.DeliveredGoodsRequest'
as
"ns0")
, xmlelement(name "Company",
trim(aqsjce)), xmlelement(name
"UserID", trim(a9sqce)),
xmlelement(name "Username",
trim(a9fhts)),
xmlagg(xml_scalar)))) as clob(1m)
including xmldeclaration)
as xml_doc from
(select aqsjce,
a9sqce,
a9fhts,
xmlelement(name "Lines",
xmlagg(xmlelement(name
"DeliveryGoodsLines",
xmlforest(trim(char(ARBFPR,
'.')) as "CostPrice",
trim(artnce) as
"ItemID", trim(artoce)
as "ConfigID",
trim(arzsce) as
"InventTransID",
trim(char(ardoqt, '.'))
as "Qty", trim(aqzrce)
as "SalesID"))))
as xml_scalar
from (select *
from anaqrep
join anarrep on aqvvne
= arvvne
where aqvvne = :packageID)
as a
join ama9rep on aqvvne = a9vvne
where a9elsv < 90
group by aqsjce,
a9sqce,
a9fhts)
as b
group by aqsjce,
a9sqce,
a9fhts
for fetch only;
exec sql
open c1;
exec sql
fetch c1 into :xmldta;
return;
Results in:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP:Envelope xmlns:SOAP="
http://schemas.xmlsoap.org/soap/envelope/";>
<SOAP:Body>
<ns0:DeliveredGoodsRequest
xmlns:ns0="
http://VDW.AS400.Schemas.DeliveredGoodsRequest";>
<Company>FMX</Company>
<UserID>EVRK</UserID>
<Username>VEREECKE ERIC</Username>
<Lines>
<DeliveryGoodsLines>
<CostPrice>.0000</CostPrice>
<ItemID>8-921-10103</ItemID>
<ConfigID>-</ConfigID>
<InventTransID>LOT0019783</InventTransID>
<Qty>20.00</Qty>
<SalesID>SO0001886_cmfx</SalesID>
</DeliveryGoodsLines>
</Lines>
</ns0:DeliveredGoodsRequest>
</SOAP:Body>
</SOAP:Envelope>
So got the result what we wanted.
Again thanks to all.
Danny
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Lynne
Noll
Sent: donderdag 22 februari 2018 12:05
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxx>
Subject: Re: Converting QM Query to rpg embedded SQL
I've seen the QMQRY utilities. They sparked my imagination. Giovanni
Perotti added the substitution marker idea as part of HTML scripts-- I saw
them before QMQRY. So many things need a few substitutions in a standard
script!
However, the main purpose of the QMQRY utilities was getting around the lack
of purchased SQL tools like embedded SQL in RPG.
I suggest building a simple RPG, COBOL or C utility to run the passed in
select statement. It is very simple as long as the SQL is composed to
return one field, which can be fields concatenated together. I have a
couple--one a universal lookup window, and one that just returns a field at
a time to a caller. Beyond letting you get data back to the caller without
an output table, you can have substitution markers in your own template SQL
string.
If you are going to an output table, you can use a CREATE TABLE AS command
to run the select from RUNSQL. No substitution markers, though; you need to
create a separate command to handle them.
You can set up for multiple variable columns back in your utility as well,
but that is more work. More payback, but more work, and needs a budget.
What I've described is super easy, and can be done as part of any project it
eases.
Sent from Outlook<
http://aka.ms/weboutlook>
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> on behalf of Vernon Hamberg
<vhamberg@xxxxxxxxxxxxxxx>
Sent: Wednesday, February 21, 2018 8:37 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Converting QM Query to rpg embedded SQL
To expand a little on Lynne's explanation, it is possible to have a QMQRY
string like this - &Q1&Q2&Q3&Q4&Q5 - this would allow an SQL statement up to
275 characters long, since each substitution variable can be at most 55
long. This is the basis of almost all the utilities people have made (Buck
Calabro had one I made a variation of, and
others) that can run any SQL statement, including SELECTs (IBM's RUNSQL does
not directly run SELECT statements). The statement is broken up into
55-character variables and passed in the SETVAR parameter of STRQMQRY, which
combines them into the statement, as Lynne describes.
I have a presentation and lab I present at COMMON about QM - usually the lab
is all we put on the schedule these days.
Regards
Vern
--
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
