[Dbix-class] how to execute stored procedures in DBIx::Class

[Jess Robinson]

On Tue, 26 Dec 2006, Wan wrote:

> In MySQL 5.0.x, I have created a procedure named boardslist, like this
>
> CREATE PROCEDURE boardslist (IN uId INT)
> BEGIN
> SELECT boards.*, hiddenboards.userId IS NOT NULL AS hidden
> FROM boards LEFT JOIN hiddenboards
> ON hiddenboards.userId = uId
> AND hiddenboards.boardId = boards.id
> WHERE boards.active = 1
> ORDER BY pos;
> END
>
> And I CALL the procedure use
>
> mysql>CALL boardslist (1);
>
> But in DBIx::Class, how can I execute this procedure?
>
> I have read the documents of DBIx::Class::Manual::Cookbook
> Using database functions or stored procedures
> I don't think this is the true method to solve my problem!
>

Since all it is doing, is returning a select (it semms), this would be 
much more sane as a VIEW, which you could then pretend o DBIC was a table, 
and it's easy.

As for SPs, we don't support one's that reutr multiple valus/rows yet.
You could set the resultsource to a select that retrieves from such an SP:

__PACKAGE__->table('dummy')
__PACKAGE__->result_source_instance->name(\'(select * from boardslist)');

But again, it's major overkill, the query is do-able as is.

Jess