Saturday, 16 June 2007

AJAX retrieval using Javascript Object Notation (JSON)

Introduction...
Typically, most of the AJAX examples out there for APEX either use plain text or xml for data transmission. This post details how to implement AJAX functionality using Javascript Object Notation or JSON, retrieving multiple items from an application process. JSON, or the fat free alternative to XML is a simple, lightweight syntax for representing data and can be easily integrated into an APEX application. Due to its light weight, it has performance advantages over using XML and is also very easy to understand and manipulate on the client side.

This post will show how to create a simple example of AJAX with JSON, where a user selects either 'Car' or 'Bike' from a select list, then either car or bike 'models' will be populated into 2 page items.


Pre-requisites...
In order to parse the JSON text and create the javascript objects in your javascript code, you will need to download the JSON open source parser. Once downloaded, it must be made available in your APEX environment. The best way to do this is to save the file into the following directory on your web server:
  ORACLE_HOME\Apache\Apache\images\javascript

And then reference it by adding the following to your 'Page Template > Definition > Header', just after the '#HEAD#' substitution string:
  <script src="#IMAGE_PREFIX#javascript/json.js"
type="text/javascript"></script>

Alternatively, if you have restricted access to the web server (as with apex.oracle.com), then upload the 'json.js' file as a static file, via 'Shared Components > Static Files', ensure you associate it with your application and then add this reference to the page template:
  <script src="#APP_IMAGES#json.js"
type="text/javascript"></script>
Note: The static file method will not be as fast in execution as copying the file directly on the server.


How...
Step 1: Create a new, blank page, call it 'AJAX - JSON Demo Page'. (I'll assume it's page 1 for the rest of this example.)

Step 2: Create a HTML, empty region, call it 'AJAX - JSON Region'.

Step 3: Create the following 3 page items:
- The activator will be a select list, where you either select 'Car' or 'Bike'. Selecting one of these options will invoke the 'onChange' event specified and call the 'Get_Multiple_Items()' javascript function, specified in step 5. Set the following:
Type = Select List
Item Name = P1_ACTIVATOR
Region = AJAX - JSON Region
List of Values Query = STATIC2:Car;Car,Bike;Bike
Label = Activator
Element > HTML Form Element Attributes = onChange="get_Multiple_Items();"

(All other attributes just leave as default.)

- Item 1 to return the first model of either 'Car' or 'Bike', set the following:
Type = Text Field
Item Name = P1_RETURN_ITEM_1
Region = AJAX - JSON Region
Label = Return Item 1

(All other attributes just leave as default.)

- Item 2 to return the second model of either 'Car' or 'Bike', set the following:
Type = Text Field
Item Name = P1_RETURN_ITEM_2
Region = AJAX - JSON Region
Label = Return Item 2

(All other attributes just leave as default.)


Step 4: Create an application process via 'Shared Components', set the following:
Name = RETURN_MULTIPLE_ITEMS
Process Point = On Demand: Run this application process when requested by a page process
Type = PL/SQL Anonymous Block
Process Text =
declare
return_item_1 varchar2(100);
return_item_2 varchar2(100);
return_str varchar2(2000) := '';
begin
owa_util.mime_header('text/html', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;

if :P1_ACTIVATOR = 'Car' then
return_item_1 := 'Ferrari';
return_item_2 := 'Ford';
elsif :P1_ACTIVATOR = 'Bike' then
return_item_1 := 'Suzuki';
return_item_2 := 'Ducati';
end if;

return_str := '{
"model":[
"'||return_item_1||'",
"'||return_item_2||'"
]
}';

htp.prn(return_str);

end;
(All other attributes just leave as default.)

Of particular interest here is the following lines of code:
  return_str := '{
"model":[
"'||return_item_1||'",
"'||return_item_2||'"
]
}';
So this is where we need to encode the result into the correct JSON syntax, so as to be parsed successfully in the client.

JSON syntax is exactly the same as the syntax used to define normal object and array literals in javascript. For a detailed description of the syntax, see the JSON homepage. In my example, if the user selects 'Bike', the following string would be returned by the process:
  {"model":["Suzuki","Ducati"]}
So here we have a single member called 'model', containing an array of the two return values, 'Suzuki' and 'Ducati'.


