Thursday, March 19, 2009

Serve JSON from PeopleSoft

Last month a reader asked me for an example of serving JSON from PeopleSoft. The following IScript demonstrates how to serve JSON by printing user and role information in JSON format:

Function IScript_GetJSON
Local SQL &usersCursor = CreateSQL("SELECT OPRID, OPRDEFNDESC, EMAILID FROM PSOPRDEFN WHERE ROWNUM < 6");
Local SQL &rolesCursor;
Local string &oprid;
Local string &oprdefndesc;
Local string &emailid;
Local string &rolename;

Local boolean &isFirstUser = True;
Local boolean &isFirstRole = True;

%Response.Write("[");
While &usersCursor.Fetch(&oprid, &oprdefndesc, &emailid)
REM ** comma logic;
If (&isFirstUser) Then
&isFirstUser = False;
Else
%Response.Write(", ");
End-If;

%Response.Write("{""OPRID"": """ | EscapeJavascriptString(&oprid) | """, ""OPRDEFNDESC"": """ | EscapeJavascriptString(&oprdefndesc) | """, ""EMAILID"": """ | EscapeJavascriptString(&emailid) | """, ""ROLES"": [");

&rolesCursor = CreateSQL("SELECT ROLENAME FROM PSROLEUSER WHERE ROLEUSER = :1 AND ROWNUM < 6", &oprid);
&isFirstRole = True;

While &rolesCursor.Fetch(&rolename);
REM ** comma logic;
If (&isFirstRole) Then
&isFirstRole = False;
Else
%Response.Write(", ");
End-If;

%Response.Write("""" | EscapeJavascriptString(&rolename) | """");
End-While;

&rolesCursor.Close();
%Response.Write("]}");
End-While;

%Response.Write("]");
&usersCursor.Close();

End-Function;

The code above uses embedded SQL. In production, be sure to use App Designer SQL definitions. This code listing also embeds JSON formatting strings. As an alternative, I recommend HTML definitions and HTML bind variables. In this manner, HTML definitions serve as templates for structured JSON data.

Formatted, the output from my demo database looks like:

[
{
"OPRID": "ADRIESSEN",
"OPRDEFNDESC": "Anton Driessen",
"EMAILID": "ADRIESSEN@server.com",
"ROLES": [
"All Processes",
"All Query Access Groups",
"EPM Scorecard Viewer",
"Portal User",
"Query Access - All FSCM"
]
},
{
"OPRID": "ADUPOND",
"OPRDEFNDESC": "Alain Dupond",
"EMAILID": "ADUPOND@server.com",
"ROLES": [
"All Processes",
"All Query Access Groups",
"EPM Scorecard Viewer",
"Portal User",
"Query Access - All FSCM"
]
},
{
"OPRID": "AEGLI",
"OPRDEFNDESC": "Anna Egli",
"EMAILID": "AEGLI@server.com",
"ROLES": [
"All Processes",
"All Query Access Groups",
"EPM Scorecard Viewer",
"Employee Global Payroll",
"Portal User"
]
},
{
"OPRID": "AERICKSON",
"OPRDEFNDESC": "Arthur Erickson",
"EMAILID": "AERICKSON@server.com",
"ROLES": [
"Accounts Payable Manager",
"All Processes",
"All Query Access Groups",
"Application Homepages",
"EP General Options"
]
},
{
"OPRID": "AFAIRCHILD",
"OPRDEFNDESC": "Alison Fairchild",
"EMAILID": "AFAIRCHILD@server.com",
"ROLES": [
"Applicant",
"All Processes",
"All Query Access Groups",
"EPM Scorecard Viewer",
"Employee ELM"
]
}
]

IScripts provide a secure free-form mechanism for serving data. This makes them perfect for serving JSON in response to a logged in user's AJAX request. If you want to serve PeopleSoft data in JSON format for consumption in a page outside PeopleSoft, then try using an Integration Broker synchronous message handler.

If you need help prototyping your JSON, take a look at the JSON homepage and JSONLint, an online JSON validator. I rely heavily on JSONLint when prototyping JSON.

26 comments:

LewisC said...

I use JSONLint also. I am writing a new JSON data type for Oracle. I'll be releasing it in the near future. I wrote about it here: http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/

LewisC

Jim Marion said...

@LewisC, very nice! I can't wait for the final!

LewisC said...

I've released v0.5 of PL/JSON. Right now it can generate JSON but not parse it. I'm working on parsing now.

I'm also adding documentation which is pretty sparse at the moment. I do include a detailed example script though.

Feedback would be great.

You can get it by reading the PL/JSON FAQ: http://oracleoss.com/

Thanks,

LewisC

SamTook said...

Calling the example you made, I noticed that it does not provide the carriage returns on the displayed page. Everything appears as one continuous (wrapped) line, but on your example it contains all the line feeds and indents. What's the difference in what you show and how PS is generating it when I test it?

Steve

Jim Marion said...

@SamTook, I think the difference is that I used jsonlint to format what you see on the screen. For HTTP transfer, though, I recommend no spaces. A JSON parser doesn't need them, and they just add more bytes to the transfer.

On another note... I was noticing that this post uses EscapeJavaScriptString, which will work most of the time, but not all the time. JSON has slightly different rules for encoded content. Rather than EscapeJavaScriptString, I suggest something like this:

JSON Encoding in PeopleCode

Ganesh said...

Jim,

1) how to send the JSON message create (in Varaible or in file) to JSON server from Peoplesoft.
Use Java script or IScript - what is the request send HTTP Request or JSONRequest. Do we need to use IB, if yes how to publish the message ?

When the user enter some message in peoplesoft page and submit it peoplesoft need to send the message to apple notification sever in JSON format (I know how the message should look like).

It will be great if you have already create an applicaiton classs in peoplecode to create standard JSON message, similar to AddNode, Addattribute for XML message.

Jim Marion said...

@Ganesh, see my response to you in the post Going Mobile with PeopleSoft.

PS Swami said...

I am working through your book and have reached a trouble spot in chapter 7 when JSON is introduced. I am able to get the JSON on the screen by calling the ISCRIPT as the book mentions. I paste the results into jsonlint.com and it says it is valid JSON. When I change the content type to application JSON and navigate to the web assets page... I don't receive the alerts as expected. However, I also don't see any errors in firebug. When I go into the firebug console for the get request... I can see the data under the JSON tab. The only difference I see between the book and my code is the appearance of the &quote vs ".
{
"appendTo": "BODY",
"data": "\nalert("This is a global scr ipt!");\n"
},
I am using the below tools:
Firefox 9.0.1
PeopleTools 8.51.07
jquery 1.7 min
JSON2.js but it doesn't load this

Any suggestion would be appreciated.

Jim Marion said...

@PS Swami, it sounds like you did everything right. We are definitely on different releases now, so that could account for some of it. JSON2.js shouldn't load since your browser has native JSON support. That is primarily for IE. Your comment mentions &quot but I don't see that in the post below. Was &quot in the contents of the book, but not in the firebug response? To which page are you referring?

PS Swami said...

[
{"appendTo": "BODY", "data": "\nalert("This is a global script!");\n"}
, {"appendTo": "BODY", "data": "\nalert("This one is included in all components attached to menu DJMCUSTOM");\n"}
, {"appendTo": "BODY", "data": "\nalert("This is the search page!");\n"}
]

Above is exactly what my page shows when I run the iscript with it set at text/javascript. When I paste that into jsonlint.com it says it is valid. The difference between your book and my output is the " vs "
When I change it back to application/json and run the iscript... I don't get the alerts. If I manually put an alert using straight javascript on an html object it works as expected.
I don't know if it is something I am causing or if it is correct and just not working. I can send you a screenshot if you think it would help.

Jim Marion said...

@PS Swami, when I paste that into jsonlint, I don't get valid, I get invalid. The problem is the nested quotes. If I change it from "\nalert("blah");\n" to "\nalert('blah')'\n" then it works. From what I see in the comments, you have quotes inside quotes. That might not really be the case, but that is how it looks after the comment is posted.

PS Swami said...
This comment has been removed by the author.
Jim Marion said...

PS Swami, here is a good way to share your screenshot: upload the image to some free file sharing site, and then use the a tag to link off to the image where you have it uploaded.

PS_Rookie said...

Hi jim, im trying to optimize de the 360 degree view, i was tryimg to use ajax and jquery forthe tree , but know im stuck.
I was wondering how good an idea is to completly customize de 360, with ajxas abd jquery, using jqgrid and other plugins.

Jim Marion said...

@PS_Rookie, I don't have any experience with the 360's, so, unfortunately, I can't give you any recommendations in that regard.

Unknown said...

Hello Jim,

I am trying to serve JSON through PS RESTFul service using a GET method.
When I try to invoke the URL from a browser, a window pops up for my UserID & Password.

Is there a way that we can pass these credentials in the URL?


Thanks

Jim Marion said...

@Narender, yes there is, but it is NOT recommended. To pass credentials in the URL, uncheck the requires authentication checkbox in the service, and then use a mechanism like the one posted here to parse the query string. With the credentials in variables, call SwitchUser to become the specified user.

It really depends on how you are using the URL. If it is in your browser, the URL is quite visible, bookmarkable, available from history, etc.

What is the reason you don't want to use Basic Auth HTTP Headers? I use REST with Ajax, and Ajax supports setting the authorization header for basic auth. In the jQuery.ajax documentation you can see the username and password parameters.

harry said...

Hi Jim

I can able to genereate xml from peoplcode from the table data...Is it possible to generate Json in peoplecode ??
If so can you please share me some link or sample code...
Thanks for you help

Jim Marion said...

@Harry, Yes, you can generate JSON. Prior to PeopleTools 8.53, however, it was more difficult, requiring you to either use a Java JSON generator/parser or string text together using PeopleCode. I wrote a PeopleCode JSON escape routine that is available here: JSON Encoding in PeopleCode. You can find an example of using Java here: Parsing JSON with PeopleCode.

If you are using PeopleTools 8.53 or higher, you can create a Document and then generate JSON from the document.

Will said...

Jim

This should be an easy one for you ... In 8.54 I an getting a jSON file externally from a url and then I want to transverse the JSON that is returned..

the ConnectorRequestUrl works great but the documentation in oracle is very limited on JSON


local document &DOC;
&json = %IntBroker.ConnectorRequestUrl(&Connection);

&Okay = &DOC.ParseJsonString(&json);


The Above gives me a NULL Error... Probably because &DOC is not instantiated
But how do I create a Blank Document to load...
I tried &DOC = Createdocument(" ");
What am I missing

Jim Marion said...

@William, this is one of the frustrations I have with parsing JSON with the Documents module: You need to create a Document with a structure that matches the JSON. This is easy if you are creating the JSON. Rarely do I find an optimized JSON file created by someone else that I can replicate with the Documents module. Instead, I choose to parse complex JSON using the Rhino JavaScript engine embedded in the Java version shipped with PeopleTools 8.53+. I should write a blog post on this. I have some examples I can share.

Will said...

I actually used your JSON java parser....

But since I was now in 8.54 and I saw ParseJsonString I thought it would be similar to parseXmlString ..

Just create an blank XML Document and load it with a XML String.. That is great..

I was hoping I would be able to try out something similar in the new version ..

ANd your right... I am getting a JSon from an external source and it could change on a whim, I really want to have the control on what an how I read it.....

I guess I will stick with the Java APPPackage

Thanks
Will

Will said...

Jim

Did you ever write the articular to use the ParseJsonString ... and transverse the structure similar to XMLDOC.

I am using JavaApPackage in 8.54 But for some reason you would think it should be In Peopelsoft by now..

Jim Marion said...

@William, PeopleCode development took the "Documents" approach to JSON parsing. Instead of including a built-in parser, they require a Document object definition and then use the Documents API to parse JSON. Unfortunately, the Documents module supports limited structures. Besides the Java class option you mentioned, I have also used JSON.parse from JavaScript through the Java 7 Script Engine API that is included with PeopleTools.

Sachin Jhawar said...

Hi Jim,

I tried your approach to post a JSON Message from peoplesoft using non-rowset based message and GenJsonString() to generate a Json message and parse it with xmldoc.
However ,I am getting HTTP Status 415 error message
The server refused this request because the request entity is in a format not supported by the requested resource for the requested method
Apache Tomcat/7.0.59

My requirement is to Post a transaction in JSON Format to Third party system (non peoplesoft) which has REST URI enabled to accept only POST methond.

I am looking to do that in asynchronous way if possible but stuck at the error as above.
any advise you can offer what might be missing , We are in peopletools 8.53

also any reference you can provide for REST consumer services which i think is alternative to what i am doing above will also help

Thanks for your help !
Sachin

Jim Marion said...

@Sachin, I suggest posting to a server where you can capture headers and data so you can verify everything is valid. The method demonstrated here will usually produce valid JSON, but not always. The JavaScript escape method does create valid JavaScript, but not necessarily valid JSON.