Making SQL Tolerable

I am still not particularly wild about SQL (or at least the Informix dialect of it), but at my suggestion, my employer recently upgraded our licenses for Server Studio 8.0 to the Suite edition, which includes an SPL debugger.  (SPL is Stored Programming Language, a series of extensions to SQL intended to make it easier to do complex database operations from a real programming language.)

And my, what a difference!  Debugging SPLs until now has required using the TRACE facility, which produces a pile of log messages--and no way to control it.  Now I can step through, line by line and inspect variables.  That does not sound particularly impressive, and it isn't.  But compared to what I have been having to do to debug this crud?  It's heaven!

There are so many aspects to SQL that are just so 20th century--and not even late 20th century.  For example, I was writing some SPL that was supposed gather up information out of a number of rows of a table, and concatenate these strings (Idaho Code sections) into a single row for output.  For no reason that I could see, it just was not working.  I would get the first row, and nothing more.

It turned out that the problem was that the variable was declared as CHAR(60), and I forgot (or perhaps never knew) that when you set a variable in SQL to a particular string, it pads that string on the right with blanks.  When I tried to concatenate the next string onto this, it said, "Oh, I already have this CHAR(60) full--I cannot add anything more to it."  But it only talks to itself under those conditions--no warnings, no messages.  At least with this SPL debugger, I eventually figured out that I needed to run TRIM on the variable before concatenating another string.

What I am working on at the moment involves quite a bit of processing of what is called the Pre-Sentence Investigation--the enormous pile of paperwork that the courts and Corrections do between the time a person is convicted of a felony, and the time that they go to prison.  We have a rather complicated but not particularly sensible strategy for mapping PSI's internal offense code system to the actual statutes that the offender violated.  (And it is not a one-to-one mapping, which is part of the complexity.)

Anyway, I sat in a meeting a couple of days ago with people from various departments outside of IT, and we went over the list of criminal statutes that appear in our database.  I've mentioned before my amusement at the Burglary With Explosives statute.  The other day I saw one that I hope never requires prosecution here: 18-5003, Cannibalism.