Connecting to SQL Server Express from Ruby

Something I’ve been interested in is creating a dynamic data viewer for SQL Server in Ruby on Rails.  Here’s the steps I had to take to establishing a connection with SQL Server.  I’m using the AdventureWorks2008 database as the test database, and I should also point out that I’m running Ruby in Windows 7.

SQL Server Configuration

Some initial configuration of SQL Server Express is necessary, depending on how you configured SQL Server Express in your initial installation options.

Enable Mixed Mode Authentication

From SQL Server Management Studio, sign in with Windows Authentication and then right-click on the SQLEXPRESS top-level node, then select properties.  Click on Security    and Enable Mixed Mode Authentication:


Enable Remote Connections

Click on the “Connections” page and check “Allow remote connections to this server”:


Create a User Account

Create a user account under Security -> Logins.  I named my account “ruby” with the password “rubyist”.  On connecting the first with SQL Server Authentication, you will be prompted to change your password, so I changed mine to “rubyist1”.

Set the Server Roles

On the same dialog box, click on the Server Roles page and enable the desired roles.

Associate the User with the Desired Databases

On the same page, click on the Server Roles page and map the user to the desired databases.  For example:


Enable Named Pipes and TCP/IP Protocols

Then, in the Sql Server Configuration Manager, enable the Named Pipes and TCP/IP protocols:


Reboot or Restart the Services

After making all these changes, I found it simpler to just reboot my machine.

Test Your Connection in SQL Server Management Studio

After rebooting / restarting the services, open SQL Server Management Studio and try logging in with Sql Server Authentication:

ConnectIf all the configuration changes went well, you should see the object explorer.  If you continue to have problems, check out this excellent article on dealing with connection issues, which includes steps that I have omitted, such as configuring the firewall, as I’m testing everything locally.

Configure Ruby

Install the tiny_tds gem by opening a command line and typing in:

install gem tiny_tds

Test It!

I use RubyMine for Ruby / Rails development, so we’ll run a few things from the Interactive Ruby Console directly in the RubyMine IDE:

RubyPay particular attention to the client connection and the dataserver value.

For more information on how tiny_tds works and the options that it supports, refer to the tiny_tds GitHub page.

Why is parent_object_id Actually the Child Object in a Foreign Key Relationship ?

Let’s look at this query, which lists the columns involved in the foreign key relationship of the child table SpecialOfferProduct and the parent table, SpecialOffer (from the AdventureWorks database):

    SCHEMA_NAME(f.schema_id) SchemaName, 
    OBJECT_NAME(f.parent_object_id) TableName, 
    COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName, 
    SCHEMA_NAME(f.schema_id) ReferencedSchemaName, 
    OBJECT_NAME(f.referenced_object_id) ReferencedTableName, 
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName 
FROM sys.foreign_keys AS f 
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id 
    INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id 
    OBJECT_NAME (f.parent_object_id) = 'SpecialOfferProduct'

Why are we qualifying the child table name by the parent object?

The reason is in the definition of parent_object_id in sys.objects:

ID of the object to which this object belongs.
0 = Not a child object.

(see TechNet)

meaning that parent_object_id does not reference the parent in the foreign key relationship, it references the owner of the foreign key definition, which is the child table in the relationship.

The moral of the story is, look at the documentation to understand the purpose of the field rather than make assumptions because it uses terminology that just so happens to coincide with a completely different concept.

Obtaining Foreign Key Table-Column References in SQL Server

Borrowing from what others have put together, this query give you the details of foreign keys:

  • The referencing schema, table, and column names
  • The referenced schema, table, and column names
        SCHEMA_NAME(f.schema_id) SchemaName,
        OBJECT_NAME(f.parent_object_id) TableName,
        COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName,
        SCHEMA_NAME(ref.schema_id) ReferencedSchemaName,
        OBJECT_NAME(f.referenced_object_id) ReferencedTableName,
        COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumnName
        FROM sys.foreign_keys AS f
        INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
        INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
        INNER JOIN sys.tables ref ON ref.object_id = f.referenced_object_id
        WHERE OBJECT_NAME (f.referenced_object_id) = 'Employee'

This example queries the foreign key relationships of the table “Employee” in the AdventureWorks sample database that Microsoft provides.  Here’s the results of the query:

awfk_exampleThis is an important part of the dynamic data explorer that I’m writing as an application example in my upcoming book Imperative to Function Programming for SyncFusion‘s “Succinctly” e-book series.

Microsoft, SQL Server 2012 Express, and Failure

I’m doing a bit of multitasking today, mainly fixing a bunch of Ruby on Rails features for my client while doing some setup stuff on my workstation, like installing the latest version of SQL Server Express.

So I go this download page:

First thing I notice is how poorly this page correlates the download options with the filenames.   Here’s a screenshot of the download page:

fail1And here’s the descriptions (which of course requires that you click on “Details” to even see.)

fail2Notice that there is nothing that says “this installer is this file”.  You have to figure out that “With Tools” is the “WT” version, etc.

Finally, after 40 minutes of waiting for a 600 MB download (why are things so slow on your systems, Microsoft?) I get this message:

fail3And yet:

fail4It says right there that Windows 7 is supported!!!

So Microsoft, four failures for what should be one simple task.  Nice batting average.

Path to devkit\bin got removed somehow

I went to install the latest json gem this morning and got “make is not recognized as an internal or external command, operable program or batch file.”

Poking around my path, I discovered that c:\railsinstaller\devkit\bin was no longer there!  I have no idea what removed it — the last thing I did on this machine was install the latest version of SmartGit/Hg (which is another story.)

Anyways, adding the path back in fixed the gem install issue.

Function Composition, Function Chaining, Currying, and Partial Functions / Application in F# and Ruby

A while ago I wrote an article comparing / contrasting language elements in Ruby with C# with regards to classes: constructors, destructors, fields, properties, initializers, events, methods, etc.  Ruby however also has a foot in the functional language paradigm, though it requires a certain discipline to ensure that you’re adhering to qualities of a functional programming language such as statelessness and immutability.  However, for purposes of this article, what I am more interested in is exploring how Ruby handles certain concepts that seem to be core to most functional programming languages:

  • function composition
  • function pipelining (chaining)
  • continuation functions
  • currying
  • partial functions
  • partial application

These concepts are all intertwined with each other, and as we’ll see, you can certainly implement these behaviors in Ruby, though not as elegantly as with F# (or other FP languages.)  Incidentally, one of the reasons Ruby gets away with being called an FP language is its support for lambda expressions and the ease in which code blocks yielded to for iteration, application specific handling (like IoC), and so forth.  Again, those are not areas that I’m interested in pursuing in this article because at this point they are part of most imperative languages as well, such as C#, further blurring the lines between imperative, declarative, and functional programming.

Continue reading this article on The Code Project!