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.

Problems with auto increment and foreign keys

A while ago we came across a puzzling problem. We had a situation at a customer site where under some seemingly random circumstances some database records were not getting cleaned up as they should.

It was hard to reproduce the problem either on the customer side or in our own test sites. After some investigation we realized the problem. We had two tables, which I'll call "site" and "site_control". When a "site" is created the associated "site_control" is created at the same time. Both tables had integer id key fields which were both based on an auto increment number (which is a sequence in Oracle) starting at 1000.

Typically the id fields in the site and associated site_control records are the same because of the auto increment. The problem occurred when in certain unusual circumstances they can get out of sync in valid ways where a site record is created but not site_control.

The defect was in our code and it had been in the code undetected for several years. When a site is to be deleted the code is supposed to call deleteSite(int siteId). Deleting a site also deletes site_control as designed due to foreign key referential integrity.

What we were doing was incorrectly passing the site_control id, calling deleteSite(siteControlId). Although incorrect, this still works most of the time since the site and site_control id's were the same. When they got out of sync, the problems occurred. The fix was pretty simple, just call deleteSite() using the correct siteId which we were holding.

This demonstrates one of the real problems with integer auto increment key fields. If the key for the wrong table is used in the code then the DB selects, inserts, updates, and deletes, may still work or seem to work in terms of foreign key checks and such. However the code is wrong and it can lead to very difficult to diagnose errors and problems after the code ships.

If there are bugs in the code then we definitely want to find them as soon as possible, certainly before the code ships. The use of auto increment with possibly shared ids is dangerous as it can allow incorrect code to pass testing and thus for defects to escape detection until the code is in the field and the bugs become extremely expensive to address.

There are a few strategies to counter this. One approach which I like is to use GUID instead of numeric keys. That way every key is unique not just within tables but across all tables. So if the code is using they key from the wrong table then the foreign key constraint will fail and it will be obvious that there is a problem.

Another approach is to "stagger" your starting point with the auto increment ids. That is a good approach too because if there is an error that it will very rarely just happen to work since the parent and child tables virtually never have the same id values. Again if there is a code issue where it is using the id from the wrong table then it will fail immediately in developer or QA testing and the code defect will be fixed before the product ships.

So in your schema definition use an offset, even 50 would be plenty. So the site table ids would start at 1000, site_control at 1050, table_c at 1100, table_d 1150, etc.

Save as PDF

A little while back I had to create a PDF version of an install guide for a maintenance patch release I was putting together.

I hadn't generated a PDF before. Historically the tech writers just took care of it in some mysterious process. Due to some company reorganization it now fell to the developer who was creating the code patch to also put together the PDF patch install guide.

The document was already done and ready to convert from Word. I asked a couple of developer coworkers about how to save as PDF. One of them pointed me in the direction of PDF 995.

It turns out this PDF 995 thing is a very handy utility. Install is fast and easy. It works as a printer driver of all things. So when you click Print the PDF 995 comes up as a "Printer" in your printer list. That's very clever.

It works fine and it generates a nice clean glossy PDF just like you expect. Then you click Save As to save the PDF on your file system and you're done. It's very easy to use.

So +1 for PDF 995, a well designed product.

Changing jobs

I'm doing something that many people in high tech do. I'm leaving my job to join a new company. I've been with SupportSoft and its acquired predecessor Core Networks since 2001. I'm joining Research In Motion later this month.

For me a job switch is a big move. I've only done one before, going from xwave to Core Networks in 2001 after 3 years at xwave and its acquired predecessor PRIOR Data Sciences. So many acquisitions in tech. Some people jump all the time, around every two years on average. But that's not my style and I'm personally a little bit suspicious of serial jumpers.

It's pretty wacky how networking and chance events can lead to new things. Last Christmas break I went to an annual road hockey game. There I met a guy who I knew over the years and worked with at PRIOR in the late 1990s. He's director level now. He was finishing up at his job after several years and was looking for a new job. We agreed to keep in touch over LinkedIn when he found a new job in case he was hiring additional people. As it turns out he landed at RIM in the first part of the year and that lead to me getting interviewed. I don't normally go to road hockey games expecting a new job to come of it but it just shows what can happen.

I don't have anything bad to say about SupportSoft. The local job market has been pretty good since around 2004 so if I was unhappy then I could have chosen to leave at any time. This just looks like a promising opportunity which I believe is in my best interest to take.

Every job has its ups and downs. Good things and little annoyances. I think expatriates mostly look back well on previous employers; even if they grumbled a bit when they worked there. I look back kindly on my time with PRIOR and xwave and I'll look back well on my seven years with Core and SupportSoft.

Java GUI coming of age

Somewhat quietly the Java JDK GUI has improved. Especially since Java 5.

I'd kind of given up on swing over the years. Everyone has bad memories of traditional Java GUIs. Battleship gray, clunky. Control-C didn't work on Windows, instead it used Unix semantics like Control-Insert for copy and paste. Select a piece of text and right click the mouse. Nothing happens.

If there was one thing which made Eclipse it was developing SWT as a much superior end user experience for Java GUI. With native widgets Eclipse was so much better. Suddenly Ctrl-C works properly on Windows. Select a piece of text and right click the mouse and the context menu comes up. Finally the GUI just looks and works the way we expect. At the time it came out SWT was the obvious and superior choice.

I have to give Sun credit. They didn't give up on JFC. They obviously worked hard to improve it. Now in Java 1.5 they pass the "10 foot test" for the first time. That is, standing 10 feet away from the computer, you can't tell that it's a Java GUI. It looks and acts much smoother now.



One of my favorite programs that I use most every day is Oracle SQL developer. This uses the JDK GUI and it is just fine to work with. It looks and feels great. The standard keyboard and mouse actions all work the way you expect. It's plenty fast.

For the first time developers can consider using swing for serious Java GUI applications.