Step 5: Create a javascript function on the page
We now need to create the function that will setup, send and receive the request from the server. This will use the built-in 'htmldb_Get' ajax function to call the application process called 'RETURN_MULTIPLE_ITEMS', created in step 4. Set the following:
Page Attributes > HTML Header > HTML Header =
<script language="JavaScript" type="text/javascript">
<!--
function get_Multiple_Items() {
var get = new htmldb_Get(null,
&APP_ID.,
'APPLICATION_PROCESS=RETURN_MULTIPLE_ITEMS',
0);
get.add('P1_ACTIVATOR',$x('P1_ACTIVATOR').value);

gReturn = get.get();

if (gReturn) {
var myJSONObject = gReturn.parseJSON();
$x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];
$x('P1_RETURN_ITEM_2').value = myJSONObject.model[1];
}
}
//-->
</script>

Of particular interest here are the following lines of code:
  var myJSONObject = gReturn.parseJSON();
This calls the function 'parseJSON()' which parses the JSON text to produce an object, and then...
  $x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];
The resulting object 'myJSONObject' can then be accessed like any normal javascript object. In this case, we are retrieving the first value from a member called 'model' (which was created in step 4). This value then sets the 'P1_RETURN_ITEM_1' value.

Now run your page and you should be able to see some nice AJAX and JSON in action! Or take a look here.


Conclusion...
So it's a pretty simple little example, but I really like how easy it is to manipulate the object client-side. The only downfall is that the syntax to encode the JSON string in the application process takes a bit of getting used to, but I found it fairly easy to get my head around. Ideally, you would have a server-side encoder for this and there are many available, just none for PL/SQL that I've found*. There are lots available for Java, which could potentially be loaded in the database and called via a PL/SQL wrapper.

I have not done this for this example because I felt it was overkill for what I was trying to achieve. To just return a few items like I have done here, manually encoding the JSON text was fairly painless, although I'm sure it could quickly get very painful if creating large, nested structures, where a proper encoder may be more beneficial (and performance gains would be more apparent).

Also, bear in mind my example doesn't really do anything in the application process which warrants a trip to the database (just checking hard-coded strings, which could be done client-side). The purpose of this post was to highlight how to implement JSON with AJAX in APEX so I tried to reduce complexity elsewhere to focus on the main points.

Hope it helps,
Anthony.


* Update...
Since writing this article, it appears a PL/SQL JSON library has cropped up from France. I have yet to try it but will take a look and blog about it soon.


Further Information and Links...
Article: 'JSON: The Fat-Free Alternative to XML'
Javascript source file containing JSON parser
JSON homepage, including links to many server-side encoders
JSON wiki
JSON RFC 4627
Article: 'Speeding Up AJAX with JSON'
Carl Backstrom's APEX AJAX Site

15 comments:

Patrick Wolf said...

Hi Anthony,

interesting introduction into JSON and APEX!

I already planned to use JSON for my generic cascading LOV solution in the ApexLib Framework. Your article gives me another push to finally do it :-)

Thanks
Patrick

Anthony Rayner said...

Patrick,

No probs glad you liked it. Let me know if I can help at all.
Anthony.

Carl Backstrom said...

One of the things that we are building into APEX is a generic JSON output for the reporting engine , among other things.

If you have seen the APEX Worksheet Demo at ODTUG or even just the screenshots Dimitri

http://dgielis.blogspot.com/2007/06/sneak-preview-oracle-application.html

All of this new functionality / and a new polish to the SQL workshop will be JSON based.

So basically sprucing up your JSON knowledge now is going to give you a huge step ahead for future release.

Can you say JSON + APEX Collections :D it's going to be awesome !

Regards,
Carl

Carl Backstrom said...

Oh and there will be a APEX based JSON parser for things like collections , sql queries , collections , lists of items.

All sorts of goodness.

Carl

Anthony Rayner said...

Carl,

Nice one, thanks for the info. Yes I have see the APEX Worksheet Demo on Dimitri's blog and also at Oracle Develop where Marc Sewtz demo'd it. Looks good. I really like using JSON for it's simplicity and speed and think it's great that it will be more integrated in the future.

Looking forward to the APEX based JSON parser too.

Anthony.

Paul said...

This was fun, as soon as I found the extra space in the javascript I picked up on the copy past.

