Thursday, 31 March 2011

AJAX File Upload


Ok, so I looked into this a while ago, and had all the idea's in my head, but just never got around to putting something together.

Some of the newer HTML5 File API's allow you to access the file contents (base64 is what we like) - by using the FileReader object. Unfortunately, not all browsers have support for it, so you would not be able to use it accross the board. There are a lot of resources on the topic. If interested, check out: http://www.html5rocks.com/tutorials/file/dndfiles/ and https://developer.mozilla.org/en/Using_files_from_web_applications. There is another plugin out there, which supports ajax uploads with IE, but unfortunately it's commercial; which is another reason I was keen to get this happening.

So anyway, I've created a demo of this - which you can check out at: http://apex.oracle.com/pls/apex/f?p=45448:afu

I used the following table to get started:


CREATE TABLE PLUGIN_BLOB 
(
  ID NUMBER NOT NULL 
, MIME_TYPE VARCHAR2(200) 
, FILENAME VARCHAR2(200) 
, DATA BLOB 
, SOME_FK NUMBER 
, CONSTRAINT PLUGIN_BLOB_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);

create sequence plugin_blob_seq;

CREATE OR REPLACE TRIGGER BI_PLUGIN_BLOB before
  INSERT ON "PLUGIN_BLOB" FOR EACH row BEGIN IF inserting THEN IF :NEW."ID" IS NULL THEN
  SELECT PLUGIN_BLOB_SEQ.nextval INTO :NEW."ID" FROM dual;
END IF;
END IF;

I wont get to deep in to the nitty gritty, but basically, when you set it up, you specify some fields (most of which you can tell is from the table defined above):


At the moment, it has attributes to specify that the table has a foreign key column; I was also going to return the primary key from the insert statements into a page item (declared in an attribute), but I was having some issues in that no settings were available in the page item once I got to 9 attributes - so I let it pass for now. It might be that my development box is still on 4.01.00.03 - I will sus it out sometime later, or someone else can pickup from where i've left off ;-) The other thing is the fact that I've hard coded the button style and label, which may like to be customized.

So, if you want to test that the foreign key insertion is working properly, just set a number value to P6_SOME_FK through the URL, and it too will be populated - at least I hope so ;-)

I've attached one file thus far - which is the plugin export file. File: item_type_plugin_ts_ajaxfileupload.sql

This little project also gave me the opportunity to learn more about the apex.ajax.clob object - I saw someone post about Carls blog on the OTN forums, that contained the information about it; Pretty handy to know! See: http://carlback.blogspot.com/2008/04/new-stuff-4-over-head-with-clob.html

