Directly execute SQL

From ClassDBI

oubodom Sometimes Class::DBI may not support a function you need (e.g., joins) or simply is not the right tool for the job (e.g., aggregate queries). In these cases, you may find that it is best to fall back into using DBI directly like you used to do before you found CDBI.

As with most things Perl, there's more than one way to do it. This section documents how to do SQL directly from CDBI. See other Recipes for how to extend CDBI to fit your needs.

The following examples are based on the database described in entity relationship diagram.

You can obtain the database handle that CDBI is using via the following:

 my $dbh = Music::MusicDB->db_Main();

An example of where you may need to use custom SQL is performing joins that have WHERE clauses which may change from search to search. The following code could be used to search for cd titles or songs (using SQL::Abstract's where function to create the WHERE query):

 # Arguments: hashref of arrays for search critera (see SQL::Abstract for details)
 sub search_cd_or_song {
   my $class = shift;
   my $sql = SQL::Abstract->new();
   my ($where_statement, @bind) = $sql->where(@_);
   my $sql = 'SELECT * FROM orders JOIN order_items ON orders.id = order_items.order_id';
   $sql .= ' ' . $where_statement;
   my $dbh = $class->db_Main();
   my $sth = $dbh->prepare_cached($sql);
   $sth->execute(@bind);
   my @results = $class->sth_to_objects($sth);
 }

Another reason to have access to the database handle would be for creating or deleting (dropping) tables. For example, to create a table:

 my $sql = qq~CREATE TABLE mytable (
   id varchar(16) primary key,
   fullname varchar(32),
   score int,
 )~;
 $dbh->do($sql);

And to drop the table:

 $dbh->do("DROP TABLE mytable");

-- Trwww

Another example ... I have two sets of (somewhat unrelated) Oracle Class::DBI objects that don't have foreign keys, but have a field that can sometimes be joined on, as long as the join is case-insensitive. These keys are not in the PK. So, what I wanted is something very much like what has_many() + map method provide, but it took me a while to figure it out. This is very similar to the example above, but pulls in an attribute of $self as an argument to the query.

 CREATE TABLE user (
     user_id NUMBER(8),         -- PK
     username VARCHAR(32) NOT NULL UNIQUE
 );
 CREATE TABLE app (
     app_id NUMBER(8) NOT NULL, -- PK
     app_name VARCHAR(500),
     app_class VARCHAR(32),
     app_host_id NUMBER(8)      -- irrelevant for this example
 );
 CREATE TABLE app_user_history (
     app_id NUMBER(8),          -- PK, FK to app.app_id
     user_id VARCHAR(32),       -- PK, no FK and wildly variant data kwality (with a K)
     end_date DATE,             -- PK
     begin_date DATE
 );

 package My::User;
 ...
 sub user_applications {
     my $self = shift;
     my $query = <<EOSQL;
         SELECT app_id                      -- id in the "application" table
         FROM SCHEMA_OWNER.APP_USER_HISTORY -- this is the pseudo-many-to-many table
         WHERE UPPER(USER_ID)=UPPER(?)      -- user_id in application happens to be a 
                                            -- VARCHAR just like $self->username
 EOSQL
     my $sth = $self->db_Main->prepare_cached( $query );
     # create Application objects to return
     My::App->sth_to_objects( $sth, [$self->username] );
 }

The moral: crack open Class/DBI.pm and take a look at the sth_to_objects() method. It comes in handy when some of the other methods of running custom SQL fall short of what you want to express. (should this be in the Class::DBI pod or is it too internal and therefore naughty to use?)

-- Tobeya

Once you use set_sql or the above method enough times, a good idea is to centralize much of the implementation code in your base class.

 # Add to CDBI base class
 package MyApp::DBI;
 use SQL::Abstract;
 ...
 sub set_search_where_sql {
    my $proto = shift;
    my $class = ref $proto || $proto;
    my ( $name, $sql ) = @_;
    my $method = "search_where_" . $name;
    $class->set_sql( $name, $sql );
    my $sql_method = "sql_" . $name;
    no strict 'refs';
    *{"$class\::$method"} = sub {
        my $proto = shift;
        my $class = ref $proto || $proto;
        my $where = ( ref $_[0] ) ? $_[0] : {@_};
        my $attr  = ( ref $_[0] ) ? $_[1] : undef;
        my $order = ($attr) ? delete( $attr->{order_by} ) : undef;
        my $sql   = SQL::Abstract->new(%$attr);
        my ( $phrase, @bind ) = $sql->where( $where, $order );
        $phrase =~ s/^\s*WHERE\s*/AND /i;
        my $sth = $class->$sql_method($phrase);
        $class->sth_to_objects( $sth, \@bind );
    };
 }

 # Code in individual CDBI sub-classes is considerably simpler now
 package MyApp::Order;
 use base 'MyApp::DBI';
 ...
 __PACKAGE__->set_search_where_sql( 'cd_or_song' => qq~
   SELECT DISTINCT orders.* FROM orders, order_items
   WHERE orders.id = order_items.order_id
   %s
 ~);

 package main;
 ...
 MyApp::Order->search_where_cd_or_song( { 'order_items.sku' => { '!=' => '1234'  } } );

-- Juan Camacho