[Dbix-class] Call mysql stored procedure using Arbitrary SQL through a custom ResultSource
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
Re: [Dbix-class] create failures with has_one
On 15/10/2009, at 21:08, Dermot wrote:
2009/10/15 Wallace Reis wall...@reis.org.br:
On 15/10/2009, at 08:34, Dermot wrote:
2009/10/15 Wallace Reis wall...@reis.org.br:
On 14/10/2009, at 13:10, Dermot wrote:
my $record = {
'validation_pass' = 1,
'files' = [],
'active' = 1,
'name' = 'Gustav MR',
'clipdata' = [
{
'contrib_id' = 2,
}
],
'contrib_id' = 2,
};
Use a HashRef for clipdata rel instead of a ArrayRef.
Here, you should note that currently find (and update) part of
*_or_create methods will fail for nested relations, so you need to
call find(update)_or_create for relationships separately.
I would really, really love to do a doc patch if I knew how. Mostly
because that's all I feel I have the ability to do. However I have no
experience of how to create patches. I guess it's a similar to `svn
diff ...`. Is there something on the Advent Calendar's or similar
that might show me?
Not sure, but a svn diff output or a svk ci -P patch is fine.
--
wallace reis/wreis Catalyst and DBIx::Class consultancy
with a clue
Software Engineer and a commit bit: http://shadowcat.co.uk/catalyst/
Shadowcat Systems Limited
http://www.shadowcat.co.uk http://www.linkedin.com/in/wallacereis
___
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
Re: [Dbix-class] Call mysql stored procedure using Arbitrary SQL through a custom ResultSource
Hi all on the Dbic list. The next technical meeting of North West England Perl Mongers will be held at the MEN offices in Manchester on the 28th October. We will be gifted with a talk by Jess Robinson on Docs and then Jess, Matt Trout and Kieren (three of the authors of the latest Catalyst book) will be answering questions live on stage. Kieren is in Australia so will be joining us via conference link-ups and we hope to stream the whole event to the internet/or record and put the video up afterwards. If you can attend via real person/web link that would be great, if not please email questions to us (in fact this is the wisest course of action) and we will ask the authors on the night. Questions can be sent to: m(dot)keating(at)shadowcat.co.uk Hope to see you there, more information can be found here: http://northwestengland.pm.org/meetings/010.html Regards Mark Mark Keating BA (Hons) | Writer, Photographer, Cat-Herder Managing Director | Shadowcat Systems Limited Director/Secretary | Enlightened Perl Organisation co-Leader | North West England Perl Mongers http://www.shadowcat.co.uk | http://www.enlightenedperl.org http://northwestengland.pm.org | http://linkedin.com/in/markkeating ___ 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
Re: [Dbix-class] create failures with has_one
2009/10/19 Wallace Reis wall...@reis.org.br:
On 15/10/2009, at 21:08, Dermot wrote:
my $record = {
'validation_pass' = 1,
'files' = [],
'active' = 1,
'name' = 'Gustav MR',
'clipdata' = [
{
'contrib_id' = 2,
}
],
'contrib_id' = 2,
};
Use a HashRef for clipdata rel instead of a ArrayRef.
Here, you should note that currently find (and update) part of *_or_create
methods will fail for nested relations, so you need to call
find(update)_or_create for relationships separately.
That might explain why, even under 0.08112, I get
DBIx::Class::ResultSet::find_or_create(): new_result needs a hash...
when I use a nested structure. Are there any methods I can use that
will allow me to create new, nested rows?
I would really, really love to do a doc patch if I knew how. Mostly
because that's all I feel I have the ability to do. However I have no
experience of how to create patches. I guess it's a similar to `svn
diff ...`. Is there something on the Advent Calendar's or similar
that might show me?
Not sure, but a svn diff output or a svk ci -P patch is fine.
Thanx for this tip. I'd better get my facts right before I submit.
Dp.
___
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
[Dbix-class] Question the Book Authors
The following message should have been sent with the above subject line, but, being a moron I replied to a message and didn't change the subject. My apologies. --- Hi all on the Dbic list. The next technical meeting of North West England Perl Mongers will be held at the MEN offices in Manchester on the 28th October. We will be gifted with a talk by Jess Robinson on Docs and then Jess, Matt Trout and Kieren (three of the authors of the latest Catalyst book) will be answering questions live on stage. Kieren is in Australia so will be joining us via conference link-ups and we hope to stream the whole event to the internet/or record and put the video up afterwards. If you can attend via real person/web link that would be great, if not please email questions to us (in fact this is the wisest course of action) and we will ask the authors on the night. Questions can be sent to: m(dot)keating(at)shadowcat.co.uk Hope to see you there, more information can be found here: http://northwestengland.pm.org/meetings/010.html Regards Mark Mark Keating BA (Hons) | Writer, Photographer, Cat-Herder Managing Director | Shadowcat Systems Limited Director/Secretary | Enlightened Perl Organisation co-Leader | North West England Perl Mongers http://www.shadowcat.co.uk | http://www.enlightenedperl.org http://northwestengland.pm.org | http://linkedin.com/in/markkeating On 19 Oct 2009, at 15:24, Mark Keating wrote: ___ 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
Re: [Dbix-class] create failures with has_one
On 19/10/2009, at 11:59, Dermot wrote:
2009/10/19 Wallace Reis wall...@reis.org.br:
On 15/10/2009, at 21:08, Dermot wrote:
my $record = {
'validation_pass' = 1,
'files' = [],
'active' = 1,
'name' = 'Gustav MR',
'clipdata' = [
{
'contrib_id' = 2,
}
],
'contrib_id' = 2,
};
Use a HashRef for clipdata rel instead of a ArrayRef.
That might explain why, even under 0.08112, I get
DBIx::Class::ResultSet::find_or_create(): new_result needs a hash...
when I use a nested structure. Are there any methods I can use that
will allow me to create new, nested rows?
You can encapsulate it in a resultset method like:
sub import_record {
my ( $self, $record ) = @_;
my ( $files, $clipdata ) = map { delete $record-{$_} } qw/files
clipdata/;
my $schema = $self-result_source-schema;
my $imp_rec;
my $import_txn = sub {
my $imported_record = $self-update_or_create($record);
foreach my $file (@$files) {
$imported_record-update_or_create_related('files', $file,
{ key = 'files_filename' }
);
}
$imported_record-update_or_create_related('clipdata',
$clipdata);
return $imported_record;
};
eval { $imp_rec = $schema-txn_do($import_txn) };
if ($@) { # Transaction failed
die something terrible has happened! #
if ($@ =~ /Rollback failed/); # Rollback failed
# deal_with_failed_transaction();
}
return $imp_rec;
}
then call $schema-resultset('Submissions')-import_record($record).
--
wallace reis/wreis Catalyst and DBIx::Class consultancy
with a clue
Software Engineer and a commit bit: http://shadowcat.co.uk/catalyst/
Shadowcat Systems Limited
http://www.shadowcat.co.uk http://www.linkedin.com/in/wallacereis
___
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
Re: [Dbix-class] Question the Book Authors
Good afternoon, On 19/10/09 at 4:18 PM +0100, Mark Keating m.keat...@shadowcat.co.uk wrote: The following message should have been sent with the above subject line, but, being a moron I replied to a message and didn't change the subject. My apologies. But that doesn't create a new thread; it simply renames an exiting thread (thread hijacking). The correct procedure is to create a new message. Charlie -- Charlie Garrison garri...@zeta.org.au PO Box 141, Windsor, NSW 2756, Australia O ascii ribbon campaign - stop html mail - www.asciiribbon.org http://www.ietf.org/rfc/rfc1855.txt ___ 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
