Run a SQL statement via Text Service

I have found myself RDPing into my SQL server just to run a few queries a lot lately. I already have unixodbc setup for my rails to sql server so I was thinking, "I should be able to run these queries from the commandline." Turns out I can via isql. This, then, got me thinking, "how about a Text Service, oh and MultiMarkdown". Automator to the rescue.

Preprocessing

First I had to sanitize my sql queries a bit. isql requires that each query be on it's own line and that it ends in a ";". I assume I have already separated the queries by semi-colon but I need to put each on their own line. I put the following in the first "Run Shell Script" of the Automator workflow. Oh and it's ruby, in case you couldn't tell.

          lines = ""
          ARGF.each do |f|
              lines << "#{f.strip} "
          end
          
          puts lines = lines.split(/;/).join(";\n") + ";\n"``
      

Run the sql

The following bash script goes in the next "Run Shell Script".

          cat - | isql [dsn] [user] [password] -b
      

MultiMarkdown

Now to get the results to MultiMarkdown. The toughest part here is to get rid of most of the +----+ isql outputs. Another ruby script.

          lines = ""
          found = 0
          ARGF.each do |f| 
              case f 
              when /^(\+-+)+\+$/
                  found = (found + 1) % 3
                  lines << f.gsub('+','|') if found == 2
              when /^\d+ rows? fetched$/
                  lines << "  \n_#{f.strip}_  \n  \n  \n"
              when /^SQLRowCount returns/
              else
                  lines << f
              end
          end
          
          puts lines.strip