New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Output Parameters for Stored Procedures #24

Open
Wardrop opened this Issue May 23, 2011 · 9 comments

Comments

Projects
None yet
3 participants
@Wardrop

Wardrop commented May 23, 2011

Microsoft SQL has the concept of output parameters for stored procedures. It's one of four methods for returning data from a stored procedures (http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx). TinyTds currently has no native support for output parameters, so this limitation must be worked around as follows:

DECLARE @out1 nvarchar(255), @out2 nvarchar(255), @result int
EXEC @result = testSproc 'some input', @out1 OUTPUT, @out2 OUTPUT
SELECT @out1 as 'out1', @out2 as 'out2', @result as 'result'

You may also notice that because the call to "EXEC" was not the last statement in this batch, using the #return_code method will return nil, so we need to add the return code (@Result) to the SELECT statement in order to retrieve its value.

Ideally, it would be nice if there was a construct for stored procedures. For a few example interfaces

client = <TinyTds::Client object>
input_params = ['value1'] # Hash or array for named or unnamed parameters
output_params = {:out1 => nil, :out2 => nil} # Hash or array for named or unnamed parameters
client.sproc('testSproc', input_params, output_params).do
puts output_params[:out1] #=> "Some value"

OR...

client = <TinyTds::Client object>
# Unnamed parameters
sproc = client.sproc('testSproc').input('param1', 'param2').output(:out1, :out2)
sproc #=> <TinyTds::Sproc object>
# Or named parameters
sproc = client.sproc('testSproc').input(:param1 => 'value', :param2 => 'value').output(:out1 => nil, :out2 => nil)
sproc.execute
sproc.output[:out1] #=> Retrieves the value of the output parameter :out1.

While you can still use stored procedures with output parameters without a special interface, this would be quite a nice addition to TinyTds, and would make it into a proper MSSQL client in my opinion.

Refer to: http://www.freetds.org/faq.html#ms.output.parameters

EDIT: Whoops, my examples were completely flawed. Now fixed.

@Wardrop

This comment has been minimized.

Show comment
Hide comment
@Wardrop

Wardrop May 23, 2011

Fixed my examples which were originally fundamentally flawed. I had a brain lapse and forgot that Ruby can't pass variables by reference, only objects are passed by reference.

Wardrop commented May 23, 2011

Fixed my examples which were originally fundamentally flawed. I had a brain lapse and forgot that Ruby can't pass variables by reference, only objects are passed by reference.

@Wardrop Wardrop closed this May 23, 2011

@Wardrop Wardrop reopened this May 23, 2011

@metaskills

This comment has been minimized.

Show comment
Hide comment
@metaskills

metaskills May 23, 2011

Contributor

Thanks for opening a ticket for this! Makes it easier to communicate in an open way so others can chime in if interested. Here are some of my initial thoughts and I will start work on this in some fashion soon.

I remember having some issues with OUTPUT parameters when I implemented the latest 3.1 version of the ActiveRecord adapter for SQL Server. In that version AR now passes down bind vars and allow different RDBM's to construct, tokenize how ever that DB needs to cope with prepared statements. Here is the thread if your interested.

http://groups.google.com/group/rails-sqlserver-adapter/browse_thread/thread/8a2422081d5cad6c?hl=en

I mention this because the way I implemented prepared statement support speaks to how I try to construct dispart libraries to SQL Server. My goal is to not spend too much time solving things from one library that another implements and to implement as much in TSQL vs hooking into low level connection modes specific ways of doing things. In this example, ODBC (ruby-odbc) supports prepared statement handles and reuse, where DBLIB (tiny_tds) has no such notion. The solution was just to use sp_executesql which reuses the query plan too without having to hook into special connection mode hooks. FFT.

So, I did mention that I had problems with OUTPUT params when I was working on learning the details of SQL Server's query plan reuse under TinyTDS. I think this is something that I want to solve. I worked around it in the adapters implementation of sp_executesql for getting the last identity (pk) returned by simply doing something similar to the workaround you mentioned above. By just taking on the SQL to the scope of the stored procedure's SQL since it is executed in another context.

I am on the fence about building in an explicit stored procedure hook with bind var parsing. Both the activerecord-sqlserver-adapter and more so importantly arel already do the hard work of quoting ruby primitives for arguments and/or parameterized SQL. I have an internal note to myself to consider a stronger TinyTds::Client#quote method that mimics what the adapter/arel does already. If we implement something like what your asking, that would almost certainly have to be the case.

Contributor

metaskills commented May 23, 2011

Thanks for opening a ticket for this! Makes it easier to communicate in an open way so others can chime in if interested. Here are some of my initial thoughts and I will start work on this in some fashion soon.

I remember having some issues with OUTPUT parameters when I implemented the latest 3.1 version of the ActiveRecord adapter for SQL Server. In that version AR now passes down bind vars and allow different RDBM's to construct, tokenize how ever that DB needs to cope with prepared statements. Here is the thread if your interested.

http://groups.google.com/group/rails-sqlserver-adapter/browse_thread/thread/8a2422081d5cad6c?hl=en

