5

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?

stevenl
  • 6,736
  • 26
  • 33

2 Answers2

6

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.

szabgab
  • 6,202
  • 11
  • 50
  • 64
djsadinoff
  • 5,519
  • 6
  • 33
  • 40
  • The `DBIx::Class::Manual::Cookbook` docs section 'Using database functions or stored procedures', though @stevenl has pointed out that it won't help with MS SQL Server, as it apparently cannot access a stored procedure via a SELECT statement. – Lee Goddard May 22 '15 at 10:36
  • 1
    nor Mysql, and my guess is not Oracle either. I wonder whether the DBIx:Class manual author is just making it up. – djsadinoff May 25 '15 at 13:39
  • Indeed - and what would be the expected behaviour? How would DBIC know with which ResultSet to associate the data returned by the stored procedure or function? I think the author meant 'function' as in SQL-function, as 'length' is the example. That doesn't explain how 'stored procedure' got added. – Lee Goddard May 27 '15 at 10:31
-2

You can use 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 );

To call with parameters do the following

my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {
+}, 
    {
      bind  => [ 12345, 12345 ]
    }
  ) ];
rouzier
  • 1,160
  • 8
  • 19
  • This solution looks very similar to what I did using ResultSource::View. The problem with this approach is the part where it adds my sproc call as a sub-select. The generated query will look like this: `SELECT me.x FROM (EXEC my_stored_proc ?) me` which causes a syntax error and the statement can't be prepared. I'm guessing this problem would not be specific to mssql since I don't see other platforms having a compatible syntax either. – stevenl Sep 23 '11 at 01:55
  • 1
    This doesn't answer the question. There is no stored procedure here. – djsadinoff May 08 '12 at 17:25