Tuesday, October 25, 2005

AJAX Select List Code Generator

Carl Backstrom has provided some truly amazing HTML DB AJAX & DHTML examples on his demo page on htmldb.oracle.com. Most of them are documented, but they assume that you have more of a technical background than not.

With Carl’s blessings, I took one of his examples – AJAX Process on Demand – and created a working example of an AJAX-based Select List. The example is quite simple: select an Employee Name, and the Job field will automatically be populated with the corresponding Job from the EMP table. This is a common behavior in Oracle Forms (when a user select one thing, another field is auto-populated) and will make the transition for those coming from a Forms environment a bit easier.

To take it one step further, I created an AJAX code generator of sorts. Give it the required six fields, and it will automatically generate the PL/SQL Application Process, the JavaScript Event and the JavaScript function. Now you can harness the power of AJAX (or at least this one simple example) without knowing J or X!

30 comments:

Anonymous said...

Sorry to ask a newbie question, but in the context of HTML DB what does AJAX acheive? What does it improve upon? Why would you use it? What does the example demonstrate?

Partha said...

This is cool. Thanks for the example. Makes life so much simpler.., was not comfortable with the J and X part :-) These kind of examples helps people like me to get a good idea and get into the grove..;

I remember the workaround that I used in my first HTMLDB application with a select list with submit, I needed a dummy flag item and had to change all my validations to check the dummy flag item before validations and had to set the dummy flag when i wanted to save.

Scott said...

Newbie,

AJAX stands for Asynchronous JavaScript & XML. It's nothing new technology-wise, but the term itself is relatively new.

Think of AJAX as a set of technologies that allows your HTML applications (HTML DB and otherwise) to behave more like traditional Client/Server applications. Things like Drag & Drop (as implemented in either Google Maps or the HTML DB 2.0 Query Builder) and queries of the database without refreshing the entire page are possible using AJAX.

Like all new technologies, AJAX is still trying to "find its groove" in the everyday application. Places that I see it making a lot of sense is anytime you want to change just a portion of your page. Rather then refreshing the entire page, there are several ways that you can refresh just an item or two using AJAX.

In my example, I do just that. When you select an Employee Name, there is a simple query that executes in the background and retrives that Employee's respective Job from the database and places it in the appropriate field. If you view the HTML source, you will not find any of the Job titles, as I only load each one as its requested.

This is helpful for a couple of reasons: 1) Forms users are used to this type of behavior. Thus, using AJAX makes Forms users more comfortable transitioning to the web and HTML DB 2) It's more efficient. Imagine that there were thousands of users using this application; one simple query vs. a complete page reload will reduce the load on the server.

I hope this helps clear things up some.

Thanks,

- Scott -

Anonymous said...

Ah yes, it did.

I hope we can see more of it!

Thank you for the reply!

JO said...

I JUST SAY : WOOOOWWWWW!

Keep doing samples and code helpers like this one!!!!!

Paul Ashton said...

Hey Scott,

I just wanted to say that the work you and Carl (and the rest of the HTMLDB team) do to help the developer community to get more and more out of HTMLDB is fantastic.. This example is great and just what I needed to start to get to grips with AJAX.

I've been using HTMLDB for about a year and a half and am impressed with the rate at which its developing and yet maturing.

Cheers,
Paul.

Scott said...

I just wanted to say that the work you and Carl (and the rest of the HTMLDB team) do to help the developer community to get more and more out of HTMLDB is fantastic.. This example is great and just what I needed to start to get to grips with AJAX.

Thanks, Paul, but I am no longer with the HTML DB Development team or even with Oracle. However, I am still working with HTML DB on a daily basis with my own company now.

Thanks,

- Scott -

Rajan said...

AJAX is working with select list. The same will it work with popup list of values?

Rajan said...

AJAX is working with Select List. Will it work with popup list of values?

Scott said...

AJAX is working with Select List. Will it work with popup list of values?

Sure, you can use AJAX almost anywhere, really. The code, of course, would be slightly different.

- Scott -

Anonymous said...

Hi Scott,
This is awesome! Unfortunately we are still in 1.6...upgrading to 2.0 someday but of course I need this functionality now. Is it possible in 1.6 to populate a text field or requery a Select List based on the LOV w/o submitting?
Thanks,
Janel

Scott said...

This is awesome! Unfortunately we are still in 1.6...upgrading to 2.0 someday but of course I need this functionality now. Is it possible in 1.6 to populate a text field or requery a Select List based on the LOV w/o submitting?

It is, but I do not have the code off the top of my head. You can try to search the ApEx forum on OTN...

Thanks,

- Scott -

Anonymous said...

