Ruby in Practice

In my current contract, it's the typical story with compressed timelines and pulling off miracles, so I haven't had the opportunity to make use of Ruby for a "real" production application.  However, we're in the midst of doing some work in migrating data from a legacy system into a completely new database schema.  All imagined challenges apply, I assure you.  Part of this exercise is repeatedly bouncing the data against the new schema, applying validations to the data to make sure all the new referential integrity holds true in the new system and reporting on data that isn't fitting into the new system properly.  I took this as an opportunity to apply some Ruby scripting as a means of automating this part of the project.

 Having played with the DBI library a bit before starting, I knew that I wanted to abstract some of the interaction with it away so I wrapped it like so.

  ########## Class to abstract the server connection ##########
  class SqlServer
   
    def initialize(connectionString)
        @dbh=DBI.connect("DBI:ADO:Provider=SQLOLEDB;" + connectionString)
        @dbh["AutoCommit"] = true
    end
   
    def execute(sql)
        statement = @dbh.prepare(sql)
        statement.execute
    end
   
    def fetch(sql)
        statement = @dbh.execute(sql)
        statement
    end
   
    def disconnect
        @dbh.disconnect
    end
  end

I needed to be able to pull fresh data from other databases into my conversion database, so I abstracted that task, as well.

In my case, all the columns were varchar, so I didn't have to fuss with the format of the data in the insert statements.  With DBI, however, it's possible to get the meta data about each column to decide whether the data needs to be quoted or not.  Now, I needed to be able to pull data about how the load went and any exceptions that were encountered.  That data needed to be saved into CSV files so they could be attached to emails.  The next class was written to do this.

 ########## Class to pull statistic about the load into csv files ##########
 class StatFile
 
    def initialize(connString)
        @server = SqlServer.new(connString)
    end
 
    def GetStats(filePath, sql)
        statement = @server.fetch(sql)
       
        output = File.open(filePath, "w")
       
        statement.fetch do |row|
            output.puts row.to_a.join(",")
        end
       
        output.close
    end
 end

 With the above classes, I was able to script the tasks at hand something like this.

Pull.new("tableName").CopyTable #Copies the table from one db to another

 conversion = SqlServer.new("Data Source=server;initial catalog=database; trusted_connection=yes")
 conversion.do("EXEC ConversionSproc")

 stat.Getstats("c:\\Stat_Files\\exceptions.csv", "select foo1, foo2 from exception_table where error='bad' group by description")

As I've stated before, I'm not planning to use Ruby to put food on the table at this point, but I would like to get some experience using the language if only to get the chance to think about things differently.

 UPDATE:
After using the above scripts a bit, I found that when executing some rather long running stored procedures, I was receiving a timeout.  It was then that I realized there was not way to set the CommandTimout property on the database connection through DBI.  After hours of searching, I was able to gleen enough information to overcome the problem.  I added the following line to the bottom of the initialize method of the SqlServer class:

  @dbh.handle.instance_variable_get(:@handle).setproperty('CommandTimeout', 7200)

Of course, you can specify whatever value you like for the timeout or even parametize the value.