Connecting Active Record to a Legacy Database with Stored Procedures

Mon Nov 26 19:34:30 -0800 2007

Sometimes you just have to go ahead and do it the hard way.

The last three days was that time for me. I had to write an application that interfaced with an existing MS SQLServer system, and I couldn’t change the schema of any existing table… what to do? Well… I can tell you it was a lesson in the difference between being opinionated software and bigoted software…

I first sat down and looked at the existing schema with it’s lack of documentation it was interesting finding the relationships between the tables and records.

It is a respectable size database with a bunch of tables and hundreds of stored procedures for most anything you needed to do.

Luckily, while ActiveRecord is opinionated about the way it should do things, it is not a Bigot, and with it’s parent Rails, we were able to massage this app into existence.

First step was identifying what it was I actually needed to do and isolating the tables involved, ensuring I wasn’t stepping on any other toes around this quite populated database.

Then, looking through the existing tables and records finding relationships and drawing them out on paper so I could see in 2 dimensions what the original author of the DB meant.

Then, as I had to develop on a Windows box for this, I grabbed a copy of e (nothing compared to Textmate) and populated my temporary Windows system with the One Click Ruby installed, Rails, the Win32 Gem library and some others I like (RSpec et al). Also had to install Cygwin for e to run specs properly.

Next step? Installing a local copy of SQL server and pulling down a copy of the DB to work on.

OK… I was all set.

Now in the editor, I first created a bunch of models, one for each table I had to model in my App. As NONE of these tables were puralized, and NONE of them followed the Rails convention of primary_key being an auto incrementing integer column called :id, I made very good use of the two override methods which override the primary_key value as well as the table_name value, basically making ActiveRecord ignore the convention of a pluralized underscorized classname for the table and id as the primary key.

1
2
3
4
5
6

class MyClassName < ActiveRecord::Base

  set_primary_key => "my_table_primary_id"
  set_table_name  => "new_table_name"

end

There was one of these in EVERY model.

Now, the down side of using the above is that Rails no longer will automagically maintain the :id column, which means you have to set it.

So instead of:



MyTableName.create(:name => "Bob", :age => "10")

You now have to do:



MyTableName.create(:my_table_primary_id => 1, :name => "Bob", :age => "10")

Note, it wants the primary key column set explicitly by name.

OK, so now I had my models in place, I started putting relationships in.

The one to many relationships were easy enough, by using the foreign_key param.



has_many :other_records, :foreign_key => my_special_foreign_key

But it was the has_many through that started causing problems. Here I resorted to using SQL on the exisitng stored procedures to fight my way through the code and get the products I needed.

Additionally, the way you got a unique primary_key_id was a stored procedure. SO that meant running stored procedures in the code.

This stored procedure accepted the table name you wanted a new primary key for as well as an SQL variable to dump the result into. So you had to call it in SQL like this:



exec get_id "my_table_name", @id output

Just running an ActiveRecord.connection.execute(the_aboe_sql_code) did not produce the result of the next ID, because SQL Server would complain that @id didn’t exist (and rightly so, I didn’t declare it…), so instead I had to do something like this:

1
2
3
4
5

sql_query = "set nocount on;\n" +
"declare @id integer;\n" +
"exec get_primary_key 'my_table_name', @id output;\n" +
"select @id;"
result = ActiveRecord.connection.select_value(sql_query)

This then dumped the id into the result variable which we could then whack into the create method. What the “set nocount on” does is tells SQLServer to suppress the output of “1 rows affected” after the execution of the stored procedure.

Leaving that inside the create method was a bit scary, so I moved it into a class method like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

class MyClassName < ActiveRecord::Base

  set_primary_key => "my_table_primary_id"
  set_table_name  => "new_table_name"
  
  class << self
  
    def get_next_id
      sql_query = "set nocount on;\n" +
      "declare @id integer;\n" +
      "exec get_id 'my_table_name', @id output;\n" +
      "select @id;"

      ActiveRecord.connection.select_value(sql_query)
    end
    
  end

end

Now I can call MyClassName.get_next_id to get the next unique primary key for the table. This is a bit neater, but not really a solution. What would be cool is being able to set inside a model an override method which tells the class how to get the next primary key. But that is a subject for another post.

blogLater

