Lately my subway-reading materials seem to broadly spin around what it means to be an artist, and processes, and what’s involved in actually setting about the doing of your work, the making of your art. I crunched through Haruki Murakami’s memoir What I Talk About When I Talk About Running so quickly that I think I need to read it again (I’m looking forward to it, and I rarely re-read books), and I’ve since moved on to Art & Fear by David Bayles and Ted Orland. This is a particularly interesting subject for me as a song-writer and a programmer, although for really different (and probably obvious) reasons that I’m not going to go into right now, save to pull some choice quotes that I’ve found to be really profound.

This one is from Art & Fear:

Vision & Execution

More often, though, fears rise in those entirely appropriate (and frequently recurring) moments when vision races ahead of execution. Consider the story of the young student – well, David Bayles, to be exact – who began piano studies with a Master. After a few months’ practice, David lamented to his teacher, “But I can hear the music so much better in my head than I can get out of my fingers.”

To which the Master replied, “What makes you think that ever changes?”

That’s why they’re called Masters. When he raised David’s discovery from an expression of self-doubt to a simple observation of reality, uncertainty became an asset. Lessn for the day: vision is always ahead of execution — and it should be. Vision, Uncertainty, and Knowledge of Materials are inevitabilities that all artists must acknowledge and learn from: vision is always ahead of execution, knowledge of materials is your contact with reality, and uncertainty is a virtue.

I’ve been a big fan of jQuery for some time now, and one of my favorite plug-ins is Ryan Cramer’s asmSelect, which is a really cool alternative implementation of a multiple select control. There’s no swing tables, there’s no explaining to users how they need to hold down control (or command for mac users) while they click their desired options. There’s just a nice manageable list, to which you add selections using a drop down. There’s a nice example here, if you’re unfamiliar with this interface.

In any event, some of our clients were not really digging the Shuttle control available in Oracle’s Application Express. “Shuttle” is really just a pair of swing tables. So I figured, “why not set those back to normal select[multiple] elements and run the asmSelect plugin?” However, it wasn’t that easy, so I’ve written up how to get it going. I’m working in APEX 3.1.2, if you decide to follow along.

First you need to make sure you’ve got the select element set to multiple in the interface:

Next, add the needed jQuery libs to your application. There are a number of ways to do this, but since I’m not going for anything fancy, I went with the latest version of jQuery (1.3), the latest version of the asmSelect plugin, the included CSS, and avoiding any jQuery UI animation options:

• jquery-1.3.1.min.js
• jquery.asmselect.js
• jquery.asmselect.css
• jquery.noconflict.js

That last one is actually a quick little file I threw together to allow for jquery to be compatible with apex’s built-in use of prototype:

/ jquery no-conflict script, sets $() function to $j() /var $j = jQuery.noConflict();

Not much to it, pretty standard.

I attached them to the application itself (Shared Components → Static Files → Create, then select your app for the association). Once they are uploaded, you need to link them in from your page template. Head over to Shared Components → Templates and select your default page template for editing. You can jam this into the head section of the document template:

<link rel="stylesheet" href="#APP_IMAGES#jquery.asmselect.css" type="text/css" />
<script src="#APP_IMAGES#jquery-1.3.1.min.js"  type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.asmselect.js"  type="text/javascript"></script>
<script src="#APP_IMAGES#jquery.noconflict.js"  type="text/javascript"></script>

These statements will include the javascript and css files we uploaded into our app as static files. Below these, in the header template (or in the html header area of a specific page), we can add a script block and some code to activate the asmSelect plugin on every select[multiple] on our page:

<script type="text/javascript"> 
$j(document).ready(function() {
    // wire up the select multiples to be asmSelects
    $j("select[multiple]").asmSelect({ });
});
</script>

At this point, if you reload your application page, having saved your changes and such and there are no errors in the setup, you’ll see:

1. the original select show up and quickly disappear (asmSelect hides it)
2. the new asmSelect element

However, if you attempt to submit your form, it will explode on you. You’ll get a pretty heinous 404 error message saying that the page couldn’t be found. What’s happening here?

Not Found
The requested URL /pls/apex/wwv_flow.accept was not found on this server.

Oracle-Application-Server-10g/10.1.3.4.0 Oracle-HTTP-Server Server at apex.example.com Port 80

APEX is at heart a set of complex stored procedures made accessible over the web via mod_plsql running in Apache. If you’ve ever written a stored procedure for mod_plsql (htp.prn and all that), you’ve probably run into this problem before: if you send any params that the procedure doesn’t expect, mod_plsql will bomb out with a 404 error.

What’s happening to us here is that by using asmSelect we’ve added a parameter that’s not being expected by APEX. Every form element on the apex page matches an ITEM that you created on the page (like P1_PRODUCT_TYPE above). In this case, asmSelect hides the element represented by P1_PRODUCT_TYPE and drops select#asmSelect0 right next to it in the DOM.

So what do we do?

What can be added to the DOM can be taken away!

I tried faking out APEX by creating a corresponding page-level item with the same name and that didn’t work, so I just moved on to the next obvious thing: remove the asmSelect element when the form’s submit element fires. Here’s how I first tried it:

