Another APEX Listener EA - More knobs to turn

There's a new listener EA released today go grab it and try out.  There's 2 real changes.  First the MIME_TYPE on file uploads is fixed. 

 The second is a result of sitting down at RMOUG last week and talking over the excel parsing with Scott.   That conversation resulted in a few new knobs to change some configuration options. 


1) I found an API which takes the item_id and gives back the item_name so the collection will default to the ITEM_NAME of the file upload item.

2) Files ending with .xls OR .xlsx will be processed.

3) Several new configuration choices:
  a) First is a new config option to have 1 sheet of the workbook per collection.  This is controlled by
<entry key="apex.excel2collection.onecollection">true|false</entry>

   b) Then, you can essentially hard code a collection name to be used for all excel uploads.
<entry key="apex.excel2collection.name">MY_COLLECTION_NAME</entry>

   c)  Lastly, an option to use the sheet name vs. a sequential WORKSHEET:1,2,3....
<entry key="apex.excel2collection.useSheetName">true|false</entry>

So, here's a recap of excel parsing with the APEX Listener

Step 1: Turn it on
    <entry key="apex.excel2collection">true</entry>
Step 2:
    The request of the upload must be : xls2collection

This will get you ONE collection which contains all sheets in the workbook and the collection will be named the APEX Item name.

Options:
To have ONE collection per sheet:

     <entry key="apex.excel2collection.onecollection">false</entry>

To have a sequential sheet name vs. the sheet name from excel.
    <entry key="apex.excel2collection.useSheetName">false</entry>

To specify the name of the collection for ALL collections created via Excel upload

    <entry key="apex.excel2collection.name">MY_COLLECTION_NAME</entry>


The results of all of these upload are a collection which is formed as this:
The default of just turning it on will look like this.

CollectionName - Item Name
C001                 - Sheet Name
C002..N            - Data from the worksheet

Yet another APEX Listener EA

We just pushed the next EA for the Application Express Listener. There's a few base bugs fixes in this like the file upload issue people on the forums posted about. The other significant bug fixed is when there's multiple listeners in one webserver. The listener will now check the following location in this order for a config file.

1)  $HOME/[MOUNT POINT]/apex-config.xml
2)  ${config.dir}/[MOUNT POINT]/apex-config.xml ( from web.xml )
3)  ${java.io.tmpdir}/[MOUNT POINT]/apex-config.xml  (default for new installs )
4)  $HOME/apex-config.xml
5)  ${config.dir}/apex-config.xml ( from web.xml )
6)  ${java.io.tmpdir}/APEX/apex-config.xml  (default for new installs )

Now for some new things.

1)    A new /listenerAdmin which gives realtime stats.  It's a long ways from the end look of the page but it give stats.  This is protected by a role named "listenerAdmin".  Users and roles are setup in the webserver. To setup this role in tomcat, edit the conf/tomcat-users.xml and add something like this:

<tomcat-users>
<role rolename="listenerAdmin"></role></pre>
<user password="klrice" roles="listenerAdmin" username="klrice"></user>
</tomcat-users>

Here's an example from the tryapexnow.com site:

Current Time: Thu Feb 11 20:08:40 EST 2010
Server uptime: 50 Days 07:16:23.966
Total accesses: 1449806.0
Total Traffic: 21.85G
Total File Downloads: 86029
Total Errors: 3678
Total Processing Time: 10 Days 21:07:36.838


2) There's now a mechanism to log the full db requests.  This is a step towards having the requests repayable in the Unit Tester.
To begin with this here's the 2 lines needed in the apex-config.xml

 <entry key="apex.log.enable">false</entry>
 <entry key="apex.log.procedure">log_proc</entry>

A sample of the required signature is in the WEB-XML/sample_capture.sql


3) The listener can now take a *.xls file that is uploaded and convert it into a collection. Here's the steps to try this out.
a)  This needs to be turned on in the apex-config.xml
<entry key="apex.excel2collection">true</entry>

b) The file upload page must have a request of xls2collection.

You should get a collection of the name of the item and the C001 is the name of the sheet in the xls file.  Here's an example.