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.
########## Class to manage pulling data from one db to another ##########
class Pull
def initialize(tableName)
@table = tableName
@destination = SqlServer.new("Data Source=server1;initial catalog=database1; trusted_connection=yes")
@source = SqlServer.new("Data Source=server2;initial catalog=database2; trusted_connection=yes")
end
def CopyTable
@destination.execute("TRUNCATE TABLE " + @table)
statement = @source.fetch("select * from " + @table)
statement.fetch do |row|
columns = ""
values = ""
insert = ""
i = 0
statement.column_names.each do |name|
columns += "," + name if columns.length > 0
columns += name if columns.length == 0
if row[i].nil?
values += ", NULL" if values.length > 0
values += "NULL" if values.length == 0
else
values += ", '" + row[i].to_s.gsub("\\", "").gsub("'", "''") + "'" if values.length > 0
values += "'" + row[i].to_s.gsub("\\", "").gsub("'", "''") + "'" if values.length == 0
end
i = i + 1
end
columns = "(" + columns + ")"
values = "values(" + values + ")"
begin
insert = "insert into " + @table + " " + columns + " " + values
@destination.execute(insert)
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
puts "Error SQLSTATE: #{e.state}"
puts insert
exit
end
end
statement.finish
@destination.disconnect
@source.disconnect
end
end
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.