API's that Suck

December 27, 2010

The Story of UltraBase: Chapter 2

Filed under: UltraBase — Grauenwolf @ 8:29 pm

Last time we introduced Fred and his UltraBase. The UltraBase wasn’t a production application, but rather a sophisticated code generator that would be used to create the middle tier that would be shared by all hundred or so applications used by SmallCo. Unfortunately it had some limitations that couldn’t be overcome.

One of the biggest stumbling blocks for the middle tier/website team is optional parameters on stored procedures. SmallCo often wrote procedures in which a given parameter may be used by some applications and ignored by others. The convention was quote inconsistent; sometimes the optional parameter would have a default value, other times a null would have to be passed in.

The website was the first application to start using the middle tier. Since it had limited functionality compared to the internally facing applications, it more than others ignored a lot of parameters on the stored procedures. When they saw this, Fred’s team became very concerned. Since they weren’t sure they could properly test web service wrappers around the stored procs, they decided that all of those parameters the website didn’t use wouldn’t be available via the middle tier either.

Unfortunately the UltraBase didn’t support skipping parameters, nor did it support defaulting them to null. So after much arguing, and a near revolt by the database team, it was decided that Fred’s team would create roughly 200 stored procedures that simply wrapped the real stored procedures and hid the optional parameters.

I still wonder what they plan to do when asked to wrap the other 7,400 stored procedures we currently use.



  1. Very interesting 🙂
    I’m curious: since UltraBase has been developed in house, why couldn’t they build in support for optional/null params? Also, were the optional params the only problem, or just the most apparent problem with this architecture?
    I must note though, that wrapping the ugly stored procs into other procs with cleaner interfaces is not the worst idea. I once attempted to sell this idea to management at one FastGrowingCo, but failed.
    And I’m so happy to see “Create a SqlConnection” and “Close the SqlConnection” in one call scope! Lately I’ve seen too many frameworks and mappers that abuse connection pooling 😉


    Comment by bbzippo — December 29, 2010 @ 11:07 pm

    • Another question. I understand the reasoning behind the introduction of a middle tier as a vehicle that would offload the bus.logic from the DB in the future. Has the Co taken any steps to actually port some business logic to the middle tier? How did it go? And how much was done to try and optimize the DB itself (the logical/physical layout and the queries, as oppsed to the attempts to vertically scale)?


      Comment by bbzippo — December 29, 2010 @ 11:28 pm

      • Actually they have been going backwards. For example, they used to have a proc that would return five rows and have the website decide which one row to use. With UltraBase that isn’t an option so the proc had to be rewritten to only return a single row.

        I’m all for reducing the amount of unused data being moved about. But you should be doing that for its own sake, not because your middle tier cannot handle even trivial logic.

        Comment by grauenwolf — January 2, 2011 @ 12:40 am

    • I honestly have no idea why it couldn’t handle null parameters. It seems to be their style to make even the most trivial things so complicated that even minor changes take weeks. So it could simply be that the didn’t have the manpower to support this using their design.

      Comment by grauenwolf — January 2, 2011 @ 12:43 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: