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

[Jason Kohles]

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...

-- 
Jason Kohles, RHCA RHCDS RHCE
email at jasonkohles.com - http://www.jasonkohles.com/
"A witty saying proves nothing."  -- Voltaire