[DbIx::Class] How to execute stored procedure?

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

I have a problem with generation of model for catalyst. It's required to use MSSQL Server stored procedures, but i don't understand how can I do it with DDIx::Class?

The specificity of MSSQL is a call of SP it using STRICT SYNTAX

<EXECUTE sp_name param1, param2>

NOT SUPPORTED SYNTAX

<select * from sp_name>

Have you any suggestions?

---
P.S. I have one decision but it's not so pretty.
There is.

Call SP in Catalyst controller through DBI.

Create Static class for SP result (static table in DB).

Using standard DBIx methods for tables to retrieve SP result into a model.

Comment on [DbIx::Class] How to execute stored procedure? Download Code

Replies are listed 'Best First'.
Re: [DBIx::Class] How to execute stored procedure? by bart (Canon) on May 26, 2007 at 19:34 UTC
I see you haven't gotten a reply in 11 hours. I must say I know very little about DBIx::Class, and my limited experience with MSSQL is more than 5 years ago. Anyway. I do have recent experience with DBI (and DBIx::Simple) and with stored procedures in Oracle. What works for me in DBIx::Simple, using Oracle, is a call like this: `$db->query('BEGIN sp_name(?,?); END;', $param1, $param2);` [download] (In Oracle, to call a procedure from SQL, is to put it in an anonymous block.) So from DBI, I just treat the call to the PL/SQL block as a straightforward SQL call, with support for placeholders for the parameters. You don't have to use DBIx::Simple; in plain DBI you could use the `do` method, in virtually the same way. I think you should do the same, with MSSQL: call the syntax you have to use as a straightforward SQL call, and use placeholders. I'm quite sure DBIx::Class has ways to directly embedding SQL calls in your modules, and that's the route to take.	[reply] [d/l] [select]
Re^2: [DBIx::Class] How to execute stored procedure? by dreel (Sexton) on May 27, 2007 at 12:06 UTC
DBIx::Class it's an ORM. It can make classes with data retrieved from DB, and in App I can use abstract of result set. The problem is how to compose SP call and retrieve data into class.	[reply]
Re: [DbIx::Class] How to execute stored procedure? by moritz (Cardinal) on May 26, 2007 at 21:40 UTC
I just wanted to point out that you don't have to use DBIx::Class together with Catalyst, you can code any model manually if you want to. However it is very convenient ;-) Perl 6 in German	[reply]
Re^2: [DbIx::Class] How to execute stored procedure? by dreel (Sexton) on May 27, 2007 at 12:18 UTC
I just want to use common interface DBIx. And the reason is that I'm not so experienced to code model manually - I don't know how? If can show me an example it will be very useful.	[reply]
Re^3: [DbIx::Class] How to execute stored procedure? by dreel (Sexton) on Jul 09, 2007 at 21:02 UTC
Hey! People! I've found the design of problem! It was totally unreachable for me till this night! Founded in Cookbook! Named as "Arbitrary SQL through a custom ResultSource" Peace of text: Arbitrary SQL through a custom ResultSource Read more... See the code (2 kB) 10x all for advice! Cookbook it's great!	[reply] [d/l] [select]
Re: [DbIx::Class] How to execute stored procedure? by perrin (Chancellor) on May 26, 2007 at 19:57 UTC
There is a DBIx::Class mailing list. Why don't you try asking there?	[reply]
Re^2: [DbIx::Class] How to execute stored procedure? by dreel (Sexton) on May 27, 2007 at 12:09 UTC
I've done it 5 minutes ago. 10x for advice.	[reply]

Arbitrary SQL through a custom ResultSource