Calling PostgreSQL Stored Procedures from Grails - Part 3, SqlFunction

SqlFunction is another class that takes a stored function (which is our sample procedure in Part 1), and runs it. I've done a rather inefficient sample below. In reality, you should be using the constructor passing the input types, and using the version of run which allows you to pass parameters.

The SqlFunction section of our service

def sqlFunction = new SqlFunction(dataSource, "select salesByBookId(${bookId})")
    sqlFunction.compile()
    def result
    try {
      result = sqlFunction.run()
    } catch (org.springframework.dao.DataAccessException e) {
      log.error "Result type cannot be inferred - " +
                 " no result so returning 0"
      0
    }
    result

I should explain a few things about the code snippet:

• The dataSource I auto-inject using def dataSource in the Grails Service
• I return 0 if the key is wrong
• The SqlFunction throws subclasses of DataAccessException when the call fails (if the row doesn't come back, for example. You could use TypeMismatchDataAccesException, which is a subclass of DataAccessException but I chose the wider exception in the hierarchy.
• I'm using the nifty Grails log variable, which is wired into every Grails object automatically. You can control logging via the Config.groovy file in your grails-app directory.
• Ideally, you should set up the SqlFunction instance and re-use it over and over again, as it is compiled. Again, this is more showing you the features, not best practices, as I'm just blogging this to put a little more information out there. I'll refactor this a bit later, if I get the chance...