Scott,
as in the past your solutions are right on the mark. But, since I am a newbie i am not sure of what to put into the code generator for my application. If i ref an item on my page do i use the :Pxx_ITEM type of notation. I am actually looking for a solution just like Carls example but using your generator to achieve it. If you could post what you would use in the generator for your working example it might help. Thanks

Scott said...

...since I am a newbie i am not sure of what to put into the code generator for my application. If i ref an item on my page do i use the :Pxx_ITEM type of notation.

Simply click on the label for each item to get some help. Each item has help which spells out what to put in the field for the EMP example.

Thanks,

- Scott -

Anonymous said...

Hi, the post is old, but i hope, that someone will read it :)
Does ajax work with select, but in such a way, that it updates the content of the select list?
Imagine that i enter new name -> john, and press ok button. does the name 'john' appear inside the select, so that i can pick him?

Anonymous said...

Scott,
Keep up the good work. Your code really provided great introduction to ajax and apex. Thanks a lot.

Steven Ho
(Java developer Intern - Chicago)

Anonymous said...

Hi Scoot,
I've just follwed your ajax mempry tree tutorial, everything works fine apart from when i search for a node, the corresponding report displayed, but the tree doesn't expand accordingly.
Can you help?

Candy

Scott said...

Candy,

Can you put your application on htmldb.oracle.com, so that I can take a look?

Thanks,

- Scot t-

John said...

Scott,

I'm missing something - do you provide the code for this example? I see how it is being used but it doesn't help me learn how to do it.

John

Scott said...

I'm missing something - do you provide the code for this example? I see how it is being used but it doesn't help me learn how to do it.

John,

Not sure what exactly you're after. The sample application which I set up generates the Ajax code - you can then copy & paste it into your own application.

Thanks,

- Scott -

Anonymous said...

Scott,

Sorry - I figured out to use the code generator. Oddly, the problem I am having now is, I build the page and followed your instuctions, but when I select a name, I get that same name in the JOB field, instead of the job. I went back and made sure I used the code in all the right places. I'm stumped.

John

Scott said...

Sorry - I figured out to use the code generator. Oddly, the problem I am having now is, I build the page and followed your instuctions, but when I select a name, I get that same name in the JOB field, instead of the job. I went back and made sure I used the code in all the right places. I'm stumped.

Jon,

If you re-run the generator and click on the labels of each item and use the default values, then follow the steps, it should work.

If that still doesn't work, then please post your application to apex.oracle.com so that I can take a look.

Thanks,

- Scott -

Anonymous said...

Hmmm... I've checked and everything looks good. The only difference is my items start with P3.., but I took that into consideration in the code generator. I am still getting the NAME in the JOB field when I run the application and select a name from the Select List.

John

Scott said...

Hmmm... I've checked and everything looks good. The only difference is my items start with P3.., but I took that into consideration in the code generator. I am still getting the NAME in the JOB field when I run the application and select a name from the Select List.

Try using "job" as the Lookup Column name. It appears that there's a typo in the help.

Thanks,

- Scott -

Anonymous said...

Scott,

Using "job" in the Lookup Colmn Name actually made things worse. With that change I get the job title in the select list where names should be.

John

PS - Did you go to Syracuse U.? I grew up in Cortland, just down the interstate.

Scott said...

Using "job" in the Lookup Colmn Name actually made things worse. With that change I get the job title in the select list where names should be.

At this point, can you put your application on apex.oracle.com and send the credentials to scott@sumnertech.com so that I can take a look at it? There's not much else I can do...


PS - Did you go to Syracuse U.? I grew up in Cortland, just down the interstate.

Yes, I did - 1992-1996. Is there snow there yet? If anyplace will escape global warming, it will be Upstate NY! :)

Anonymous said...

I am living in NH now but I wouldn't doubt your theory on Upstate NY!!

I found the problem. In the getJob process this is the code that is to be placed in the process (step 2):

declare
l_ename varchar2(100);
begin
select ename into l_ename from emp
where ename = :P3_ENAME;
htp.prn(l_ename);
end;

When I substitute 'job' for 'ename' in the line:
select ename into l_ename from emp, it works correctly.

Has anyone else pointed this out? Seems odd to find something like this after this post has been out on the web as long as it has been out here.

John

Scott said...

I agree - figure someone would have noticed by now...

In any case, I've updated the example application so that it is correct.

Thanks for pointing it out.

- Scott -

Anonymous said...

Hello Scott,
Thanks for the excellent code generator. It works fine to populate one item. Can pls help me in populating more than one item in the form. In this case if I want to populate MGR, SAL as well as HIREDATE along with JOB, what changes shall I do? Any help would be highly appreciated. Thanks in advance.

Anonymous said...

Thank you for your work, I don't know why they don't have an easy way to do this in Apex, I hope you can create a similar generator for tabular forms too

-- Jasem