Calling Stored Procedures from Rails

I know DHH thinks this is not the Rails way, but I've got an urgent deadline and the stuff that the stored procedure does is 1) well tested and 2) very complex.

So I need to call it, to get this stuff done in time.

Afterwards I will wrap a unit test around it (that's a story for another time) so I can reimplement it in pure Rails, but for that to happen as it should, I also need to get the webservice stuff working (so the logic currently tied up in the stored procedure is accessible to the outside world) and rewrite parts of the desktop application to call the webservice instead of the proc. So a bit of a way off yet.

Anyway, to call a stored procedure in Rails I used the following:

sql = "declare @result int \n"
sql += "declare @some_value uniqueidentifier \n"
sql += "exec @result = my_stored_procedure @input1 = 'hello', @input2 = 'world', @output = @some_value output \n"
sql += "select @result\n" # change this to select @some_value if you want to access the output parameter

result = connection.select_value(sql)

You could probably change the last line of the SQL to select @result, @some_value and use select_all to grab the results if you need to.

Anyway, this is the equivalent of what I would do in Query Analyser - declare some variables for storing stuff, call the proc (using @result to store the return value) and then select the values I am interested in to take a look at them.

However, sometimes it worked and sometimes I would get a "cannot perform this object when the dataset is closed" - looking at the stack trace, it appeared to be coming from the ADO Recordset itself.

Why? There seemed to be no reason for it, until my colleague Nick had a revelation. ADO allows a single Recordset object to contain multiple Recordsets (excellent class naming there Bill). When you send a compound statement via Query Analyser, it splits the actual recordsets into the "data pane" and various messages into the "messages pane". However, in ADO the messages sometimes get sent back as a recordset. Therefore, Rails was trying to read a "recordset" containing "2 row(s) affected" when the actual data it was interested was in the "next" recordset. As I wasn't sure if I could get at the underlying Recordset, and I didn't really want to, I was a bit stumped. But Nick came to the rescue again - adding the line "set nocount on" as the very first statement, before the declares, seemed to solve the problem. Basically, we were telling SQL Server to suppress the messages and it seems to work.

Of course, there will be some configuration somewhere, when it goes live, that will screw it up - so I need to start copying that proc into Rails as soon as.