<script type="text/javascript"> 
$j(document).ready(function() {
    // wire up the select multiples to be asmSelects
    $j("select[multiple]").asmSelect({ });
    $j("#wwvFormFlow").submit(function() {
          $j(".asmSelect").remove();
        });
});
</script>

That didn’t quite work out, because APEX already has its own ideas about the form’s submit event. And honestly, I don’t really want to mess with APEX, I just want to work around it. So I decided on another trick we worked out some time ago, intercepting the submit event and then letting APEX do its thing afterward:

<script type="text/javascript"> 
$j(document).ready(function() {
    // wire up the select multiples to be asmSelects
    $j("select[multiple]").asmSelect({ });
    
    // subordinate the submit function in apex so we can supplant it
    function submitOverride(event) {
        // remove the asmSelects from the DOM so they don't mess up submitted params
        $j(".asmSelect").remove();
        
        // execute the original submit
        this._submit();
    }
    document.wwv_flow._submit = document.wwv_flow.submit;
    document.wwv_flow.submit = submitOverride;
    
});
</script>

This really did the trick, removing the asmSelect right after submit fires, sending up the correct form elements. And since asmSelect manages the original selection list as your are making changes, the selected items are preserved.

For one last bit of finesse, hide the original select element (in our case the item P1_PRODUCT_TYPE) on page load by setting style="display:none;" on the element, which makes your page load a bit smoother:

Works like a charm!

I needed a bit of SQL to gin up a series of sequential dates in Oracle that I could use in generating data for flash charts in APEX. It’s important to have a solid series of dates that you can LEFT OUTER JOIN against so that NULL values correspond to one of your series points, keeping all the data properly aligned.

In looking around for some good examples I found some way more complex date series tricks
, but didn’t see any article on the simple basics of doing a series of dates between a start and end date. Here’s what I found along the way:

-- notes for generating date sequences in oracle
-- 
-- gleaned from a pretty interesting article by Indrajit Agasti on generating
-- various series of working (non-weekend) days:
-- http://www.dba-oracle.com/t_test_data_date_generation_sql.htm
--
-- i'm sure this old hat for many oracle devs but i didn't see it out there 
-- anywhere so i figured a quick ref of notes i took in the process might 
-- be useful to somebody else

-- to knock a string into a date object:
SELECT TO_DATE('01-JUN-2008') FROM DUAL;

-- getting an integer difference between two dates:
SELECT TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008') FROM DUAL;

-- generating a sequential series using dim (this is odd, admittedly)
-- 0, 1, 2 ... 9  (n - 1)
SELECT dim FROM DUAL
MODEL
DIMENSION BY (0 dim) MEASURES (0 rnum)
RULES ITERATE (10) (
rnum [ITERATION_NUMBER] = ITERATION_NUMBER
);

-- easier, returns 1,1,1... (10 rows), so use rownum to get increasing integers
SELECT ROWNUM FROM (
  SELECT 1 FROM DUAL
  CONNECT BY LEVEL <= 10
);

-- drop in our dates
-- returns 1, 2, 3 ... 31
SELECT ROWNUM FROM (
  SELECT 1 FROM DUAL
  CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008'))
);

-- full date set then: (may 1st, to may 31st)
SELECT TO_DATE('01-MAY-2008') + ROWNUM - 1
FROM (
  SELECT ROWNUM FROM (
    SELECT 1 FROM DUAL
    CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008'))
  )
);

-- being inclusive of the end-date (june 1st) 
SELECT ROWNUM FROM (
  SELECT 1 FROM DUAL
  CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008') + 1)
)

We’ve mentioned in the past that we’re no longer supporting or developing Strip on the PalmOS platform. Even so, people still ask about looking at the code and making updates. Strip for Palm OS is free and open-source software, so we’ve pushed the code up to Github to facilitate any further development others may wish to do. It’s the beauty of open source – feel free to fork it, hack it, send pull requests, or release your own version. Fair warning – the code was written a long time ago on an ancient (in relative terms) platform so it might leave something to desired if you’re used to more modern toolkits!

It’s been in the news for a few days: two of the MySQL execs, Martin Mickos and Monty Widenus, are leaving Sun Microsystems, Sun having bought and absorbed MySQL AB.

I’ve always been a Sun fan (Solaris FTW), but it’s a well-known joke that being bought-out by Sun is the kiss of death. So what happens to MySQL now, and what happens in the open-source database space? Drizzle, an open-source and derivative project led by Brian Aker, doesn’t seem to have clearly defined its space (“the cloud?”), with people wondering if it’s really just a SQLite competitor (alternative might be a better turn of phrase because it’s got a long way to go before it could “compete” with SQLite). Speculation aside, what is known is that it drops many of the features that make MySQL a full-featured relational database.

Ladies and gentlemen, I think that we have a winner, the elephant in the room: PostgreSQL. Somebody’s gotta say it. Sorry to gloat. PostgreSQL is and has been “the most advanced open-source database,” long before these MySQL upstarts came along.

In the end, MySQL was never a completely open project. MySQL AB offered a GPL version but only included code that they owned the copyright to. Contributing developers wouldn’t see changes included in the core unless they were rewritten by MySQL AB or donated (read sign over their IP) to the company. It’s pretty heinous to tout such a model as an open-source success story, and it looks like the opposite is now true: MySQL is clearly floundering.