[Catalyst] [newbie]How to call stored procedure?

[Gene Selkov]

On Fri, 28 Dec 2007, Jason Kohles wrote:

> On Dec 28, 2007, at 8:19 AM, Tony Winslow wrote:

>> I've stored procedures defined in my database schema, and I need to call 
>> them in my code.
>> The arbitrary-sql approach won't help since it writes sql statements in 
>> source code to act as stored procedures. So could I do that in DBIx?

> You were probably looking in the Catalyst docs rather than the DBIx::Class 
> (I'm assuming because you asked the Catalyst mailing list, rather than the 
> DBIx::Class mailing list, which would have been more appropriate.  Had you 
> checked the DBIx::Class documentation, you probably would have found this in 
> the cookbook...

The DBIx::Class documentation wasn't very helpful to me when I was looking 
for the same solution. I suspect that most new users, such as myself, 
would want their model schemas generated for them, and so will fall for 
the DBIC::Schema approach as I did.

If, having done that, you try to follow the recipes in the DBIx cookbook 
without complete understanding of what they intend to do, you will be 
breaking the invisible under-the-hood stuff created by the schema 
generator. I think it will be well for the Catalyst docs to mention that, 
perhaps in the form of a recipe -- something that you can follow right 
through.

To answer the original question: you can do everything you want with 
arbitrary SQL in Catalyst: stored procedures, views, or whatever -- and 
that's not a bad way of doing things, as long as you are aware of the 
portability issues introduced by SQL (if that matters to you at all).

Please see my posting at perlmonks explaining this:

   http://www.perlmonks.org/?node_id=658193

To elaborate a little bit on what I've written there, I think you will 
walk away with a complete understanding of what's going on with "arbitrary 
SQL" if you consider that most SQL dialects allow you to put any query (in 
parentheses) wherever a table name is allowed. That's why all the 
arbitrary SQL recipes for DBIx::Class will advise you about the 
parentheses (without mentioning why, because it's "obvious"), and they 
will show you how to stash your SQL query into the "name" attribute, 
perhaps leaving you wondering why something you think of as a "code" is 
strangely referred to as "name". That's because it's hack -- the one 
exploiting SQL's recursiveness.

It will work if your backend supports subselects and will fail otherwise.

--Gene