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 <[email protected]> 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/[email protected] _______________________________________________ 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/[email protected]
