Execute SQL query from Rails when connecting to SQL Server 2005 through activerecord-sqlserver-adapter

Question

So I'm connecting to SQL Server 2005 from my Rails app via the activerecord-sqlserver-adapter.

I'm able to execute stored procs by doing

Mymodel.execute_procedure("thisProcedure", param1, param2)

But I have a stored proc that has a SQL INOUT variable that I'm having trouble with. I execute it, and I'm not seeing that variable returned.

So now I'm trying to just execute some raw sql, such as

declare @thisVar int
EXEC thatProcedure 1, 1, @thisVar = @thisVar output
print @thisVar

When I do

sql = "declare @thisVar int
EXEC thatProcedure 1, 1, @thisVar = @thisVar output
print @thisVar"

foo = Mymodel.connection.execute(sql)

I'm not getting any errors and everything looks successful. I get back foo that has a class of DBI::StatementHandle. How do I actually see the response from the SQL?

Thanks in advance!

Answer 1

It has been a while since I waded into DBI, we left stored procedures in favor of ORMs. To get the data from the StatementHandle object, you need to issue a fetch all. That should have all of your return values/output parameters as well as result set if applicable as an array. This was dbi version .4.1 which I believe is the last version to work with the sqlserver adapter.

sql = "declare @thisVar int
EXEC thatProcedure 1, 1, @thisVar = @thisVar output
print @thisVar"

foo = Mymodel.connection.execute(sql)

result_array = foo.fetch_all

and then you can loop through the resulting array.

Here is the DBI documentation http://ruby-dbi.rubyforge.org/rdoc/index.html

You might want to also look at the new activerecord-sqlserver-adapter. It eliminates DBI entirely, not sure how it handles SPs though.

http://github.com/rails-sqlserver/2000-2005-adapter

My advice, for what it is worth, mixing ORMs and stored procedures is extremely difficult because you are splitting the business logic between two layers and it gets increasingly difficult to manage.

Best of luck!