Mikel

  1. DoriLeveBeere Says:
    Mon May 02 20:05:30 -0700 2011 bags[/url]gucci bags gucci bags gucci bags gucci bags gucci bags
  2. vavikhmn Says:
    Mon May 09 07:13:39 -0700 2011

    Каждому Доброе утро! Заходите на [url=http://www.vavilonkh.at.ua/]www.Vavilonkh.at.ua – Промышленное оборудование. Металлоконструкции![/url] предлагает Вам широкий спектр оборудования, используемого в производстве строительной керамики и других отраслях промышленности, а также услуг, связанных с его эксплуатацией.Вам нужно качественное оборудование по приемлемым ценам? Вы хотите получить его в краткие сроки и с гарантией? Тогда Вы обратились по адресу. Для Вас мы предлагаем современный подход в решении вопросов строительства заводов, технических и технологических сложностей, связанных с действующим производством. Вам на помощь придут наши высококвалифицированные специалисты с огромным практическим опытом в сфере производства кирпича, использующие современные методики работы. На сайте Вы сможете ознакомиться с нашим асортиментом, меню “Продукция” , где найдёте фото и описание технических характеристик оборудования. Комплексный подход к решению проблем клиента дает нам возможность утверждать, что такое сотрудничество будет длительным, взаимовыгодным и успешным. Наши изделия: Установка приготовления и сжигания твёрдого топлива (производит обжиг кирпича углём); Буртоукладчики; Формующая оснастка (шнеки, мундштуки, головки, рубашки, керна); Роторный, однострунный и многострунный резательные автоматы бруса; Печные и сушильные вагонетки, сушильные рамки и полки; Промышленная вентиляция и воздуховоды; Конвейера ленточные, цепные, винтовые и запчасти к ним; Расстилающие устройства над валковыми дробилками; Ящичные питатели; Сито-бурат, вибросито (рассев до 4 фракций); Лабораторное оборудование (валковая дробилка ВДЛ-1, мощностью 1.5кВт; щековая дробилка ЖДЛ-1; пресс ручной ЛРП-1), прибор Чижского, прибор Васильева (изготовление новых).

  3. Lorireubbeple Says:
    Wed May 11 23:32:33 -0700 2011

    We Accept Houses With No Tolerance,We Bribe Any Houses,We take Over Payments.Please expand supply form on
    http://www.cashsellfast.com and get furnish in 48 hours
    or right us 410-978-7981,entertain refer to friends who needfulness help.Bless daytime!



    Distinction Landlords and Rehabbers:
    If you are a grim investor buying at least one firm per thirteen weeks for rental or rehab,
    then you are in the truth situation precise now.
    http://www.renewreality.com.


    Proprietress Financing May Be The Secret Answer Learn How To Own A Stamping-ground Without Dealing With A Bank!
    Search out The Horrific Really In all directions Renting & Why It May Be Background You Subsidize Financially??¦ Get
    Insider Secrets on How Proprietor Financing Surely Works
    http://www.reimatcher.com/Baltimore/Reality_properties/sp1.


    Requirement Fresh Start? We can help.Painting callers can represent your domicile within desired loiter again and again frame,
    in colors of your preference with win out over discounts in support of paints,with adequate by way of client quality.
    Objective prices,references,allow,union,insuranse.For pictures of your works gladden by
    http://www.renewhouse4u.com.

  4. Lorireubbeple Says:
    Mon May 23 10:21:43 -0700 2011

    We Purchase Houses With No Tolerance,We Accept Any Houses,We crook Over Payments.Please fill stamp on
    http://www.cashsellfast.com and irritate tender in 48 hours
    or invite us 410-978-7981,please refer to friends who needfulness help.Bless heyday!



    Acclaim Landlords and Rehabbers:
    If you are a solemn investor buying at least anyone house per compassion in the interest rental or rehab,
    then you are in the truth situation fair now.
    http://www.renewreality.com.


    Proprietor Financing May Be The Veiled Colloidal suspension Learn How To Own A Domestic Without Dealing With A Bank!
    Search out The Horrific Truth About Renting & Why It May Be Frame You Secretly Financially??¦ Reach
    Insider Secrets on How Owner Financing Unusually Works
    http://www.reimatcher.com/Baltimore/Reality_properties/sp1.


    Need Latest Start? We can help.Painting callers can picture your homestead within desired loiter again and again box in,
    in colors of your realm of possibilities with best discounts in support of paints,with acceptable by client quality.
    Fair prices,references,allow,fetters,insuranse.For pictures of your works please drop in on
    http://www.renewhouse4u.com.

  5. onlinelider Says:
    Wed May 25 05:11:02 -0700 2011

    [url=http://onlinelider.ru/]онлайн тв[/url] смотреть, телевидение ОНЛАЙН

  6. onlinelider Says:
    Wed May 25 05:27:34 -0700 2011

    [url=http://onlinelider.ru/]онлайн тв[/url] смотреть, телевидение ОНЛАЙН

Leave a Reply