Can DBIx :: Class be used with stored procedures instead of tables?

Question

Reading access from db was granted to me through mssql stored procedures that return sets of results, not tables or views. But I want to read data using ORM.

I tried to use DBIx::Class::ResultSource::Viewthe procedure call (for example EXEC my_stored_proc ?) as a custom request, but this did not work because it tried to convert the procedure call to a select statement.

Does anyone have any other suggestion?

Answer 1

No, there is no sensible way to execute a stored procedure in the context of DBIx :: Class.

, " ORM" , :

   my @results = $schema->storage->dbh_do(sub{
         my ($storage, $dbh, @args) = @_;
         my $sth = $dbh->prepare('call storedProcNameFooBar()');
         my @data;
         $sth->execute();
         while( my $row = $sth->fetchrow_hashref){
             push @data, $row;
         }
         return @data;
    },());

[ . http://metacpan.org/pod/DBIx::Class::Storage::DBI#dbh_do]

... ORM .

Answer 2

register_source

 package My::Schema::User;

  use base qw/DBIx::Class/;

  # ->load_components, ->table, ->add_columns, etc.

  # Make a new ResultSource based on the User class
  my $source = __PACKAGE__->result_source_instance();
  my $new_source = $source->new( $source );
  $new_source->source_name( 'UserFriendsComplex' );

  # Hand in your query as a scalar reference
  # It will be added as a sub-select after FROM,
  # so pay attention to the surrounding brackets!
  $new_source->name( \<<SQL );
  ( SELECT u.* FROM user u 
  INNER JOIN user_friends f ON u.id = f.user_id 
  WHERE f.friend_user_id = ?
  UNION 
  SELECT u.* FROM user u 
  INNER JOIN user_friends f ON u.id = f.friend_user_id 
  WHERE f.user_id = ? )
  SQL 

  # Finally, register your new ResultSource with your Schema
  My::Schema->register_source( 'UserFriendsComplex' => $new_source );

my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {
+}, 
    {
      bind  => [ 12345, 12345 ]
    }
  ) ];