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
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