I mention this because the way I implemented prepared statement support speaks to how I try to construct dispart libraries to SQL Server. My goal is to not spend too much time solving things from one library that another implements and to implement as much in TSQL vs hooking into low level connection modes specific ways of doing things. In this example, ODBC (ruby-odbc) supports prepared statement handles and reuse, where DBLIB (tiny_tds) has no such notion. The solution was just to use sp_executesql which reuses the query plan too without having to hook into special connection mode hooks. FFT.

So, I did mention that I had problems with OUTPUT params when I was working on learning the details of SQL Server's query plan reuse under TinyTDS. I think this is something that I want to solve. I worked around it in the adapters implementation of sp_executesql for getting the last identity (pk) returned by simply doing something similar to the workaround you mentioned above. By just taking on the SQL to the scope of the stored procedure's SQL since it is executed in another context.

I am on the fence about building in an explicit stored procedure hook with bind var parsing. Both the activerecord-sqlserver-adapter and more so importantly arel already do the hard work of quoting ruby primitives for arguments and/or parameterized SQL. I have an internal note to myself to consider a stronger TinyTds::Client#quote method that mimics what the adapter/arel does already. If we implement something like what your asking, that would almost certainly have to be the case.

@Wardrop

This comment has been minimized.

Show comment
Hide comment
@Wardrop

Wardrop May 23, 2011

Thanks for the reply.

Wardrop commented May 23, 2011

Thanks for the reply.

@Wardrop

This comment has been minimized.

Show comment
Hide comment
@Wardrop

Wardrop Oct 25, 2013

Hey Ken,

Just wondering if you ever revisited the issue of output parameters. I'm also wondering whether prepared statements is on the table for TinyTDS. You say above that DBLib, which I assume TinyTds depends on, doesn't have the notion of prepared statements. Is this still the case?

Cheers,
Tom

Wardrop commented Oct 25, 2013

Hey Ken,

Just wondering if you ever revisited the issue of output parameters. I'm also wondering whether prepared statements is on the table for TinyTDS. You say above that DBLib, which I assume TinyTds depends on, doesn't have the notion of prepared statements. Is this still the case?

Cheers,
Tom

jrgns added a commit to jrgns/sequel that referenced this issue Dec 23, 2013

Documented execution of stored procedures for MSSQL
This feature is somewhat of a hack to work around the fact that
TinyTDS doesn't have native support for output variables:
rails-sqlserver/tiny_tds#24

More information here:
https://mail.google.com/mail/u/0/?shva=1#inbox/1430ec6f437fe558
@MrLinDowsMac

This comment has been minimized.

Show comment
Hide comment
@MrLinDowsMac

MrLinDowsMac Sep 7, 2015

Hi, I would like to know if there is any chance to use OUTPUT variables in Rails now, I don't find any doc about it, I just found this issue from 2011... Is this not supported yet?

Hi, I would like to know if there is any chance to use OUTPUT variables in Rails now, I don't find any doc about it, I just found this issue from 2011... Is this not supported yet?

@metaskills

This comment has been minimized.

Show comment
Hide comment
@metaskills

metaskills Sep 7, 2015

Contributor

Rails/ActiveRecord has no need for these and hence this has not moved. That said, I am interested in hearing how someone thinks TinyTDS could do this. Propose an interface, etc.

Contributor

metaskills commented Sep 7, 2015

Rails/ActiveRecord has no need for these and hence this has not moved. That said, I am interested in hearing how someone thinks TinyTDS could do this. Propose an interface, etc.

@MrLinDowsMac

This comment has been minimized.

Show comment
Hide comment
@MrLinDowsMac

MrLinDowsMac Sep 7, 2015

Mmm... At this point I can use execute_procedure to get results, but I have no way or don't know how to get the output variables.
Sequel collaborators made something like this:
jeremyevans/sequel#748

Mmm... At this point I can use execute_procedure to get results, but I have no way or don't know how to get the output variables.
Sequel collaborators made something like this:
jeremyevans/sequel#748

@MrLinDowsMac

This comment has been minimized.

Show comment
Hide comment
@MrLinDowsMac

MrLinDowsMac Sep 7, 2015

I was planning to move to sequel-rails, but the current problem is that don't provide support for SQL Server :(

I was planning to move to sequel-rails, but the current problem is that don't provide support for SQL Server :(

@metaskills

This comment has been minimized.

Show comment
Hide comment
@metaskills

metaskills Oct 18, 2015

Contributor

From FreeTDS's FAQ site. Just posting this for my own info on when I get to this.

Microsoft SQL Server 7 with SP3, and later versions, quietly changed (which is to say, broke) how they respond to queries that execute stored procedures with output parameters. Earlier servers let you send a query like EXECUTE A @P OUTPUT and fetch the output parameter as a special result row (technique varying by library). Newer servers simply don't send back that data. To elicit output parameters from them, you have to use the RPC functions such as the DB-Library dbrpcparam.

Contributor

metaskills commented Oct 18, 2015

From FreeTDS's FAQ site. Just posting this for my own info on when I get to this.

Microsoft SQL Server 7 with SP3, and later versions, quietly changed (which is to say, broke) how they respond to queries that execute stored procedures with output parameters. Earlier servers let you send a query like EXECUTE A @P OUTPUT and fetch the output parameter as a special result row (technique varying by library). Newer servers simply don't send back that data. To elicit output parameters from them, you have to use the RPC functions such as the DB-Library dbrpcparam.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment