Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

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

by rlb3 (Deacon)
on Dec 08, 2004 at 22:15 UTC ( #413348=perlquestion: print w/replies, xml ) Need Help??
rlb3 has asked for the wisdom of the Perl Monks concerning the following question:

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 = AND (starting_point_topics.topic = AND ( 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 line 95 ORDER BY `rank` DESC SELECT res.* FRO +M res,related_topics,topics WHERE ((starting_point_topics.res = AND (starting_point_topics.topic = + AND ( LIKE '%')) AND ((related_topics.res = AND (related_topics.topic = topics AND ( LIKE '%'))

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


Replies are listed 'Best First'.
Re: How do I build a SQL statements in Class::DBI programmatically
by perrin (Chancellor) on Dec 08, 2004 at 22:31 UTC
    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.
      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.
        rb3 is already writing them by hand. The problem is the use of set_sql, which is not meant for this.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://413348]
Approved by Arunbear
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (6)
As of 2017-03-24 09:23 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (298 votes). Check out past polls.