'APPLICATION_PROCESS= RETURN_MULTIPLE_ITEMS'
is not the same as
'APPLICATION_PROCESS=RETURN_MULTIPLE_ITEMS'

Thanks for taking the time to post this.

Anthony Rayner said...

Paul,

No worries, glad you enjoyed it!

Thanks for pointing that out to me, I need to look at tweaking the blogger theme to give me more width. It's too narrow and I keep on having to put carriage returns into the code to make it display properly, thus leading to issues like this.

When I get time I will sort the theme, in the mean time I have reformatted that code so the issue should not arise again.

Thanks again!
Anthony.

Pierre-Gilles said...

Hi,
There is a open source (!) simple Pl/sql package that do json stuff.

http://reseau.erasme.org/Librairie-JSON

Anthony Rayner said...

Pierre-Gilles,

Hi there. Yes I saw the link to your library on the JSON homepage a couple of weeks ago and updated my blog with a link to it.

Haven't had a chance to try it yet but I will and blog about it. As far as I'm aware this is the only PL/SQL / JSON library currently available.

Good work!
Anthony.

jurassicGeek said...

What about submitting the form you load with JSOB-formatted data...
I did the same then coudn't submit any more the data of my form.
As test case, if i do not load the form via Ajax, but simply fill it manually, i could do the submit.

Anthony Rayner said...

jurassicgeek,

This should definitely work with form submission, What is the error you're getting? Please send me some more info via the email listed on my profile page.

Anthony.

Angel Ortiz said...

Anthony:

Thanks for the great example on your site. I have implemented it no problems on my side. however, I would like to ask you if you think I could use queries instead of hard-coded values, as to bring me information from my database. It would go something like this:

declare
return_item_1 varchar2(100);
return_item_2 varchar2(100);
return_str varchar2(2000) := '';
begin
owa_util.mime_header('text/html', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;

if :P1_ACTIVATOR is not NULL then
return_item_1 := (Select EMPLOYEE_NUMBER from employees where SS = :P1_ACTIVATOR);
return_item_2 := (Select EMPLOYEE_NAME from employees where SS = :P1_ACTIVATOR);
elsif :P1_ACTIVATOR is NULL then
return_item_1 := NULL;
return_item_2 := NULL;
end if;

return_str := '{
"model":[
"'||return_item_1||'",
"'||return_item_2||'"
]
}';

htp.prn(return_str);

end;


---

So would this work, or do I need a special syntaxis to get the data from the database? Thanks for your support.

Anthony Rayner said...

Absolutely!! If we were just dealing with hard coded values then we could just stay on the client side, and there would be no need to go to the server.

Just adjust your syntax to something like this:

--------------
DECLARE
l_emp_number employees.employee_number%TYPE;
l_emp_name employees.employee_name%TYPE;
return_str VARCHAR2(2000) := '';
BEGIN
owa_util.mime_header('text/html', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;

IF :P1_ACTIVATOR IS NOT NULL THEN
SELECT employee_number
, employee_name
INTO l_emp_number
, l_emp_name
FROM employees
WHERE ss = :P1_ACTIVATOR;

ELSIF :P1_ACTIVATOR IS NULL THEN
l_emp_number := NULL;
l_emp_name := NULL;
END IF;

return_str := '{
"model":[
"'||l_emp_number||'",
"'||l_emp_name||'"]}';

htp.prn(return_str);

END;
--------------------

You should probably change the JSON element name to be something more meaningful than 'model', say 'emp' for example. If you do this, just bear in mind you will need to change the get_Multiple_Items() function to reference 'emp' instead of 'model'.

I am about to post a whole load of demos and update to this example, which will provide a generic JavaScript function to take care of a lot of the more common Ajax use-cases. Thus keeping all the business logic in the database. Keep posted!

Anthony.

Angel Ortiz said...

Thanks a lot Anthony!

This does exactly what I intended it to :). I will now implement it on the other field (EMPLOYEE_NUMBER) so I can populate the fields (Social Security, EMPLOYEE_NAME) when I provide an Employee Number. I will probably have another procedure that brings me the data I want, filtered by the employee number and activate the procedure on blur (like I did with the first).

I really look forward to your examples, as I get more experienced in APEX. Thanks again!

andry said...

json is a very interesting language to be used. very good tutorial and can hopefully help me in building json in the application that I created for this lecture. thank you