Web application frameworks like RubyOnRails and Oracle’s Application Express (APEX) are generally built as full suites providing the most common functions of web frameworks in a way that’s extensible so that you don’t have to roll your own. They are incredible products, both great for what they do. But in the real world, many businesses have widely diverse data sources in their environments, and sometimes you’ll have to mix and match.

Case in point: in one of our clients’ environments there’s an instance of APEX providing data reporting and there’s a separate web app for manipulating directory services; both are protected by a single-sign-on solution. If you log into one, you have the cookie that allows you into the other without having to log in again. A recent change requested of the web-app was that a particular text field be given type-ahead suggest / auto-complete functionality for a particular piece of data stored in the Oracle DB schema atop which the APEX app sits.

At first I looked up a number of the various posted ways on how to do an auto-complete in APEX alone, just to get the ball rolling and see what functionality was there. There’s a lot of good information out there in the Application Express section or the OTN forums. I figured at first that I ought to be able to code up the server-side processing in APEX using an On Demand process (basically a bit of PL/SQL) to respond to the very simple, one-parameter request of a type ahead. I figured on the client side I’d just use Ajax.Autocompleter from the script.aculo.us library to keep things simple for the web-app, too.

This ended up tanking due to how APEX handles requests through mod_plsql. To run an On-Demand application process the calling request has to contain a valid session ID number in the query string. Not to mention I’d have to hack up Autocompleter a bit to supply the param name and value in APEX’s tortured query string syntax (/pls/f?p:page_id:session_id::::param,param:value,value:etcetcetc).

But I didn’t shy away from the task, I set about trying to figure out how I could first grab a session ID, import it into the client side javascript and use that to dynamically construct the URL and params. It was ugly, and I never did get that session ID cleanly.

But as you can see in that thread, Louis-Guillaume suggested a much simpler solution: since we’re using mod_plsql anyway (APEX runs inside it), why not just go old school and create a stored procedure to respond to the Autocompleter call?

It’s basically the same code as the On-Demand process. You create your stored procedure, set up a DAD connection string for mod_plsql (this is done in marvel.conf or dads.conf in Oracle HTTP Server (Apache!)), and call it like thus:

http://example.com//StoredProcName?paramName=paramValue

The parameters should literally match what’s defined for the parameters on the PL/SQL procedure. Leaving some off is fine (as long as you set defaults and account for the possibility), but adding extra undeclared parameters causes a problem, as we’ll see in a moment.

So here’s the stored procedure:

CREATE OR REPLACE PROCEDURE autocomplete_for_empname( p_search_text IN context.val%TYPE DEFAULT NULL ) 
AS
  v_employee_row   employees%ROWTYPE;
  v_count NUMBER := 0;
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 p_search_text IS NOT NULL THEN
    HTP.prn ('<ul>');
  
    FOR v_context_row IN
      (SELECT e.* FROM employees e WHERE LOWER(e.name) LIKE '%' || LOWER( p_search_text ) || '%' AND ROWNUM < 11 ORDER BY c.val)
    LOOP
      v_count := v_count + 1;
      HTP.prn ('<li>' || v_employee_row.val || '</li>');
    END LOOP;
  END IF;
  
  IF v_count = 0 THEN
    HTP.prn ( '<li>No results found</li>' );
  END IF;
  
  HTP.prn ('</ul>');
EXCEPTION WHEN OTHERS THEN
  HTP.p('Error: ' || SQLERRM);
END autocomplete_for_empname;

What this code does is provide the unordered list expected by Ajax.Autocompleter. We can call the stored proc like this to test the results:

http://example.com//autocomplete_for_empname?p_search_text=jon

And we’ll get results like:

<ul>
<li>Jonathan Myers</li>
<li>Jones Johnson</li>
<li>Jon From Garfield</li>
... &c. ...
</ul>

The constructor for the Autocompleter was an easy setup:

  <input type="text" id="autocomplete" name="p_search_text" size="50" />
  <div id="autocomplete_choices" class="autocomplete"></div>
  <script type="text/javascript">
    new Ajax.Autocompleter("autocomplete", "autocomplete_choices", "http://example.com/our_dad_name/autocomplete_for_empname", {
      paramName: "p_search_text", 
      minChars: 3,
      method: 'get'
    });
  </script>

There was one final hang-up. Even though I was getting the correct responses by hitting autocomplete_for_empname directly when testing from my browser, Ajax.Autocompleter was getting a 404 response. I took a look at the request being sent in Firebug, and it turns out that with every Ajax request, Prototype, the supporting library, adds an extra ‘_’ parameter with no value.

