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

Question

The access to read from the db has been given to me via mssql stored procedures that return result sets rather than tables or views. But I want to be able to read the data using ORM.

I tried to use DBIx::Class::ResultSource::View to do the procedure call (e.g. EXEC my_stored_proc ?) as a custom query but this didn't work because it tried to convert the procedure call into a select statement.

Does anyone have another suggestion?

Answer 1

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

As far as I can tell, the closest thing to a workaround is "using the ORM" to get a database handle, which is weak soup:

   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;
    },());

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

...as you get none of the benefits of an ORM for your trouble.