RE: YAJLDTAGEN with SQL into DS

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