Multiple File Upload with jQuery and APEX Listener

Did you ever want to do this?

Envious of the fancy multiple image upload functionality I saw from Google+, I set out to do the same. This is more of a log of observations than a full recipe. There is no APEX involved but the listener. To follow this you have to have an APEX Listener v1.1 (or higher) up and running against an Oracle 10g (or higher) database. This will not work with Internet Explorer or Opera (not multiple files), I have only tested on Google Chrome.

jQuery to the Rescue
jQuery has a lot (capital L type of lot) of plugins, one really nice I stumbled upon was Sebastian Tschan's jQuery-file-upload. Demo is here, download is here, documentation is here.

Setup Database Objects
The images has to go somewhere, and I created an images table in the hr-schema:

   --drop table images cascade constraints;
   --drop sequence images_pk_seq;

   create table images (
      image_id        number,
      content_type    varchar2(255),
      filename        varchar2(4000),
      image           blob,
      constraint img_pk primary key (image_id) using index)
   /

   create sequence images_pk_seq;

   create or replace trigger trg_images_bi 
   before insert on images 
   referencing new as new old as old for each row
   declare
      l_new_id number;
   begin
      if :new.image_id is null then
         select images_pk_seq.nextval into l_new_id from dual;
         :new.image_id := l_new_id;
      end if;
   exception
   when others then
      raise;
   end trg_images_bi;
   /

   alter trigger trg_images_bi enable;

   grant all on hr.images to APEX_PUBLIC_USER;

You have to grant insert/delete at the least to the user defined in the listener (normally APEX_PUBLIC_USER).

Setup Imagehandler Resource Template
This is where the actual data manipulation is going to happen. You can import all resource templates in this example by importing this file: resources.zip.

Log into listenerAdmin (http://myhost/apex/listenerAdmin).

• Click Resource Template
• Click Add Resource Template
• Click Add Handler, and choose method POST
• The Post handler will handle all uploads and inserts
• Click Add Parameter, and set the following: status, X-APEX-STATUS-CODE, Header, Out, Integer
• Click Add Parameter, and set the following: location, X-APEX-FORWARD, Header, Out, String
• Click Add Parameter, and set the following: file, filename, Header, In, String (This should actually map to the filename, and be inserted with the rest of the data. For some reason, this does not work, and I'm too lazy to find out why...)
• Add the data as seen below, and click Save

   declare
      l_image_id number;
   begin
      insert into hr.images (content_type, image, filename) values (:contentType, :body, :file) returning image_id into l_image_id;
      commit;
      /* This status will actually not be seen, as we have to redirect to get control of response */
      :status := 201;
      :location := 'imagehandler/response/'||l_image_id;
   end;

• Click the imagehandler resource template
• Click Add Handler
• Choose method DELETE
• Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
• Click Add Parameter, and set the following: status, X-APEX-STATUS-CODE, Header, OUT, Integer
• Add the data as seen below, click Save

   begin
      delete from hr.images where image_id = :imageid; 
      commit;
      :status := 200;
   end;

• Click the imagehandler resource template
• Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
• Click the GET tab Add the code as seen below, click Save

   select content_type, image
   from   hr.images
   where  image_id = :imageid

Setup Imagehandler Response Resource Template
I have yet to find a way to get a PL/SQL Block resource to respond with a json array, only single json attributes. It is a shame really, giving control of the response to the developer would have helped a lot. As it stands now, the client will not get a proper response (201 - created), and we have to add an extra resource template. If there is a way, please do tell, the docs gives nada here.

This jQuery plugin requires a json array, and in order to do that, we have to cheat.

• Click Add Resource
• Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
• Template Add the code as seen below, click Save

   select 'application/json','[{"name":"'||:imageid||'.jpg","size":'||dbms_lob.getlength(img.image)||',"url":"\/apex\/imagehandler\/'||image_id||'","thumbnail_url":"\/apex\/imagehandler\/'||image_id||'","delete_url":"\/apex\/imagehandler/'||image_id||'","delete_type":"DELETE"}]'
   from hr.images img
   where img.image_id = :imageid

Looks a bit greek, I know. You can look at the server response for more details, but basically you are returning file name, download url, thumbnail url and delete uri/method in a json array.

Setup jQuery Plugin
Nothing to it really, download and unzip the archive somewhere reachable on a web server or appserver. In this example I unzipped and renamed the folder so that I could reach the example/index.html on http://myhost/js/multiupload/example/index.html.

You have to make one change in the example/index.html to make it work: Point the form action to your image handler:

<form action="/apex/imagehandler/0" method="POST" enctype="multipart/form-data"> 

Notice how we have to add {imageid} at the end, parameters are not optional (at least not in this version of the listener), so if it is left out, it will not match the resource templates URI pattern. 

Ready for a test run! 

Some notes on working with the Application Express Listener

• The documentation is a bit lacking (to say the least)
• Debugging is hard, 500 server error does not say much

This is a very simplistic example, there are no security involved, and it is not embedded into an APEX page. Anyhow,  as a quick example, it (barely) serves the purpose.