How do I build a SQL statement in Class::DBI programmatically

[rlb3]

Hello Monks,

I'm having some trouble building an SQL statement from a
web form. I'm using Class::DBI's set_sql method to create and new method
that changes everytime through a loop.

my $tables = [ qw/ starting_point_topics related_topics other_topics /
+ ];
my $results = {};
foreach my $table (@{$tables}) {

    my $query = qq{ SELECT res.* FROM res,$table,topics WHERE $query_t
+emp ORDER BY `rank` DESC };
    Library::Data::Res->set_sql(full_text => $query);
    $results->{$table} = Library::Data::Res->search_full_text;
}

A full SQL statement would look like this:

 SELECT 
    res.* 
FROM 
    res,starting_point_topics,topics 
WHERE
    ((starting_point_topics.res = res.id)
AND
    (starting_point_topics.topic = topics.id)
AND
    (topics.name LIKE '%'))

The starting_point_topics would change as it loops and WHERE statement will change also but this is the one i'm testing right now. The error i'm getting is:

search_full_text() already exists at /data/web/perl/lib/Library/Result
+s.pm line 95
                                 ORDER BY `rank` DESC SELECT res.* FRO
+M res,related_topics,topics WHERE
                                        ((starting_point_topics.res = 
+res.id)
                                        AND
                                        (starting_point_topics.topic =
+ topics.id)
                                        AND
                                        (topics.name LIKE '%'))
                                 AND
                                        ((related_topics.res = res.id)
                                        AND
                                        (related_topics.topic = topics
+.id)
                                        AND
                                        (topics.name LIKE '%'))

I 'm not sure why the table names in the where statement are not changing.

rlb3

[perrin]

Don't do that. The set_sql() method is for class data that never changes. If you want to build statements on the fly, just grab the $dbh and use prepare_cached(). You can hand the results off to $sth_to_objects. See the Ima::DBI section of the Class::DBI docs for an example.

[saberworks]

Agreed - but in simpler terms: Class::DBI is meant to help you more easily do the easy things. It allows you to use objects to do simple interactions with your database. However, when you need more complex queries, it's better to just write them by hand.

[perrin]

rb3 is already writing them by hand. The problem is the use of set_sql, which is not meant for this.