[Dbix-class] [newbie]How to call stored procedure?(doc already read)

[Tony Winslow]

Danny Warren wrote:
> Tony Winslow wrote:
>> Jason Kohles wrote:
>>> On Dec 28, 2007, at 10:23 AM, Tony Winslow wrote:
>>>
>>>> Jason Kohles wrote:
>>>>>
>>>>>> Also from DBIx::Class::Manual::Cookbook:
>>>>>
>>>>>      Using database functions or stored procedures
>>>>>
>>>>>       The combination of "select" and "as" can be used to return 
>>>>> the result
>>>>>       of a database function or stored procedure as a column 
>>>>> value. You use
>>>>>       "select" to specify the source for your column value (e.g. a 
>>>>> column
>>>>>       name, function, or stored procedure name).
>>>>>
>>>> I'm confused of it. The whole paragraph!
>>>> eg: I've a procedure called proc1 which has one output parameter.
>>>> So in the MySQL client env. I have to call it sth. like:
>>>>   CALL proc1(@a);
>>>>   SELECT @a;
>>>>
>>>> And in programs, I think it is sth. more like this:
>>>>   execute('CALL proc1(?)');
>>>>   my $result = get_column(1);
>>>>
>>>> The problem now is I don't know how to do it under the DBIx-Class env.
>>>> My Env: Catalyst/TT, DBIx-Class/MySQL
>>>>
>>>
>>>
>>> Something like this should work...
>>>
>>> package MyDB::Something;
>>> use strict;
>>> use warnings;
>>> use base qw( DBIx::Class );
>>>
>>> __PACKAGE__->load_components(qw( Core ));
>>> __PACKAGE__->table( "fake_table_name_for_proc1" );
>>> __PACKAGE__->result_source_instance->name( \'(select * from 
>>> proc1(?))' );
>>>
>>>
>>> Then you should be able to do:
>>>
>>> my $rs = $schema->resultset( 'Something' )->search( undef, { bind => 
>>> \@a } );
>>>
>>>
>>> I'm not a big fan of MySQL though, so I don't know if it has any 
>>> special requirements for this stuff...
>>>
>> I tried the way you suggested, yet the following exception was 
>> thrown: |"Odd number of arguments to search"
>>
>> I think maybe it is caused by the SQL Statement since procedures 
>> don't have return values, and, in fact, in the procedure I wrote the 
>> parameter passed into it is an output parameter.
>> |
>
> It would be helpful if you could post a code sample.  I am sure 
> someone could spot it quickly that way (more eyes, etc etc).
>
> Check to make sure the first argument to your search call is truly 
> undef, and make sure you are passing your array of bind values as a 
> reference.
>
> This is the error I get when I fat-finger in a search call, and 
> checking for undefs and refs at the offending point is the first thing 
> I try.
>
Here is my codes:
# Revision.pm --- use base qw/DBIx::Class/;
.......
my $source = __PACKAGE__->result_source_instance();
my $new_source = $source->new($source);
$new_source->source_name('RevComp');

$new_source->name(\<<SQL);
(select * from countRevs(?))
SQL

MyDB->register_source('RevComp' => $new_source);

# Document.pm -- Controller
......
sub test : Local {
    my ($self, $c) = @_;
    my @cnt;
#  my $cnt;
    $c->model('MyDB::RevComp')->search(
        undef,
        bind => \@cnt
#      bind => \$cnt
    );
    .......
}

# the procedure defs in MySQL
DROP PROCEDURE IF EXISTS countRevs;
DELIMITER //
CREATE PROCEDURE countRevs (OUT rev_cnt INT)
    BEGIN
        SELECT COUNT(*) INTO rev_cnt FROM revisions;
    END;
//
DELIMITER ;

I'm just trying to find out how to use procedures, so I wrote it that way.