17 comments:

  1. hey, could you send me the sql plugin export file (or even the plugin itself)

    i´d want to make some experiments with it and i think it would be very useful

    regards

    ReplyDelete
    Replies
    1. Hi,

      Actually, the plugin file is in the sample application - it's the first file uploaded to that page, if you sort by ID.

      I've chucked the sql in a Gist: https://gist.github.com/trent-/5606476

      Delete
  2. Hi , do you have any idea why is not working if i have two upload buttons on the same page bud different regions ?
    Simptoms
    Auto refresh not working.
    Upload goes to the 2nd upload button if i select at the first one.

    Thanks in advance !

    ReplyDelete
  3. Hey there,

    This plugin is exactly what I'm looking for, and have started using it however, I have noticed that it appears to corrupt .pdf files.

    Just wondering if you had any solutions or input on fixing this issue?

    Regards,

    Josh

    ReplyDelete
    Replies
    1. Hi Josh, Just went to sus this out but couldn't reproduce it. Do you have any particular pdf that gets corrupted that I could test with?

      Delete
    2. Hey Trent

      I've tried many different pdf files, so not just one in particular. I even tried uploading some to your apex example application, which came up with the same issue. Same with downloading other .pdf files that were already uploaded.

      However, I have tried it again, presumably using the files you tested with, and it works? Now even my uploads, download correctly without corruption.

      Just wondering if you have changed anything, or if there are permissions or specific settings required for pdf or the plugin in general?

      Regards,

      Josh

      P.S - thanks for the speedy reply, even if mine was not :)

      Delete
    3. Hi Josh, Unfortunately I didn't change anything. I cleaned up the table then started uploading a few pdf's. So not really sure what the story is there!

      Delete
    4. Can you confirm this plugin is working with on Apex 4.2.5+ and listener 2.0+?

      I see it has not been added to your updated project as of yet. :)

      Thanks.

      Delete
    5. Hi Josh,

      I tested on my ORDS VM last night. All seems to be working, apart from only supporting rather small file uploads - but that's something to do with the servlet container configuration, I believe. Which shows there seems to be a slight bug there, in that if it encounters a http 500 with the posting of data, it will just insert the file data from the last successful post to the collection.

      Also, actually, I noticed on your first comment that it was missing from my new and improved workspace! I added it over the weekend :). See here: https://apex.oracle.com/pls/apex/f?p=14882:22 .

      Cheers,
      Trent

      Delete
  4. Hi,

    there seems to be a limitation wrt to the max filesize. This is due to the fact that moplsql has the parameter PlsqlMaxParameters set to 2000 by default, which limits the max filesize to be around 8MB. (2000 x 4kB) This plugin passes file content as parameters to the http request to modplsql. Is there a workaround/solution to this (apart from increasing the number of paramaters to modplsql)?

    ReplyDelete
    Replies
    1. Hi Miroslav,

      Around line 98 of the plugin code, I'm using a call to an undocumented API as is on Carls blog here: http://carlback.blogspot.com.au/2008/04/new-stuff-4-over-head-with-clob.html . This performs a single AJAX POST which seems to be exceeding the limit you described.

      The alternatives of course are adding your own on demand process to write append the clob in the collection (and use apex.server.process to perform AJAX calls in chunks). I didn't go this route simply because it's something that couldn't be packaged in the plugin.

      Another option would be figuring a way to interface with the Enkitec Clob Load plugin.

      Sorry I couldn't offer a better solution for the time being.

      Cheers,
      Trent

      Delete
    2. Hi Miroslav,

      I just moved this plugin into a github repo, and re-worked the code a bit to support larger file uploads (including moving the code into a package). All seems to be working.

      If you'd like to give the newer version a try, the repo is here: https://github.com/trent-/apex-ajax-upload

      I haven't had a chance to create proper documentation, or install instructions yet. But basically, install the package in src, and then the plugin :-)

      Cheers,
      Trent

      Delete
  5. hy, how make download per row?

    ReplyDelete
  6. Hi Trent, Thanks for this post and publishing the plugin on GitHub. I'm interested to learn what is the maximum file size that is supported by the plugin. Concerning the commit "Support for large uploads" from December 4th, 2014 could explain briefly what changes you made to support larger uploads? Thanks a lot, Andre

    ReplyDelete
    Replies
    1. Hi Andre,
      When I was testing on an ORDS instance I had running I noticed only very small uploads seemed to work. I'm talking 100KB or less. I did see some comments about changing some server side parameters, but I wanted to avoid that if possible.
      I was using the un-documented (apex.ajax.clob) so I stopped using that, and starting using my own AJAX call posting to the plugin itself. I saw some examples of posting f01 as an array, so I followed that design, and it seemed to work well.
      When I say large, I think I tested upto 10MB with success.
      Cheers,

      Delete
  7. Hi Trent,
    Can you please let me know how to have download link in the report to download the files uploaded using this plugin.I tried to use WPG_DOCLOAD package but I noticed junk characters in the downloaded files .I am restricted to use this package as I have some problems using other approach.(Blob format in report attributes).

    Please help.

    You can also mail me at ashwinrao286@gmail.com

    Thanks,
    Ashwin

    ReplyDelete
    Replies
    1. Hi Ashwin,
      I have used the blob format in report attributes in my example, and have never noticed any junk in the files. I'm not sure what your problems are with that technique, but in any case I've uploaded an export the sample app just now: https://gist.github.com/tschf/5571f96bb2bab5f407db

      I also just tried with the wpg_docload without issue: https://gist.github.com/tschf/38a18f50ef8d313970b6

      Must be something wrong with the upload if it contains junk.
      Cheers,

      Delete