I had to open up the prototype.js library and comment out this line in the Ajax.request method:

  request: function(url) {
    var parameters = this.options.parameters || '';
    // if (parameters.length > 0) parameters += '&_='; 

Then, all was right with the world. Turns out the extra param is included to get around an old bug in Safari 2.x#.

So there you have it. If you want to do an external autocomplete against data in your APEX schema, use Ajax.Autocompleter and a stored procedure over mod_plsql.

An article on how to create full web apps using OWA and mod_plsql on Oracle from 11 years ago. Brutal!

You kids don’t know how good you’ve got it.

My friend Matt Jones is one of the design leads on Dopplr, “an online tool for business travelers.” It’s a rather slick utility for anybody who does a lot of traveling and wants to share that information with others, connect up in various cities, track their travels, etc. That’s not all it does, but those are the very basics of it.

Anyway, Matt has an excited post out on their blog about some new features they just added – mainly the ability for you to not only send in your trip information via E-mail, SMS, or Twitter, but you can literally forward eTickets and various other itinerary confirmations from airlines and bookers straight to Dopplr, and have it enter all the trip info for you. Check out the slick video demonstration!

This is a huge ease-of-use improvement to their already great service, cutting down on double entry and making manual entry much easier. If you’re a reader of our site, or you use our tools, you’ll know we’re total geeks for streaming information into tools and using command-line interfaces. Nice job, fellas!

We use the Rails auto_complete plugin in both PingMe and Tempo, and while it’s incredibly handy, it takes a little bit of hacking to use it on a page where it will be used more than once. In the main time screen on Tempo any of the entries can be opened for editing, meaning more than one can be open at once, and each has its own auto complete field for tags:

Similarly in PingMe, we’ve got a list of pings on the screen, each a potential edit form (more than one can be edited at once), with auto complete on the tags field:

In this situation, when each form has the text_field_with_auto_complete in use, each has the same DOM ID: object_method, so ping_tags in PingMe and entry_tags in Tempo. Screwy things happen when you try to remotely update a DOM ID used more than once on a page, and those screwy things tend to change based on your browser, but basically the auto_complete plugin out-of-the-box will not work in this scenario.

We got around this by doing a bit of monkey patching to the auto complete plugin. This is what the initial text_field_with_auto_complete method looks like inside the plugin’s auto_complete_macros_helper.rb:

def text_field_with_auto_complete(object, method, tag_options = {}, completion_options = {})
    (completion_options[:skip_style] ? "" : auto_complete_stylesheet) +
    text_field(object, method, tag_options) +
    content_tag("div", "", :id => "#{object}_#{method}_auto_complete", :class => "auto_complete") +
    auto_complete_field("#{object}_#{method}", { :url => { :action => "auto_complete_for_#{object}_#{method}" } }.update(completion_options))
end

What we do is add an extra, optional parameter to this method that accepts an id, that we then patch in as the dom id we’ll use:

module AutoCompleteMacrosHelper
  def text_field_with_auto_complete(object, method, tag_options = {}, completion_options = {}, object_id = nil)
    field_id = (object_id.nil?) ? "#{object}_#{method}" : "#{object}_#{object_id}_#{method}"
    (completion_options[:skip_style] ? "" : auto_complete_stylesheet) +
    text_field(object, method, tag_options.merge({:id => field_id})) +
    content_tag("div", "", :id => "#{field_id}_auto_complete", :class => "auto_complete") +
    auto_complete_field(field_id, { :url => { :action => "auto_complete_for_#{object}_#{method}" } }.update(completion_options))
  end
end

Now, on the form/template itself, we use this to generate the text field and type ahead div (we’re using HAML, not RHTML):

= text_field_with_auto_complete :entry, :tag_s, {:size => 50}, { :indicator => "entry_#{entry.id}_tag_s_form_loader", :frequency => 0.4, :tokens => ' ' }, entry.id
= image_tag 'loading.gif', {:id => "entry_#{entry.id}_tag_s_form_loader", :style => 'display: none;'}
.auto_complete{:id => "entry_#{entry.id}_tag_s_auto_complete", :style => "display: none;"}

You’ll notice that each dom id is of the format entry_#id_tag_s_*.

In our controller, we have a few things that we need, nothing that complex:

auto_complete_for :entry, :tag_s

  def auto_complete_for_entry_tag_s
    unless params[:entry].nil? || params[:entry][:tag_s].blank?
      tags = @current_user.tags.collect{|tag| tag.name.downcase.index(params[:ping][:tag_s].downcase) == 0 ? tag.name : nil}.compact
      render :partial => 'autocomplete', :locals => { :items => tags }
    end
  end

Finally, we create a partial called ‘_autocomplete.haml’ that cranks through the items when the suggestions are displayed:

%ul.autocomplete_list
  - items.each do |item|
    %li.autocomplete_item= h item

Happily, this isn’t very different from the way the plugin recommends you do things, but allows you to support more than one field on the page at once when you have multiple instances of an object. I’m actually a bit surprised that there isn’t an inherent facility in the plugin for this, but it’s good learning for anyone looking to see how it’s done.

I should note that the above doesn’t denote how we really select out the set of tags for a user in Tempo, but it’s a simple example of how one might do it ;-)

With the new smart timers, I no longer need PingMe to remind me to sum up my time for the day. Thanks, old buddy!

(Although the street cleaning reminders are still pretty clutch…)