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

[Arvind Singh]

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