getting output from stored procedures?

ixemul has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to get values back from stored procedures in an MS SQL DB (using DBD::ODBC and freetds from CVS) and I'm not having any luck at all. I've read the posts here as well as the section of the DBI manual that deals with bind_param and bind_param_inout, and I have to say, I'm pretty confused. I'm not going to post any code, as I've tried a whole bunch of different stuff, but I do know that I am connecting OK and I can run stored procedures without issues. Can anyone give me an explanation or example of how to do this?

Comment on getting output from stored procedures?

Replies are listed 'Best First'.
Re: getting output from stored procedures? by busunsl (Vicar) on Dec 23, 2002 at 09:24 UTC
Output parameters of stored procedures are sent as a special resultset in the tds stream to the client. DBD::Sybase and freetds should be able to manage output parameters. The DBD::Sybase documentation has a good explanation on how to use output parameters of stored procedures.	[reply]
Re: getting output from stored procedures? by paulbort (Hermit) on Dec 23, 2002 at 15:31 UTC
A few things I've run into that might help: 1. To get this working I had to use DBD::Sybase 0.91, not the latest version. I was using FreeTDS 0.53, haven't tried this with 0.60. 2. That configuration does not support bind parameters at all. You have to do the 'bad thing' and build your query string outside the DBD call. Hopefully FreeTDS 0.60 supports the newer version of DBD::Sybase that supports bind parameters. I'm hoping to get a chance to play with it next month. 3. Microsoft SQL Server stored procedures can return multiple result sets from a single call, and support for this is iffy. Try testing with a stored procedure that you know will only return one result set. Good Luck! -- Spring: Forces, Coiled Again!	[reply]
Re: getting output from stored procedures? by mpeppler (Vicar) on Dec 23, 2002 at 16:33 UTC
There was some discussion recently on the freetds mailing list about how to handle OUTPUT parameters from stored procedures when talking to MS-SQL. I don't remember exactly what the problem was, but you may want to check the archives of the freetds mailing list (should be linked from the FreeTDS site) Michael	[reply]