Menu ▾ ▴

#9 Convert DS to JSON object

open
nobody
None
5
2010-07-16
2010-07-16
Aaron Bartell
No

I am finding that I work a lot with an entire DB2 row - passing it back and forth from the browser to my RPGUI program using JSON. It would be great if we could devise a way to read a record and call a routine that would convert the record in memory to a JSON row.

So instead of this:

chain key PERSONTBL PER;
json_putInt( pJson: 'C@PER#': PER.C@PER#);
json_putInt( pJson: 'C@FAM#': PER.C@FAM#);
json_putInt( pJson: 'C@MPER': PER.C@MPER);
json_putInt( pJson: 'C@SEQ#': PER.C@SEQ#);
json_putString(pJson: 'C@RSHP': PER.C@RSHP);
json_putString(pJson: 'C@SSN': %editc(PER.C@SSN:'X'));
json_putString(pJson: 'C@NAML': PER.C@NAML);
json_putString(pJson: 'C@NAMF': PER.C@NAMF);
json_putString(pJson: 'C@NAMM': PER.C@NAMM);
json_putString(pJson: 'C@NSUF': PER.C@NSUF);
json_putString(pJson: 'C@NAME': PER.C@NAME);
json_putString(pJson: 'C@NAMD': PER.C@NAMD);
json_putString(pJson: 'C@AD1#': PER.C@AD1#);
json_putString(pJson: 'C@AD1N': PER.C@AD1N);
json_putString(pJson: 'C@CITY': PER.C@CITY);
json_putString(pJson: 'C@STA': PER.C@STA);
json_putString(pJson: 'C@ZIP': PER.C@ZIP);
json_putString(pJson: 'C@MADC': PER.C@MADC);
json_putString(pJson: 'C@MAD1': PER.C@MAD1);
json_putString(pJson: 'C@MAD2': PER.C@MAD2);
json_putString(pJson: 'C@MCTY': PER.C@MCTY);
json_putString(pJson: 'C@MAST': PER.C@MAST);
json_putString(pJson: 'C@MZIP': PER.C@MZIP);
json_putString(pJson: 'C@PPH#': PER.C@PPH#);
json_putString(pJson: 'C@PPFL': PER.C@PPFL);
json_putString(pJson: 'C@SPH#': PER.C@SPH#);
json_putString(pJson: 'C@SPFL': PER.C@SPFL);
json_putString(pJson: 'C@TPH#': PER.C@TPH#);
json_putString(pJson: 'C@TPFL': PER.C@TPFL);
json_putString(pJson: 'C@EMAD': PER.C@EMAD);
json_putString(pJson: 'C@FSPH': %char(PER.C@FSPH));
json_putString(pJson: 'C@DOB': %char(PER.C@DOB));
json_putString(pJson: 'C@AGE': %char(PER.C@AGE));
json_putString(pJson: 'C@SEX': PER.C@SEX);
json_putString(pJson: 'C@MSTA': %trim(PER.C@MSTA));
json_putString(pJson: 'C@CTZS': %trim(PER.C@CTZS));
json_putString(pJson: 'C@LANG': PER.C@LANG);
json_putString(pJson: 'C@ITPR': PER.C@ITPR);
json_putString(pJson: 'C@ETHN': PER.C@ETHN);
json_putString(pJson: 'C@STAT': PER.C@STAT);
json_putString(pJson: 'C@BYCK': PER.C@BYCK);
json_putString(pJson: 'C@TAB1': PER.C@TAB1);
json_putString(pJson: 'C@TAB2': PER.C@TAB2);
json_putString(pJson: 'C@TAB3': PER.C@TAB3);
json_putString(pJson: 'C@TAB4': PER.C@TAB4);
json_putString(pJson: 'C@TAB5': PER.C@TAB5);
json_putString(pJson: 'C@TAB6': PER.C@TAB6);
json_putString(pJson: 'C@TAB7': PER.C@TAB7);
json_putString(pJson: 'C@TAB8': PER.C@TAB8);
json_putString(pJson: 'C@TAB9': PER.C@TAB9);
json_putString(pJson: 'C@TABA': PER.C@TABA);
json_putString(pJson: 'C@APPD': %char(PER.C@APPD));
json_putString(pJson: 'C@CUSR': PER.C@CUSR);
json_putString(pJson: 'C@CDAT': %char(PER.C@CDAT));

... we could do this...

chain key PERSONTBL PER;
jsonObj = json_cvtRec(PER: 'PERSONTBL');

Then as long as PERSONTBL is externally defined we could get the layouts of each field. In theory we should be able to use an incrementing pointer to "parse" the passed in DS based on the meta data found by calling an API and passing in the externally define table definition.

I have done this before at a previous employer in a slightly different web scenario, but unfortunately I don't have access to that code anymore. Anybody else have a start on a code base that would do something similar to what I describe above?

AaronBartell.com

Discussion

  • Darron Smith

    Darron Smith - 2010-08-14

    Hi Aaron,
    I did exactly this about a year ago. I created a Service Program implementation of the QDBRTVFD API. It has to be one of the ugliest mofo API's out there but I restricted the implementation to grabbing field lists and key lists for files using reasonably nice interface functions. Personally I'd be happy to share but I'll have to run it past the powers that be at work. We're interesting in what is happening here so it should pass and it's also quite innocuous from a business pov.
    I'll get back to you.

     

  • Aaron Bartell

    Aaron Bartell - 2010-08-16

    Darron, that would be excellent if you could donate that code! Let us know what your company says.

    AaronBartell.com

     

  • Darron Smith

    Darron Smith - 2010-08-24

    A brief update. I have engaged the powers that be and it is now in their hands. There are some considerations that need to be thought through such aslegal implications and setting a precedent for other things. An interesting proposition that I don't think the business has faced before, despite the fact that we use open source elsewhere. If we exceed a time frame you're unhappy about let me know, but I expect an answer will be forthcoming within the next week or two. Initial discussions were promising.

    Darron

     

  • Aaron Bartell

    Aaron Bartell - 2010-08-24

    Thanks for the update Darron. This is usually where companies back out because people start theorizing about how somebody might sue the company and cause them to go out of business. Lots of FUD, but you can't ask somebody to do something they are uncomfortable with I guess. Here are a couple options:
    1) Submit the code with a short form statement that eliminates all warranties and liabilities similar to the following:

    This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

    2) Submit it anonymously to http://code.midrange.com and then let me know where I can pick it up. This would more or less make me your proxy.

    Hope they are open to being part of the community.
    AaronBartell.com

     

  • Darron Smith

    Darron Smith - 2010-08-27

    I got rejected. No reasoning was forthcoming however it will be fully explained to me soon, so I'll follow up. I suspect it's just an REJECT(*ALL) default setting.

     

  • Aaron Bartell

    Aaron Bartell - 2010-08-27

    Bummer. It's a shame how much politics gets in the way of progress.

     

  • Brian Garland

    Brian Garland - 2011-06-13

    Aaron,

    I have to write this exact routine for my current project and I already have permission to submit it to the community.

    I was thinking it probably should be added to the JSON service program instead of just being a wrapper. I guess I'd have to ask Mihael Schmidt if he wants to incorporate it in his project. Does this line up with how you were thinking?

    Brian

     

  • Aaron Bartell

    Aaron Bartell - 2011-06-13

    Hi Brian,

    Glad to hear it!

    My guess would be that Mihael Schmidt wouldn't add it to his JSON service program because it would be more of an implementation to his service program vs. a feature.

    Eventually we will be create a all-encompassing wrapper program where all APIs will start with ORU_ (i.e. ORU_getCGIVal, ORU_putString, etc), so it might be somewhat of a moot point of whether it makes it into the JSON service program. We will probably call it ORU_rowToJson or something like that.

    Thanks again! So much fun to see others donate code!

    AaronBartell.com

     

  • Brian Garland

    Brian Garland - 2011-06-14

    Aaron,

    Okay, It will probably be easier to do it as a wrapper anyway.

    Any suggestions on dealing with numeric, date, and time fields? For my particular scenario all the data being sent is character but I imagine that we should handle all types. Although I don't see the JSON service program supporting fields with decimal points other than "float".

    How does this sound: For numeric types, I'll get the edit code / edit word info from the file and use the APIs to format the value. If the value has decimal positions, I'll add it as a float, otherwise an int. For dates and times, I'll get the format info and convert them to strings.

    Or, should I just add everything as a string?

    Brian

     

  • Aaron Bartell

    Aaron Bartell - 2011-06-14

    Brian,

    The json_putDouble() would be a good place to put Decimal values.

    AaronBartell.com

     

  • Brian Garland

    Brian Garland - 2011-06-24

    Aaron,

    I've been in touch with Mihael regarding a problem with the JSON service program (floats start with a + and they should not). I mentioned this procedure and he is interested in including it as he wanted one that did that too.

    I have it just about done and will release it soon. I can share it with you at the same time, but in the long run you can expect it to be integrated into the JSON service program.

    Brian

     

  • Aaron Bartell

    Aaron Bartell - 2011-06-24

    Thanks for the update Brian and thanks for working with Mihael. I will wait for his version and then incorporate that into OpenRPGUI.

    AaronBartell.com

     

  • Nobody/Anonymous

    Nobody/Anonymous - 2011-07-17

    Hi all,

    don't hold your breath for the next version of the JSON service program. There are several features which has been floating in for the JSON service programs which needs some time for me to add/implement (time is a rare resource atm). So it would probably best to take the code from Brian. As you seem to make wrapper procedures anyway you can later point the wrapper procedure to the one from the JSON service program.

    Mihael

     

  • Nobody/Anonymous

    Nobody/Anonymous - 2011-07-18

    Brian, did you release a version of your code? I am actively working on a couple projects that could use this immediately.

    Thanks,
    AaronBartell.com

     

  • Mihael Schmidt

    Mihael Schmidt - 2011-07-20

    Hi,

    I am wondering to what a packed or zoned field should be converted. Brian did a conversion to an int or double depending on the number of decimal positions. Aaron had a request to add get/put decimal to the JSON service program. These procedure will store the value as a string and convert it to/from a decimal.

    So what would be better? String or int/double?

    Mihael

     

  • Nobody/Anonymous

    Nobody/Anonymous - 2011-07-20

    The issue I was having with double is it doesn't keep the number of decimal positions when converted from DB2 decimal to JSON double.

    I am thinking it would be best to implement it as double to keep it in the truest form. But with that said I am also wondering if we could add another parm at the end so we could declare how many decimal positions to include:

    json_putDouble(ptr: 'name': packedDecVar: 2);

    Thoughts?
    AaronBartell.com

     

  • Nobody/Anonymous

    Nobody/Anonymous - 2011-07-20

    Hmm... well, accuracy problems will most certainly be an issue for the types of applications I am developing :-) I rarely use floating point data types so my ignorance is probably shining strong.

    I propose we introduce something like this:

    json_putDecimal(ptr: 'name': 12345.12)

    That will then be represented like this in JSON:

    { 'name' : 12345.12 }

    That seems to coincide with the number spec on the json.org page, but I am not certain.

    Also, you will obviously need the third parm in json_putDecimal to be the largest supported at the current OS level (i.e. 31,9 or whatever it is). Is there a way for you to know how many decimal positions were on the passed variable/literal? Or is it a case where you would just trim digits back to the first non-zero number. So 00001234.12000 would be converted to 1234.12 in the JSON.

    AaronBartell.com

     

  • Mihael Schmidt

    Mihael Schmidt - 2011-07-21

    I already added json_putDecimal/getDecimal to the JSON_UTIL for version 1.4.0. The value will be stored as a string to avoid any complications with other JSON libs.

     

  • Mihael Schmidt

    Mihael Schmidt - 2011-07-24

    Including the contributions and makeing some tests were easier as it first seemed. There is a new version for the JSON service program to download. The code is also checked into a SVN repository: https://svn.code.sf.net/u/fist/src/json

     

  • Brian Garland

    Brian Garland - 2011-07-25

    Aaron & Mihael,

    I've been out of town for a week and am catching up. It seems you have worked out the discussion.

    Please let me know if you would like me to make any changes.

    Brian