Forums | Admin

Discussion Forums: open-discussion

Start New Thread Start New Thread

 

By: Michael Mazour
RE: SQL Stored Procedure Calls [ reply ]  
2006-07-06 15:21
After a day of exercising Ruby with ODBC, I concur that it seems really to be the way to go. ODBC mode works painlessly with stored procedures without requiring any of the hacky workarounds I needed with ADO. I have not benchmarked ODBC vs. ADO for SELECT/INSERT/UPDATE/DELETE, but subjectively the two seem roughly on par with each other -- at least enough for me to stick to the ODBC route without second thoughts.

Thanks again for your helpful posts, Carl.

By: Michael Mazour
RE: SQL Stored Procedure Calls [ reply ]  
2006-07-03 15:47
Thanks, Carl, really interesting tip about moving to ODBC.

FWIW I have been continuing down the ADO path over the past few days, here's what I've learned. (A near-duplicate of this is posted over at http://www.softiesonrails.com.)

There turn out to be a couple different issues involved in using SQL2K stored procedures from Ruby and obtaining result sets. Fortunately both are solveable or at least hackable.

1. Under the ADO adaptor, EXEC is not expected to return a result set.

2. Stored procedures that use PRINT seem not to be ADO-friendly.

Let's start with issue #1: the execute method of ADO.rb calls the commit method unless either AutoCommit is off or SQL.query?(@statement) returns true (which it does only for SELECT statements). I haven't traced the code completely, but it appears that the commit call has the side effect of invalidating any result set that might have been associated with the statement.

There are two straightforward workarounds: either turn off AutoCommit, or patch the SQL.query? method so that EXEC calls as well as SELECT statements return true. I've found both to work, but prefer the latter as I would rather not tamper with the default commit behavior that AR presumably expects.

To patch SQL.query?, open sql.rb (in lib/ruby/site_ruby/1.8/dbi on my system) and change the line

sql =~ /^\s*select\b/i

to

sql =~ /^\s*(select|exec)\b/i

This will cause EXECs to be treated the same as SELECTs in leaving the result set open and uncommitted. After this change, a call to a simple stored procedure that produces a result set and returns works just as if it had been a normal select.

(Note, by the way, that I patched SQL.query? to treat EXEC the same as SELECT, but not EXECUTE. This is a kluge on my part. I have in mind that in cases where I want SELECT-style behavior with a result set and no autocommit I'll use EXEC, and where I want INSERT/UPDATE/DELETE-style behavior with autocommit and no result set, I'll use EXECUTE. Your thinking on this topic may vary.)

Issue #2 is just plain frustrating: using PRINT in a stored procedure seems to 'spoil' ADO's ability to return a result set from it, something you can't see in Query Analyzer but which you can easily test not just from Ruby but with VB or ASP. I have some complex stored procedures which routinely use PRINT to log their actions for diagnostic purposes when running from a scripted batch from OSQL.EXE or SQLCMD.EXE, but it appears I'll have to forego this functionality when calling these procedures from Ruby (which I suppose is OK; they're old enough to be pretty thoroughly debugged at this point).

What I did in this case was to add an @printing TINYINT=1 parameter to these stored procedures, and wrap each PRINT in an IF @printing > 0 statement. Since @printing has a default value of 1, the legacy scripts continue to get their accustomed behavior, but when I call the procedures from Ruby I pass @printing=0 to suppress printing, and the procs run happily and return an ADO result set.

It's possible that one or both of these issues might have been circumvented by using the ODBC adaptor rather than the ADO adaptor. I may explore that in the coming days.

P.S. one final note: above I've talked only about using stored procedures that return a result set. What about stored procedures that return values in OUTPUT parameters of their argument lists? I decided not to tackle that one directly, but instead create wrapper procedures that would return the output parameters as if they were a result set. That is, the wrapper procedure calls the original stored procedure and receives those results in a set of variables; it then performs a SELECT @retval1 AS retval1, @retval2 AS retval2 (etc.) before returning.

By: Carl Graff
RE: SQL Stored Procedure Calls [ reply ]  
2006-07-02 20:56
I have resolved my error with stored procedures and learned acouple of things along the way:

1. The ODBC driver is much more reliable for SQL200 - not sure about 2005 - but it does require you set up ODBC DSN. This is usually farly easy to do unless you have to do it on a bunch of machines.

2. If your stored procedure calls other stored procedures you must make sure to "fetch" any output that the nested stored procedures may be producing otherwise the DBI call to the parent stored procedure will fail silently.

I think I will be creating a class or method that will parse and process SQL statement and stored proc calls and post it to DBI area on the internet. It really is best to use parameritized calls so it will pick apart a string such as "exec sp_1 'Y', 'N'" and correctly execute the proper DBI statements using parameters.

So basically the solution curretnly looks like this:

@parm1 = "sp_1 'Y', 'N'"
# Connect to database
dbcon = DBI.connect('DBI:ODBC:YourDSN',@dbuser,@dbpswd)
execStr = 'exec ' + @parm1
sth = dbcon.execute(execStr)
while row = sth.fetch do
temp = row[0] # just throw away
end
dbcon.commit
dbcon.disconnect if dbcon

But I will, as I said, be using paramters in the future to guard against SQL Injection.

Best,
Carl

By: Carl Graff
SQL Stored Procedure Calls [ reply ]  
2006-06-29 22:49
Hi,

I have tried calling T-SQL stored procedures using dbi with mixed results. In fact in SQL7 I had it working bt the same code didn't work in SQL2000.

This seemed to work for a while (note the sleep3)
# Connect to database
con_str = "DBI:ADO:Provider=SQLOLEDB;Data Source = #{@dbserv};"
con_str += "Initial Catalog = #{@dbdbase};User Id = #{@dbuser};"
con_str += "Password = #{@dbpswd};"
dbcon = DBI.connect(con_str)
execStr = 'exec ' + @parm1
dbcon.do(execStr)
sleep 3
dbcon.commit
dbcon.disconnect if dbcon

but alas it is not reliable and will not work at all on 2000.

I need to call some fairly sophisticated SP's so I wonder if anyone can suggest a better way - perhpas I should use a .net wrapper by requiring the .net library.

Here is the complexity of one such SP:
CREATE PROC osp_p5_analysis_main
@order_skey int=0,
@order_type char(2)=NULL,
@mr_status char(1)=NULL,
@rc int=0 output
<<a few hundred lines of code wioth execs to other stored procedures>>

It is legacy code and it would take a very long time to rewrite so Ihope someone can help.

Thanks,
Carl