Probably: Why I’m Not a Great DBA
One of the interesting side effects of writing an API is that I’m writing a lot of SQL queries. To be more accurate I’m transitioning a lot of SQL queries from SQL embedded in Pascal to SQL referenced by Java code.
The first thing that I did when I started writing SQL was to figure out how to embed that into something that wasn’t in line with the Java code, and an XML based Java properties file was the easiest option that I found. The CDATA tag means I can store exactly the SQL, not worry about symbols, and generally have an easy time storing strings to turn into PreparedStatement objects.
Then I hit the 5th or so block of SQL and realized that I needed to write a script to automate adding SQL files to properties file. So I wrote a very simple script using Ruby and the Builder gem to turn a directory of .sql file into a properties file.
After about the 20th SQL file, I got sick of having to keep looking up strings to load the files, and so I sorted the SQL files into subfolders, made the script recurse through everything, and then built a class with methods for all of the sql, so I now get code completion for my sql references in Java.
Now I’m losing track of how many times I’ve written an enumerator for the columns in a SQL result set, a custom method to set all of the values properly in a prepared statement, and so forth, and one of my back burner projects is thinking about how to insert metadata into the SQL files to add code generation capabilities for all of that.