Re: YAJLDTAGEN with SQL into DS -- RPG400-L

Hi Greg,

after fiddling around for 10 minutes or so, I came up with this SQL statement:

select json_object(
'Customers' : json_arrayagg(
json_object(
'addr_line1' : addr_line1,
'addr_line2' : addr_line2,
'city' : city,
'state' : state,
'zip' : zip,
'phone' : phone,
'email' : email
)
)
)
from mytable;

Which results in the following JSON string (which I formatted to be better readable):

{
"Customers":[
{
"addr_line1":"64a N Williams St",
"addr_line2":"",
"city":"Crystal Lake",
"state":"IL",
"zip":"60014",
"phone":"7792204179",
"email":"marvinstoys@xxxxxxxxx"
},
{
"addr_line1":"267 Main St",
"addr_line2":"",
"city":"Huntington",
"state":"NY",
"zip":"11743",
"phone":"6315498743",
"email":"lsdolls@xxxxxxx"
}]
}

I hope I understood your problem correctly.

Of course I understand, that it is always a "problem" to make big changes to existing code - but IMHO modernization starts exactly there.

HTH
Daniel

Am 02.02.2022 um 22:06 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>:

Chris,

Thank you for your response. I did not realize that I could SELECT "INTO" a qualified data structure. But it worked perfectly.

I set up my SQL Cursor in a procedure called OpenCursor() then:

OpenCursor();

Dow FetchCursor(AllCustomers.Customers);
WriteJson(Stmf);
jsonCount += 1;
enddo;

CloseCursor();

...in WriteJson()
DATA-GEN AllCustomers
%DATA(inStmf: 'doc=file output=clear countprefix=num_')
%GEN('YAJLDTAGEN');

//------------------------------------------------------------------------------
// Fetch the Next Record
//
//------------------------------------------------------------------------------

dcl-proc FetchCursor;

dcl-pi FetchCursor ind;
rec likeds(AllCustomers.Customers) dim(50);
end-pi;

sqlRows = 0;

Exec Sql
Fetch Next From sqlcsr for :maxrows rows
Into :rec ;

if SqlStt <> sqlOK;
return *off;
endif;

Exec Sql Get Diagnostics :sqlRows = ROW_COUNT;

AllCustomers.num_Customers = sqlRows;

return *on;

end-proc;

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Hiebert, Chris
Sent: Wednesday, February 2, 2022 1:19 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: YAJLDTAGEN with SQL into DS

Try:

dcl-ds AllCustomers qualified;
num_Customers uns(5);

dcl-ds Customers qualified dim(50) inz;

addr_line1 varchar(40) inz('');

addr_line2 varchar(40) inz('');

city varchar(40) inz('');

state varchar(3) inz('');

zip varchar(10) inz('');

phone varchar(22) inz('');

email varchar(50) inz('');

end-ds;
End-ds;

AllCustomers.num_Customers = sqlRows;

DATA-GEN AllCustomers

%DATA(inStmf: 'doc=file output=clear countprefix=num_')

%GEN('YAJLDTAGEN');

If that doesn't work with the SQL you can keep the other customers array and continue to load that with sql and then:

%Subarr( AllCustomers.Customers:1:SqlRows ) = %SUBARR(customers:1:sqlRows);

Then run the DATA-GEN.

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg Wilburn
Sent: Tuesday, February 1, 2022 3:03 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: YAJLDTAGEN with SQL into DS

So I have a working application where I've used SQL data access to load a Data structure.

dcl-ds Customers qualified dim(50) inz;

addr_line1 varchar(40) inz('');

addr_line2 varchar(40) inz('');

city varchar(40) inz('');

state varchar(3) inz('');

zip varchar(10) inz('');

phone varchar(22) inz('');

email varchar(50) inz('');

end-ds;

I then use YAJLDTAGEN to create the JSON file:

DATA-GEN %SUBARR(customers:1:sqlRows)

%DATA(inStmf: 'doc=file output=clear')

%GEN('YAJLDTAGEN');

Output looks like this:

[

{

"addr_line1": "64a N Williams St",

"addr_line2": "",

"city": "Crystal Lake",

"state": "IL",

"zip": "60014",

"phone": "7792204179",

"email": "marvinstoys@xxxxxxxxx"

},

{

"addr_line1": "267 Main St",

"addr_line2": "",

"city": "Huntington",

"state": "NY",

"zip": "11743",

"phone": "6315498743",

"email": "lsdolls@xxxxxxx"

}

]

The endpoint has asked for this:

{

"Customers": [

{

"addr_line1": "64a N Williams St",

"addr_line2": "",

"city": "Crystal Lake",

"state": "IL",

"zip": "60014",

"phone": "7792204179",

"email": "marvinstoys@xxxxxxxxx"

},

{

"addr_line1": "267 Main St",

"addr_line2": "",

"city": "Huntington",

"state": "NY",

"zip": "11743",

"phone": "6315498743",

"email": "lsdolls@xxxxxxx"

}

]

}

I know how to create the DS... but then I'm not sure how to do Exec SQL INTO that DS.

I'm pretty sure the "into variable" cannot be "json.Customers".

What am I missing?

TIA,

Greg

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx 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 RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com