Database Migrations Converting DbScriptomate to Ruby Run SQL Scripts Take 1
So I had a quick spike tonight to find a way to run SQL scripts against SQL Server. I found TinyTDS that piggybacks off of FreeTDS. TinyTDS - A modern, simple and fast FreeTDS library for Ruby using DB-Library. FreeTDS is a set of libraries for Unix and Linux that allows your programs to talk natively to Microsoft SQL Server and Sybase databases.
To get started, you are going to need to install FreeTDS. FreeTDS is available through homebrew, if you don’t have homebrew installed you can head over to the homebrew site and follow the steps to get it installed. If you have homebrew installed then just type in:
brew install freetds
Next you will want to install the TinyTDS gem, all you need to type is:
gem install tiny_tds
All setup lets write some code to send some SQL commands to our SQL Server.
Just one require pulling tiny_tds, we can then new up an instance of the Client class. The Client class accepts a few options, here is the full list of options:
:username - The database server user. :password - The user password. :dataserver - The name for your data server as defined in freetds.conf. Raw hostname or hostname:port will work here too. :host - Used if :dataserver blank. Can be an host name or IP. :port - Defaults to 1433. Only used if :host is used. :database - The default database to use. :appname - Short string seen in SQL Servers process/activity window. :tds_version - TDS version. Defaults to "71" (7.1) and is not recommended to change! :login_timeout - Seconds to wait for login. Default to 60 seconds. :timeout - Seconds to wait for a response to a SQL command. Default 5 seconds. :encoding - Any valid iconv value like CP1251 or ISO-8859-1. Default UTF-8. :azure - Pass true to signal that you are connecting to azure.
For our use we are only going to need:
:username :password :host :database
Setting these options are just enough for our spike. Next we call the execute method on the client instance passing in the SQL statement to be executed. The execute method returns a Result object that will allow us to loop over and readout all the rows from the result of our SQL expression.
Finally, to be safe we call close on the connection just to be safe and ensure we cleanup after ourselves.
With this code, I was able to execute CRUD operations and create a stored procedure. This means that TinyTDS looks like it can handle my requirements. The next step is to start writing tests that we can test that our SQL statements are correctly executed when using DbScriptomate.
Tomorrow we take a break to talk about Modules again, but this weekend we will dive back into this code and run some migration scripts against our DB!