Just Add Water ...

Hi mashers,

I'm a lazy programmer. If I can avoid redoing code I will and I guess I've always been that way.  I've been a proponent of DRY (Don't Repeat Yourself) for many years ... long before that school of thought had a cool acronym like that. So what does this "work avoidance" ethic have to do with mashups.

Well, as anyone would have read in my recent blog, my latest mashup challenge was to set about exposing more of our corporate data in a kind of "basic pantry" to be used in quickly building new mashups. Our core business system (as a large veterinary practice) is built around a practice management application which currently uses MS Access as the backend. There are lots of tables that would be useful to expose, but building a mashup for each one seemed like a lot of repetition.

So I got to thinking, could I build a SQL mashup engine that would take SQL configurations in XML form, run the query and emit the results to be used in another mashup. Better still, what if I had another mashup that could be called to fetch the predefined SQL config, build the query string, and drop that into the SQL execution mashup. It sounded doable and I like the idea of being able to have fewer mashups.

As always, the devil is in the detail. It was pretty straightforward to build the SQL execution mashup (more work needed there yet) and another one to fetch and build a basic query statement, but I also wanted to be able to be able to add the odd WHERE statement into the query to filter some records. Mostly I anticipated returning all the data from a table, but sometimes I might not want that many records. Of course I could always build a static WHERE but I might want to be able to pass in a variable from time to time.

So here's where the trouble started. What I hoped I could do was include a WHERE in my SQL configuration that looked something like this: <SQLWhere>[client number] = $clientNo</SQLWhere> and then be able to pass in a variable to the build emml script called clientNo that contained the value (e.g. 1024) to be replaced in the string, i.e. producing [client number] = 1024.

It all sounded pretty straight forward, but the problem was this. Without declaring every possible variable I might need in the SQL query build mashup, how was I going to make an instance of the variable (e.g. clientNo) that held a value (e.g. 1025) present in the script at execution time. I knew I could pass in the data in the form <name>clientNo</name> and <value>1025</value> but then I wanted to turn that in to a real variable with that value.

I thought <template> would be the answer, perhaps combined with a tricky <assign> here or there, but no matter how hard I tried I just couldn't get the mashup to create an instance of clientNo = 1024 and resolve the WHERE clause for me. I then tried all manner of combinations of emml but with no success whatsoever, that is until I remembered my first love as a scripting language, REXX. I'd had this problem many years ago; the need to dynamically create a variable and set its content based on a given name and value.

Perhaps, my latest scripting love, Groovy, could come to my aid. What I needed was something like the INTERPRET statement in REXX that allowed you to build and execute script code at runtime. This let you build a string to create your variable, and then execute it. Groovy, came through for me once again, with the evaluate method.

With the Groovy evaluate method I found a way to build a string, then have it executed to instantiate a local mashup variable of the specified name that contained the provided value. With a bit more trickery using XPath string-join and concat, and the judicious use of quotes I could resolve my WHERE configuration string at run time with whatever variables/value I needed.

So, given

<SQLWhere>"[client number]=",$clientNo</SQLWhere>  and

<parmlist><parm><name>clientNo</name><value>1024</value></parm><parmlist> I could now produce

the following string: [client number] = 1024 which was just perfect for my WHERE clause in a built up SQL query.

Now I'm sure a fellow masher can probably come up with a creative way to do the same thing (a challenge perhaps) but this proved to be a very easy way to dynamically create mashup variables at runtime with a provided name and value. The only limitation is in the scope of the instantiated variable, but buried in a macro, it hardly matters. At least it's there when you need it.

Here's the Groovy script that does the magic. Perhaps we can  improve it further.

                    <!--
                        Using a bit of Groovy trickery we now instantiate a
                        mashup variable containing the supplied value. It's scope
                        is only within the macro though.
                     -->   
                    <script inputvariables="varName, varContent" type="groovy">
                        <![CDATA[evaluate("${varName} = varContent")]]>
                    </script>

Happy mashing everyone.

Cheers, Innes