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.

17 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.

Narender Dontu 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.