Query results as SQL function arguments

I kind of discovered this by accident. I was going through a manual multi step process to convert some numeric IP addresses in the database to a text dotted quad format, for input into a command line load testing script. I was finding it tedious.

The thing is I had SQL functions available to convert numeric to dotted quad and dotted quad to numeric IP addresses. In the database device table we have the IP address as numeric.

The device table has a unique index on serial number. I was using serial number to identify the devices. What I wanted to do was for a given serial number get the IP address in dotted quad format.

Originally I used a two step approach. First get the numeric IP address for the device serial number, in this example "serial1000".

select ipaddress from device where serial_number = 'serial1000';

Then copy that numeric IP address query result into a second query.

select numberToDottedQuad( result_from_above_query ) from dual;

That worked well enough but it was slow, manual and tedious all of the copying and
pasting. I wanted to combine them into a single query so I only had to enter the serial number and could get the dotted quad IP from that.

I couldn't figure out how to get the original query result to be the argument to the numberToDottedQuad() function. I tried a bit then gave up. This is what the DB wouldn't allow, syntax error.

select numberToDottedQuad(select ipaddress from device where serial_number = 'serial1000') from dual;

After a while though it kind of just came to me. Use the same style syntax as insert .. select statements. Nest the inner result in parenthesis. That was the aha moment and I set back to it. This worked.

select numberToDottedQuad((select ipaddress from device where serial_number = 'serial1000')) from dual;

The trick is that you have to use parenthesis on the inner query so the SQL engine evaluates that first, then it can feed it as the input to the outer query. Which makes sense. This was nice because essentially the same syntax can be used for both Oracle and SQL Server [except the dual part but that's trivial to take out].

If I had created a separate stored function to run the first query and feed the result into the second function then it would have meant separate code for Oracle and SQL Server. This way I didn't have to create a stored function (get the database to do the work) and I can use the same SQL query on Oracle and SQL Server.

Struts and JSP

I've spent the last couple of weeks doing something that I'd avoided for the last four years. I developed some Web pages in Java using Struts and JSP.

You have to know the history on it. Back at Core Networks in 2004 there was this core of around 5 Java developers who had been with the company for a little over a year on a next generation product. Additionally there were around 12 existing pre Java team who were working on various legacy products like CoreOS and point solutions like CoreMeter and others.

In 2004 we had this important business opportunity around a big Java/J2EE project. So the old guard joined together with the newer Java developers to create a unified team. What then happened was some of the people who joined the Java team had to create the GUI pages. One of the new Java people had some stuff set up using this exciting technology we'd heard of called Struts.

Well the Struts/JSP web pages were a disaster for the new developers. Previously we used PHP to create pages for our GUIs and it took around 1 developer day to create a basic page with data and a form. We used SOAP calls to both obtain the data to display and to invoke the change actions when the user clicked Submit on the HTML forms. It worked pretty well. The PHP UI developers and SOAP API backend developers could work independantly and in parallel.

Anyway the switch to Struts has horrible for the new Java developers. It was now taking a week or more to create each page in the GUI! The output was flakey and nobody seemed able to understand how it worked or how to get anything that was broken fixed. I would go by the printer for something of my own and see these e-mail printouts with the title "Struts hell"


But on this project with a new company I had to do some web pages so I delved into struts for the first time with some trepidation. I started off by reading the Struts Survival Guide which was very good, especially chapter 2. From there I was able to grasp enough about MVC to get started and understand that Struts is generally not concerned with the presentation aspect of things.

There were some rough parts and it was hard at times but I was able to get the pages to come up. It took a while due to all of the learning curve. Besides the struff you need to learn around struts like Actions, Forms, execute(), validate(), struts-config.xml there is so much more you have to grasp at least some of like JSP, HTML, JavaScript. We aren't using JSTL a whole lot yet in the existing code which is a bit surprising but at least I didn't have to grasp that as well.

I'd forgotten how enjoyable JavaScript is. Ahhh, scripting. It's so nice at times to be running the code right inside the browser and JavaScript is great to work with. I can understand why Google is so up on it.

So all in all it was a positive first real experience with struts. It was quite satisfying when I entered some stuff with known bad data and the page redisplayed properly with the error message and the original input data preserved.

I can't imagine how things were so messed up back in 2004 at Core that it was so painful but I'm glad I missed that. Historically I always thought I liked being in the back end server side processing away from the user interface. However after doing some GUI stuff it was kind of good in a way. We had a nice separation of concerns on this project where I only had to get the JSP and navigation working and another guy who wasn't interested in the GUI side had to do the validation and business processing part. That back end part now seems less interesting than it might have in the past.