Column or global variable xxx not found.

[wimjongman]

I adapted and executed some of the nested SQL examples but I get the message

  Column or global variable PPAPPC not found.

PPAPPC is a field in the row.

   sql = 'Select * from omsxmp.omapp';
   appHandler  = json_sqlOpen(sql);

   // Now iterate on each row in the resultset
   appRow = json_sqlFetchNext(appHandler);
   DoW (appRow <> *NULL);
      // Note: the SQL can be a template, we fill with values from a json row
      sql = 'Select * from omsxmp.ompdl where dlappc = $ppappc' ;
      envArrary  = json_sqlResultSet(sql: 1 : 9999 : 0 : appRow);
      // Append the row to the end of the result array.
      json_MoveObjectInto (appRow   : 'environments' : envArrary );
      json_ArrayPush (resultArray : appRow );
      appRow = json_sqlFetchNext(appHandler) ;
   EndDo;

[NielsLiisberg]

Hey Wim, I can produce something similar if my job ccsid is not the same as the code ccsid (and your NOXDB is ccid 500) by having the $ in hex format I can make it work giving the $ in ccsid(500): Dcl-s dollar char(1) inz(x'67'); I'll have to find a more permanent solution for this - thanx for the "issue" !! Here I have redone your work - P.s. i prefer using iterator on a resultset ( you should do the same) : // ------------------------------------------------------------------------------------ // example4 // Use iterators for nested results // ------------------------------------------------------------------------------------ dcl-proc example4; Dcl-S pManufacturer Pointer; Dcl-S pProducts Pointer; Dcl-s dollar char(1) inz(x'67'); Dcl-DS manufacturereList likeds(json_iterator); // return an simple array with rows pManufacturer = json_sqlResultSet( 'Select * from noxdbdemo.icManufacturer' ); // Test errors: If json_Error(pManufacturer) ; json_joblog(json_Message(pManufacturer)); Return; EndIf; manufacturereList = json_setIterator(pManufacturer); DoW json_ForEach(manufacturereList ); pProducts = json_sqlResultSet( 'Select * from noxdbdemo.icProduct where manuid = ' + dollar + 'manuid': 1: // first row -1: // all rows 0: // simple array manufacturereList.this // from where to pick the key ); json_MoveObjectInto (manufacturereList.this : 'products' : pProducts ); EndDo; json_WriteJsonStmf(pManufacturer:'/prj/noxdb/testout/nested.json':1208:*OFF); on-exit; // Cleanup: dispose the rows in the array json_delete(pManufacturer); end-proc;

…

On Mon, Nov 25, 2024 at 3:53 PM Wim Jongman ***@***.***> wrote: I adapted and executed some of the nested SQL examples but I get the message Column or global variable PPAPPC not found. PPAPPC is a field in the row. sql = 'Select * from omsxmp.omapp'; appHandler = json_sqlOpen(sql); // Now iterate on each row in the resultset appRow = json_sqlFetchNext(appHandler); DoW (appRow <> *NULL); // Note: the SQL can be a template, we fill with values from a json row sql = 'Select * from omsxmp.ompdl where dlappc = $ppappc' ; envArrary = json_sqlResultSet(sql: 1 : 9999 : 0 : appRow); // Append the row to the end of the result array. json_MoveObjectInto (appRow : 'environments' : envArrary ); json_ArrayPush (resultArray : appRow ); appRow = json_sqlFetchNext(appHandler) ; EndDo; image.png (view on web) <https://github.com/user-attachments/assets/2f3ec7fa-ee2e-4279-b544-469e9531a342> — Reply to this email directly, view it on GitHub <#92>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAVIPHSSISXZV2GWQ6Q7SND2CM2VLAVCNFSM6AAAAABSOFXLOWVHI2DSMVQWIX3LMV43ASLTON2WKOZSGY4TCMBUG44DEMY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[NielsLiisberg]

Test case and workaround is created here:

https://github.com/sitemule/noxDB/blob/master/test/issue92.rpgle

[wimjongman]

Thanks for your time, Niels. My JSONXML is 65535 and the modules are 37 so that would not explain it. Instead, I think I will be adding the values directly, or do something like: sql = 'Select * from omsxmp.ompdl where dlappc = ''$ppappc''' ; sql = %scanrpl('$ppappc' : json_getStr(appRow:'ppappc') : sql );

[NielsLiisberg]

No !! Please don't !! There is a procedure for exactly that . The changes the delimiters: *xml_setDelimiters('/\@[] .{}''"$');* That is designed in cases where you have ccsid 65535 - so the delimiers you provide are the same as you will use for constants and variables. You can even set (force) the delimiers from a ccsid if that is not consistent with the source / object /job : *xml_setDelimitersByCcsid(500);* By default it is the job ccsid - and with ccsid 65535 noxDb has no idea The code I provided in "issue92" was to pinpoint the issue, not a real work around..But I can see it looks like that - sorry.

…

On Tue, Nov 26, 2024 at 9:59 AM Wim Jongman ***@***.***> wrote: Thanks for your time, Niels. My JSONXML is 65535 and the modules are 37 so that would not explain it. Instead, I think I will be adding the values directly, or do something like: sql = 'Select * from omsxmp.ompdl where dlappc = ''$ppappc''' ; sql = %scanrpl('$ppappc' : json_getStr(appRow:'ppappc') : sql ); — Reply to this email directly, view it on GitHub <#92 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAVIPHQSZ7R4EOUZEQF2LEL2CQ2AHAVCNFSM6AAAAABSOFXLOWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMBQGAZTQOJUGU> . You are receiving this because you commented.Message ID: ***@***.***>

[wimjongman]

I will play around with setDelimeters* and report back.

I am running in 37 so I'm not sure why $ falls of the wagon. Should JSONXML *SRVPGM be in 37 (or any except 65535)?

[NielsLiisberg]

By default - JSONXML, the service program under the covers i build with CCSID 500 - multinational. You can always "pull" and roll your own by change the CCSID value in "makefile" .. it is one place

…

On Tue, Nov 26, 2024 at 12:19 PM Wim Jongman ***@***.***> wrote: I will play around with setDelimeters* and report back. I am running in 37 so I'm not sure why $ falls of the wagon. Should JSONXML *SRVPGM be in 37 (or any except 65535)? — Reply to this email directly, view it on GitHub <#92 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAVIPHRKICXCIJKSML4YGKD2CRKMNAVCNFSM6AAAAABSOFXLOWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMBQGM3DANZZGE> . You are receiving this because you commented.Message ID: ***@***.***>