[Dbix-class] Call mysql stored procedure using Arbitrary SQL through a custom ResultSource

[Sung Gong]

Hi,

Have you found the proper answer yet?
Googled but with no satisfactory answers to me except an advise of
redefining my stored procedure as suggested below:
http://search.cpan.org/~arodland/DBIx-Class-0.08196/lib/DBIx/Class/Manual/Cookbook.pod#Retrieve_one_and_only_one_row_from_a_resultset
However, still wondering it's possible to call the sp rather than redefining it.
Also found this:
http://lists.scsys.co.uk/pipermail/dbix-class/2007-December/005459.html
but it looks a bit awkward.

Cheers,
Sung

On 19 October 2009 11:42, Arvind Singh <arvindsgulati at gmail.com> wrote:
> I created a stored procedure in mysql using
>
> CREATE PROCEDURE productpricing(in iskucode varchar(20))
> BEGIN
> SET
>     @qty := 0
> ;
> SELECT year_week, skucode, sold_this_week,
>     @qty := sold_this_week +
>     @qty as commulative
>     FROM (    SELECT date_format(str_to_date(DATE, '%b %d %Y'),
> '%Y-%v') AS year_week, skucode, SUM(QtySold) AS sold_this_week
>             FROM sales
>             WHERE skucode=iskucode
>             GROUP BY year_week
>             ORDER BY year_week ASC
>         )
>     AS x
> ;
>
> END
> go
>
>
> I want to access the result of the same in resultset and followed the
> instructions
>
> Sometimes you have to run arbitrary SQL because your query is too
> complex (e.g. it contains Unions, Sub-Selects, Stored Procedures,
> etc.) or has to be optimized for your database in a special way, but
> you still want to get the results as a DBIx::Class::ResultSet.
> This is accomplished by defining a ResultSource::View for your query,
> almost like you would define a regular ResultSource.
>
>  at
>
> http://search.cpan.org/~ribasushi/DBIx-Class-0.08111/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary_SQL_through_a_custom_ResultSource
>
>
>
> with only major change as
>
> __PACKAGE__->result_source_instance->view_definition(
>     q[call productpricing(?)]
> );
>
>
> Now when i try to access this class using
>
>  $schema->resultset( 'UserFriendsComplex' )->search( {},
>
> ->search( {},
>     {
>       bind  => [ $skucode ]
>     }
>   );
>
> I am getting a mysql error as dbix tries to call
>
> "SELECT * FROM (call productpricing(?)) me"
>
> Can anybody please guide me to proper way to call a mysql stored
> procedure using dbix.
>
>
> --
> Arvind Singh
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk