-2

I need to run plain SQL with DBIx::Class:

select proc_name();

Is there a way to do this with DBIx::Class?

UPD
I know I can do different connection with DBI and then:

$dbh->do( 'select proc_name();' )

But I need to run it within same transaction

UPD
FAQ does not cover that

UPD
For downvoters: I know DBIx::Class is not for plain SQL. But from one side sometimes query is too complex to rewrite it as DBIx::Class understand, from the other side we need functionality of DBIx::Class

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158

2 Answers2

4

Thanks to mst on #dbix-class IRC channel for help.

I should use dbh_do

  my @stuff = $schema->storage->dbh_do(
    sub {
      my ($storage, $dbh, @cols) = @_;
      my $cols = join(q{, }, @cols);
      $dbh->selectrow_array("SELECT $cols FROM foo");
    },
    @column_list
  );

UPD

DBIx::Class::Report module suggest another way to run complex queries and get DBIx::Class::Result objects for rows as result

UPD

Possible DBIx::Raw will be interesting too

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
1

Another way to run plain sql and get DBIx::Class functionality is to use ::View

package MyApp::Schema::Result::Year2000CDs;

use base qw/DBIx::Class::Core/;

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');

__PACKAGE__->table('year2000cds');
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition(
    # PUT HERE YOUR QUERY
    "SELECT cdid, artist, title FROM cd WHERE year ='2000'"
);
__PACKAGE__->add_columns(
  'cdid' => {
    data_type => 'integer',
    is_auto_increment => 1,
  },
  'artist' => {
    data_type => 'integer',
  },
  'title' => {
    data_type => 'varchar',
    size      => 100,
  },
);

Here is blog post by Ovid

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158